officefileapi-117098-excel-export-library-grouping.md
The following example demonstrates how to group worksheet data.
To group rows or columns, call the IXlSheet.BeginGroup method to start grouping, create the necessary number of rows/columns and finalize it by calling the IXlSheet.EndGroup method.
To create nested groups, call the IXlSheet.BeginGroup - IXlSheet.EndGroup paired methods inside another pair, as illustrated in the code snippet below. If necessary, pass the outline level number as the IXlSheet.BeginGroup method parameter.
The grouping direction can be specified by setting the IXlOutlineProperties.SummaryBelow and IXlOutlineProperties.SummaryRight properties.
In this code snippet, one level of grouped columns and two levels of grouped rows are created. After the outer group of rows is created, the IXlSheet.BeginGroup method is called again to create the inner group of rows. Note that both methods are enclosed with paired IXlSheet.EndGroup methods.
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using(IXlDocument document = exporter.CreateDocument(stream))
{
document.Options.Culture = CultureInfo.CurrentCulture;
using (FileStream stream = new FileStream("Document.xlsx", FileMode.Create))
{
// Create a worksheet.
using(IXlSheet sheet = document.CreateSheet())
{
// Specify the summary row and summary column location for the grouped data.
sheet.OutlineProperties.SummaryBelow = true;
sheet.OutlineProperties.SummaryRight = true;
// Create the column "A" and set its width.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 200;
}
// Begin to group worksheet columns starting from the column "B" to the column "E".
sheet.BeginGroup(false);
// Create four successive columns ("B", "C", "D" and "E") and set the specific number format for their cells.
for (int i = 0; i < 4; i++) {
using(IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
}
// Finalize the group creation.
sheet.EndGroup();
// Create the column "F", adjust its width and set the specific number format for its cells.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 100;
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
// Specify formatting settings for cells containing data.
XlCellFormatting rowFormatting = new XlCellFormatting();
rowFormatting.Font = XlFont.BodyFont();
rowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light1, 0.0));
// Specify formatting settings for the header rows.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.Font = XlFont.BodyFont();
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0));
// Specify formatting settings for the total rows.
XlCellFormatting totalRowFormatting = new XlCellFormatting();
totalRowFormatting.Font = XlFont.BodyFont();
totalRowFormatting.Font.Bold = true;
totalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0));
// Specify formatting settings for the grand total row.
XlCellFormatting grandTotalRowFormatting = new XlCellFormatting();
grandTotalRowFormatting.Font = XlFont.BodyFont();
grandTotalRowFormatting.Font.Bold = true;
grandTotalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, -0.2));
// Generate data for the document.
Random random = new Random();
string[] products = new string[] { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" };
// Begin to group worksheet rows (create the outer group of rows).
sheet.BeginGroup(false);
for(int p = 0; p < 2; p++)
{
// Generate the header row.
using(IXlRow row = sheet.CreateRow())
{
using(IXlCell cell = row.CreateCell())
{
cell.Value = (p == 0) ? "East" : "West";
cell.ApplyFormatting(headerRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
}
for(int i = 0; i < 4; i++)
{
using(IXlCell cell = row.CreateCell())
{
cell.Value = string.Format("Q{0}", i + 1);
cell.ApplyFormatting(headerRowFormatting);
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom));
}
}
using(IXlCell cell = row.CreateCell())
{
cell.Value = "Yearly total";
cell.ApplyFormatting(headerRowFormatting);
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom));
}
}
// Create and group data rows (create the inner group of rows containing sales data for the specific region).
sheet.BeginGroup(false);
for(int i = 0; i < 4; i++)
{
using(IXlRow row = sheet.CreateRow())
{
using(IXlCell cell = row.CreateCell())
{
cell.Value = products[i];
cell.ApplyFormatting(rowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.8));
}
for(int j = 0; j < 4; j++) {
using(IXlCell cell = row.CreateCell())
{
cell.Value = Math.Round(random.NextDouble() * 2000 + 3000);
cell.ApplyFormatting(rowFormatting);
}
}
using(IXlCell cell = row.CreateCell())
{
cell.SetFormula(XlFunc.Sum(XlCellRange.FromLTRB(1, row.RowIndex, 4, row.RowIndex)));
cell.ApplyFormatting(rowFormatting);
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0)));
}
}
}
// Finalize the group creation.
sheet.EndGroup();
// Create the total row.
using(IXlRow row = sheet.CreateRow())
{
using(IXlCell cell = row.CreateCell())
{
cell.Value = "Total";
cell.ApplyFormatting(totalRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6));
}
for(int j = 0; j < 5; j++)
{
using(IXlCell cell = row.CreateCell())
{
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, row.RowIndex - 4, j + 1, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
}
}
}
}
// Finalize the group creation.
sheet.EndGroup();
// Create the grand total row.
using(IXlRow row = sheet.CreateRow())
{
using(IXlCell cell = row.CreateCell())
{
cell.Value = "Grand total";
cell.ApplyFormatting(grandTotalRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.4));
}
for(int j = 0; j < 5; j++) {
using(IXlCell cell = row.CreateCell())
{
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, 1, j + 1, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(grandTotalRowFormatting);
}
}
}
}
}
}
' Create an exporter instance.
Dim exporter As IXlExporter = XlExport.CreateExporter(documentFormat)
' Create a new document.
Using document As IXlDocument = exporter.CreateDocument(stream)
document.Options.Culture = CultureInfo.CurrentCulture
Using stream As New FileStream("Document.xlsx", FileMode.Create)
' Create a worksheet.
Using sheet As IXlSheet = document.CreateSheet()
' Specify the summary row and summary column location for the grouped data.
sheet.OutlineProperties.SummaryBelow = True
sheet.OutlineProperties.SummaryRight = True
' Create the column "A" and set its width.
Using column As IXlColumn = sheet.CreateColumn()
column.WidthInPixels = 200
End Using
' Begin to group worksheet columns starting from the column "B" to the column "E".
sheet.BeginGroup(False)
' Create four successive columns ("B", "C", "D" and "E") and set the specific number format for their cells.
For i As Integer = 0 To 3
Using column As IXlColumn = sheet.CreateColumn()
column.WidthInPixels = 100
column.Formatting = New XlCellFormatting()
column.Formatting.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"
End Using
Next i
' Finalize the group creation.
sheet.EndGroup()
' Create the column "F", adjust its width and set the specific number format for its cells.
Using column As IXlColumn = sheet.CreateColumn()
column.WidthInPixels = 100
column.Formatting = New XlCellFormatting()
column.Formatting.NumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"
End Using
' Specify formatting settings for cells containing data.
Dim rowFormatting As New XlCellFormatting()
rowFormatting.Font = XlFont.BodyFont()
rowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light1, 0.0))
' Specify formatting settings for the header rows.
Dim headerRowFormatting As New XlCellFormatting()
headerRowFormatting.Font = XlFont.BodyFont()
headerRowFormatting.Font.Bold = True
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0)
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent1, 0.0))
' Specify formatting settings for the total rows.
Dim totalRowFormatting As New XlCellFormatting()
totalRowFormatting.Font = XlFont.BodyFont()
totalRowFormatting.Font.Bold = True
totalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0))
' Specify formatting settings for the grand total row.
Dim grandTotalRowFormatting As New XlCellFormatting()
grandTotalRowFormatting.Font = XlFont.BodyFont()
grandTotalRowFormatting.Font.Bold = True
grandTotalRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, -0.2))
' Generate data for the document.
Dim random As New Random()
Dim products() As String = { "Camembert Pierrot", "Gorgonzola Telino", "Mascarpone Fabioli", "Mozzarella di Giovanni" }
' Begin to group worksheet rows (create the outer group of rows).
sheet.BeginGroup(False)
For p As Integer = 0 To 1
' Generate the header row.
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
cell.Value = If(p = 0, "East", "West")
cell.ApplyFormatting(headerRowFormatting)
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0))
End Using
For i As Integer = 0 To 3
Using cell As IXlCell = row.CreateCell()
cell.Value = String.Format("Q{0}", i + 1)
cell.ApplyFormatting(headerRowFormatting)
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom))
End Using
Next i
Using cell As IXlCell = row.CreateCell()
cell.Value = "Yearly total"
cell.ApplyFormatting(headerRowFormatting)
cell.ApplyFormatting(XlCellAlignment.FromHV(XlHorizontalAlignment.Right, XlVerticalAlignment.Bottom))
End Using
End Using
' Create and group data rows (create the inner group of rows containing sales data for the specific region).
sheet.BeginGroup(False)
For i As Integer = 0 To 3
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
cell.Value = products(i)
cell.ApplyFormatting(rowFormatting)
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.8))
End Using
For j As Integer = 0 To 3
Using cell As IXlCell = row.CreateCell()
cell.Value = Math.Round(random.NextDouble() * 2000 + 3000)
cell.ApplyFormatting(rowFormatting)
End Using
Next j
Using cell As IXlCell = row.CreateCell()
cell.SetFormula(XlFunc.Sum(XlCellRange.FromLTRB(1, row.RowIndex, 4, row.RowIndex)))
cell.ApplyFormatting(rowFormatting)
cell.ApplyFormatting(XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Light2, 0.0)))
End Using
End Using
Next i
' Finalize the group creation.
sheet.EndGroup()
' Create the total row.
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
cell.Value = "Total"
cell.ApplyFormatting(totalRowFormatting)
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6))
End Using
For j As Integer = 0 To 4
Using cell As IXlCell = row.CreateCell()
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, row.RowIndex - 4, j + 1, row.RowIndex - 1), XlSummary.Sum, False))
cell.ApplyFormatting(totalRowFormatting)
End Using
Next j
End Using
Next p
' Finalize the group creation.
sheet.EndGroup()
' Create the grand total row.
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
cell.Value = "Grand total"
cell.ApplyFormatting(grandTotalRowFormatting)
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.4))
End Using
For j As Integer = 0 To 4
Using cell As IXlCell = row.CreateCell()
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, 1, j + 1, row.RowIndex - 1), XlSummary.Sum, False))
cell.ApplyFormatting(grandTotalRowFormatting)
End Using
Next j
End Using
End Using
End Using
End Using
The following image illustrates the grouped worksheet data.