ContextQMD
Back to Devexpress

Use the Excel Export API to Apply a Table Style

officefileapi-115958-excel-export-library-tables-how-to-apply-a-table-style.md

latest14.7 KB
Original Source

Use the Excel Export API to Apply a Table Style

  • Sep 19, 2023
  • 9 minutes to read

The Excel Export Library allows you to format a table by applying one of the Microsoft® Excel® built-in table styles. To do this, use the IXlTable.Style property to get access to the IXlTableStyleInfo object containing table style options, and then set the IXlTableStyleInfo.Name property to the name of the built-in style you wish to apply. The built-in table style names can be obtained as constant fields of the XlBuiltInTableStyleId class.

View Example

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

    // Apply the table style.
    table.Style.Name = XlBuiltInTableStyleId.Dark7;
}

// 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);
// Create the last table row and finish the table.
// The total row is not displayed for the table. 
using (IXlRow row = sheet.CreateRow())
{
    row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
    row.EndTable(table, false);
}
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)

    ' Apply the table style.
    table.Style.Name = XlBuiltInTableStyleId.Dark7
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
' Create the last table row and finish the table.
' The total row is not displayed for the table. 
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Ravioli Angelo", "Grains/Cereals", 12500 }, Nothing)
    row.EndTable(table, False)
End Using

Control Table Style Options

After you apply a table style, you can fine-tune the table appearance by turning style formatting on or off for specific table elements. Table style options are controlled by the corresponding properties of the IXlTableStyleInfo object.

|

Property

|

Description

| | --- | --- | |

IXlTableStyleInfo.ShowRowStripes

|

Applies striped row formatting to the table.

| |

IXlTableStyleInfo.ShowColumnStripes

|

Applies striped column formatting to the table.

| |

IXlTableStyleInfo.ShowFirstColumn

|

Applies style formatting to the first column of the table.

| |

IXlTableStyleInfo.ShowLastColumn

|

Applies style formatting to the last column of the table.

|

View Example

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

// Create the row containing the table title.
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Disable banded rows" }, XlCellFormatting.Title);
sheet.SkipRows(1);

// Start generating the table with a header row displayed.
using (IXlRow row = sheet.CreateRow())
{
    table = row.BeginTable(columnNames, true);
    // Disable banded row formatting for the table.
    table.Style.ShowRowStripes = false;
}
// 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);
// Create the last table row and finish the table.
// The total row is not displayed for the table. 
using (IXlRow row = sheet.CreateRow())
{
    row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
    row.EndTable(table, false);
}
sheet.SkipRows(1);

// Create the row containing the table title.
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Enable banded columns" }, XlCellFormatting.Title);
sheet.SkipRows(1);

// Start generating the table with a header row displayed.
using (IXlRow row = sheet.CreateRow())
{
    table = row.BeginTable(columnNames, true);
    // Apply banded column formatting to the table.
    table.Style.ShowRowStripes = false;
    table.Style.ShowColumnStripes = true;
}
// 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);
// Create the last table row and finish the table.
// The total row is not displayed for the table. 
using (IXlRow row = sheet.CreateRow())
{
    row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
    row.EndTable(table, false);
}
sheet.SkipRows(1);

// Create the row containing the table title.
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Highlight first column" }, XlCellFormatting.Title);
sheet.SkipRows(1);

// Start generating the table with a header row displayed.
using (IXlRow row = sheet.CreateRow())
{
    table = row.BeginTable(columnNames, true);
    // Display special formatting for the first column of the table.
    table.Style.ShowFirstColumn = true;
}
// 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);
// Create the last table row and finish the table.
// The total row is not displayed for the table. 
using (IXlRow row = sheet.CreateRow())
{
    row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
    row.EndTable(table, false);
}
sheet.SkipRows(1);

// Create the row containing the table title.
using (IXlRow row = sheet.CreateRow())
    row.BulkCells(new object[] { "Highlight last column" }, XlCellFormatting.Title);
sheet.SkipRows(1);

// Start generating the table with a header row displayed.
using (IXlRow row = sheet.CreateRow())
{
    table = row.BeginTable(columnNames, true);
    // Display special formatting for the last column of the table.
    table.Style.ShowLastColumn = true;
}
// 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);
// Create the last table row and finish the table.
// The total row is not displayed for the table. 
using (IXlRow row = sheet.CreateRow())
{
    row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
    row.EndTable(table, false);
}
vb
Dim table As IXlTable
' Specify an array containing column headings for tables.
Dim columnNames() As String = { "Product", "Category", "Amount" }

' Create the row containing the table title.
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Disable banded rows" }, XlCellFormatting.Title)
End Using
sheet.SkipRows(1)

' Start generating the table with a header row displayed.
Using row As IXlRow = sheet.CreateRow()
    table = row.BeginTable(columnNames, True)
    ' Disable banded row formatting for the table.
    table.Style.ShowRowStripes = False
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
' Create the last table row and finish the table.
' The total row is not displayed for the table. 
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Ravioli Angelo", "Grains/Cereals", 12500 }, Nothing)
    row.EndTable(table, False)
End Using
sheet.SkipRows(1)

' Create the row containing the table title.
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Enable banded columns" }, XlCellFormatting.Title)
End Using
sheet.SkipRows(1)

' Start generating the table with a header row displayed.
Using row As IXlRow = sheet.CreateRow()
    table = row.BeginTable(columnNames, True)
    ' Apply banded column formatting to the table.
    table.Style.ShowRowStripes = False
    table.Style.ShowColumnStripes = True
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
' Create the last table row and finish the table.
' The total row is not displayed for the table. 
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Ravioli Angelo", "Grains/Cereals", 12500 }, Nothing)
    row.EndTable(table, False)
End Using
sheet.SkipRows(1)

' Create the row containing the table title.
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Highlight first column" }, XlCellFormatting.Title)
End Using
sheet.SkipRows(1)

' Start generating the table with a header row displayed.
Using row As IXlRow = sheet.CreateRow()
    table = row.BeginTable(columnNames, True)
    ' Display special formatting for the first column of the table.
    table.Style.ShowFirstColumn = True
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
' Create the last table row and finish the table.
' The total row is not displayed for the table. 
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Ravioli Angelo", "Grains/Cereals", 12500 }, Nothing)
    row.EndTable(table, False)
End Using
sheet.SkipRows(1)

' Create the row containing the table title.
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Highlight last column" }, XlCellFormatting.Title)
End Using
sheet.SkipRows(1)

' Start generating the table with a header row displayed.
Using row As IXlRow = sheet.CreateRow()
    table = row.BeginTable(columnNames, True)
    ' Display special formatting for the last column of the table.
    table.Style.ShowLastColumn = True
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
' Create the last table row and finish the table.
' The total row is not displayed for the table. 
Using row As IXlRow = sheet.CreateRow()
    row.BulkCells(New Object() { "Ravioli Angelo", "Grains/Cereals", 12500 }, Nothing)
    row.EndTable(table, False)
End Using

See Also

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