Multi-level grouping of rows


#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 http://www.aspose.com/Products/Aspose.Excel/Fixes/Aspose.Excel.zip.


#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 http://www.aspose.com/Products/Aspose.Excel/Fixes/Aspose.Excel.zip.


#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 http://www.aspose.com/Products/Aspose.Excel/Fixes/Aspose.Excel.zip.

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.