Getting data from an OpenOffice.org spreadsheet

UPDATE 2019: It seems that the website containing the library required by the below code is down. As such, it’s not really useful anymore.

There is an easy way to get data from an OpenOffice.org spreadsheet using Python. Behold the following simple program. It allows you to simply get the contents from certain cells and handle them as you wish. I’m using this to fill a database with customer-supplied data in a spreadsheet.

#!/usr/bin/env python

"""Demonstration program for obtaining data from an OOo
spreadsheet.

Requires that OOo is started with:
    ooffice "-accept=socket,host=localhost,port=8100;urp;"

Then open the spreadsheet as usual, and run this Python script.

Also requires Danny's OOo lib from
    https://www.oooforum.org/forum/viewtopic.php?p=56015
"""

import Danny.OOo.OOoLib as OOoLib

desktop = OOoLib.getDesktop()

# access the current document
doc = desktop.getCurrentComponent()
sheet = doc.getSheets().getByIndex(0)

row = 0
while True:
    name = sheet.getCellByPosition(0, row).getFormula()
    email = sheet.getCellByPosition(1, row).getFormula()

    if not email:
        break

    print '%30s %20s' % (name, email)

    row += 1

As stated in the docstring, it requires Danny’s OOo lib.

I had to do a lot of digging to create this very simple program. The OOo API documentation is very complex. Hopefully this will simplify things for a lot of people!