XlTableColumnInfo Class

Represents an object used to create a table column with custom formatting applied to its regions.

Namespace: DevExpress.Export.Xl

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

Declaration

C#

public class XlTableColumnInfo

VB.NET

Public Class XlTableColumnInfo

Remarks

If you wish to apply custom formatting to individual table columns, use the IXlRow.BeginTable method overload that accepts a list of XlTableColumnInfo objects as a parameter. An instance of the XlTableColumnInfo class represents a table column with a specified name and provides a set of properties to format different column regions: the column’s header row cell (XlTableColumnInfo.HeaderRowFormatting), data area (XlTableColumnInfo.DataFormatting) and total row cell (XlTableColumnInfo.TotalRowFormatting).

Example

Note

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

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

Related GitHub Examples

The following code snippet (auto-collected from DevExpress Examples) contains a reference to the XlTableColumnInfo class.

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.

C#

excel-export-api-examples-t253492/CS/XLExportExamples/SpreadsheetActions/TableActions.cs#L554

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

VB.NET

excel-export-api-examples-t253492/VB/XLExportExamples/SpreadsheetActions/TableActions.vb#L563

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

Inheritance

Object

XlTableColumnInfo