ContextQMD
Back to Devexpress

XlTableColumnInfo Class

corelibraries-devexpress-dot-export-dot-xl-6bd56c60.md

latest8.5 KB
Original Source

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.v25.2.Core.dll

NuGet Package : DevExpress.Printing.Core

Declaration

csharp
public class XlTableColumnInfo
vb
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/excel-export-api-examples

csharp
// 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);
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

Inheritance

Object XlTableColumnInfo

See Also

XlTableColumnInfo Members

Use the Excel Export API to Create a Table

Use the Excel Export API to Apply Custom Formatting to a Table

DevExpress.Export.Xl Namespace