Create Nested Group in Excel in C#, VB.NET

Nested group is a group that contains multiple inner, nested groups. This article demonstrates how to create groups and how to outline the outer and inner groups using Spire.XLS with C# and VB.NET.

Step 1: Create a Workbook instance and get the first worksheet.

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

Step 2: Insert sample data to cells.

sheet.Range["A1"].Value = "Project plan for project X";
sheet.Range["A3"].Value = "Set up";
sheet.Range["A4"].Value = "Task 1";
sheet.Range["A5"].Value = "Task 2";
sheet.Range["A7"].Value = "Launch";
sheet.Range["A8"].Value = "Task 1";
sheet.Range["A9"].Value = "Task 2";

Step 3: Set the IsSummaryRowBelow property as false, which indicates the summary rows appear above detail rows.

sheet.PageSetup.IsSummaryRowBelow = false;

Step 4: Group the rows that you want to group.

sheet.GroupByRows(2, 9, false);
sheet.GroupByRows(4, 5, false);
sheet.GroupByRows(8, 9, false);

Step 5: Save the file.

workbook.SaveToFile("output.xlsx", ExcelVersion.Version2013);

Output:

Create Nested Group in Excel in C#, VB.NET*

Full Code:

[C#]
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

CellStyle style = workbook.Styles.Add("style");
style.Font.Color = Color.CadetBlue;
style.Font.IsBold = true;

sheet.PageSetup.IsSummaryRowBelow= false;
sheet.Range["A1"].Value = "Project plan for project X";
sheet.Range["A1"].CellStyleName = style.Name;

sheet.Range["A3"].Value = "Set up";
sheet.Range["A3"].CellStyleName = style.Name;
sheet.Range["A4"].Value = "Task 1";
sheet.Range["A5"].Value = "Task 2";
sheet.Range["A4:A5"].BorderAround(LineStyleType.Thin);
sheet.Range["A4:A5"].BorderInside(LineStyleType.Thin);

sheet.Range["A7"].Value = "Launch";
sheet.Range["A7"].CellStyleName = style.Name;
sheet.Range["A8"].Value = "Task 1";
sheet.Range["A9"].Value = "Task 2";
sheet.Range["A8:A9"].BorderAround(LineStyleType.Thin);
sheet.Range["A8:A9"].BorderInside(LineStyleType.Thin);

sheet.GroupByRows(2, 9, false);
sheet.GroupByRows(4, 5, false);
sheet.GroupByRows(8, 9, false);
workbook.SaveToFile("output.xlsx", ExcelVersion.Version2013);
[VB.NET]
Dim workbook As New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)

Dim style As CellStyle = workbook.Styles.Add("style")
style.Font.Color = Color.CadetBlue
style.Font.IsBold = True

sheet.PageSetup.IsSummaryRowBelow = False
sheet.Range("A1").Value = "Project plan for project X"
sheet.Range("A1").CellStyleName = style.Name

sheet.Range("A3").Value = "Set up"
sheet.Range("A3").CellStyleName = style.Name
sheet.Range("A4").Value = "Task 1"
sheet.Range("A5").Value = "Task 2"
sheet.Range("A4:A5").BorderAround(LineStyleType.Thin)
sheet.Range("A4:A5").BorderInside(LineStyleType.Thin)

sheet.Range("A7").Value = "Launch"
sheet.Range("A7").CellStyleName = style.Name
sheet.Range("A8").Value = "Task 1"
sheet.Range("A9").Value = "Task 2"
sheet.Range("A8:A9").BorderAround(LineStyleType.Thin)
sheet.Range("A8:A9").BorderInside(LineStyleType.Thin)

sheet.GroupByRows(2, 9, False)
sheet.GroupByRows(4, 5, False)
sheet.GroupByRows(8, 9, False)
workbook.SaveToFile("output.xlsx", ExcelVersion.Version2013)