How to Automate a Report in OpenOffice.org Calc
Writing a Macro that will Create a Professional Looking Report
Nov 3, 2008
Macros are incredibly powerful - especially when it comes to automating tasks such as producing a financial report in OpenOffice.org Calc; with such a report it is possible to program the macro to:
- open a blank OpenOffice.org Calc document
- add any required information
- format cells in the document
- save the document
and it can do all of this completely invisibly - just showing the end result to the user.
Initialising the Macro
As always start by ensuring that all variables have to be declared:
Now that variable declaration has been made mandatory then that task should take up the first lines of the macro:
Opening a Blank, Hidden Calc Document
With the variable declarations in place the next job is to populate the variables with any necessary data, so that's the the initial url (which will be a blank file) and the location to which the document will be saved:
In this example all of the processing is actually going to be hidden and so the property array defined by the macro needs to be loaded with this information:
Now a blank spreadsheet can be opened (but the user will see nothing at this point):
Populating and Formatting the Calc Document
With the document open (but in the background) then the sheet to be worked with can be selected:
In this example the sheets name has been changed (from "Sheet1" to "Financial Report") and the font changed to Arial. Next data can be added to the sheet:
As well as text, values can be added:
The information entered here is going to be a currency value and not just that, but the numbers will be green if they're greater than zero, and red if less than zero. This actually needs an additional function to be written, but that can wait until after the main macro.
Formulae can also be inserted into cells:
Once all of the information has been added then the rows containing the data can be set to their optimal widths:
Now, that the formatting is completed then a header can be added:
Next the hidden document can be saved and closed:
And finally the completed document can be shown to the user:
A Function for Obtaining Number Format Ids
When a number format is applied to a cell by a macro then the format's id is required - this id will depend on the current local and (obviously) the format itself:
If this is a new format then a new id will need to be created:
Conclusion
This code can be stored into an OpenOffice.org module; once that's been done then it can be run and the user will see a simple, but effective, example which can easily be expanded into a real report.
 |
 |
