Hello,
I am trying to programatically collapse or expand multiple levels of groups.
In Excel file provided, I have 2 groups, on 2 levels.
First group on level 1 which ends on column index 7
Second group on level 2 which ends on column index 4
groups.png (1.3 KB)
By running the following code, I would expect the followin in the saved.xlsx file.
If I expand the first level group, the second level group should still be collapsed. However, it is expanded. Am I doing something wrong or io this a bug?
void Main()
{
var folder = @"PATH_TO_FILE";
var data = File.ReadAllBytes(@$"{folder}\groups.xlsx");
var workbook = data.ToWorkbook();
var worksheet = workbook.Worksheets["Sheet1"];
worksheet.Cells.HideGroupDetail(false, 4);
worksheet.Cells.HideGroupDetail(false, 7);
workbook.Save(@$"{folder}\saved.xlsx", SaveFormat.Xlsx);
}
static class ByteArrayAsposeExtensions
{
public static Workbook ToWorkbook(this byte[] workbookData)
{
var ms = new MemoryStream();
ms.Write(workbookData, 0, workbookData.Length);
ms.Position = 0;
var workbook = new Workbook(ms);
return workbook;
}
}
I am using Aspose.Cells for .NET 21.11.0
Thank you!
groups.zip (6.1 KB)
@kirederf81
1,If you only wanto to collapse the second level group, please simply use the following codes:
var workbook = new Workbook(dir + @“groups.xlsx”);
var worksheet = workbook.Worksheets[“Sheet1”];
worksheet.Cells.HideGroupDetail(false, 4);
workbook.Save(dir + @“dest.xlsx”);
That is not what I’m trying to accomplish.
I want to collapse both groups.
But when I open the saved excel file, if I expand the first level group, I expect the second level group to still be collapsed, but that isn’t the case.
@kirederf81,
Could you please zip and share your expected Excel file, we will look into it soon.
AsposeHideShowGroupDetail.zip (5.8 MB)
Hello,
See the attached zip file which contains a .NET 5 console app.
Included in the Templates folder are 3 Excel files.
- groups.xlsx : Contains 2 expanded groups.
- Level 1 group that spans from A to H.
- Level 2 group that spans from A to E
- groupsSavedManually.xlsx : To get this file I opened groups.xlsx, manually collapsed both groups and saved.
- groupsSavedWithAspose (kirederf81 execution).xlsx : A copy of the execution result of my console app
I would expect the savedManually and the savedWithAspose files to have the save behavior, but they don’t.
If you open the savedManually file and expand the first level group, the second level group is still collapsed, which is the expected result.
If you open the savedWithAspose file and expand the first level group, the second level group is also expanded and it should not.
Thanks
@kirederf81,
Thanks for the sample project with sample files.
I understand your issue now. I found different behavior with Aspose.Cells generated file regarding collapse and expand multi level groups. I played upon with different Aspose.Cells APIs to try to mimic the resultant behavior of the output file but to no avail. I have logged an investigation ticket with an id “CELLSNET-50038” for your issue. We will look into it soon.
Once we have an update on it, we will let you know.
I was doing more tests and I realized that ShowGroupDetail and HideGroupDetail with IsVertical set to true (for row groups) do not work at all.
So far I had only been testing with column groups
@kirederf81,
Thanks for sharing more findings.
We will evaluate row groups as well.
I see the status was changed to Resolved.
Can I have access to a dll to test it?
@kirederf81,
Yes, your issue is resolved now. We will provide you the fix in the next few days (3-5 days or so) after performing QA and incorporating other enhancements and fixes.
Hello Simon,
I’ve tested the new dll. The problem seems to be fixed for columns, but it still isn’t working for rows.
The following image is the result of my code trying to collapse row 5 and 8.
image.png (1.2 KB)
I’ve adapted my console app to collapse rows so you can reproduce the output.
AsposeHideShowGroupDetail_rows.zip (6.1 MB)
My console app only tests collapsing rows, but expanding rows does not work as well.
May I suggest you expose meaningful separate methods for rows and columns instead of a parameter “IsVertical”?
Thanks!
@kirederf81,
The issue is reproduced using your new sample. I have logged a separate ticket with an id “CELLSNET-50065” for the issue. We will investigate and look into it soon.
Once we have an update on it, we will let you know.
Hello Simon,
I’ve tested the latest fix and it seems to be working for columns and rows.
When can I expect the fix to be released on nuget?
Thanks!
@kirederf81,
Good to know that your issue is resolved by the new fix. You may use this fix as an official release, it can be used on production server (it has all the features/enhancements of previous version (21.12) and fixes (full fledged). However, if you still want this fix to be downloaded from Nuget/Maven, you may wait at least 2/3 weeks. Our next official release Aspose.Cells for .NET v22.1 is expected in the second week of January, 2022. Please note, only official release are uploaded to Downloads section or Nuget repos.
Thanks for your quick response and fixes. We are nowhere near production ready with our app, but I may use to fix temporarily to close the story blocked by this issue.
Have a nice day!
@kirederf81,
Sure, you may use the fix for the time being.
AsposeHideShowGroupDetail.zip (6.3 MB)
Hello,
Please check this test sample for a new issue.
Sheet1: Saved in excel with simple group rows/columns collapsed, then expand the group with aspose. The sheets seems in a corrupted state, it shows the minus icon while still collapsed.
Sheet2: The code open the sheet2, add the same groups as in Sheet1, collapse then expand them. Work as intented.
The issue seems to appear when aspose load a sheet with existing collapsed groups, the state in memory might be incorrect.
Thanks!