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
Then, 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 wish 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
| Style Methods and Properties |
| BackgroundColor |
Sets or retrieves a style's background color.
Use RGB values to assign colors, as in the following example.
Set mystyle = xlw.CreateStyle
mystyle.BackgroundColor = rgb(0,0,255) '--- blue
ws.cells("a1").value = "Blue Cell"
ws.cells("a1").style = mystyle
Top |
| BorderColor |
Sets or retrieves a cell's border color.
Use RGB values to assign colors, as in the following example.
The property could take an optional parameter, such as BorderColor(sabpLeft), by specifying the border index defined as:
| sabpLeft | 1 | sabpDiagonalDown | 16 |
| sabpRight | 2 | sabpDiagonalUp | 32 |
| sabpTop | 4 | sabpDiagonalBoth | 48 |
| sabpBottom | 8 | sabpAll | 63 |
| sabpOutline (default) | 15 |
Set mystyle = xlw.CreateStyle
mystyle.BorderColor = rgb(0,0,255)
ws.cells("a1").value = "Blue border"
ws.cells("a1").style = mystyle
Top |
| BorderLineStyle |
Sets or retrieves a cell's border style.
ExcelWriter supports the following thirteen border styles.
| sadgThin | 1 | sadgMediumDashed | 8 |
| sadgMedium | 2 | sadgDashDot | 9 |
| sadgDashed | 3 | sadgMediumDashDot | 10 |
| sadgDotted | 4 | sadgDashDotDot | 11 |
| sadgThick | 5 | sadgMediumDashDotDot | 12 |
| sadgDouble | 6 | sadgSlantedDashDot | 13 |
| sadgHair | 7 |
The property could take an optional parameter, such as BorderLineSytle(sabpLeft), by specifying the border index defined as:
| sabpLeft | 1 | sabpDiagonalDown | 16 |
| sabpRight | 2 | sabpDiagonalUp | 32 |
| sabpTop | 4 | sabpDiagonalBoth | 48 |
| sabpBottom | 8 | sabpAll | 63 |
| sabpOutline (default) | 15 |
Example:
Set mystyle = xlw.CreateStyle
mystyle.BorderLineStyle = 3
ws.cells("a1").value = "Dashed border"
ws.cells("a1").style = mystyle
Top |
| ForegroundColor |
Sets or retrieves a style's foreground color. A foreground color is
visible when a pattern is assigned to a cell. Use RGB values to assign colors,
as in the following example.
Set mystyle = xlw.CreateStyle
mystyle.Pattern = 8 '--- diagonal stripe
mystyle.ForegroundColor = rgb(255,0,0) '--- red
ws.cells("a1").value = "Red stripe"
ws.cells("a1").style = mystyle
Top |
| Font |
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. To define your style's font as 10pt italic Tahoma, use,
Set mystyle = xlw.CreateStyle
mystyle.Font.Name = "Tahoma"
mystyle.Font.Size = 10
mystyle.Font.Italic = true
Top |
| Hidden* |
Hides cell formulas. The Hidden property is set to False by default. To hide
formulas, use
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.
Top |
| HorizontalAlignment |
Determines the horizontal alignment of the text in your spreadsheet.
HorizontalAlignment has six possible values. You can assign a value by its name or
code. The HorizontalAlignment value names and codes are,
| sahaUnspecified | -1 |
| sahaGeneral | 0 |
| sahaLeft | 1 |
| sahaCenter | 2 |
| sahaRight | 3 |
| sahaFill | 4 |
| sahaJustify | 5 |
| sahaCenterAcross | 6 |
To center text within your cells, use,
mystyle.HorizontalAlignment = haCenter
Top |
| Locked* |
Determines whether a cell can be modified or not. The Locked property is set
to True by default. To unlock cells, use,
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.
Top |
| Name* |
Sets or returns a style name. The following example demonstrates
setting a style name.
Top |
| Number |
Sets the display format of numbers and dates. For a complete list
of Number codes, see Formatting Codes. The following example
demonstrates assigning the percentage format to a style.
Top |
| Orientation* |
Determines text orientation. Orientation has four possible values. You can assign a value by its name or
code. The Orientation value names and codes are,
| 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 to the right |
| saoriClockWise | 3 | Rotates text 90 degrees to the left |
To display text from top to bottom, use,
mystyle.orientation = TopToBottom
Top |
| 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:
Set mystyle = xlw.CreateStyle
mystyle.Pattern = 9
ws.cells("a1").value = "Diagonal Crosshatch"
ws.cells("a1").style = mystyle
Top |
| 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
code. The VerticalAlignment value names and codes are,
| 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 |
To align text with the top of cells, use,
mystyle.VerticalAlignment = vaTop
Top |
| WrapText |
Use the WrapText property to wrap text within cells. WrapText may be either
True or False. WrapText is False by default. To set WrapText to True, use,
Top |
| *These features are not available in ExcelWriterSE, ExcelWriterLE, or ExcelWriterFree. |
Copyright © 2003, SoftArtisans, Inc.