Cell Styles
A cell style is a predefined set of formatting options, such as cell borders, fonts, font sizes and number formats. Using cell styles allows you to apply multiple format options in one step and also offers an easy approach to achieve consistency in cell formatting. The document model has a number of built-in cell styles that you can modify or apply directly. Its API allows you to duplicate an existing style or create a new one according to your preferences.
You can create cell styles that depend on the current document theme. Such styles are updated automatically when you change the selected document theme. You can read more about document themes and dependent cell styles in the Document Themes article.
Cell Style Properties
A cell style is represented by the CellStyle class. The properties of the class can be categorized into five groups: number, alignment, font, border and fill. Following are all properties distributed in their corresponding groups:
-
Number group
- Format
-
Alignment group
HorizontalAlignment
VerticalAlignment
Indent
IsWrapped
TextRotation (supported in XLSX format only)
-
Font group
FontFamily
FontSize
IsBold
IsItalic
Underline
ForeColor
-
Border group
LeftBorder
TopBorder
RightBorder
BottomBorder
DiagonalUpBorder
DiagonalDownBorder
-
Fill group
- Fill
In addition to the properties above, the CellStyle class exposes five Boolean properties that indicate whether the groups above will be applied:
IncludeNumber
IncludeAlignment
IncludeFont
IncludeBorder
IncludeFill
When you apply a style to a cell with locally set properties, the end result is an addition of the style properties to the cell's local properties. The end result of such an addition of styles depends on which elements (groups) of the style have been selected as being part of the style. You can select a group to be part of the style by setting the appropriate property to true.
Example 1 shows what including the Number group looks like.
Example 1: Include Number group in CellStyle
Workbook workbook = new Workbook();
CellStyle tempStyle = workbook.Styles["Bad"];
tempStyle.IncludeNumber = true;
Through the API you can add, modify or remove styles from the Styles collection residing in the worksheet.
Create Cell Style
Creating a new style is pretty straight-forward. All you have to do is invoke the Add() method of workbook's Styles collection. The method returns an object of type CellStyle, which you can manipulate. Note that setting several properties raises multiple UI updates. To avoid the additional overhead, you can enclose the code blocks that set the properties with the BeginUpdate() and EndUpdate() methods.
Example 2 creates a new style and applies it to cell A1.
Example 2: Create a style
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
CellStyle cellStyle = workbook.Styles.Add("My style", CellStyleCategory.Custom);
cellStyle.BeginUpdate();
CellBorder border = new CellBorder(CellBorderStyle.DashDotDot, new ThemableColor(Colors.Red));
cellStyle.LeftBorder = border;
cellStyle.TopBorder = border;
cellStyle.RightBorder = border;
cellStyle.BottomBorder = border;
ThemableColor patternColor = new ThemableColor(ThemeColorType.Accent1);
ThemableColor backgroundColor = new ThemableColor(ThemeColorType.Accent5, ColorShadeType.Shade2);
IFill fill = new PatternFill(PatternType.Gray75Percent, patternColor, backgroundColor);
cellStyle.Fill = fill;
cellStyle.HorizontalAlignment = RadHorizontalAlignment.Left;
cellStyle.VerticalAlignment = RadVerticalAlignment.Center;
cellStyle.EndUpdate();
workbook.ActiveWorksheet.Cells[0, 0].SetStyleName("My style");
Modify Cell Style
Modifying a style is even easier than creating one. All you need to do is retrieve the style from the Styles collection and set the properties you need.
Example 3 obtains the Bad style from the styles collection of a workbook and modifies it.
Example 3: Modify a style
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
CellStyle style = workbook.Styles["Bad"];
style.BeginUpdate();
style.Fill = new PatternFill(PatternType.DiagonalCrosshatch, Colors.Red, Colors.Transparent);
style.FontSize = UnitHelper.PointToDip(20);
style.ForeColor = new ThemableColor(Colors.Black);
style.EndUpdate();
Copy Existing Cell Style
The API enables you to copy the properties of an existing style so you can modify it according to your preferences while keeping the original style untouched.
Example 4: Copy an existing style and modify its properties
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
CellStyle originalStyle = workbook.Styles["Good"];
CellStyle customStyle = workbook.Styles.Add("My good style", CellStyleCategory.Custom);
customStyle.BeginUpdate();
customStyle.CopyPropertiesFrom(originalStyle);
customStyle.Fill = new PatternFill(PatternType.Solid, Colors.LightSkyBlue, Colors.Transparent);
customStyle.EndUpdate();
Remove Cell Style
You can also remove a style from the Styles collection. It is as easy as removing an object from a list, you simply invoke the Remove() method, which returns a Boolean value. If such style exist in the collection, the method will return true.
Example 5: Remove a style
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
if (workbook.Styles.Remove("Bad"))
{
Debug.WriteLine("Style removed");
}
else
{
Debug.WriteLine("The style does not exist");
}