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)
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.
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
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.