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:

Expand and Collapse Groups in Excel with C#

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:

Expand and Collapse Groups in Excel with C#

Full code:

//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");