How to Use a Macro to Format Calc Cells

Formatting the Text in an OpenOffice.org Cell by Using Macros

Nov 3, 2008

Write a Macro to Format Text in a Calc Document, Mark Alexander Bain

This article shows how to use a macro to alter the format of text in OpenOffice.org Calc cells - so that a fully formatted report can be created at the touch of a button.

There is an urban legend that the Eskimos have dozens of words for snow, however it is an actual fact that OpenOffice.org has 19 words for underline alone, a fact that is of particular use for anyone wishing to create macros to automate the formatting of the text in an OpenOffice.org Calc Spreadsheet. In fact, by using a macro, it is possible to format the:

cell font
cell font height
cell font color
cell background color
cell underline
cell line wrapping

However, a cell must be selected before that cell can be formatted.

Selecting Sheets, Rows, Columns and Cells

Text formatting can applied on four levels in an OpenOffice.org Calc spreadsheet:

the whole sheet
individual rows
individual columns
individual cells

The macro must, therefore, select the sheet to be used, for example 'Sheet1' in the current document:

sheet = thisComponent.Sheets(0)

The macro can the choose a row, for example row 1:

row = sheet.rows(0) ' Row 1 has index number 0

or a column, for example column B:

column = sheet.columns(1)

and, finally, a single cell can be selected:

cell = sheet.getCellRangeByName("B2")

or:

cell = sheet.getCellByReference(1,1)

Changing the Cell Font

Once the sheet, row, column or cell has been selected then the formatting properties can be altered, for example changing the font of the whole sheet to Courier:

sheet.CharFontName = "Courier"

or the text of a single cell to Arial:

cell.CharFontName = "Arial"

Changing the Cell Font Height

The font height can also be changed for the whole sheet:

sheet.CharHeight = 10

or even a whole column:

column.CharHeight = 12

Changing the Font Color

The font type and size properties are useful, but it may also be useful to highlight a cell by changing the text color:

cell.CharColor = RGB (255, 0, 0)

Here the RGB function has been used to change the color of the text to red.

Changing the Background color

The cell background color can also be change, in this case to yellow:

cell.CellBackColor = RGB (255, 255, 0)

Underlining Text in a Cell

As mentioned at the start of this article, there are quite a number of underline styles that can be used, each of which has it's own OpenOffice.org constant id number; these are:

none (ID number 0)
single (1)
double (2)
dotted (3)
dash (5), long dash (6), dash dot (7) and dash dot dot (8)
wave (9), small wave (10), double wave (11)
bold (12), bold dotted (13), bold dash (14), bold long dash (15), bold dash dot (16), bold dash dot dot (17) and bold wave (18)

The underline can be set in either of two ways:

use the constant id number, for example the bold dash dot underline:
cell.CharUnderline = 16
use the OpenOffice.org constant name for the underline, for example:
row.CharUnderline = com.sun.star.awt.FontUnderLine.DOUBLEWAVE

Interestingly (especially for anyone that noticed that there's a gap in the underline ID numbers) there's even an underline type called Don't know (with its own id number of 4), although this just has the same effect as setting the underline to 'none'.

Adding Line Wrapping

Line wrapping is not turned on by default and so all of the text in a cell will appear on a single line; however, the text may be displayed over multiple lines by using the macro to turn line wrapping on:

column.IsTextWrapped = True

Conclusion

Text formatting can be applied to:

the whole sheet
individual rows
individual columns
individual cells

and the formatting consists of setting a number of properties:

CharFontName - the cell font
CharHeight - the cell font height
chancellor - the cell font color
CellBackColor - the cell background color
CharUnderline - the cell underline
IsTextWrapped - cell line wrapping

With those simple properties it is possible to create a very professional looking report just by using a macro.

The copyright of the article How to Use a Macro to Format Calc Cells in Computer Programming is owned by Mark Alexander Bain. Permission to republish How to Use a Macro to Format Calc Cells in print or online must be granted by the author in writing.