Multi-level grouping of rows

Aspose.Cells Product Family
1

Is there any way to create a multi-level rows grouping in Aspose.Excel?

Example (4 kbytes PNG image + tons of ads, sorry): http://roquentoine.tripod.com/SamplePrice.html

I am trying to port my Excel price-list to Aspose.Excel, and Cells.GroupRows() method throws IndexOutOfRange exception. My code:

excel.Worksheets[0].Cells.GroupRows(4, 6);
excel.Worksheets[0].Cells.GroupRows(8, 10);
excel.Worksheets[0].Cells.GroupRows(4, 10); // throws an exception

Am I doing something wrong?

2

Hi Antoine,

It’s a bug and I fixed it. Please download the latest hotfix at

3

Thank you very much for prompt response!
Rows grouping works well now.

I also found the undocumented feature in GroupRows() method (or maybe, my documentation is outdated as I've taken it from 2.4.0.0 release): there is a handy boolean argument that allows hiding of grouped rows.

I need to create tree-like structure with all rows hidden (as much as possible), so I group rows and hide them like this:

excel.Worksheets[0].Cells.GroupRows(11, 12, true); // 3rd level
excel.Worksheets[0].Cells.GroupRows(14, 15, true); // 3rd
excel.Worksheets[0].Cells.GroupRows(18, 18, true); // 3rd
excel.Worksheets[0].Cells.GroupRows(20, 25, true); // 3rd
excel.Worksheets[0].Cells.GroupRows(4, 8, true); // 2nd level
excel.Worksheets[0].Cells.GroupRows(10, 15, true); // 2nd
excel.Worksheets[0].Cells.GroupRows(17, 25, true); // 2nd
excel.Worksheets[0].Cells.GroupRows(3, 25, true); // top level

When open the file is Excel, the outline shows summary rows for the top level grouping (as expected). But when I press the [+] button, it shows all child rows unfolded. In fact, MS Excel displays the file as if it was created with the following code instead:

excel.Worksheets[0].Cells.GroupRows(11, 12);
excel.Worksheets[0].Cells.GroupRows(14, 15);
excel.Worksheets[0].Cells.GroupRows(18, 18);
excel.Worksheets[0].Cells.GroupRows(20, 25);
excel.Worksheets[0].Cells.GroupRows(4, 8);
excel.Worksheets[0].Cells.GroupRows(10, 15);
excel.Worksheets[0].Cells.GroupRows(17, 25);
excel.Worksheets[0].Cells.GroupRows(3, 25, true);

(only the top level grouping hides child rows). In MS Excel, I can

ActiveSheet.Outline.ShowLevels(4);
ActiveSheet.Outline.ShowLevels(3);
ActiveSheet.Outline.ShowLevels(2);
ActiveSheet.Outline.ShowLevels(1);

to hide all rows. Can I do something like that in Aspose.Excel?

4

Hi Antoine,

I shape the group rows routine and upload the hotfix. Please download it again at

5

Hi Laurence!
Very nice! It's exactly what I asked for!

But, sadly, there is still one small issue left: the topmost group of rows on each grouping level (except of the 1st level) remains unhidden:

excel.Worksheets[0].Cells.GroupRows(11, 12, true); // unhidden
excel.Worksheets[0].Cells.GroupRows(14, 15, true);
excel.Worksheets[0].Cells.GroupRows(18, 18, true); // unhidden
excel.Worksheets[0].Cells.GroupRows(20, 25, true);
excel.Worksheets[0].Cells.GroupRows(4, 8, true); // unhidden
excel.Worksheets[0].Cells.GroupRows(10, 15, true);
excel.Worksheets[0].Cells.GroupRows(17, 25, true);
excel.Worksheets[0].Cells.GroupRows(3, 25, true);

6

I run your code in my machine and all works fine. Could you send me your created file?

7

I forgot to mention (sorry!) that I use designer template with non-default outline settings. On the Data menu, point to Group and Outline, and then click Settings. Clear the Summary rows below detail check box.

By the way, can I control this setting via Aspose.Excel API?

8

This feature is not supported yet. I will add it in the next release.

9

And what's with the bug?

Antoine wrote:
the topmost group of rows on each grouping level (except of the 1st level) remains unhidden

To reproduce: create blank worksheet in MS Excel, uncheck Summary rows below detail, then

excel.Open("blank.xls");
excel.Worksheets[0].Cells.GroupRows(11, 12, true); // the same code as before
excel.Worksheets[0].Cells.GroupRows(14, 15, true);
excel.Worksheets[0].Cells.GroupRows(18, 18, true);
excel.Worksheets[0].Cells.GroupRows(20, 25, true);
excel.Worksheets[0].Cells.GroupRows(4, 8, true);
excel.Worksheets[0].Cells.GroupRows(10, 15, true);
excel.Worksheets[0].Cells.GroupRows(17, 25, true);
excel.Worksheets[0].Cells.GroupRows(3, 25, true);
excel.Save("output.xls", FileFormatType.Default);

10

It’s fixed. Please re-download the hotfix at

And you can use

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

to change the outline option.

11

Thanks a lot!
Rows grouping is just perfect now!

12

Yet another bug in row grouping: IsHidden setting is lost when Excel is saved and reopened:

// group some rows (please note: all rows are hidden)
excel.Worksheets[0].Cells.GroupRows(0, 2, true);
excel.Worksheets[0].Cells.GroupRows(4, 5, true);
excel.Worksheets[0].Cells.GroupRows(0, 6, true);
excel.Worksheets[0].Cells.GroupRows(8, 9, true);
excel.Worksheets[0].Cells.GroupRows(0, 10, true);
excel.Save("source.xls");

// reopen/save
excel.Open("source.xls");
excel.Save("destination.xls");

In "destination.xls" only the top-level grouping is collapsed, and all child rows are unhidden (just press the [+] button).

13

Hi Antoine,

Thanks for your report.

All these bugs related to group rows will be fixed in next release.

14

@Antoine,
Aspose.Excel is deprecated now and no more under active development now. It is replaced by an other advanced version Aspose.Cells which is much advanced and rich in terms of features. This new product supports all the features of its predecessor along with the support for the features available in different versions of MS Excel. We can group and un-group rows and columns using this new product as well as shown in the following sample code:

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(dataDir + "book1.xls", FileMode.Open);

// Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Grouping first six rows (from 0 to 5) and making them hidden by passing true
worksheet.Cells.GroupRows(0, 5, true);

// Grouping first three columns (from 0 to 2) and making them hidden by passing true
worksheet.Cells.GroupColumns(0, 2, true);

// Saving the modified Excel file
workbook.Save(dataDir + "output.xls");

// Closing the file stream to free all resources
fstream.Close();

Follow the link below for more information about grouping/ungrouping rows and columns in a workbook:
Grouping and ungrouping rows and columns

The latest free trial version of Aspose.Cells is available here:
Aspose.Cells for .NET (Latest Version)

A ready to run solution is available here which can be used to test the product features with minimum effort.