Sub-totals and Totals

Aspose.Cells Product Family
1

Could you please provide me a way to calculate sub-totals and Totals.





Suppose I have below records and want to display sub totals and Totals.





1 ALTO-LXI 300

2 ALTO-800 200



Sub-total 500



3 RITZ VXI 219

4 RITZ ZXI 190



sub-total 409





Total 909

2

Hi Vidya,


Thank you for contacting Aspose support.

You can easily apply Subtotal and Grand Total on repeating values by following the steps provided in this article, however, your sample data does not seem to be repeating. Please check the article and give it a try on your side. In case you face any difficulty, please share your sample spreadsheet containing actual data.

3

Thanks for sharing the article.



Now I have got an idea on how to add sub-totals using Aspose. Actually I have huge data in my report (like 20 columns and 4000+ rows) and want to display sub-totals on few columns which is having amounts, quantity, possitions etc…(around 8 columns)



how to use below statement for multiple columns



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



Thanks in advance.

4

Hi Vidya,


I have modified the example from the article to apply subtotals on two columns. Please check the following piece of code with comments. You may amend it as per your application needs.

C#

//Load a spreadsheet
Workbook workbook = new Workbook(“D:/Book1.xlsx”);
//Get the Cells collection for the first worksheet
var cells = workbook.Worksheets[0].Cells;

//Create a CellArea on which Subtotal has to be applied
//for the sake of demonstration the CellArea is defined
//as B1:D20
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 1;
ca.EndRow = 19;
ca.EndColumn = 3;
//Create Subtotal on the CellArea, where grouping is done on column B (0 of CellArea)
//ConsolidationFunction is Sum
//Subtotal to be applied on columns C & D (1 & 2 from the CellArea)
cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] {1,2});
//Save the excel file
workbook.Save(“D:/output.xlsx”);
5

Thanks for posting the respose.



I want to do this for more columns and I am capturing the same in below object

Dim strList()



where as I am getting error when pass this parameter in subtotal method.



xlWs.Cells.Subtotal(ca, intGroupByColIndex, ConsolidationFunction.Sum, totalList:=strList)



Kindly suggest.

6

Hi Vidya,


I believe the strList is a list of strings that is the reason you are getting error while passing this parameter to the Cells.Subtotal. Please note, the said method expects to receive an array of type integer where each value in the array represents column index in the CellArea. If you have the list of column names instead of their indices, you can convert them to column indices using the CellsHelper.ColumnNameToIndex factory method.
7

I have issues in displaying sutotals in my report.

In my report I have 12 columns,20 rows and need to display subtotals.

sometimes my calculation is ignoring the first row… I used above example for displaying the subtotals.

Thanks in advance.

8

Hi Vidya,


Please share your sample spreadsheet along with your modified code for our review.
9

My code is very simple.



1) Extracting data from Database and keeping in an Excel using aspose.



We used ImportDataTable method to copy the data. I am extracting 12 columns and 20 records.



But at the bottom I am expecting sub-totals in a separate row.



Could you please give me an example of displaying sample data of (8 columns and 3-5 rows) along with the sub-totals so that I will follow in the same way.



Thanks a lot for your support.

10

Hi Vidya,


I have already shared the code snippet with proper comments to apply subtotal on 2 columns so you could get a hint and modify the code to achieve your goal. I can provide you another generic sample but that may not fulfill your requirement as I am not aware what kind of data you have and on which columns do you wish to apply the subtotal. It would be of great help in understanding your exact requirement if you can share a sample spreadsheet with your desired results, that you may create manually using Excel application. This will give us a good idea of your ultimate goal, and we can easily share the code snippet to achieve the same while using Aspose.Cells APIs.

Thank you for your understanding.