Expand and Collapse Groups in Excel with C#

When we group rows or columns in Excel, we can use the expand/collapse button to show/hide the grouped data. This article demonstrates how to expand and collapse the groups in an Excel file programmatically using Spire.XLS.

Below screenshot shows the sample Excel file with rows 3 and 4 grouped:

Detail steps:

Step 1: Create a Workbook instance and load the Excel file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Input.xlsx");

Step 2: Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Expand or Collapse the grouped rows with ExpandCollapseFlags set to expand parent.

//Expand group
sheet.Range["A3:B4"].ExpandGroup(GroupByType.ByRows, ExpandCollapseFlags.ExpandParent);

//Collapse group
//sheet.Range["A3:B4"].CollapseGroup(GroupByType.ByRows);

Step 4: Save the file.

workbook.SaveToFile("Output.xlsx");

The screenshot after running the project:

Full code:

using Spire.Xls;
namespace ExpandandCollapseGroups
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create Workbook instance
            Workbook workbook = new Workbook();
            //Load the Excel file
            workbook.LoadFromFile("Input.xlsx");

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Expand the grouped rows with ExpandCollapseFlags set to expand parent
            sheet.Range["A3:B4"].ExpandGroup(GroupByType.ByRows, ExpandCollapseFlags.ExpandParent);

            //Collapse the grouped rows
            //sheet.Range["A3:B4"].CollapseGroup(GroupByType.ByRows);

            //Save the file
            workbook.SaveToFile("Output.xlsx");
        }
    }
}

Expand and Collapse Groups in Excel with C#

See Also