Nested Subtotals Don't Work?

Aspose.Cells Product Family
1

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");

2

Hi,

Thanks for providing us the template files with sample code.

jpumphrey:
  • 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.

We have already corrected the first issue in the latest fix. We found the other two issues you have mentioned though. We will figure them out and provide the supported version soon.

Thank you.

3

Thanks. I tried again with version 4.8.0 and I see the "total" wording in the correct column, although unfortunately it didn't fix the expand/contract behavior so maybe that is a separate issue and I will try to describe it in more detail.

When I do manual nested subtotals subtotals in Excel this is the correct behavior I get (see result_excel.xls from my original message):

  • There are 4 expansion levels in the upper left corner of the worksheet - 1/2/3/4.
  • When I click on "1" I see the grand total.
  • When I click on "2" I see the region subtotals and the grand total. (see attached screen shot - "result_excel.xls expanded to region.png")
  • When I click on "3" I see district subtotals, region subtotals, and the grand total. (see attached screen shot - "result_excel.xls expanded to district.png")
  • When I click on "4" I see everything fully expanded.

When I do region and district subtotals with aspose cells 4.8.0 I get very different expand/contract behavior that doesn't look correct:

  • There are 5 expansion levels in the upper left corner instead of 4 - there's an extra one.
  • I clicked on all of the expansion levels but none of them expand to region. See screen shots - I get district names instead of region names when I expand to level 2 and 3.
  • When I click on "4" it looks like it's expanded to districts and the only problems are the two you said you were working on - multiple grand total rows and subtotals in the wrong order (M.Viola and J.Cubero districts are affected by the wrong order issue).
  • The only levels that look correct are the first one (which shows just 1 grand total row) and the last one (which shows everything fully expanded).

So, there are 3 nested subtotal issues total that would prevent me from being able to buy this product ... can't expand/contract correctly, there are multiple grand totals, and the subtotals are out of order at the end of each region.

Do you have an estimated delivery date for fixing all of these issues? Preferably soon? I am trying to decide which component to buy, and nested subtotals are really the only feature I need.

4

Hi,

Thanks for sharing us further details.

We will look into your mentioned issues and get back to you soon.

Thank you.

5

Hi,
Please try the attached version. Please use the following sample code.
Sample code:

Workbook wb = new Workbook();
wb.Open(“f:\test\input_excel.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 }, true, false, true);
//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 }, false, false, true);
//save
wb.Save(“f:\test\out.xls”);


Thank you.

6

Thanks for the reply - I think this fixed most of the issues but the expand/contract buttons in the upper left are still not right. There is still an extra expansion level, and one of the regions is missing when I expand to region level. I decided to use SSRS for my report since it creates the nested subtotals automatically when I export a report with a grouped table, and it doesn’t have any expand/contract issues. Thanks for your help though!

7

The issues you have found earlier (filed as 10317) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.