Unexpected behaviour while using Subtotals function

[a35068]

Hi there,

I'm trying to use the Subtotals function, but with subtotals on top.

looking at the code found here (which works okay), I changed this line:

cells.Subtotal(ca, 0, ConsolidationFunction.Sum, New Integer() { 1 })
into:

cells.Subtotal(ca, 0, ConsolidationFunction.Sum, New Integer() {1}, False, False, False)

which should result in showing the totals on top in stead of below the data. But when I look at output.xlsx (attached), i'm not seeing what I expect. (please look at expected.xlsx to see what I did expect)

any ideas on what might have gone wrong?

thanks in advance.

[Amjad_Sahi]

Hi,

Thanks for the template files.

I think you may try to change the line of code:

i.e.,

cells.Subtotal(ca, 0, ConsolidationFunction.Sum, New Integer() {1}, False, False, False)

to:

cells.Subtotal(ca, 0, ConsolidationFunction.Sum, New Integer() {1}, True, False, False)

Let us know if you still find any issue.

Thank you.

[a35068]

Hello Amjad,

No, same result. Did you have a look at the attachments? You can see that the subtotal header represents the wrong subgroup.

so subtotal “b total” is summing all “b” records, but it collapses all “a” records.

Do you know how i can accomplish the situation as described in expected.xlsx?

[Amjad_Sahi]

Hi,

I observed the issue as you mentioned. I can see wrong position of the Grand Total (although it is there) with an addition of invalid Subtotal entry to the outline report. I used the following sample code to try to accomplish the task with latest version v8.1.1 as per your expected file. I used the your template file.
e.g
Sample code:

//Instantiate a new workbook
//Open the template file
Workbook workbook = new Workbook(@“e:\test2\Book1.xlsx”);

//Get the Cells collection in the first worksheet
Cells cells = workbook.Worksheets[0].Cells;

//Create a cellarea i.e…, A1:B11
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 0;
ca.EndRow = 10;
ca.EndColumn = 1;

//Apply subtotal, the consolidation function is Sum and it will applied to
//Second column (B) in the list
cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] {1},true,false,false);

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

Please see the attached screen shot for better illustration of the issue.

I have logged a ticket with an id “CELLSNET-42813” for your issue. We will look into it if this is a bug or we can accomplish your task by other workaround using the APIs.

Once we have any update on it, we will let you know here.

Thank you.

[shakeel.faiz]

Hi,

Thanks for using Aspose.Cells.

Aspose.Cells does not check whether the first row is header row. So
please set data area as A2:B11. Please see the following code:

C#

Workbook workbook = new Workbook(@“d:\Filetemp\Book1.xlsx”);


//Get the Cells collection in the first worksheet

Cells cells = workbook.Worksheets[0].Cells;


//Create a cellarea i.e…, A2:B11

CellArea ca = new CellArea();

ca.StartRow = 1;

ca.StartColumn = 0;

ca.EndRow = 10;

ca.EndColumn = 1;


//Apply subtotal, the consolidation function is Sum and it will applied to

//Second column (B) in the list

cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 1 }, true, false, false);


//Save the excel file

workbook.Save(@“d:\Filetemp\dest.xlsx”);

[a35068]

Hi Shakeel,

Unfortunately, that doesn't solve the problem

I think the problem is not with the header row contained in the data or not, it's about the outline.

The individual subtotals are placed above their set of rows as they should be, but when you try to collapse using the outline, it's just messed up.

[shakeel.faiz]

Hi Bas,

Thanks for your feedback and using Aspose.Cells.

I was able to observe this issue. There is a difference in grouping (outline) of your expected output file and the output file created by Aspose.Cells code. I highlighted it in a screenshot for a reference. I used the following code for testing this issue.

We have logged your feedback with these details in our database for investigation. We will look into it and see how it could be avoided or if there need any code change, we would suggest it. Once, there is some update for you, we will let you know asap.

C#

Workbook workbook = new Workbook(@“Book1.xlsx”);


//Get the Cells collection in the first worksheet

Cells cells = workbook.Worksheets[0].Cells;


//Create a cellarea i.e…, A2:B11

CellArea ca = CellArea.CreateCellArea(“A2”,“B11”);


//Apply subtotal, the consolidation function is Sum and it will applied to

//Second column (B) in the list

cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 1 }, true, false, false);


//Save the excel file

workbook.Save(@“out2.xlsx”);

[shakeel.faiz]

Hi Bas,

Please change outline setting of the worksheet, please see following
code. I have attached the output xlsx file generated by it for your reference.

C#

Workbook workbook = new Workbook(@“Book1.xlsx”);


//Get the Cells collection in the first worksheet

Cells cells = workbook.Worksheets[0].Cells;


//Create a cellarea i.e…, A2:B11

CellArea ca = CellArea.CreateCellArea(“A2”, “B11”);


//Apply subtotal, the consolidation function is Sum and it will applied to

//Second column (B) in the list

cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 1 }, true, false, false);


workbook.Worksheets[0].Outline.SummaryRowBelow = false;


//Save the excel file

workbook.Save(@“dest.xlsx”);

[a35068]

Hi Shakeel,

That seems to work! thanks a lot…

I still think it’s a bug though, because there is no logic in outlining the subtotal of one group with the lines of another group. And I think one should not have to set 2 parameters to accomplish this.

Thanks for your effort!

[shakeel.faiz]

Hi Bas,

Thanks for your posting and using Aspose.Cells.

If you check “Summary rows below detail” in your expected.xlsx file, it will become same as Aspose.Cells created output xlsx (out2.xlsx) file

So, we think, Aspose.Cells is working correctly.

I have attached the screenshot for illustration of this issue for your reference.