SoftArtisans ExcelWriter Guide

The Style Object (SAStyle)

To define a style, first set a Style object, and assign values to any or all of its properties, for example:

Dim mystyle As SAStyle
Set mystyle = xlw.CreateStyle
mystyle.Font.Name = "Arial"
mystyle.Font.Bold = True
mystyle.HorizontalAlignment = sahaRight

Next, assign your style to an individual cell, for example,

ws.cells("A1").style = mystyle

Alternatively, you could assign your style to a pre-defined Range object, for example, one that encompasses two rows and spans 5 columns:

Set Rng1 = Ws.Cells.Range(3,3,2,5)
Rng1.style = mystyle

Then, if you want to supplement the style for a single cell (for example, one that is contained within "Rng1") with additional formatting properties, use the Cell.Format property as follows:

Ws.Cells(3,5).Format.WrapText= True

Style Methods and Properties

BackgroundColor

Signature:


[VBScript]
Property BackgroundColor As Long (read/write)

Description:

BackgroundColor sets or retrieves a style's background color.

Example:



[VBScript]

Set XlwApp  = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Create a SAStyle object
Set BlueStyle = XlwApp.CreateStyle

'--- Set the Background color
BlueStyle.BackgroundColor = RGB(0,0,255) ' blue

'--- Apply the style
Cells("a1").value = "Blue Cell"
Cells("a1").style = BlueStyle

Top

BorderColor

Signature:


[VBScript]
Property BorderColor([Borders As SACellBorderIndex]) As Long (read/write)

Description:

BorderColor sets or retrieves a cell's border color. Use RGB values to assign colors, as in the example below.

BorderColor takes two parameters: an SACellBorderIndex value and a color.

SACellBorderIndex Values
sabpLeft	1	sabpDiagonalDown	16
sabpRight	2	sabpDiagonalUp	32
sabpTop	4	sabpDiagonalBoth	48
sabpBottom	8	sabpAll	63
sabpOutline (default)	15

Example:



[VBScript]

Set XlwApp  = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Create a SAStyle object
Set BorderStyle = XlwApp.CreateStyle()

'--- Set the Border type and color
BorderStyle.BorderColor(sabpOutline) = RGB(0,0,255) ' Blue

'--- Apply the style
Cells(1,1).Style = BorderStyle

Top

BorderLineStyle

Signature:


[VBScript]
Property BorderLineStyle([Borders As SACellBorderIndex]) _
As SABorderLineStyle (read/write)

Description

BorderLineStyle sets or retrieves a cell's border style.

BorderLineStyle takes two parameters: an SACellBorderIndex and an SABorderLineStyle.

SABorderLineStyle Values

sadgNone 0 sadgHair 7

sadgThin 1 sadgMediumDashed 8

sadgMedium 2 sadgDashDot 9

sadgDashed 3 sadgMediumDashDot 10

sadgDotted 4 sadgDashDotDot 11

sadgThick 5 sadgMediumDashDotDot 12

sadgDouble 6 sadgSlantedDashDot 13

SACellBorderIndex Values

sabpLeft 1 sabpDiagonalDown 16

sabpRight 2 sabpDiagonalUp 32

sabpTop 4 sabpDiagonalBoth 48

sabpBottom 8 sabpAll 63

sabpOutline (default) 15

Example:



[VBScript]

Set XlwApp  = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Create a SAStyle object
Set BorderStyle = XlwApp.CreateStyle()

'--- Set the Border line style
BorderStyle.BorderLineStyle(sabpLeft) = sadgThin

'--- Apply the style
Cells(1,1).Style = BorderStyle

Top

ForegroundColor

Signature:


[VBScript]
Property ForegroundColor As Long (read/write)

Description:

ForegroundColor sets or retrieves a style's foreground color. A foreground color is visible when a pattern is assigned to a cell.

Example:



[VBScript]

Set XlwApp  = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Create a SAStyle object
Set BlueStyle = XlwApp.CreateStyle

'--- Set the Foreground color
BlueStyle.ForegroundColor = RGB(0,0,255) ' blue

'--- Apply the style
Cells("a1").value = "Blue text"
Cells("a1").style = BlueStyle

Top

Font

Signature:


[VBScript]
Property Font As SAFont (read/write)

Description

Use the Font property to set a font for your style. Font contains several properties, each of which define an aspect your font. These properties are Name, Size, Bold, Italic, Underline, Strikethrough, Weight, Charset, Subscript, Superscript, and Color.

Example:



[VBScript]

Set XlwApp  = Server.CreateObject("SoftArtisans.ExcelWriter")
Set Cells = XlwApp.Worksheets(1).Cells

'--- Create a SAStyle object
Set BlueStyle = XlwApp.CreateStyle

'--- Set properties of the style's Font property
Set MyStyle = XlwApp.CreateStyle()
MyStyle.Font.Name = "Tahoma"
MyStyle.Font.Size = 10
MyStyle.Font.Italic = True

'--- Apply the style
Cells("a1").Style = MyStyle

Top

Hidden*

Signature:


[VBScript]
Property Hidden As Boolean (read/write)

Description:

Hidden hides cell formulas. The Hidden property is set to False by default.

Hiding cells has no effect unless the worksheet is protected. To protect a worksheet:

Open the worksheet in Microsoft Excel.
Open the Tools menu, and select Protection -> Protect Sheet.

Example:

To hide formulas, use:



[VBScript]
MyStyle.Hidden = True

Top

HorizontalAlignment

Signature:


[VBScript]
Property HorizontalAlignment As SACellHorizontalAlignment (read/write)

Description:

HorizontalAlignment determines the horizontal alignment of the text in your spreadsheet. Assign a value to HorizontalAlignment by its name or number:

`sahaUnspecified`	`-1`
`sahaGeneral`	`0`
`sahaLeft`	`1`
`sahaCenter`	`2`
`sahaRight`	`3`
`sahaFill`	`4`
`sahaJustify`	`5`
`sahaCenterAcross`	`6`

Example:

To center text within your cells, use:



[VBScript]
mystyle.HorizontalAlignment = sahaCenter

Top

Locked*

Signature:


[VBScript]
Property Locked As Boolean (read/write)

Description:

Locked determines whether a cell can be modified or not. The property is set to True by default.

Locking cells has no effect unless the worksheet is protected. To protect a worksheet:

Open the worksheet in Microsoft Excel.
Open the Tools menu, and select Protection -> Protect Sheet.

Or, use Worksheet.WriteProtect.

Example:

To unlock cells, use:



[VBScript]
mystyle.Locked = False

Top

Name*

Signature:


[VBScript]
Property Name As String (read/write)

Description:

Name sets or returns a style name.

Example:

The following example demonstrates setting a style name.


[VBScript]
mystyle.Name = "MyStyleName"

Top

Number

Signature:


[VBScript]
In VBScript, this is the default property for the SAStyle class.

Property Number As Variant (read/write)

Description:

Number sets the display format of numbers and dates. For a complete list of Number codes, see Formatting Codes.

Example:

The following example demonstrates assigning the percentage format to a style.


[VBScript]
mystyle.Number = 10

Top

Orientation*

Determines text orientation.

Signature:


[VBScript]
Property Orientation As SACellTextOrientation (read/write)

Set Orientation to an SAOrientation value, by name or number:

SAOrientation Values
`saoriUnspecified`	`-1`	Value not specified
`saoriNoRotation`	`0`	Default value
`saoriTopToBottom`	`1`	Displays text from top to bottom of the cell
`saoriCounterclockwise`	`2`	Rotates text 90 degrees counterclockwise
`saoriClockwise`	`3`	Rotates text 90 degrees clockwise
`saoriCounterclockwise15`	`4`	Rotates text 15 degrees counterclockwise
`saoriClockwise15`	`5`	Rotates text 15 degrees clockwise
`saoriCounterclockwise30`	`6`	Rotates text 30 degrees counterclockwise
`saoriClockwise30`	`7`	Rotates text 30 degrees clockwise
`saoriCounterClockwise45`	`8`	Rotates text 45 degrees counterclockwise
`saoriClockwise45`	`9`	Rotates text 45 degrees clockwise
`saoriCounterclockwise60`	`10`	Rotates text 60 degrees counterclockwise
`saoriClockwise60`	`11`	Rotates text 60 degrees clockwise
`saoriCounterclockwise75`	`12`	Rotates text 75 degrees counterclockwise
`saoriClockwise75`	`13`	Rotates text 75 degrees clockwise

To display text from top to bottom, use,

	
	[VBScript]
	
	mystyle.Orientation = saoriClockWise

Top

Pattern

Signature:


[VBScript]
Property SACellBackgroundPattern As SACellBackgroundPattern (read/write)

Description:

Pattern sets or retrieves a cell background pattern. ExcelWriter supports the following 18 patterns:

`saptrnSolid`	`1`	`saptrnThickDiagonalCrosshatch`	`10`
`saptrn50Gray`	`2`	`saptrnThinHorizontalStripe`	`11`
`saptrn75Gray`	`3`	`saptrnThinVerticalStripe`	`12`
`saptrn25Gray`	`4`	`saptrnThinReverseDiagonalStripe`	`13`
`saptrnHorizontalStripe`	`5`	`saptrnThinDiagonalStripe`	`14`
`saptrnVerticalStripe`	`6`	`saptrnThinHorizontalCrosshatch`	`15`
`saptrnReverseDiagonalStripe`	`7`	`saptrnThinDiagonalCrosshatch`	`16`
`saptrnDiagonalStripe`	`8`	`saptrn12Gray`	`17`
`saptrnDiagonalCrosshatch`	`9`	`saptrn6Gray`	`18`

Example:



[VBScript]
Set mystyle = xlw.CreateStyle
mystyle.Pattern = 9  
Cells("a1").value = "Diagonal Crosshatch"
Cells("a1").style = mystyle

Top

VerticalAlignment

Signature:


[VBScript]
Property VerticalAlignment As SACellVerticalAlignment (read/write)

Example:

VerticalAlignment determines the vertical alignment of the text in your spreadsheet. VerticalAlignment has four possible values. You can assign a value by its name or number:

`savaUnspecified`	`-1`	Alignment is not specified
`savaTop`	`0`	Aligns text with the top of cells
`savaCenter`	`1`	Vertically centers text within cells
`savaBottom`	`2`	Aligns text with the bottom of cells (Default value)
`savaJustify`	`3`	Vertically justifies text within cells

Example:

To align text with the top of cells, use:



[VBScript]
mystyle.VerticalAlignment = savaTop

Top

WrapText

Signature:


[VBScript]
Property WrapText As Boolean (read/write)

Description:

Use the WrapText property to wrap text within cells. WrapText may be either True or False. WrapText is set to False by default.

Example:



[VBScript]
mystyle.WrapText = True

Top

*This features is not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree.