CollapseChildren() incompatable with Cells.Autofilter

[s-bresler]

EPPlus usage

Noncommercial use

Environment

Windows

Epplus version

7.4.2

Spreadsheet application

Excel

Description

CollapseChildren seems to be incompatable with having an AutoFilter.

I can't create an excel file with both auto filters on the top row and collapsed groupings. The groupings only collapse when autofilter is set to false.

This issue may be linked to #1499

See code below:

using OfficeOpenXml;

public static class Report
{

    public static List<string> headers = new List<string> { "DBA", "Address" };
    public static List<string> subHeaders = new List<string> { "Name", "Date" };
    public static List<string> subSubHeaders = new List<string> { "Order Number", "Price" };

    public static List<Company> companies = new List<Company>
{
    new Company
    {
        Name = "Company A", Address="4 Fairgold Way",
        Employees = new List<Employee>
        {
            new Employee
            {
                Name = "Alice", BirthDate = new DateTime(1990, 1, 1),
                Orders = new List<Order>
                {
                    new Order { OrderNumber = "A001-001", Price = 100.50m },
                    new Order { OrderNumber = "A001-002", Price = 200.75m },
                    new Order { OrderNumber = "A001-003", Price = 150.00m },
                    new Order { OrderNumber = "A001-004", Price = 175.25m },
                    new Order { OrderNumber = "A001-005", Price = 225.80m }
                }
            },
            new Employee
            {
                Name = "Bob", BirthDate = new DateTime(1985, 2, 15),
                Orders = new List<Order>
                {
                    new Order { OrderNumber = "A002-001", Price = 105.30m },
                    new Order { OrderNumber = "A002-002", Price = 210.20m },
                    new Order { OrderNumber = "A002-003", Price = 140.00m },
                    new Order { OrderNumber = "A002-004", Price = 160.75m },
                    new Order { OrderNumber = "A002-005", Price = 250.60m }
                }
            },
            new Employee
            {
                Name = "Charlie", BirthDate = new DateTime(1992, 3, 30),
                Orders = new List<Order>
                {
                    new Order { OrderNumber = "A003-001", Price = 120.10m },
                    new Order { OrderNumber = "A003-002", Price = 230.90m },
                    new Order { OrderNumber = "A003-003", Price = 170.30m },
                    new Order { OrderNumber = "A003-004", Price = 155.00m },
                    new Order { OrderNumber = "A003-005", Price = 260.40m }
                }
            },
            new Employee
            {
                Name = "Diana", BirthDate = new DateTime(1988, 4, 10),
                Orders = new List<Order>
                {
                    new Order { OrderNumber = "A004-001", Price = 130.00m },
                    new Order { OrderNumber = "A004-002", Price = 245.00m },
                    new Order { OrderNumber = "A004-003", Price = 165.25m },
                    new Order { OrderNumber = "A004-004", Price = 180.80m },
                    new Order { OrderNumber = "A004-005", Price = 275.20m }
                }
            },
            new Employee
            {
                Name = "Edward", BirthDate = new DateTime(1995, 5, 20),
                Orders = new List<Order>
                {
                    new Order { OrderNumber = "A005-001", Price = 110.45m },
                    new Order { OrderNumber = "A005-002", Price = 220.55m },
                    new Order { OrderNumber = "A005-003", Price = 160.15m },
                    new Order { OrderNumber = "A005-004", Price = 170.60m },
                    new Order { OrderNumber = "A005-005", Price = 240.75m }
                }
            }
        }
    },
    new Company
    {
        Name = "Company B", Address="388 52nd St",
        Employees = new List<Employee>
        {
            new Employee
            {
                Name = "Fiona", BirthDate = new DateTime(1991, 6, 25),
                Orders = new List<Order>
                {
                    new Order { OrderNumber = "B001-001", Price = 115.30m },
                    new Order { OrderNumber = "B001-002", Price = 205.75m },
                    new Order { OrderNumber = "B001-003", Price = 155.50m },
                    new Order { OrderNumber = "B001-004", Price = 175.00m },
                    new Order { OrderNumber = "B001-005", Price = 215.90m }
                }
            },
            new Employee
            {
                Name = "George", BirthDate = new DateTime(1987, 7, 14),
                Orders = new List<Order>
                {
                    new Order { OrderNumber = "B002-001", Price = 105.00m },
                    new Order { OrderNumber = "B002-002", Price = 220.40m },
                    new Order { OrderNumber = "B002-003", Price = 135.60m },
                    new Order { OrderNumber = "B002-004", Price = 195.30m },
                    new Order { OrderNumber = "B002-005", Price = 240.25m }
                }
            },
            new Employee
            {
                Name = "Hannah", BirthDate = new DateTime(1993, 8, 5),
                Orders = new List<Order>
                {
                    new Order { OrderNumber = "B003-001", Price = 125.50m },
                    new Order { OrderNumber = "B003-002", Price = 210.00m },
                    new Order { OrderNumber = "B003-003", Price = 145.20m },
                    new Order { OrderNumber = "B003-004", Price = 160.00m },
                    new Order { OrderNumber = "B003-005", Price = 230.75m }
                }
            },
            new Employee
            {
                Name = "Ian", BirthDate = new DateTime(1986, 9, 12),
                Orders = new List<Order>
                {
                    new Order { OrderNumber = "B004-001", Price = 140.40m },
                    new Order { OrderNumber = "B004-002", Price = 225.60m },
                    new Order { OrderNumber = "B004-003", Price = 160.80m },
                    new Order { OrderNumber = "B004-004", Price = 185.90m },
                    new Order { OrderNumber = "B004-005", Price = 270.30m }
                }
            },
            new Employee
            {
                Name = "Jane", BirthDate = new DateTime(1994, 10, 18),
                Orders = new List<Order>
                {
                    new Order { OrderNumber = "B005-001", Price = 115.50m },
                    new Order { OrderNumber = "B005-002", Price = 235.75m },
                    new Order { OrderNumber = "B005-003", Price = 175.40m },
                    new Order { OrderNumber = "B005-004", Price = 160.60m },
                    new Order { OrderNumber = "B005-005", Price = 255.80m }
                }
            }
        }
    }
    };

    public static void CreateSheet()
    {

        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

        using (ExcelPackage package = new ExcelPackage())
        {
            ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("Employees");
            int row = 1;
            row = CreateHeaders(workSheet, row, headers, 0);
            foreach (var company in companies)
            {
                int startRow = row;
                workSheet.Cells[row, 1].Value = company.Name;
                workSheet.Cells[row, 2].Value = company.Address;
                workSheet.Row(row).OutlineLevel = 0;
                row++;

                row = CreateHeaders(workSheet, row, subHeaders, 1);
                foreach (Employee employee in company.Employees)
                {
                    workSheet.Cells[row, 1].Value = employee.Name;
                    workSheet.Cells[row, 2].Value = employee.BirthDate;
                    workSheet.Row(row).OutlineLevel = 1;
                    row++;
                    row = CreateHeaders(workSheet, row, subSubHeaders, 2);
                    foreach (var order in employee.Orders)
                    {
                        workSheet.Cells[row, 1].Value = order.OrderNumber;
                        workSheet.Cells[row, 2].Value = order.Price;
                        workSheet.Row(row).OutlineLevel = 2;
                        row++;
                    }
                }
                workSheet.Rows[startRow, row].CollapseChildren(true);
            }

            workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();

            workSheet.Cells[1, 1, row, 3].AutoFilter = true; //When this line is commented out the groupings collapse

            string path = @$"C:\ReportsPath\" + $"Report_Test_{DateTime.Now.ToString("yyyy_MM_dd hh_mm_ss_tt")}.xlsx";

            package.SaveAs(new FileInfo(path));

        }
    }
    static int CreateHeaders(ExcelWorksheet ws, int row, List<string> headers, int outlineLevel = 1)
    {
        for (int i = 1; i <= headers.Count(); i++)
        {
            ws.Cells[row, i].Value = headers[i - 1];
        }
        ws.Row(row).Style.Font.Bold = true;
        ws.Row(row).OutlineLevel = outlineLevel;

        return ++row;
    }

    public class Company
    {
        public string? Name { get; set; }
        public string? Address { get; set; }
        public List<Employee>? Employees { get; set; }

    }

    public class Employee
    {
        public string? Name { get; set; }
        public DateTime BirthDate { get; set; }
        public List<Order> Orders { get; set; }
    }

    public class Order
    {
        public string OrderNumber { get; set; }
        public decimal Price { get; set; }
    }
}

[AdrianEPPlus]

Yes, this is a known issue that we have on our to do list. Just as I described in the other issue you linked, this happens because when we save the workbook and apply the filter, the hidden property on the row is set to false. This should of course be fixed in a future version. For now it appears that by setting worksheet.Workbook._package.Settings.ApplyFiltersOnSave to false should give you the behavior you expect.

[s-bresler]

Ok thanks. It is working in the example code now after I tweaked the line a bit to package.Settings.ApplyFiltersOnSave = false;