IXlSheet.EndGroup() Method

Finalizes the group creation after calling the IXlSheet.BeginGroup method.

Namespace: DevExpress.Export.Xl

Assembly: DevExpress.Printing.v21.1.Core.dll

Declaration

void EndGroup()

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.

// 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