6

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Garrett Berg
  • 2,585
  • 1
  • 22
  • 21

4 Answers4

8

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

jsbueno
  • 99,910
  • 10
  • 151
  • 209
  • Wow, thanks for all the code and the time you spent putting this together. Is there a way to also access arrays/matrixies of data? Excel is also slow at accessing single points, but it goes pretty quick when dealing with arrays. Maybe there is a similar thing for open office. – Garrett Berg Mar 24 '11 at 15:42
  • 1
    The code you give here references a name `model` that was never bound to anything. Is there something missing from this code? – bignose Jul 11 '12 at 03:48
  • @bignose -sorry.. fixed now – jsbueno Feb 11 '13 at 19:48
  • Thngsa have not changed much> I have some example OOCalc Extension code that allows one to put Python code inside Sheet cells themselves (and compute, and inject values in other cells in an easy way) - it may be useful for anyone reaching here: https://github.com/jsbueno/librepylot – jsbueno Feb 15 '16 at 19:50
1

The inter-process API for connecting to LibreOffice (and also OpenOffice and StarOffice) is called UNO. It is documented at the LibreOffice API documentation site.

As jsbueno says, it expects that a daemon is running to communicate with. The command-line arguments to the ‘soffice’ command starting the daemon determine what host and port values you need to supply in your UNO calls.

bignose
  • 30,281
  • 14
  • 77
  • 110
1

You could also try ezodf This was the best python odf library I found

yvess
  • 1,992
  • 19
  • 17
0

You can use pyoo. Here is my answer to similar question https://stackoverflow.com/a/27082610/886607

Community
  • 1
  • 1
Ahmad Yoosofan
  • 961
  • 12
  • 21