IXlTable Interface

Represents a table in a worksheet.

Namespace: DevExpress.Export.Xl

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

NuGet Package: DevExpress.Printing.Core

Declaration

C#

public interface IXlTable

VB.NET

Public Interface IXlTable

Related API Members

The following members return IXlTable objects:

• BeforeExportTableEventArgs.Table
• IXlRow.BeginTable(IEnumerable<String>, Boolean, XlCellFormatting)
• IXlRow.BeginTable(IEnumerable<String>, Boolean)
• IXlRow.BeginTable(IEnumerable<XlTableColumnInfo>, Boolean, XlCellFormatting)
• XlTableReference.Table

Remarks

To manage data more easily and effectively, you can format a cell range containing related data as a table. Table generation starts by calling the IXlRow.BeginTable method and ends when the IXlRow.EndTable method is called. After table export is initiated, you can utilize the following functionalities to fine-tune your table.

Table Columns

You can access and manage table columns specified by the IXlTableColumn objects that are stored in the IXlTable.Columns collection. The IXlTableColumn.SetFormula method overloads allow you to create calculated columns.

Total Row

You can calculate data totals for each column in the table by using the IXlTableColumn.TotalRowFunction property and display them at the end of the table.

Table Formatting

You can format tables by applying a built-in table style (IXlTable.Style) or setting custom formatting for different table regions and individual table columns.

For details on formatting tables, see the How to: Apply a Table Style and How to: Apply Custom Formatting to a Table examples.

Structured References

You can use structured references to refer to table data ranges within formulas. Use the IXlTable.GetReference and IXlTable.GetRowReference methods to construct the required reference. Refer to the How to: Create a Calculated Column topic for an example on how to use a structured reference to create a formula for a calculated table column.

All tables in a worksheet are stored in the read-only IXlTableCollection collection that is returned by the IXlSheet.Tables property.

Example

Note

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

TableActions.cs

IXlTable table;
// Specify an array containing column headings for a table.
string[] columnNames = new string[] { "Product", "Category", "Amount" };

// Create the first row in the worksheet from which the table starts.
using (IXlRow row = sheet.CreateRow())
{
    // Start generating the table with a header row displayed.
    table = row.BeginTable(columnNames, true);
    // Specify the total row label.
    table.Columns[0].TotalRowLabel = "Total";
    // Specify the function to calculate the total.
    table.Columns[2].TotalRowFunction = XlTotalRowFunction.Sum;
    // Specify the number format for the "Amount" column and its total cell.
    XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
    table.Columns[2].DataFormatting = accounting;
    table.Columns[2].TotalRowFormatting = accounting;
}

// 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(table, true);

TableActions.vb

Dim table As IXlTable
' Specify an array containing column headings for a table.
Dim columnNames() As String = { "Product", "Category", "Amount" }

' Create the first row in the worksheet from which the table starts.
Using row As IXlRow = sheet.CreateRow()
    ' Start generating the table with a header row displayed.
    table = row.BeginTable(columnNames, True)
    ' Specify the total row label.
    table.Columns(0).TotalRowLabel = "Total"
    ' Specify the function to calculate the total.
    table.Columns(2).TotalRowFunction = XlTotalRowFunction.Sum
    ' Specify the number format for the "Amount" column and its total cell.
    Dim accounting As XlNumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"
    table.Columns(2).DataFormatting = accounting
    table.Columns(2).TotalRowFormatting = accounting
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(table, True)
End Using