Writing/Reading arrays of Data in Open Office using Python. Anyone have any example code?

Question

So I have written a class that makes it extremely easy to interface with either Excel or Gnumeric using Python, and would like to extend the class to include Open Office as well. I could do this in 30 minutes if I just had the ability to do the following:

• Set a single value on an arbitrary sheet and workbook
• Get a single value on an arbitrary sheet and workbook

If these are slow/there is a way to do the following, I also need to be able to:

• set/get an array '''
• set/get a matrix '''

ALSO, the ability to create and rename sheets would be great.

This is a shoutout if anyone has worked on this before. If they give me the information, I will reference them at the top of the file

My project can be found here: https://sourceforge.net/projects/pyworkbooks/ and I encourage you to check it out.

Answer 1

As a matter of fact, to acess OpenOffice or LibreOffice via Python one has to go through an absolutely opaque amount of boiler plate inherited from the StarOffice times - never properly documented (one feels) or simplified since then.

I had once lectured on this subject, and I took almot 40 minutes, just to retrieve the parts of my lecture to set up the example bellow.

On the other hand it just worked with the latest LibreOffice version - 3.3 - I am confident it works for OpenOffice as well (but I would not advice anyone to stick to OpenOffice, it is an Oracle dead end at this point)

The example bellow use the slow method of connecting to a running LibreOffice instance from the "outside". This is extremely slow - you will have to refer to the documentation on how to make it work as a macro from "within" the program, for better performance. (it is really slow in this way).

However, this method allows you to explore the methods available to developers using a Python terminal and introspection.

The first poorly documented part is that you have to start Open/LibreOffice with: soffice "-accept=socket,host=0,port=2002;urp;" For connections to be accepted. Then, create a new spreadsheet through its interface and with the python interpreter that comes with the Office Suite run the following code (either interactively or as a script):

import uno
import socket  # only needed on win32-OOo3.0.0 

# get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()     

# create the UnoUrlResolver
resolver = localContext.ServiceManager.createInstanceWithContext(
                       "com.sun.star.bridge.UnoUrlResolver", localContext )

# connect to the running office
ctx = resolver.resolve( "uno:socket,host=localhost,port=2002;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager  
# get the central desktop object
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)

# access the current writer document
model = desktop.getCurrentComponent()

try:
    sheets = model.getSheets()
except Exception:
    raise TypeError("Model retrived was not a spreadsheet")

sheet1 = getattr(sheets, sheets.ElementNames[0])

# At this point, you can use "dir" to check the methods and 
# attributes available for the sheet
# the methots "getCellByPosition, to retrieve a cell object,
# which has "getFormula" and "setFormula"
# methods. 

for i in xrange(10):
    for j in xrange(10):
        cell = sheet1.getCellByPosition(i, j)
        cell.setFormula(str(i * j))

c1 = sheet1.getCellByPosition(1,1)

As you can see, the connecting part of this is boilerplate I got somewhere else years ago, and I doubt any living person could find any rationale in such stuff. Once you get down to the "sheets" object, though, the attributes and methods on the object start to make sense.

There is a complete developer manual online, that even could allow one to understand the connection part:

http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/OpenOffice.org_Developers_Guide