IXlSheet.BeginGroup(Int32, Boolean) Method
Starts creating a new group in the worksheet.
Namespace: DevExpress.Export.Xl
Assembly: DevExpress.Printing.v23.2.Core.dll
NuGet Package: DevExpress.Printing.Core
Declaration
Parameters
| Name | Type | Description |
|---|---|---|
| outlineLevel | Int32 | An integer value specifying the group’s outline level. This value must be between 1 and 7. |
| collapsed | Boolean | true, to collapse the group; otherwise, false. |
Returns
| Type | Description |
|---|---|
| Int32 | An integer value that is the current outline level of the created group. |
Remarks
Use the IXlSheet.BeginGroup - IXlSheet.EndGroup method pair to group rows and columns in a worksheet.
You can place one data group inside another, but the number of nested groups is limited: you can create a maximum of seven levels of grouping.
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);
}
}
}
}
}
See Also