Generate OpenDocument spreadsheets from DB2 (or any other) database

DB2 pureXML is IBM software for management of XML data that eliminates much of the work typically involved in the management of XML data.The OpenDocument Format (ODF) is an open international standard for office texts, presentations and spreadsheets that is very simple to process or generate automatically. This page is a short synthesis of an article published in September 2010 by N. Subrahmanyam, Using DB2 pureXML and ODF Spreadsheets, to give an idea (see my comments at the end) of how flexible ODF scripting is. Please read the original full article to know how to actually generate ODF documents from DB2 pureXML files. Continue reading Generate OpenDocument spreadsheets from DB2 (or any other) database

How to generate and update ODF spreadsheets without OpenOffice

Sooner or later, many of us need to process some numeric data in plain text format, be they system logs or sales totals, and to generate reports and charts out of those data. Scripts and utilities like gnuplot could be very useful in such cases, except when the results needs to be a normal spreadsheets with charts and formulas, which is both editable and compatible with people who only know how to deal with spreadsheets in office suites.

Continue reading How to generate and update ODF spreadsheets without OpenOffice

How to quickly apply color schemes to a spreadsheet with OpenOffice or LibreOffice

How do you quickly create a spreadsheet where, for example, every other line has a background of a different color? Here are a couple of methods.

The first one is faster (and works even in controlled environments where you can’t install extensions) if you want white rows alternated with colored ones: define a cell style and apply it only to cells in even (or odd) rows. Here is the detailed procedure:

Continue reading How to quickly apply color schemes to a spreadsheet with OpenOffice or LibreOffice

Why and how the OpenDocument format can save you a lot of time!

The OpenDocument Format (ODF) is an internationally recognized open standard for digital office documents whose importance has also been acknowledged by Microsoft. ODF is good for a lot of reasons I have already explained in Everybody’s Guide to OpenDocument. However, there is also one more reason why ODF is great for everybody who must produce a lot of office documents, one that will be the subjects of many posts on this website: ODF is really simple to generate or edit automatically. Even if you aren’t a professional programmer, it takes very little effort to put together a script that generates or processes in any way texts, presentations or spreadshets in ODF format.

How the openness of ODF makes automatic generation of documents much simpler

Very often, we use computers to produce many different versions, every time with new data, of some reference text, presentation or spreadsheet. Changing those kind of files manually makes sense only if it happens once in a while. When it’s a regular activity, instead, it can become a huge waste of time. ODF, however, makes it very quick and easy to insert raw data into texts, spreadsheets or presentations with the slightest possible amount of manual work and without even running OpenOffice. This is possible because an ODF file is just a ZIP archive, with pictures and macros in their own folders and the actual text written, in XML format, inside a file called content.xml. Therefore, in order to create a new, 100% compliant ODF file with different data, tables or images, you only have to open the archive, process the text inside content.xml or put new pictures in their folder if necessary and zip everything again. You must only use OpenOffice once, to create a template by hand if you if you don’t find a suitable one online.

The power of script-based ODF processing

You could perform repetitive generation and editing of ODF office files even manually, with a text editor like Notepad, Emacs or VI. The real power of ODF, of course, is in the fact that you can (and should) do all that processing automatically, with very simple shell or Perl scripts, that is with tools that are included in any Gnu/Linux distribution but can also work on Windows and Mac. The main advantages of this approach to office document processing are:

  • it works even without Openoffice, so it could even run on a server
  • there is no need of any relational database but you can use one if necessary
  • learning to do these things with shell scripts instead of OpenOffice macros:
    • gives you skills that you can reuse in a lot of other contexts.
    • allows very easy integration with other command line tools, from cron jobs to mass mailing, chart generation with Gnuplot or scaling, watermarking, framing of all images in a document with ImageMagick
  • above all, it’s much simpler (and faster) than you’d think!

The last point is the most important. Using the method explained here everybody with just a basic grasp of shell scripting can generate, modify or analyze hundreds of ODF text documents with just a few minutes of easy coding.

Of course, this approach is not really flexible, scalable or really robust, unless you add lots of code for error management, but the idea here is not to develop industrial strength solutions. If that’s what you need, you’ll have to either use real XML based tools like Odfpy or go straight to the source, the book OpenDocument Essentials by J. David Eisenberg, that you can also purchase at

This said, there are tons of cases where heavyweight tools like those aren’t worth studying, installing and deploying, but people still end up wasting many hours on repetitive edits. Learning how to write quick and dirty shell scripts that can open and update an ODF file is an easy but huge time saver in such situations.

What’s next?

Here are some of the ODF scripting recipes that you’ll find on this website in the next days (but if there are other recipes that you would like to see published, just ask and if possible I’ll write them!):

(note: some of these posts are updated excerpts of articles originally written for Linux Format, and are republished here with their permission)