Use the Excel Export API to Format a Cell
- 6 minutes to read
The proper formatting of worksheet cells improves document appearance and makes document data easier to read and understand. Cell formatting includes a variety of features such as font settings (font size, color, character style, etc.), text alignment, background and foreground colors, borders and so on. Formatting options for a cell are defined by the XlCellFormatting class. This object provides methods and properties that allow you to specify the following types of cell formatting.
To apply the specified format options to a cell, pass the corresponding XlCellFormatting object to the IXlCell.ApplyFormatting method as a parameter, or assign it to the IXlCell.Formatting property.
To format the entire row or column, use the IXlRow.ApplyFormatting and IXlColumn.ApplyFormatting methods, or IXlRow.Formatting and IXlColumn.Formatting properties, respectively.
Predefined Style-Like Formatting
Use static properties of the XlCellFormatting class to format a cell using the predefined format settings corresponding to one of the preset Microsoft® Excel® styles. Predefined formatting includes font settings, content alignment, cell borders and fill color. The image below shows cells to which different types of predefined formatting are applied.
For an example on how to specify predefined formatting for a cell, refer to the How to: Apply Predefined Formatting to a Cell topic.
Note that all these static properties return an instance of the XlCellFormatting class, so that you can use its properties to modify the predefined formatting options to create a custom format.
Themed Formatting
You can use the XlCellFormatting.Themed method to apply the predefined formatting to a cell that is based on the document theme. A document theme is a set of fonts, colors, and graphic effects you can apply to a workbook. Currently, the Excel Export Library supports only the Office theme (the supported document themes are listed by the XlDocumentTheme enumeration). To set the document theme for a workbook, use the IXlDocument.Theme property. By default, the Office 2013 theme is used.
Theme formatting uses six accent colors listed by the XlThemeColor enumeration to fill the cell background. The tint parameter of the XlCellFormatting.Themed method allows you to lighten or darken the original theme color. If you apply theme formatting to a cell, the cell font will be automatically set to the body font of the current document theme. 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 of the XlThemeColor.Light1 theme color will be used; and if the background color is light and pale (the tint value is greater than or equal to 0.5), the XlThemeColor.Dark1 font will be used.
For an example on how to specify theme formatting for a cell, refer to the How to: Apply Themed Formatting to a Cell topic.
Note that the XlCellFormatting.Themed method returns an instance of the XlCellFormatting class, so that you can use its properties to modify current theme formatting settings to create a custom format You can also use theme formatting elements individually. For example, the XlColor.FromTheme method allows you to create a theme-based XlColor to be applied anywhere in the document, while the XlFont.BodyFont and XlFont.HeadingsFont methods enable you to specify the theme body and heading fonts for cell content.
Custom Formatting
The aforementioned methods format a worksheet cell using a predefined set of formatting attributes. However, you may wish to apply your own custom formatting to a cell: color the cell background, adjust font settings, align the cell content, add borders and specify number format options. For this purpose, the XlCellFormatting object provides a number of properties inherited from the XlFormatting class: XlFormatting.Fill, XlFormatting.Font, XlFormatting.Alignment, XlFormatting.Border and XlFormatting.NumberFormat.
Use these properties to specify the desired formatting settings for a cell, as shown in the example below.
// Create a new document and begin to write it to the specified stream.
using (IXlDocument document = exporter.CreateDocument(stream)) {
// Specify formatting settings to be applied to document content.
XlCellFormatting formatting = new XlCellFormatting();
// Specify cell background color.
formatting.Fill = XlFill.SolidFill(XlColor.FromArgb(0xCE, 0x8B, 0xDA));
// Specify font settings (font name, color, size and style).
formatting.Font = new XlFont();
formatting.Font.Name = "MV Boli";
formatting.Font.SchemeStyle = XlFontSchemeStyles.None;
formatting.Font.Size = 16;
formatting.Font.Color = Color.Wheat;
formatting.Font.Bold = true;
// Specify the alignment of cell content.
formatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.Center, XlVerticalAlignment.Center);
// Specify outside border settings.
formatting.Border = XlBorder.OutlineBorders(XlColor.FromArgb(0x47, 0x7B, 0xD1), XlBorderLineStyle.Thick);
// Create a new worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
// Create the first column and set its width.
using (IXlColumn column = sheet.CreateColumn())
column.WidthInPixels = 180;
// Create the first row in the worksheet.
using (IXlRow row = sheet.CreateRow()){
// Create a cell and specify its format settings.
using (IXlCell cell = row.CreateCell())
{
cell.Value = "Custom Format";
cell.ApplyFormatting(formatting);
}
}
}
}
Moreover, the XlCellFormatting object implements implicit conversion from the XlBorder, XlCellAlignment, XlFill, XlFont and XlNumberFormat objects. Therefore, you can directly pass an object specifying the required format characteristics to the IXlCell.ApplyFormatting method, or assign it to the IXlCell.Formatting property without using unnecessary cast operators.
The following examples elaborate on how to apply different format attributes to a worksheet cell.