I love OpenOffice as a user. I have absolutely no envy for (ahem) That Other Product. In truth, just about every new MS-Office feature added since 1997 has been completely meaningless to me.
I have been manipulating OpenOffice/LibreOffice/StarOffice in various creative docs for some years now, but mostly by running XML processes against the ODF files. While XSLT is a royal pain, it beats the alternatives, and the ODT format is straightforward enough.
Recently, however, I’ve had a need to do some heavy reformatting of spreadsheets. They get generated as CSV files, imported, formatted, and various formulas plugged into them. I got tired of doing this by hand, so I decided it was time to automate the process.
¡Ay, Caramba! What a nightmare!
On the one hand, there are all sorts of different ways to script OpenOffice. Java, Python, beanshell, and Basic. Two different kinds of Basic.
If you use the macro recorder, it captures scripts using one programming interface. However, when you write scripts by hand, you would normally use a completely different, less-awkward interface. Even if you wanted to use the capture API, trying to figure out what the various functions and features are makes it a rough proposition, since almost everything is basically set up an array of parameters and passing it to an execute function.
The simpler, hand-coded BASIC API, however, isn’t much better. Unlike the capture API, which maps onto the document model objects directly, the BASIC API attempts to provide convenience methods and properties. Unfortunately, some of them are pretty darned inconvenient, and as awful as the access documentation is, at least the low-level stuff is documented. Lots of luck with the high-level stuff.
I’ve heard the excuse made that the reason MS has so much better Office programming documentation is that they have the force of a Fortune 50 company behind them, but that rings hollow. OpenOffice after all was owned by Sun, then Oracle, and the community of OOffice users is pretty large. There really isn’t an excuse. About the best I’ve seen for how to program OpenOffice is a book that’s out of print.
So, while I myself don’t have the resources to remedy this situation, I’m putting together something that will at least work as a “cheat sheet” for OOCalc macro programming. Here it is: OpenOffice Calc Macro CheatSheet
