SubTotal - SummaryBelowData

I am trying to create subtotal rows with the summary rows above the data. Is there a way of doing this?

Here is the vba code of what I would like to do:

ExcelSheet.Application.Selection.Subtotal(GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3, 4, 5), Replace:=True, PageBreaks:=False, SummaryBelowData:=False)

Here is the Aspose version of the code, but this would not create the summay rows above the data:

WorkSheet.Cells.Subtotal(CellArea, 0, Aspose.Cells.ConsolidationFunction.Sum, TotalList)

Thanks.

Hi,

Yes, by default, Aspose.Cells's Subtotals feature only displays data in such a way that the summary rows will be always below the data. We will check the feasibility of your requested feature (summary rows above data in Subtotals) and get back to you soon.

Thank you.

Hi,

Please try the attached version, we have added a new version for the method i.e..,

Cells.Subtotal(CellArea ca, int groupBy, ConsolidationFunction function, int[] totalList, bool replace,bool pageBreaks,bool summaryBelowData)

Sample code:

//Instantiate a new workbook

Workbook workbook = new Workbook();

//Open the template file

workbook.Open(@"F:\test\Book1.xls");

//Get the Cells collection in the first worksheet

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

//Create a cellarea i.e.., B3:C19

CellArea ca = new CellArea();

ca.StartRow = 2;

ca.StartColumn = 1;

ca.EndRow = 18;

ca.EndColumn = 2;

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

//Second column (C) in the list

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

//Save the excel file

workbook.Save(@"F:\test\outtSubtotals.xls");

Thank you.

Thanks for providing a new version, but there seems to be a problem with the row groupings. I have attached the input, output and expected output files.

Hi,

Please add the following codes after Subtotal:

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

Perfect! Thank you.

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


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

Note: Just for your knowledge. In the new release v4.8.0, we have merged Aspose.Grid suite to Aspose.Cells for .NET msi installer as Aspose.Grid would be no longer offered as a separate product now. You need to install the installer (uninstall older one first if you have) and use only Aspose.Cells.dll library in your project for your need. You may also take advantage using Aspose.Cells Grid suite though.

Hi,


I am just wondering if there is a function to collapse the groups.

Thanks.

Hi,

Well, apparently, Aspose.Cells for .NET does not have any function to collapse the groups by default.

We will look into it and get back to you soon.

Thank you.

Hi,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We have added two methods to collapse and uncollapse groups:

Cells.HideGroupDetail(bool isVertical,int index);

Cells.ShowGroupDetail(bool isVertical,int index);

Thank You & Best Regards,

Thanks for implementing the functions so quickly.


I believe the second parameter is the column/row index and not the outline level, right?
Just wondering if there is a way to collapse all rows with outline level 3 without knowing which rows are level 3 ahead of time. I have attached a sample input and output file.

Thanks.

Hi,

OK, we will provide the following methods in the next fix (which will be provided before the end of this week):
1) Cells.GetGroupedRowOutlineLevel
2) Cells.GetGroupedColumnOutlineLevel
3) Cells.GetMaxGroupedColumnOutlineLevel
4) Cells.GetMaxGroupedRowOutlineLevel


So you can simply hide them by yourself, e.g.
Workbook workbook = new Workbook();
workbook.Open(@“F:\FileTemp\Excel+Groups-Before.xls”);
Cells cells = workbook.Worksheets[0].Cells;
int maxRow = cells.MaxRow;
for (int row = 0; row <= maxRow; row++)
{
int rowOutlineLevel = cells.GetGroupedRowOutlineLevel(row);
if (rowOutlineLevel == 2)//zero-based
{
cells.HideRow(row);
}
}
workbook.Save(@“F:\FileTemp\dest.xls”);


Thanks for your patience.

Hi,

Please try the attached version. Please refer to the code segment and api list for reference in the previous post.

Thank you.