We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Controling collapse categories when using Subtotals

Hi,

I have the need to use subtotals and I followed the link below it works great.

http://docs.aspose.com/display/cellsnet/Creating+Subtotals

But after I do the subtotals the workbook is saved with all the categories expanded. I need to be able to collapse a categorie and then save the workbook. How can I do that ?

This is what I have:

Dim ca As New CellArea()

ca.StartRow = 10

ca.StartColumn = 0

ca.EndRow = lastRow + 1

ca.EndColumn = 12

w.Worksheets(0).Cells.Subtotal(ca, 0, ConsolidationFunction.Sum, New Integer() {3, 4, 5, 6, 7, 8, 9, 10, 11, 12})

'Here I need to collapse categorie how to in aspose??

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please provide us your source code and actual and expected output xls/xlsx files.

Please also try the latest version:

Aspose.Cells for .NET 7.1.1



We will look into it and help you asap.

Given an input like Input.JPG

I am able to produce an output like AsposeOutput.JPG using :

Dim ca As New CellArea()

ca.StartRow = 0

ca.StartColumn = 0

ca.EndRow = 11

ca.EndColumn = 2

w.Worksheets(0).Cells.Subtotal(ca, 0, ConsolidationFunction.Sum, New Integer() {1, 2})

But I need an output like NeededOutput.JPG

To obtain the desired output I need to do 2 things in Aspose.

Need to select range "A1:C16" (on AsposeOutput.JPG) and copy and paste special as values. (that will get rid of formulas in my range)

Next I need to be able to programatically click 2 on margin (as outlined in AsposeOutput.JPG) to collapse the list

how can I do those 2 steps on aspose.cells ?

Thank you,

Hi,


1) You may use Cells.HideGroupDetail() method to collapse the categories.
2) You may create ranges (source and destination ranges) and then copy the values from source range to destination range by using Range.CopyValue() method. Also, don’t forget to call Workbook.CalculateFormula() method to calculate the formulas first.

See the sample code for your complete reference to achieve your desired results:

Sample code (I used your attached template file’s first worksheet’s data as source range for the subtotals):

Aspose.Cells.Workbook asposeWorkBook = new Aspose.Cells.Workbook(“e:\test2\SubtotalsExample.xlsx”);
Aspose.Cells.Worksheet asposeWorkSheet = null;
asposeWorkSheet = asposeWorkBook.Worksheets[0];
int[] totalList = new int[2] { 1,2 };
Aspose.Cells.CellArea ca = new Aspose.Cells.CellArea();
Aspose.Cells.Cells cells = asposeWorkSheet.Cells;
ca.StartRow = 1;
ca.StartColumn = 0;
ca.EndRow = cells.MaxDataRow;
ca.EndColumn = cells.MaxColumn;
asposeWorkSheet.Cells.Subtotal(ca, 0, Aspose.Cells.ConsolidationFunction.Sum, totalList, true, false, true);
asposeWorkSheet.Cells.HideGroupDetail(true, 1);
asposeWorkSheet.Cells.HideGroupDetail(true, 5);
asposeWorkSheet.Cells.HideGroupDetail(true, 8);
asposeWorkSheet.Cells.HideGroupDetail(true, 11);

//Calculate the formulas
asposeWorkBook.CalculateFormula();
Range r1 = asposeWorkSheet.Cells.CreateRange(“A1:C16”);
Range r2 = asposeWorkSheet.Cells.CreateRange(“I31:K46”);
r2.CopyValue(r1);
asposeWorkBook.Save(“e:\test2\output_subtotals1.xlsx”);

Thank you.

It works great.

Maybe it will be good to support that extra feature of collapsing all totals with a single command since that is a common thing. But is not necessary can be done by looping and collapsing one by one. Just a suggestion.

Thanks,

Hi,


Good to know that my sample code helps you a lot for your needs.

Yes, sure, we may think over your suggestion and might add this feature in later versions.

Thanks