Style, Format, and Font

Individual cells can be formatted by using a combination of Styles, Formats and Fonts.

A style ensures that a set of cells (for instance those that are contained in a pre-defined Range object) have the same look. If the style is changed, all cells that reference that particular style will automatically have the updated look.

Like a style, a format is used to set the look of text, defining characteristics such as alignment, text wrap, and so on. However, a format is specific to one cell. Updating a cell's format will not automatically update the look of other cells. Updating a style will automatically update all cells where the style was applied.

Once a style is applied to a cell, it is possible to supplement that style with the addition or modification of certain properties by using Cell.Format. However, this should be done cautiously. See, Applying Formatting Efficiently.

A font is always the property of a style or format. Use Font to define text properties like size, color, italic, and font name.

Top

Example: Adding Formatting to a Spreadsheet

The spreadsheet used in the previous example will be reused and formatting will be added.

A spreadsheet is created, with the same values as in the previous exercise. Creating a font is a good first step towards creating a desired look. This creates a new font object using the Tahoma font.

	set MyFont = xlw.CreateFont
	MyFont.Name = "Tahoma"
	MyFont.Size = 10
	MyFont.Italic = true

The font can then be applied to multiple new style objects.

	set NumStyle = xlw.CreateStyle
	NumStyle.Font = MyFont
	NumStyle.HorizontalAlignment = haRight

	set TextStyle = xlw.CreateStyle
	TextStyle.Font = MyFont
	TextStyle.HorizontalAlignment = haLeft

	set HeadingStyle = xlw.CreateStyle
	HeadingStyle.Font = MyFont
	HeadingStyle.Font.Size = 12
	HeadingStyle.Font.Bold = true
	HeadingStyle.HorizontalAlignment = haCenter

Here, the font is applied to two different styles: a numeric style which is right-aligned, and a text style which is left-aligned. The Heading style illustrates how you can use a base font and then override specific settings, such as the size within the style.

Once the styles are created, they could be applied to cells one at a time:

	ws.Cells("A1").Style = HeadingStyle
	ws.Cells("B1").Style = HeadingStyle
	ws.Cells("C1").Style = HeadingStyle

	ws.Cells("A2").Style = TextStyle
	ws.Cells("A3").Style = TextStyle

	ws.Cells("B2").Style = NumStyle
	ws.Cells("B3").Style = NumStyle

A more efficient means of setting the style would be to make a single line application to a pre-defined range object. This would, thereby, affect all cells that are members of that range. This method is illustrated in the range.style documentation.

Finally, it is possible to create a format for a specific cell:

	ws.Cells("E6").Format.Number = 2
	ws.Cells("E6").Format.Font.Name = "Courier New"
	ws.Cells("E6").Format.Font.Color = 255 ' RGB Value

Top

Applying Formatting Efficiently

ExcelWriter allows you to apply an unlimited number of formats and styles to a workbook. However, Microsoft Excel supports up to 4074 unique formats and styles in a single workbook. If this limit is exceeded in an ExcelWriter-generated spreadsheet, ExcelWriter will throw an error when the Save method is called. Therefore, when designing the layout of the workbook, it is essential to carefully plan the application of formats and styles to prevent unnecessary overlapping of these.

Styles are "shared objects" while formats are unique for each assignment. This means that application of a style to more than one cell counts as a single instance of unique formatting, but the application of a format to a cell counts as a single instance for each application. Therefore, styles should be used whenever possible and formats for specific cells only when required to supplement a style.

Avoid the application of styles to non-essential cells within an ExcelWriter-generated workbook. To accomplish this, narrow the cell range by setting all four optional parameters (FirstRow, FirstCol, NumRows, NumCols) as opposed to relying on the defaults. This prevents the creation of excessive cell formatting records that can increase file size and thereby, affect the performance of creating and saving the workbook.

Keep in mind that since a style is shared, once it is assigned to a range, any subsequent modifications of the style will update the look of all cells in the range. This is often not the intention of developers and it can be a source of confusion when the output is not as intended.

Note that starting with Version 4, ExcelWriter saves only unique fonts, formats, and styles. This reduces the size of generated spreadsheets, improves performance, and prevents exceeding Excel's limit.

Top

Section Summary

1. Style, Format and Font objects create the look for a range that can consist of one or more cells.
2. Use Formats to override Styles in a more specific context.
3. Fonts are applied to Styles or Formats. Styles and Formats are applied to cells.
4. While it is possible to create a format for one specific cell, it is much more efficient to reapply one style object multiple times.

Top

Copyright © 2005, SoftArtisans, Inc.