TotalsCalculation not working

Aspose.Cells Product Family
1

Hi Team,


We are implementing the listobject in one of our projects. We are facing below issues. Can you please help us out.

1. ShowTotals adding an extra row at the end before the totals row.
2. TotalsCalculation is not working as expected.

For the issue #1, I saw a post where it is suggested to delete the row which is working fine. But, the totals row is not being populated with the results.

though, when I open the generated excel and see, I do see ‘sum’ selected as we assigned using the code. But, the sum of the columns is not populated. When I again select ‘sum’ manually, I get the count.

Can you please let us know what can be wrong here. Below is the piece of code that we are using.

ListObjectCollection tableStructure = wSheet.ListObjects;

ListObject table = tableStructure[0];

table.ShowTotals = true;

table.ShowTableStyleRowStripes = true;

wSheet.Cells.DeleteRow(table.EndRow - 1);

table.ListColumns[6].TotalsCalculation = Aspose.Cells.Tables.TotalsCalculation.Sum;
2

Hi,


I have tested your scenario/ case a bit. I could not find any of your mentioned issues. I used the following sample code to dynamically input data into the cells of a worksheet and then add a list object/ table to it. I have also attached the output file for your reference:
e.g
Sample code:

//Create a workbook.
Workbook workbook = new Workbook();

//Obtaining the reference of the default(first) worksheet
Worksheet sheet = workbook.Worksheets[0];

//Obtaining Worksheet’s cells collection
Cells cells = sheet.Cells;

//Setting the value to the cells
Aspose.Cells.Cell cell = cells[“A1”];
cell.PutValue(“Employee”);
cell = cells[“B1”];
cell.PutValue(“Quarter”);
cell = cells[“C1”];
cell.PutValue(“Product”);
cell = cells[“D1”];
cell.PutValue(“Continent”);
cell = cells[“E1”];
cell.PutValue(“Country”);
cell = cells[“F1”];
cell.PutValue(“Sale”);

cell = cells[“A2”];
cell.PutValue(“David”);
cell = cells[“A3”];
cell.PutValue(“David”);
cell = cells[“A4”];
cell.PutValue(“David”);
cell = cells[“A5”];
cell.PutValue(“David”);
cell = cells[“A6”];
cell.PutValue(“James”);
cell = cells[“A7”];
cell.PutValue(“James”);
cell = cells[“A8”];
cell.PutValue(“James”);
cell = cells[“A9”];
cell.PutValue(“James”);
cell = cells[“A10”];
cell.PutValue(“James”);
cell = cells[“A11”];
cell.PutValue(“Miya”);
cell = cells[“A12”];
cell.PutValue(“Miya”);
cell = cells[“A13”];
cell.PutValue(“Miya”);
cell = cells[“A14”];
cell.PutValue(“Miya”);
cell = cells[“A15”];
cell.PutValue(“Miya”);


cell = cells[“B2”];
cell.PutValue(1);
cell = cells[“B3”];
cell.PutValue(2);
cell = cells[“B4”];
cell.PutValue(3);
cell = cells[“B5”];
cell.PutValue(4);
cell = cells[“B6”];
cell.PutValue(1);
cell = cells[“B7”];
cell.PutValue(2);
cell = cells[“B8”];
cell.PutValue(3);
cell = cells[“B9”];
cell.PutValue(4);
cell = cells[“B10”];
cell.PutValue(4);
cell = cells[“B11”];
cell.PutValue(1);
cell = cells[“B12”];
cell.PutValue(1);
cell = cells[“B13”];
cell.PutValue(2);
cell = cells[“B14”];
cell.PutValue(2);
cell = cells[“B15”];
cell.PutValue(2);

cell = cells[“C2”];
cell.PutValue(“Maxilaku”);
cell = cells[“C3”];
cell.PutValue(“Maxilaku”);
cell = cells[“C4”];
cell.PutValue(“Chai”);
cell = cells[“C5”];
cell.PutValue(“Maxilaku”);
cell = cells[“C6”];
cell.PutValue(“Chang”);
cell = cells[“C7”];
cell.PutValue(“Chang”);
cell = cells[“C8”];
cell.PutValue(“Chang”);
cell = cells[“C9”];
cell.PutValue(“Chang”);
cell = cells[“C10”];
cell.PutValue(“Chang”);
cell = cells[“C11”];
cell.PutValue(“Geitost”);
cell = cells[“C12”];
cell.PutValue(“Chai”);
cell = cells[“C13”];
cell.PutValue(“Geitost”);
cell = cells[“C14”];
cell.PutValue(“Geitost”);
cell = cells[“C15”];
cell.PutValue(“Geitost”);

cell = cells[“D2”];
cell.PutValue(“Asia”);
cell = cells[“D3”];
cell.PutValue(“Asia”);
cell = cells[“D4”];
cell.PutValue(“Asia”);
cell = cells[“D5”];
cell.PutValue(“Asia”);
cell = cells[“D6”];
cell.PutValue(“Europe”);
cell = cells[“D7”];
cell.PutValue(“Europe”);
cell = cells[“D8”];
cell.PutValue(“Europe”);
cell = cells[“D9”];
cell.PutValue(“Europe”);
cell = cells[“D10”];
cell.PutValue(“Europe”);
cell = cells[“D11”];
cell.PutValue(“America”);
cell = cells[“D12”];
cell.PutValue(“America”);
cell = cells[“D13”];
cell.PutValue(“America”);
cell = cells[“D14”];
cell.PutValue(“America”);
cell = cells[“D15”];
cell.PutValue(“America”);


cell = cells[“E2”];
cell.PutValue(“China”);
cell = cells[“E3”];
cell.PutValue(“India”);
cell = cells[“E4”];
cell.PutValue(“Korea”);
cell = cells[“E5”];
cell.PutValue(“India”);
cell = cells[“E6”];
cell.PutValue(“France”);
cell = cells[“E7”];
cell.PutValue(“France”);
cell = cells[“E8”];
cell.PutValue(“Germany”);
cell = cells[“E9”];
cell.PutValue(“Italy”);
cell = cells[“E10”];
cell.PutValue(“France”);
cell = cells[“E11”];
cell.PutValue(“U.S.”);
cell = cells[“E12”];
cell.PutValue(“U.S.”);
cell = cells[“E13”];
cell.PutValue(“Brazil”);
cell = cells[“E14”];
cell.PutValue(“U.S.”);
cell = cells[“E15”];
cell.PutValue(“U.S.”);


cell = cells[“F2”];
cell.PutValue(2000);
cell = cells[“F3”];
cell.PutValue(500);
cell = cells[“F4”];
cell.PutValue(1200);
cell = cells[“F5”];
cell.PutValue(1500);
cell = cells[“F6”];
cell.PutValue(500);
cell = cells[“F7”];
cell.PutValue(1500);
cell = cells[“F8”];
cell.PutValue(800);
cell = cells[“F9”];
cell.PutValue(900);
cell = cells[“F10”];
cell.PutValue(500);
cell = cells[“F11”];
cell.PutValue(1600);
cell = cells[“F12”];
cell.PutValue(600);
cell = cells[“F13”];
cell.PutValue(2000);
cell = cells[“F14”];
cell.PutValue(500);
cell = cells[“F15”];
cell.PutValue(900);

//Adding a new List Object to the worksheet
ListObject listObject = sheet.ListObjects[sheet.ListObjects.Add(“A1”, “F15”, true)];

//Adding Default Style to the Worksheet
listObject.TableStyleType = TableStyleType.TableStyleMedium10;

//Show Total
listObject.ShowTotals = true;

listObject.ShowTableStyleRowStripes = true;

//AutoFit Columns
sheet.AutoFitColumns();

//Set the Quarter field’s calculation type
listObject.ListColumns[1].TotalsCalculation = TotalsCalculation.Sum;


//Saving the Excel file
workbook.Save(“e:\test2\out1.xlsx”);

I am using our latest version/fix: Aspose.Cells for .NET v17.3.x. If you still find any issue, kindly do post your sample code (runnable) with your template file, we will check it soon.

Thank you.
3

Thank you so much for the reply Amjad.


So, here’s what I’m doing.

1. I already have the table structure setup in the template
2. Fetch the data from the SQL and import into the sheet using ImportDataTable
3. Add the table structure with the necessary rows and columns
4. Set showtotals to true
5. Calculate the sum.

But the sum calculated is not being populated when the sheet is saved. Can you please let me know what can be wrong here.

Thank you so much for your help.

Below is the piece of code that I’m using for the above purposes.


wSheet.Cells.ImportDataTable(dt1.Tables[0], false, 7, 0, false);

ListObjectCollection tableStructure = wSheet.ListObjects;

tableStructure.Clear();

int index = tableStructure.Add(6, 0, dt1.Tables[0].Rows.Count + 6, dt1.Tables[0].Columns.Count - 1, true);

ListObject table = tableStructure[index];

table.ShowTotals = true;
//I have some code for alignments, styling and conditional formatting here//
wSheet.ActiveCell = “A8”;
table.ShowTotals = true;

table.ShowTableStyleRowStripes = true;

wSheet.AutoFitColumns();

wSheet.Cells.DeleteRow(table.EndRow - 1);

table.ListColumns[6].TotalsCalculation = TotalsCalculation.Sum;

4

Hi,


Thanks for your posting and using Aspose.Cells.

Whenever you add or delete the table’s data, you must resize it. Please use the ListObject.Resize() method for this purpose. It means, you do not need to re-create your existing table, you just need to resize it and it should fix your issue.

We have created the table as per your code after some modifications and it looks good. Please see the following sample code, it source excel file, its output excel file as well as screenshot for your reference. Let us know your feedback.

C#
Workbook wb = new Workbook(dirPath + “totals.xlsx”);

Worksheet wSheet = wb.Worksheets[0];

ListObjectCollection tableStructure = wSheet.ListObjects;
tableStructure.Clear();

int startRow = 0;
int startColumn = 0;
int endRow = 18;
int endColumn = 4;

int index = tableStructure.Add(startRow, startColumn, endRow, endColumn, true);

ListObject table = tableStructure[index];
table.ShowTotals = true;
table.ShowTableStyleRowStripes = true;
table.TableStyleType = TableStyleType.TableStyleDark10;

table.ListColumns[0].TotalsCalculation = TotalsCalculation.Sum;
table.ListColumns[1].TotalsCalculation = TotalsCalculation.Sum;
table.ListColumns[2].TotalsCalculation = TotalsCalculation.Sum;
table.ListColumns[3].TotalsCalculation = TotalsCalculation.Sum;
table.ListColumns[4].TotalsCalculation = TotalsCalculation.Sum;

//Call this line to resize the table.
table.Resize(startRow, startColumn, endRow - 5, endColumn, true);

wb.Save(“output.xlsx”);

5

Thank you Shakeel. I have now added a line to resize instead of creating a new list object. I do see the formula showing up on the generated excel, but the values are not being populated. Please check the screenshot for reference.


When I manually go and select sum in the totals row, I do get the sum calculated, but I would like to display the sum without the manual intervention.

Can you please let me know if there’s anything missing on my end. I have tried various scenarios, but nothing is helping me out.

Thank you.
6

Actually nevermind. I found a workaround to make it work.


I had include wbook.CalculateFormula(); to make the formula work. Thank you for your inputs :slight_smile:
7

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that you were able to sort out your issue with workbook calculation function. Let us know if you encounter any other issue, we will be glad to look into it and help you further.