SoftArtisans OfficeWriter

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.

Example: Adding Formatting to a Spreadsheet

In the following example, styles are applied to individual styles. You can also apply a style to range of cells. For more information, see Range.Style.

<%@ Language=VBScript %>
<% Option Explicit 
%>
<!-- METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%
'--- Declarations
Dim oXLW, oWS
Dim oMyFont, oNumStyle, oTextStyle, oHeadingStyle, 
Dim oBorderStyle, oBGColorStyle, oWrapStyle

Set oXLW = Server.CreateObject("Softartisans.ExcelWriter")
Set oWS = oXLW.Worksheets(1)

'--- Create header row
oWS.Cells("A1").Value = "Name"
oWS.Cells("B1").Value = "Count"
oWS.Cells("C1").Value = "Dollar"

'--- Add values
oWS.Cells("A2").Value = "Fred Smith"
oWS.Cells("B2").Value = 10
oWS.Cells("C2").Value = 37.5

oWS.Cells("A3").Value = "John Doe"
oWS.Cells("B3").Value = 20
oWS.Cells("C3").Value = 62.85

'--- Add formulas
oWS.Cells("B5").Value = "Total:"
oWS.Cells("B6").Formula = "=sum(b2:b3)"

oWS.Cells("C5").Value = "Average:"
oWS.Cells("C6").Formula = "=average(c2:c3)"

oWS.Cells("E5").Value = "Total x Average:"
oWS.Cells("E6").Formula = "=b6*c6"

'--- Create a font
Set oMyFont = oXLW.CreateFont
oMyFont.Size = 10
oMyFont.Italic = True
oMyFont.Name = "Tahoma"

'--- Create styles
Set oNumStyle = oXLW.CreateStyle
oNumStyle.Font = oMyFont
oNumStyle.HorizontalAlignment = sahaRight

Set oTextStyle = oXLW.CreateStyle
oTextStyle.Font = oMyFont
oTextStyle.HorizontalAlignment = sahaLeft

Set oHeadingStyle = oXLW.CreateStyle
oHeadingStyle.Font = oMyFont
oHeadingStyle.Font.Size = 12
oHeadingStyle.Font.Bold = True
oHeadingStyle.HorizontalAlignment = sahaCenter

'--- A boxed border around a cell
Set oBorderStyle = oXLW.CreateStyle
oBorderStyle.BorderLineStyle = 6

'--- A Blue background color
Set oBGColorStyle = oXLW.CreateStyle
oBGColorStyle.BackgroundColor = RGB(0,0,255)

'--- Word-wrap style
Set oWrapStyle = oXLW.CreateStyle
oWrapStyle.WrapText = True

'--- Apply styles to cells
oWS.Cells("A1").Style = oHeadingStyle
oWS.Cells("B1").Style = oHeadingStyle
oWS.Cells("C1").Style = oHeadingStyle

oWS.Cells("A2").Style = oTextStyle
oWS.Cells("A3").Style = oTextStyle

oWS.Cells("B2").Style = oNumStyle
oWS.Cells("B3").Style = oNumStyle

'--- Define a format for a single cell
oWS.Cells("E6").Format.Number = 2
oWS.Cells("E6").Format.Font.Name = "Courier New"
oWS.Cells("E6").Format.Font.Color = 255 '---RGB Value

'--- Demonstrate the Border capability
oWS.Cells("B10").Value = "Boxed in!"
oWS.Cells("B10").Style = oBorderStyle

'--- Demonstrate Background colorization
oWS.Cells("D10").Value = "BGColor"
oWS.Cells("D10").Style = oBGColorStyle

'--- Wordwrap text inside a cell
oWS.Cells("B12").Value = "Hello this is a string that will be wrapped."
oWS.Cells("B12").Style = oWrapStyle
oWS.Cells.Range(12,2,1,1).AutoFitHeight

'--- Open the workbook in the browser
oXLW.Save "Format.xls", saOpenInPlace

Set oXLW = nothing
Response.end
%>

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.

Adding Formatting to a Spreadsheet

Style, Format, and Font

Example: Adding Formatting to a Spreadsheet

Applying Formatting Efficiently

Section Summary