corelibraries-devexpress-dot-export-dot-xl-dot-ixlsheet-b2d01241.md
Finalizes the group creation after calling the IXlSheet.BeginGroup method.
Namespace : DevExpress.Export.Xl
Assembly : DevExpress.Printing.v25.2.Core.dll
NuGet Package : DevExpress.Printing.Core
void EndGroup()
Sub
Use the IXlSheet.BeginGroup - IXlSheet.EndGroup method pair to group rows and columns in a worksheet.
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.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/excel-export-api-examples
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using(IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// 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
' 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
The following code snippet (auto-collected from DevExpress Examples) contains a reference to the EndGroup() method.
Note
The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.
excel-export-library-generate-sales-report/CS/XLExportExampleSalesReport/Form1.cs#L152
// Finalize the group creation.
sheet.EndGroup();
excel-export-library-generate-sales-report/VB/XLExportExampleSalesReport/Form1.vb#L197
' Finalize the group creation.
sheet.EndGroup()
See Also