Can't apply CollapseChildren() after using Group()

[hansgfixed]

EPPlus usage

Noncommercial use

Environment

Windows

Epplus version

7.1.2

Spreadsheet application

Excel

Description

Seems I either can't figure out to properly apply CollapseChildren() or there's a bug. CollapsingChildren() has no effect at all, doesn't matter if I provide startRow or the whole range.

Affected code snipped:

        private static void ExportComponentsRecursiveToExcel(Component component, ExcelWorksheet worksheet, ref int row, bool isRoot)
        {
            int startRow = row;
            ExportComponentToExcel(component, worksheet, row);
            row++;

            if (component.IsComposite)
            {
                for (int i = 0; i < component.Children.Count; i++)
                {
                    var child = component.Children[i];
                    ExportComponentsRecursiveToExcel(child, worksheet, ref row, false);
                }

                worksheet.Rows[startRow + 1, row - 1].Group();
                if (!isRoot)
                {
                    worksheet.Rows[startRow].CollapseChildren();
                    // OR
                    worksheet.Cells[startRow + 1, 1, row - 1, 1].EntireRow.CollapseChildren();
                    // OR
                    worksheet.Cells[$"A{startRow}"].EntireRow.CollapseChildren();
                }
            }
        }

The documentation shows a sample by using Cells which doesn't work for me either. There's only one stackoverflow question in regard to this issue and it doesn't solve it.

Can someone help me out please?

[AdrianEPPlus]

Could you please attach a file you are having this issue with.

[hansgfixed]

@AdrianEPPlus
Hi Adrian, I attached the class file as txt. Thanks in advance for your analysis.
DataExportHelper.txt

[AdrianEPPlus]

Sorry for not being clear. I meant an excel workbook that replicates the issue you are having.

[hansgfixed]

@AdrianEPPlus
No worries. I expect the grouped rows being collapsed (hidden) like if I do it manually in the Excel:

DependencyReport_23-06-2024_16_51.xlsx

[AdrianEPPlus]

You can set worksheet.Workbook._package.Settings.ApplyFiltersOnSave to false and it should work. Also set the range of the rows you wish to collapse and set the start row to be the row before.

Example

    worksheet.Rows[10, 15].Group(); //Group rows 10 - 15
    worksheet.Rows[9, 15].CollapseChildren(); //Collapse rows 10 - 15

This is an issue where we apply autofilter on the tables when we save which in turn sets the hidden property on the row to false. We should look into making this more user friendly in the future.

[hansgfixed]

@AdrianEPPlus
Thank you, Adrian! This worked out for me. Key was to ApplyFiltersOnSave() to false.

[AdrianEPPlus]

Glad it worked out for you. I will leave this issue open as a reminder for us to fix this issue in the future.