Use the Excel Export API to Apply Themed Formatting to a Cell
- 6 minutes to read
Excel Export Library provides the capability to change the appearance of worksheet cells using formatting settings that are based on the current document theme. A document theme is a set of fonts, colors, and graphic effects you can apply to a workbook. The XlDocumentTheme enumeration lists the available document themes (currently, only Office themes are supported). Use the IXlDocument.Theme property to set the theme of your document. By default, the Office 2013 theme is used.
To specify themed formatting for a cell, use the XlCellFormatting.Themed method. Pass the following parameters.
- An XlThemeColor enumeration member that specifies one of the six accent colors to fill the cell background. These colors are defined by the current document theme and change when another theme is applied to a workbook.
- A tint value between -1.0 and 1.0 used to darken (negative values) or lighten (positive values) the original theme color. -1.0 means darken 100% (black) and 1.0 means lighten 100% (white). The zero value corresponds to the original color used at full strength.
The XlCellFormatting.Themed method automatically sets the cell font to the body font of the current document theme (that is Calibri, 11 for Office themes). The font color depends on the cell background color: if the background color is dark or saturated (the tint value is less than 0.5), the font color will be set to the XlThemeColor.Light1 theme color; and if the background color is light (the tint value is greater than or equal to 0.5), the XlThemeColor.Dark1 font color will be used.
To apply theme formatting settings to a cell, pass the XlCellFormatting object returned by the XlCellFormatting.Themed method to the IXlCell.ApplyFormatting method as a parameter, or assign it to the IXlCell.Formatting property.
Tip
To share formatting settings with multiple cells in a row at once, use the IXlRow.BlankCells and IXlRow.BulkCells methods.
To specify formatting settings for the entire row or column, use the IXlRow.ApplyFormatting and IXlColumn.ApplyFormatting methods, or IXlRow.Formatting and IXlColumn.Formatting properties, respectively.
// Create a worksheet.
using(IXlSheet sheet = document.CreateSheet()) {
// Create six successive columns and set their widths.
for(int i = 0; i < 6; i++) {
using(IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 100;
}
}
// Specify an array that stores six accent colors of the document theme.
XlThemeColor[] themeColors = new XlThemeColor[] { XlThemeColor.Accent1, XlThemeColor.Accent2, XlThemeColor.Accent3, XlThemeColor.Accent4, XlThemeColor.Accent5, XlThemeColor.Accent6 };
// Specify the "20% - AccentN" themed cell formatting.
// Create a worksheet row.
using(IXlRow row = sheet.CreateRow()) {
for(int i = 0; i < 6; i++) {
// Create a new cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = string.Format("Accent{0} 20%", i + 1);
// Apply the themed formatting to the cell using one of the predefined accent colors lightened by 80%.
cell.Formatting = XlCellFormatting.Themed(themeColors[i], 0.8);
}
}
}
// Specify the "40% - AccentN" themed cell formatting.
// Create a worksheet row.
using(IXlRow row = sheet.CreateRow()) {
for(int i = 0; i < 6; i++) {
// Create a new cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = string.Format("Accent{0} 40%", i + 1);
// Apply the themed formatting to the cell using one of the predefined accent colors lightened by 60%.
cell.Formatting = XlCellFormatting.Themed(themeColors[i], 0.6);
}
}
}
// Specify the "60% - AccentN" themed cell formatting.
// Create a worksheet row.
using(IXlRow row = sheet.CreateRow()) {
for(int i = 0; i < 6; i++) {
// Create a new cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = string.Format("Accent{0} 60%", i + 1);
// Apply the themed formatting to the cell using one of the predefined accent colors lightened by 40%.
cell.Formatting = XlCellFormatting.Themed(themeColors[i], 0.4);
}
}
}
// Specify the "AccentN" themed cell formatting.
// Create a worksheet row.
using(IXlRow row = sheet.CreateRow()) {
for(int i = 0; i < 6; i++) {
// Create a new cell in the row.
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = string.Format("Accent{0}", i + 1);
// Apply the themed formatting to the cell using one of the predefined accent colors.
cell.Formatting = XlCellFormatting.Themed(themeColors[i], 0.0);
}
}
}
}
The images below show the results for different document themes (the workbook is opened in Microsoft® Excel®).
| Theme | Result |
|---|---|
| Office 2007-2010 |  |
| Office 2013 (default) |  |