IXlRow.BeginTable(IEnumerable<XlTableColumnInfo>, Boolean, XlCellFormatting) Method

Starts generating a table in the worksheet.

Namespace: DevExpress.Export.Xl

Assembly: DevExpress.Printing.v24.2.Core.dll

Declaration

C#

IXlTable BeginTable(
    IEnumerable<XlTableColumnInfo> columns,
    bool hasHeaderRow,
    XlCellFormatting headerRowFormatting
)

VB.NET

Function BeginTable(
    columns As IEnumerable(Of XlTableColumnInfo),
    hasHeaderRow As Boolean,
    headerRowFormatting As XlCellFormatting
) As IXlTable

Parameters

Name	Type	Description
columns	IEnumerable<XlTableColumnInfo>	A list of the XlTableColumnInfo objects containing the unique names of table columns and their formatting settings.
hasHeaderRow	Boolean	A Boolean value indicating whether the header row is displayed for the table.
headerRowFormatting	XlCellFormatting	An XlCellFormatting object that specifies format settings for the header row of the table.

Returns

Type	Description
IXlTable	An IXlTable object representing the generated table.

Remarks

Use the IXlRow.BeginTable - IXlRow.EndTable method pair to format a cell range containing related data as a table. For details on how to generate a table and adjust its properties, refer to the Tables section for examples.

Example

Note

A complete sample project is available at https://github.com/DevExpress-Examples/excel-export-api-examples

TableActions.cs

// Create the first row in the worksheet from which the table starts.
using (IXlRow row = sheet.CreateRow())
{

    XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";

    // Create objects containing information about table columns (their names and formatting).
    List<XlTableColumnInfo> columns = new List<XlTableColumnInfo>();
    columns.Add(new XlTableColumnInfo("Product"));
    columns.Add(new XlTableColumnInfo("Category"));
    columns.Add(new XlTableColumnInfo("Amount"));

    // Specify formatting settings for the last column of the table.
    columns[2].HeaderRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent6, -0.3));
    columns[2].DataFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.9));
    columns[2].DataFormatting.NumberFormat = accounting;
    columns[2].TotalRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.8));
    columns[2].TotalRowFormatting.NumberFormat = accounting;

    // Specify formatting settings for the header row of the table.
    XlCellFormatting headerRowFormatting = new XlCellFormatting();
    headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent6, 0.0));
    headerRowFormatting.Border = new XlBorder();
    headerRowFormatting.Border.BottomColor = XlColor.FromArgb(0, 0, 0);
    headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Dashed;

    // Start generating the table with a header row displayed.
    IXlTable table = row.BeginTable(columns, true, headerRowFormatting);
    // Apply the table style.
    table.Style.Name = XlBuiltInTableStyleId.Medium16;
    // Disable banded row formatting for the table.
    table.Style.ShowRowStripes = false;
    // Disable the filtering functionality for the table. 
    table.HasAutoFilter = false;

    // Specify formatting settings for the total row of the table.
    table.TotalRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.9));
    table.TotalRowFormatting.Border = new XlBorder()
    {
        BottomColor = XlColor.FromTheme(XlThemeColor.Accent6, 0.0),
        BottomLineStyle = XlBorderLineStyle.Thick,
        TopColor = XlColor.FromArgb(0, 0, 0),
        TopLineStyle = XlBorderLineStyle.Dashed
    };

    // Specify the total row label.
    table.Columns[0].TotalRowLabel = "Total";
    // Specify the function to calculate the total.
    table.Columns[2].TotalRowFunction = XlTotalRowFunction.Sum;
}

// Generate table rows and populate them with data.
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Camembert Pierrot", "Dairy Products", 17000 }, null);
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, null);
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Mascarpone Fabioli", "Dairy Products", 15000 }, null);
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);

// Create the total row and finish the table.
using (IXlRow row = sheet.CreateRow())
    row.EndTable(sheet.Tables[0], true);

TableActions.vb

' Create the first row in the worksheet from which the table starts.
Using row As IXlRow = sheet.CreateRow()

    Dim accounting As XlNumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"

    ' Create objects containing information about table columns (their names and formatting).
    Dim columns As New List(Of XlTableColumnInfo)()
    columns.Add(New XlTableColumnInfo("Product"))
    columns.Add(New XlTableColumnInfo("Category"))
    columns.Add(New XlTableColumnInfo("Amount"))

    ' Specify formatting settings for the last column of the table.
    columns(2).HeaderRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent6, -0.3))
    columns(2).DataFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.9))
    columns(2).DataFormatting.NumberFormat = accounting
    columns(2).TotalRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.8))
    columns(2).TotalRowFormatting.NumberFormat = accounting

    ' Specify formatting settings for the header row of the table.
    Dim headerRowFormatting As New XlCellFormatting()
    headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent6, 0.0))
    headerRowFormatting.Border = New XlBorder()
    headerRowFormatting.Border.BottomColor = XlColor.FromArgb(0, 0, 0)
    headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Dashed

    ' Start generating the table with a header row displayed.
    Dim table As IXlTable = row.BeginTable(columns, True, headerRowFormatting)
    ' Apply the table style.
    table.Style.Name = XlBuiltInTableStyleId.Medium16
    ' Disable banded row formatting for the table.
    table.Style.ShowRowStripes = False
    ' Disable the filtering functionality for the table. 
    table.HasAutoFilter = False

    ' Specify formatting settings for the total row of the table.
    table.TotalRowFormatting = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Dark1, 0.9))
    table.TotalRowFormatting.Border = New XlBorder() With {.BottomColor = XlColor.FromTheme(XlThemeColor.Accent6, 0.0), .BottomLineStyle = XlBorderLineStyle.Thick, .TopColor = XlColor.FromArgb(0, 0, 0), .TopLineStyle = XlBorderLineStyle.Dashed}

    ' Specify the total row label.
    table.Columns(0).TotalRowLabel = "Total"
    ' Specify the function to calculate the total.
    table.Columns(2).TotalRowFunction = XlTotalRowFunction.Sum
End Using

' Generate table rows and populate them with data.
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Camembert Pierrot", "Dairy Products", 17000 }, Nothing)
End Using
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, Nothing)
End Using
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Mascarpone Fabioli", "Dairy Products", 15000 }, Nothing)
End Using
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Ravioli Angelo", "Grains/Cereals", 12500 }, Nothing)
End Using

' Create the total row and finish the table.
Using row As IXlRow = sheet.CreateRow()
    row.EndTable(sheet.Tables(0), True)
End Using