OpenOffice Calc Macro CheatSheet
Programming OpenOffice/Libre Office is not, alas, for the faint of heart. You can get utterly lost even trying. This cheat sheet is a short intro that I hope will help.
There are essentially 4 different APIs for OpenOffice, some of which interconnect, some of which overlap. They are:
- Binary API. This means basically customizing the OO applications itself. Very few people will be doing this, and I’m not going to cover it.
- Recorder API. This is the “Official” OO macro API. However, all it really is is the engine that drives:
- Object Model API. Technically, this isn’t an “API” per se, but it’s where the real work is done, because these are the object-oriented units that make up a document and its components. So to do anything meaningful with the API engine, you have to know what these component types are and what properties and methods they support. A task made even more challenging, since the objects themselves generally expose multiple functional interfaces. Nine-tenths of the fun is figuring out which interface you want so you can find the docs on the property or method you intend to manipulate.
- High-level API. This acts as a convenience to access the Object Model API. Most of us will want to program to this API. And we’d have a lot more fun if we didn’t have to do so by first figuring out what’s in the Object Model API and working our way backwards!
But wait! There’s more!
In the Object API as of OO Version 3, there are 2 different kinds of object properties. Some of them are inherent properties, and can be set directly. Others are keyword properties and must be set by invoking a “setProperty()” method. Some are in-between, where you have a specific set/get method for the property. Which one you need for any particular function isn’t intuitively obvious.
And the fun keeps on coming. The high-level BASIC API doesn’t return component collections as directly-indexable arrays, so you have to use an explicit accessor method to obtain a collection object. The getByIndex method provides access for what more civilized platforms would allow you to get using the subscript operator. The getByName method provides the equivalent associative array function.
And, last, but not least – whoops! Senility takes its toll. I’ll come back and whinge on the third major annoyance as soon as I remember it.
Working with document objects
Despite all the shortcomings, one virtue that the OO document model API does possess is universality. Writer (Word) documents support tables in documents. Calc (Spreadsheet) documents are basically just books full of tables. Rather than replicate a Not-Invented-Here model for each, they use a common model for tables, augmented by document-specific extensions, as needed. So a Cell in a spreadsheet has both the XCell interface for general table cells, and it has the XSheetCell interface for things that are specific to spreadsheet cells. Typically, the OO document object implementation pairs object classes (such as Cell) with class interfaces (XCell). This is soft of a medium between pure Object-Oriented and the Interface-Oriented approach that Microsoft’s OLE first introduced. Mainly, it allows for a form of multiple inheritance.
The Document Object Model Architecture
OOCalc documents structures more or less parallel those in Microsoft Excel™. They consist of workbooks containing sheets which are tables containing cells organized into rows and columns. So let’s start at the top.
In a hand-coded BASIC OO macro, the outermost object is “ThisComponent”. Which is the WorkBook. Operations conducted against ThisComponent apply to the workbook as a whole.
In most cases, what you really want is to play with individual sheets within the workbook. Sheets are (quoting the model API docs):
A spreadsheet is nothing else than a cell range with extended functionality
Since sheets are collected together in a workbook, to get a sheet, you obtain the workbook’s collection object. Then, since this object isn’t an array in its own right, you employ an accessor to get the sheet you want:
Dim sheet1 sheet1 = ThisComponent.getSheet().getByIndex(0)
Note that indexes start at 0. Or, for access by name:
Dim sheet1 sheet1 = ThisComponent.getSheet().getByName("Sheet1")
WorkSheets and CellRanges
Most of the real fun comes with working with cell ranges. A cell range can be anything from a single cell to the entire extent of the worksheet.
Here are some useful functions.
Dim theCell theCell = sheet1.getCellByPosition(1, iRow) Dim theRange theRange = sheet1.getCellRangeByPosition(1, iRow, 5, iRow+3)
And, of course, you can also obtain ranges and cells by name. Also, don’t forget that ranges can be aggregated into collections of ranges!
A special form of range is all the cells in a row or column.
cols = oSheet.getColumns() row5 = oSheet.getRows().getByIndex(4)
Remember, indexes start from 0.
Here’s one way to obtain the index of the last used row on a sheet. I don’t guarantee it’s the best, but it shows the API being used with a cursor object:
Dim Cursor Dim nRows as Integer Curs = oSheet.createCursor Curs.gotoEndOfUsedArea(True) nrows = Curs.Rows.Count
Note that this sample uses a property named “Rows” instead of the getRows() function call. Go figure.
At the Cellular level
Some useful manipulations:
theCell = oSheet.getCellByPosition(1, iRow) theCell.setPropertyValue("CharWeight",150) theCell.setPropertyValue("CharHeight",14) theCell.setPropertyValue("CharColor",0)
These items wanted to be set the hard way. CharWeight is how bold the typeface is, with 100% being normal, and 150% being bold. CharHeight is point size.
On the other hand, here are some properties that want to be set directly:
theCell.IsTextWrapped = true cols = oSheet.getColumns() cols.getByIndex(0).width=600 cols.getByIndex(1).width=7200 cols.getByIndex(2).width=1600 rowz = oSheet.getRows() rowz.OptimalHeight = true
Width is in .0001 meter increments, I believe. It’s in the manual. Note that in the final example, the entire sheet is affected. All of the rows are being optimized, since we didn’t obtain just one row.
All of this makes for pretty cells, but what about what’s in them? A cell can contain a value (floating-point number), a formula, or a string. There are therefore get/set methods for each of these as well as a method to determine which type of content a cell has.
Not much, I know, but it’s a start!