officefileapi-116567-excel-export-library-tables-how-to-apply-custom-formatting-to-a-table.md
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.
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.
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).
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);
' 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
The following image shows the result of the above-mentioned code’s execution (the workbook is opened in Microsoft® Excel®).