How to Automate a Report in OpenOffice.org Calc

Writing a Macro that will Create a Professional Looking Report

Nov 3, 2008
Mark Alexander Bain

Writing Macros for OpenOffice.org - Mark Alexander Bain

This tutorial shows how just a simple macro can automate the production of a complex report in OpenOffice.org Calc

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:

Option Explicit

Now that variable declaration has been made mandatory then that task should take up the first lines of the macro:

Sub Main

Dim doc, sheet, cell

Dim url1, url2 as String

Dim args(0) As New com.sun.star.beans.PropertyValue

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:

url1 = "private:factory/scalc"

url2 = convertToUrl("H:/suite101/report.ods")

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:

args(0).Name = "Hidden"

args(0).Value = TRUE

Now a blank spreadsheet can be opened (but the user will see nothing at this point):

doc = starDeskTop.loadComponentFromUrl (url1, "_blank", 0, args())

Populating and Formatting the Calc Document

With the document open (but in the background) then the sheet to be worked with can be selected:

sheet = doc.sheets(0)

sheet.name = "Financial Report"

sheet.CharFontName = "Arial"

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:

cell = sheet.getCellRangeByName("B2")

cell.String = "Company Sales"

cell.IsTextWrapped = True 'Add text wrapping to cell

cell = sheet.getCellRangeByName("C2")

cell.String = "VAT Rate"

cell = sheet.getCellRangeByName("D2")

cell.String = "Total VAT"

cell.CharColor = RGB (255, 0, 0) 'Red text

As well as text, values can be added:

cell = sheet.getCellRangeByName("B3")

cell.Value = 125679.34

cell.numberFormat = NumberFormatId (doc, "[GREEN]$#,##0;[RED]-$#,##0")

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.

cell = sheet.getCellRangeByName("C3")

cell.Value = 17.5

Formulae can also be inserted into cells:

cell = sheet.getCellRangeByName("D3")

cell.Formula = "=B3*C3/100"

cell.numberFormat = NumberFormatId (doc, "[RED]$#,##0;[RED]-$#,##0")

'Set the cell background to yellow

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

Once all of the information has been added then the rows containing the data can be set to their optimal widths:

sheet.Columns(1).OptimalWidth = True

sheet.Columns(2).OptimalWidth = True

sheet.Columns(3).OptimalWidth = True

Now, that the formatting is completed then a header can be added:

'Put this last so that it does not affect any of the formatting

cell = sheet.getCellRangeByName("C1")

cell.String = sheet.Name

cell.CellStyle="Heading"

Next the hidden document can be saved and closed:

doc.storeAsUrl (url2, Array ())

doc.Close (True)

And finally the completed document can be shown to the user:

doc = starDeskTop.loadComponentFromUrl (url2, "_blank", 0, Array())

End Sub

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:

Function NumberFormatId (idoc As Object, _

numberFormat As String) As Integer

Dim locale As New com.sun.star.lang.Locale

locale.Language = "EN"

locale.Country = "USA"

NumberFormatId = idoc.numberFormats.queryKey( numberFormat, locale, True)

If this is a new format then a new id will need to be created:

If (NumberFormatId = -1) Then

NumberFormatId = idoc.numberFormats.addNew(numberFormat, locale)

End If

End Function

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.

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 ...