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:
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