Use the Excel Export API to Apply a Table Style
- 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.
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);
}
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 |
|---|---|
Applies striped row formatting to the table.
| |
Applies striped column formatting to the table.
| |
Applies style formatting to the first column of the table.
| |
Applies style formatting to the last column of the table.
|
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);
}
See Also