Hi,
I noticed some issues with the isHidden attribute when calling Cells.GroupRows with some nested groups. For example, I have the same set of data. I would expect worksheet1 and worksheet2 to be identical. However, worksheet1 is showing some strange behavior where Cells A3, A4, and A5 are visible even though the outer group should be hidden. Worksheet2 merely changed the order of the GroupRows calls, but Cells A3+A4+A5 are collapsed when they shouldn’t be. Worksheet3 is another edge case where the inner groups should be collapsed, but they aren’t because the Cells.GroupRows(1,8,false) came last. Worksheet4 demonstrates the correct behavior for worksheet3.
var workbook = new Workbook();
workbook.Worksheets.Add();
workbook.Worksheets.Add();
workbook.Worksheets.Add();
var worksheet1 = workbook.Worksheets[0];
var worksheet2 = workbook.Worksheets[1];
var worksheet3 = workbook.Worksheets[2];
var worksheet4 = workbook.Worksheets[3];
worksheet1.Outline.SummaryRowBelow = false;
worksheet2.Outline.SummaryRowBelow = false;
worksheet3.Outline.SummaryRowBelow = false;
worksheet4.Outline.SummaryRowBelow = false;
for (int i = 1; i <= 9; ++i)
{
worksheet1.Cells[$"A{i}"].PutValue($"Row{i}");
worksheet2.Cells[$"A{i}"].PutValue($"Row{i}");
worksheet3.Cells[$"A{i}"].PutValue($"Row{i}");
worksheet4.Cells[$"A{i}"].PutValue($"Row{i}");
}
//Cells A3, A4, A5 are visible and shouldn't be
//The document behaves the way I would expect after I toggle the group manually
worksheet1.Cells.GroupRows(1, 8, true);
worksheet1.Cells.GroupRows(6, 8, true);
worksheet1.Cells.GroupRows(2, 4, false);
//Order of the GroupRows calls seems to matter as well as this produces a different result
//Cells A3, A4, A5 should be visible but are hidden after uncollapsing the outer group manually in excel
worksheet2.Cells.GroupRows(6, 8, true);
worksheet2.Cells.GroupRows(2, 4, false);
worksheet2.Cells.GroupRows(1, 8, true);
//Another modification on the same values
//The inner groups should be collapsed, but aren't when I set the outer group to be visible
worksheet3.Cells.GroupRows(6, 8, true);
worksheet3.Cells.GroupRows(2, 4, true);
worksheet3.Cells.GroupRows(1, 8, false);
//Moving the outer row call first works
worksheet4.Cells.GroupRows(1, 8, false);
worksheet4.Cells.GroupRows(6, 8, true);
worksheet4.Cells.GroupRows(2, 4, true);
workbook.Save("RowGroupingOrderBug.xlsx");
@dfactset
Thanks for using Aspose APIs.
We were able to observe this issue but we request you to explain the issue with screenshots so that product team could find and fix it quickly and precisely. Thanks for your cooperation in this regard and have a good day.
FYI:
This issue has been logged as CELLSNET-45555.
Expected view of worksheet 1 and 2 when opening the workbook. The main group should be hidden because Cells.GroupRows(1, 8, true) was called on worksheet1 and worksheet2. The order in which GroupRows is called shouldn’t affect the resulting workbook :
image.png (36.4 KB)
Expected view of worksheet 1 and 2 after manually expanding the first group. Notice that rows 2-5 are visible and 6-9 are hidden because of Cells.GroupRows(2, 4, false) and Cells.GroupRows(6, 8, true). Once again, the order in which GroupRows is called has changed how worksheet1 and worksheet2 look.:
image.png (2.1 KB)
Current behavior for worksheet 1 after opening the workbook. Rows 3-5 shouldn’t be visible:
image.png (2.3 KB)
Current behavior for worksheet 2 after expanding the outer group. Notice that Rows 2-5 are hidden even though Cells.GroupRows(2, 4, false) was called.
image.png (1.6 KB)
Expected behavior for worksheet3 and worksheet4. The same calls to GroupRows are made, but the order in which they’re called is different:
image.png (1.6 KB)
Current behavior for worksheet3. The inner groups should be collapsed because Cells.GroupRows(6, 8, true) and Cells.GroupRows(2, 4, true) have been called. However, since GroupRows(1, 8, false) comes last the inner groups are not hidden properly:
image.png (2.4 KB)
Current behavior for worksheet4. This is the expected result, and it only works because Cells.GroupRows(1, 8, false) is called first:
image.png (1.6 KB)
@dfactset
Thanks for your screenshots with explanations and using Aspose APIs.
We have logged your provided information in our database against this issue CELLSNET-45555. It will be helpful in resolving this issue. Once, there is some fix or any other news for you, we will let you know asap.
@dfactset
Thanks for using Aspose APIs.
This is to inform you that we have fixed your issue CELLSNET-45555 now. We will soon provide the fix after performing QA and including other enhancements and fixes.
The issues you have found earlier (filed as CELLSNET-45555) have been fixed in latest version of Aspose.Cells for .NET (Download | NuGet).
Hi, I tested out 17.8 and still notice one bug with this that hasn’t been fixed. On Worksheet1, Cells A3, A4, A5 are visible even though worksheet1.Cells.GroupRows(1, 8, true) has been called first. I would think that Worksheet1 should look like worksheet2 at this point since the same exact GroupRows calls are being made, but in a slightly different order.
@dfactset
Please change your code. Because, if the row 1-8 are hidden, you cannot group hidden 3-5 rows in Microsoft Excel.
I’m not sure I follow what you’re saying. Your API is allowing for it right now as I can group CellsA2-A5 and CellsA6-A9. They are properly grouped, the only problem is that CellsA2-A5 are visible even though the outer group should be collapsed. The workbook renders properly if I open it up and manually uncollapse and recollapse the outer group. Are you saying that the order of the GroupRows call do matter then?
@dfactset
Please spare us some more time to look into this issue further. We will update you asap.
@dfactset
Please change your code as
worksheet2.Cells.GroupRows(6, 8, true);
worksheet2.Cells.GroupRows(2, 4, false);
worksheet2.Cells.GroupRows(1, 8, true);
We do not know the behavior of the MS Excel:
worksheet1.Cells.GroupRows(1, 8, true);
worksheet1.Cells.GroupRows(6, 8, true);
worksheet1.Cells.GroupRows(2, 4, false);
So, we do not know how to work.
We are looking into whether we should throw an exception.