Excel Grouping

GemBox.Spreadsheet supports grouping of rows and columns.

In the following example grouping is set both on rows and columns, where rows are expanded and columns are collapsed.

Screenshot

Grouping Screenshot

See the full code below, use RUN EXAMPLE to execute. 

1using System;
2using System.Collections.Generic;
3using System.Text;
4using GemBox.Spreadsheet;
5using GemBox.Spreadsheet.ConditionalFormatting;
6using GemBox.Spreadsheet.PivotTables;
7
8class Sample
9{
10    [STAThread]
11    static void Main(string[] args)
12    {
13        // If using Professional version, put your serial key below.
14        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
15
16        ExcelFile ef = new ExcelFile();
17        ExcelWorksheet ws = ef.Worksheets.Add("Grouping");
18
19        ws.Cells[0].Value = "Cell grouping examples:";
20
21        // Vertical grouping.
22        ws.Cells[2, 0].Value = "GroupA Start";
23        ws.Rows[2].OutlineLevel = 1;
24        ws.Cells[3, 0].Value = "A";
25        ws.Rows[3].OutlineLevel = 1;
26        ws.Cells[4, 1].Value = "GroupB Start";
27        ws.Rows[4].OutlineLevel = 2;
28        ws.Cells[5, 1].Value = "B";
29        ws.Rows[5].OutlineLevel = 2;
30        ws.Cells[6, 1].Value = "GroupB End";
31        ws.Rows[6].OutlineLevel = 2;
32        ws.Cells[7, 0].Value = "GroupA End";
33        ws.Rows[7].OutlineLevel = 1;
34        // Put outline row buttons above groups.
35        ws.ViewOptions.OutlineRowButtonsBelow = false;
36
37        // Horizontal grouping (collapsed).
38        ws.Cells["E2"].Value = "Gr.C Start";
39        ws.Columns["E"].OutlineLevel = 1;
40        ws.Columns["E"].Hidden = true;
41        ws.Cells["F2"].Value = "C";
42        ws.Columns["F"].OutlineLevel = 1;
43        ws.Columns["F"].Hidden = true;
44        ws.Cells["G2"].Value = "Gr.C End";
45        ws.Columns["G"].OutlineLevel = 1;
46        ws.Columns["G"].Hidden = true;
47        ws.Columns["H"].Collapsed = true;
48
49        ef.Save("Grouping.xls");
50    }
51}

1Imports System
2Imports System.Collections.Generic
3Imports System.Text
4Imports GemBox.Spreadsheet
5Imports GemBox.Spreadsheet.ConditionalFormatting
6Imports GemBox.Spreadsheet.PivotTables
7
8Module Samples
9
10    Sub Main()
11
12        ' If using Professional version, put your serial key below.
13        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
14
15        Dim ef As ExcelFile = New ExcelFile
16        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Grouping")
17
18        ws.Cells(0).Value = "Cell grouping examples:"
19
20        ' Vertical grouping.
21        ws.Cells(2, 0).Value = "GroupA Start"
22        ws.Rows(2).OutlineLevel = 1
23        ws.Cells(3, 0).Value = "A"
24        ws.Rows(3).OutlineLevel = 1
25        ws.Cells(4, 1).Value = "GroupB Start"
26        ws.Rows(4).OutlineLevel = 2
27        ws.Cells(5, 1).Value = "B"
28        ws.Rows(5).OutlineLevel = 2
29        ws.Cells(6, 1).Value = "GroupB End"
30        ws.Rows(6).OutlineLevel = 2
31        ws.Cells(7, 0).Value = "GroupA End"
32        ws.Rows(7).OutlineLevel = 1
33        ' Put outline row buttons above groups.
34        ws.ViewOptions.OutlineRowButtonsBelow = False
35
36        ' Horizontal grouping (collapsed).
37        ws.Cells("E2").Value = "Gr.C Start"
38        ws.Columns("E").OutlineLevel = 1
39        ws.Columns("E").Hidden = True
40        ws.Cells("F2").Value = "C"
41        ws.Columns("F").OutlineLevel = 1
42        ws.Columns("F").Hidden = True
43        ws.Cells("G2").Value = "Gr.C End"
44        ws.Columns("G").OutlineLevel = 1
45        ws.Columns("G").Hidden = True
46        ws.Columns("H").Collapsed = True
47
48        ef.Save("Grouping.xls")
49
50    End Sub
51
52End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.