How to create Subtotals with Aspose.Excel?

Aspose.Cells Product Family
1

Hi,

I have recently bought the Aspose Excel Component, and I am trying to create Subtotals in a worksheet.

Here is the Equivalent Excel Automation code of what I want to do with Aspose:

selection = sheet.get_Range( "A:N", Type.Missing );
selection.Subtotal( Column1, XlConsolidationFunction.xlSum, new int[ 5 ] { Column4, Column5, Column6, Column7, Column8 }, false, false, XlSummaryRow.xlSummaryBelow );
selection.Subtotal( Column2, XlConsolidationFunction.xlSum, new int[ 5 ] { Column4, Column5, Column6, Column7, Column8 }, false, false, XlSummaryRow.xlSummaryBelow );
selection.Subtotal( Column3, XlConsolidationFunction.xlSum, new int[ 5 ] { Column4, Column5, Column6, Column7, Column8 }, false, false, XlSummaryRow.xlSummaryBelow );

What is the equivalent code with Aspose ?

Thank you,

Fred

2

Hi Fred,

Do you want to group rows and columns to make a subtotal outline? Please check

for reference.

3

Hi,

I have tried the function "GroupRow" of the cells objects. However,

it does not calculate the summary rows automatically, like the "Subtotal" method

of the VBA "Range" object does.

Do you think this feature could be added to Aspose.Excel in the near future ?

It would help me very much in a couple of projects that I have to do soon.

Thank you very much,

Fred

4

Hi Fred,

I am not familiar with Excel automation code. Could you please post file created with your automation code? Then I can see what it will generate.

With Aspose.Excel, I think you can use Cell.Formula to add a subtotal formula to cell which can calculate summary rows.

5

Lawrence,

The Cell.Formula method - for adding a subtotal formula - doesn't seem to work the same way as the sample Excel code above (or below):

Range("A7:I34").Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

The above code will select the A7:I34 range and subtotal the values in the 8th column while breaking on values in the first column, and provide grouping and labeling of the subtotal subgroups on each subtotal line.

I performed this function in Excel by going to the Data menu, Subtotals option. Can this type of subtotaling be done in Aspose?

Try creating an Excel spreadsheet as follows:

NAME SALES

Bob 90

Bob 95

Lawrence 101

Lawrence 403

Then select the ten cells containing data (five rows and two columns), go to Data, Subtotals. Change on NAME, use the Sum function, add subtotals to SALES, and take a look at what Excel does. You will see subtotal rows inserted, along with labels and subtotal values, and a grand total at the bottom, along with grouping functionality on the left side of the worksheet.

Bob

6

Hi Bob,

Well, Implementing subtotals and grandtotals automatically against your data is not supported yet, you have to manually do using Cell.Formula. We may consider this feature in future. Currently, you can implement subtotals while implementing pivot tables which Aspose.Cells APIs support now.

Regards

Amjad Sahi

Aspose Nanjing Team

7

Amjad,

Thank you for your quick reply!

Bob

8

@Fred999,

Please note, Aspose.Excel has been changed to become Aspose.Cells now. The new API is more robust and feature rich and includes many advanced level features. Aspose.Cells supports Subtotal feature. See the article with example code for your reference:
How to create Subtotals in Excel spreadsheet

Also, See the sample code on how to create Subtotals in the worksheet for your reference:
e.g.
Sample code:

// Instantiate a new workbook
// Open the template file
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.., 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 });

// Save the excel file
workbook.Save("output.out.xlsx");

Please use the link to get free trial version of this new product:
Aspose.Cells for .NET(Latest version)

Also, Here is a runnable complete solution that contains a lot of examples to test different features of Aspose.Cells