Support Knowledge Base, Article 1236

The ExcelApplication COM object provides two ways to set formatting: setting styles and setting individual formatting properties.

• Styles can be set by assigning a Style object to the Style property on a SACell or SARange object (See SACell.Style and SARange.Style). This overwrites all existing formatting properties on the object.
• Individual properties can be set using the Format property on an SACell object (See SACell.Format). For example, the font name of a cell can be changed by calling myCell.Format.Font.Name = "Verdana";

The native .NET ExcelApplication class provides three ways to set formatting: setting styles (overwriting all existing properties), applying (merging or layering) styles, or setting individual style properties on a cell.

• Styles can be set by assigning a Style object to the Style property on a Cell object (See Cell.Style) or by calling the SetStyle method on an Area or Range object (See Area.SetStyle and Range.SetStyle).
• Styles can be layered or merged using the ApplyStyle method on a Cell, Area, or Range (See Cell.ApplyStyle, Area.ApplyStyle, and Range.ApplyStyle). Calling ApplyStyle only sets the properties of the specified style that have been set programmatically in your code. (When you create a style, all of its properties default to settings based on the Normal style.)
• Individual properties can be set on a cell by accessing them through the Style property on a Cell object (See Cell.Style). For example, the font name of a cell can be changed by calling myCell.Style.Font.Name = "Verdana";

The following table compares similar style and formatting behavior available in the COM and .NET ExcelApplication object.

So as you can see, the .NET ExcelApplication class provides a way to layer or merge styles using ApplyStyle, while the ExcelApplication COM object does not. SACell.Format provides some of this functionality by allowing individual properties to be set, but in order to acheive functionality similar to ApplyStyle in .NET, we need the following:

• A way to store specific formatting property values that will be set.
• A way to set specific formatting property values on a range of cells. SARange only has a Style property, and does not have a Format property.)

To work around these limitations, we can write some helper methods that internally use SACell.Format and that allow us to specify which style formatting values and properties we want to set.

This article demonstrates how SACell.Format and helper methods can be used with ExcelApplication in COM/ASP to provide functionality similar to ApplyStyle.

Creating ApplyStyle Behavior

The first step in building behavior with the ExcelApplication COM object that is similar to that of the .NET ExcelApplication's ApplyStyle methods is to create a helper class.

This helper class will need three key pieces of information:

1. A cell or range of cells on which to apply a style
   This is what the style will be applied to.
   
   
2. A style containing the settings that we want to apply
   We only need to set the properties that we plan on using. (The others will be ignored.)
   
   
3. A list of properties we want to apply
   This is necessary because there is no way to tell which properties on the style were changed programmatically.

Once we have this information, we will use SACell.Format to set individual properties based on the settings of the style and list of properties to be applied.

We will not be able to duplicate every property of a style, however, because Cell.Format only supports a subset of style properties: Hidden, HorizontalAlignment, Locked, Number, Orientation, VerticalAlignment, WrapText, Font.Bold, Font.Charset, Font.Color, Font.Italic, Font.Name, Font.Size, Font.Strikethrough, Font.Subscript, Font.Superscript, Font.Underline

We create two subs for providing this ApplyStyle behavior, and place them in an include file called ApplyStyle.inc, so that they can easily be used in any ASP page:

Sub ApplyStyleToRange(range, refStyle, propsToCopy)

This method loops through all the cells in a range and calls ApplyStyleToCell on each cell.

Sub ApplyStyleToCell(cell, refStyle, propsToCopy)

This method loops through all the properties in the propsToCopy string and set's the specified cell's Format properties with the same values as the provided refStyle's properties.

Both methods accept the following parameters, representing the information we needed as specified above:

• range (SARange object) or cell (SACell object) on which to apply the style
• refStyle (SAStyle object) containing the actual settings to apply
• propsToCopy (String) containing comma-delimited property names that correspond to SACell.Format properties. For example: "HorizontalAlignment,Font.Bold"

Using ApplyStyle.inc

To use ApplyStyle.inc in an ASP page with ExcelWriter, use an include statement toward the top of the page:

<!--#include file="ApplyStyle.inc"-->

Then, call either of the ApplyStyle methods from your own code.

Example

The following example demonstrates opening an Excel workbook and applying properties of a style to a range in that workbook. We create an ASP page called ApplyStyleSample.asp to contain this logic.

First, we import the TypeLib for ExcelWriter, set the script language, and include the ApplyStyle.inc file.

<!--METADATA TYPE="TypeLib" UUID="{7BCD2133-64A0-4770-843C-090637114583}" -->
<%@ Language="VBScript" %>
<% '--- Include ApplyStyle helper methods %>
<!--#include file="ApplyStyle.inc"-->

Then, we define all the objects we will need, create an instance of ExcelWriter, and open the workbook to be modified.

<%
Dim xla, cells, myRange, myStyle, myFont, myProps

'--- Instantiate ExcelWriter
Set xla = Server.CreateObject("SoftArtisans.ExcelWriter")

'--- Open workbook
xla.Open(Server.MapPath("in.xls"))

Next, we create the three objects needed to call ApplyStyleToRange: a range of cells, a style with the properties we wish to apply set appropriately, and a comma-delimited string of properties we want applied. This example has all properties included.

'--- Get cells from first worksheet
Set cells = xla.Worksheets(1).Cells

'--- Get range
Set myRange = cells.Range(2, 1, 4, 4)

'--- Create style, setting any or all properties
Set myStyle = xla.CreateStyle()
myStyle.Hidden = True
myStyle.HorizontalAlignment = sahaRight
myStyle.Locked = False
myStyle.Number = "@"
myStyle.Orientation = saoriClockwise
myStyle.VerticalAlignment = savaCenter
myStyle.WrapText = True
myStyle.Font.Bold = True
myStyle.Font.Charset = 1 '--- Changing this can affect other properties such as 
                         '--- Font Name
myStyle.Font.Color = 255
myStyle.Font.Italic = True
myStyle.Font.Name = "Tahoma"
myStyle.Font.Size = 16
myStyle.Font.Strikethrough = True
myStyle.Font.Subscript = True '--- Setting Subscript to True makes 
                              '--- Superscript False.
myStyle.Font.Superscript = False '--- Setting Superscript to True makes 
                                 '--- Subscript False.
myStyle.Font.Underline = saxlSingleUnderline

'--- Create comma-delimited string of properties to apply 
'--- (this example has all properties included)
myProps = "Hidden,HorizontalAlignment,Locked,Number,Orientation," & _
          "VerticalAlignment,WrapText,Font.Bold,Font.Charset,Font.Color," & _ 
          "Font.Italic,Font.Name,Font.Size,Font.Strikethrough,Font.Subscript," & _ 
          "Font.Superscript,Font.Underline"

Then, we call ApplyStyleToRange.

'--- Apply style to range
ApplyStyleToRange myRange, myStyle, myProps

Finally, we save the workbook and stream it to the user.

'--- Stream workbook to user
xla.Save "out.xls", saOpenInExcel, saFileFormatDefault
%>

This full example is included as an attachment to this article. It contains:

• ApplyStyle.inc - ApplyStyle helper methods
• ApplyStyleSample.asp - Sample page that uses ApplyStyleToRange
• in.xls - Starting Excel workbook