Does anyone have a working example of making nested subtotals with aspose.cells?
I tried it and it's not working. I tried to subtotal on my 1st column and then add another subtotal on the 2nd column. Here are the issues ...
- The subtotal text is always displayed in the first column of the cell area - not the column that I am subtotaling. This makes the expand/contract act very screwy.
- There are 2 grand total rows at the bottom instead of 1.
- The subtotal rows are in a strange order. At the end of each region, it displays the region total and THEN the district total for the last district in that region ... the opposite order that would make sense.
See attached files:
- result_aspose.xls is the resulting file after trying to do this with Aspose.cells
- result_excel.xls is the correct result that I get when I do the same subtotals manually in Excel. This is what I need to recreate - expand/contract works as expected, there is only 1 grand total row, and the subtotal rows appear in an order that makes sense (district then region).
Here's my code. I'm using aspose cells version 4.7.1
//open workbook
Workbook wb = new Workbook();
wb.Open("c:\\temp\\test.xls");
Cells cells = wb.Worksheets[0].Cells;
CellArea cellArea = new CellArea();
//first subtotal (region)
cellArea.StartRow = 1;
cellArea.StartColumn = 0;
cellArea.EndRow = cells.EndCellInColumn(5).Row;
cellArea.EndColumn = cells.EndCellInRow(1).Column;
cells.Subtotal(cellArea, 0, ConsolidationFunction.Sum, new int[] { 3, 4, 5 });
//second subtotal (district)
cellArea.StartRow = 1;
cellArea.StartColumn = 0;
cellArea.EndRow = cells.EndCellInColumn(5).Row;
cellArea.EndColumn = cells.EndCellInRow(1).Column;
cells.Subtotal(cellArea, 1, ConsolidationFunction.Sum, new int[] { 3, 4, 5 });
//save
wb.Save("c:\\temp\\result_aspose.xls");