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