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:
The macro can the choose a row, for example row 1:
or a column, for example column B:
and, finally, a single cell can be selected:
or:
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:
or the text of a single cell to Arial:
Changing the Cell Font Height
The font height can also be changed for the whole sheet:
or even a whole column:
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:
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:
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:
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.
Mark Alexander Bain - Mark Alexander Bain is a writer, Mo Bro and consultant for all aspects of software development at dsquared. He has also written regularly ...
