Use the Excel Export API to Apply Custom Formatting to a Table
- 5 minutes to read
If the predefined table styles do not comply with your requirements, you can apply custom formatting to different parts of a table. For example, you can fill the background of table cells with the desired color, change cell font attributes, align the cell content, add table borders or specify number format options to be applied to table data.
Important
Due to the infrastructure of the Excel Export Library, which writes data directly into a stream in consecutive order, you should set the required format characteristics for a table before you start generating any table data. Otherwise, you may get a table with partial or incorrect formatting.
Format Table Regions
You can apply specific formatting to different table areas. To format the header row of the table, create the XlCellFormatting class instance containing the required format characteristics and pass it to the IXlRow.BeginTable method as the last parameter. To apply custom formatting to the table’s data area or total row, use the IXlTable.DataFormatting or IXlTable.TotalRowFormatting property, respectively.
Format Table Columns
If you wish to apply custom formatting to individual table columns, do one of the following.
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).
This approach is useful when you need to set special formatting for the header cells of table columns. If it is not necessary, you can customize the appearance of table columns as described below.
- Get access to the column you wish to format by its index in the table’s column collection (IXlTable.Columns) and use one of the following properties of the returned IXlTableColumn object: the IXlTableColumn.DataFormatting property - to apply special formatting to the column’s data area, or the IXlTableColumn.TotalRowFormatting property - to format the total cell of the table column.
// 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);
The following image shows the result of the above-mentioned code’s execution (the workbook is opened in Microsoft® Excel®).
