Totals is not recovering correctly

Aspose.Cells Product Family
1

Hi,

We are using table with multiple Totals
One of them as Total Row. The others are user created and they are part of the table.

We use the mechanism table.ShowTotals = false; and after manipulating the data (including adding/deleting/changing rows) we do table.ShowTotals = true;

Formula in input file: =IF(Table2[[#Totals],[Column7]]=0,0,SUBTOTAL(103,[Column5]))
Formula in output file: =SUBTOTAL(109,[Column5])
There are two problems here:

  1. The formula became different
  2. The function num from 109 turned into 103
var input = "C:\input_totals.xlsx";
var output = "C:\output_totals.xlsx";

var workbook = new Workbook(input);
var table = workbook.Worksheets[0].ListObjects.First();

table.ShowTotals = false;
// DO SOMETHING
table.ShowTotals = true;

workbook.Save(output);

UPDATE:
I noticed that if I make the formula =SUBTOTAL(103,[Column5]) in custom Total, it will remain as =SUBTOTAL(103,[Column5]) maybe this will help you.

input_totals.xlsx.zip (10.4 KB)
output_totals.xlsx.zip (8.0 KB)

.NET: 9.0
Aspose.Cells: 25.5.0

2

@aleksandr.l

Cause The issue you’re experiencing with the formula changing from =IF(Table2[[#Totals],[Column7]]=0,0,SUBTOTAL(103,[Column5])) to =SUBTOTAL(109,[Column5]) when toggling the totals visibility is likely due to how Aspose.Cells handles the recalculation of formulas in tables. Specifically, the behavior of the ShowTotals property may not preserve the original formula structure when totals are re-enabled. Solution To maintain the original formula structure, you can manually set the formula back to its intended state after toggling the totals. Here’s how you can do it: 1. Set ShowTotals to false.
2. Perform your data manipulation.
3. Set ShowTotals to true.
4. Reassign the original formula to the total row. Code Here’s a modified version of your code that includes reassigning the formula: ```csharp
var input = @“C:\input_totals.xlsx”;
var output = @“C:\output_totals.xlsx”; var workbook = new Workbook(input);
var table = workbook.Worksheets[0].ListObjects.First(); // Store the original formula
string originalFormula = “=IF(Table2[[#Totals],[Column7]]=0,0,SUBTOTAL(103,[Column5]))”; table.ShowTotals = false;
// Perform your data manipulation here
// For example: Add/Delete/Change rows
table.ShowTotals = true; // Reassign the original formula to the total row
table.ListColumns[0].TotalsFormula = originalFormula; // Adjust the index as needed workbook.Save(output);

2. If the issue persists, check for updates to the Aspose.Cells library, as newer versions may have improvements or fixes related to formula handling.
3. If you encounter further issues, please provide additional details about your data manipulation steps for more tailored assistance. Let me know if that helps!
3

@aleksandr.l,

Thanks for the sample files and details.

Please add a line (in bold) to your code segment. I tested by adding the line and it works fine and as expected:

var input = “C:\input_totals.xlsx”;
var output = “C:\output_totals.xlsx”;

var workbook = new Workbook(input);
var table = workbook.Worksheets[0].ListObjects.First();

table.ShowTotals = false;
// DO SOMETHING
table.ShowTotals = true;

workbook.CalculateFormula();

workbook.Save(output);

Let us know if you still find the issue.

4

Hi,

Thank you
But the behaviour remained the same.
Cell F6 before: =IF(Table2[[#Totals],[Column7]]=0,0,SUBTOTAL(103,[Column5]))
Cell F6 after: =SUBTOTAL(109,[Column5])

var input = @"C:\input_totals.xlsx";
var output = @"C:\output_totals.xlsx";

var workbook = new Workbook(input);
var table = workbook.Worksheets[0].ListObjects.First();

table.ShowTotals = false;
// DO SOMETHING
table.ShowTotals = true;
workbook.CalculateFormula();

workbook.Save(output);

input_totals.zip (10.3 KB)

5

@aleksandr.l
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-58611

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

1 Like
6

@aleksandr.l,

We are pleased to inform you that your issue (Ticket ID: “CELLSNET-58611”) has been resolved now. The fix/enhancement will be included in the upcoming release (Aspose.Cells v25.7) that we plan to release in the first half of July 2025. You will be notified when the next version is published.

7

The issues you have found earlier (filed as CELLSNET-58611) have been fixed in this update. This message was posted using Bugs notification tool by leoluo