Python, OpenOffice: Programmatically Manipulating spreadsheets

Question

I have an spreadsheet-based automated report that needs to be created daily, with some charts, aggregating functions (e.g. SUM and AVERAGE) and formatted cells (Dates, percentage, etc.).

I have tried to write these results directly to an Excel file, but Python's xlwt and xlrd don'y support charts and functions.

Moreover, trying to open an existing, formatted Excel file and changing some cell's values ended up erasing all charts and functions in the existing file.

Is there a way to write charts and functions to an OpenOffice spreadsheet, or at least change cells in an existing spreadsheet without erasing data? If there is a Pythonic way to do it, I can easily convert the OO file into an Excel file and deliver it.

score 5 · Accepted Answer · answered Mar 01 '10 at 14:40

5

You can use PyUNO, a Python library to use UNO API.

Here is a Python example to do some manipulations in a Calc document.

answered Mar 01 '10 at 14:40

Desintegr

6,992
1
22
17

Thanks, will look into it. If I'm lucky, I can create the charts and formulae beforehand and simply change the cell's contents. – Adam Matan Mar 01 '10 at 14:53

score 1 · Answer 2 · answered Mar 01 '10 at 14:37

1

Are you looking for this: http://ooopy.sourceforge.net/

Open Office.org API's accessible from Python?

Or this? http://api.openoffice.org/

The OpenOffice.org API Project?

This may be helpful, also: http://wiki.services.openoffice.org/wiki/Python

answered Mar 01 '10 at 14:37

S.Lott

384,516
81
508
779

I've been looking there, but found no concrete explanation on adding charts and formulae. – Adam Matan Mar 01 '10 at 14:52

flowtron · Answer 3 · 2020-04-01T15:22:33.520

For creating spreadsheets easily: use odslib or for python3: odslib3 … even if the project was last updated over six years ago (2013-07-19) it worked straight out of the box and with just one example viewed. Download the package from the repository for the examples.

$ pip install odslib

and then a sample spreadsheet could be created like this:

#!/usr/bin/python

import sys
import odslib

doc = odslib.ODS()

def writeRow( doc, row, name, power, rating ):
    doc.content.getCell( 0, row).stringValue( name )
    doc.content.getCell( 1, row).stringValue( power )
    doc.content.getCell( 2, row).floatValue( rating )

# the column names
writeRow( doc, 0, "Name", "Power", "Rating" ) 

# some lines of content
writeRow( doc, 1, "Mr. Incredible", "Strength", 0.8 ) 
writeRow( doc, 2, "Elastigirl", "Elasticity", 0.9 ) 
writeRow( doc, 3, "Frozone", "Ice", 0.7 ) 
writeRow( doc, 4, "Syndrome", "n/a", 0.3 ) 
writeRow( doc, 5, "Jack-Jack", "All", 1.0 ) 

doc.save("supers.ods")

Python, OpenOffice: Programmatically Manipulating spreadsheets

3 Answers3

Linked

Related