IXlSheet.EndGroup() Method
Finalizes the group creation after calling the IXlSheet.BeginGroup method.
Namespace: DevExpress.Export.Xl
Assembly: DevExpress.Printing.v24.1.Core.dll
NuGet Package: DevExpress.Printing.Core
Declaration
Remarks
Use the IXlSheet.BeginGroup - IXlSheet.EndGroup method pair to group rows and columns in a worksheet.
Example
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);
}
}
}
}
}
Related GitHub Examples
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.
See Also