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#]

using Spire.Xls;
using System.Drawing;
namespace CreateNestedGroup
{
    class Program
    {
        static void Main(string[] args)
        {
            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]

Imports Spire.Xls
Imports System.Drawing
Namespace CreateNestedGroup
	Class Program
		Private Shared Sub Main(args As String())
			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)
		End Sub
	End Class
End Namespace

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

See Also