Free Support Forum - aspose.com

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 http://www.aspose.com/wiki/default.aspx/Aspose.Excel/GroupingRowsAndColumns.html 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