PyOOCalc - Python Libre/Open Office Calc Interface API (UNO)

• Download the library from Github

Introduction

From time to time, I have tasks to generate reports or other documents for users. I mean the real end user, not other developer or a system administrator. Really well-designed reports.

I have had such tasks in the following projects:

• Document circulation project
• Accounting system
• Real estate accounting

The easiest way is to create document templates in Office software like Libre/OpenOffice. User is able to generate his own templates in the Office without the help of a developer.

Background

My first experience was with Microsoft Office, but later it became necessary to develop cross-platform solutions, so the choice fell on OpenOffice.

The most frequent document or report consists of:

• Header
• Table
• Footer

So, the OpenOffice Calc was selected.

The first realisation was on C++. I was using that library in numerous projects. But every new version of the Office requires library compilation. Or moreover, make some changes to the code. I have to compile library for a number of linux distributions. This is very tedious and a time consuming task.

These difficulties have led me to the search for simpler solution. I looked for the Office API and the list of the supported programming languages. I saw Python, found a simple example and tried it. The same python script works well on different distributions and versions of Office.

So the choice was made.

Description

There are a lot of python libraries for dealing with Libre/OpenOffice via API (UNO). As for me, one of the most interesting projects is pyoo. It supports a lot of features from open/save documents up to cell merging and working with charts and diagrams. But none of them implements number of functions wich I need.

I have to generate different documents for number of projects such as accounting system, estate management, document circulation and others. The easiest way is to use standard office software. End users can create their own template without great efforts.

It's easy to create template but how to know where I have to insert data into the template. I can use Cell indexes (column, row) or name (E5).

But what if I want to use more than one template for the report. For example, one report for landscape format, another for portrait. There is no warranty that I have to set the same value into the same cell name or cell position in different templates. So I have to store rules for different templates somewhere. I have found an easier way and that is NamedRange.

NamedRange is a name for a cell or cell range on a sheet. NamedRange is unique for entire document.

One more unfound feature is inserting rows. Any report or invoice has table with header and footprint. So I need to insert rows into table area and keep row format (font, cell merging, etc.).

Main Features

• Opening and creation of spreadsheet documents
• Saving documents to all formats available in OpenOffice
• Insert remove sheets
• Insert rows
• Set/get value by NamedRange
• Set/get value by Cell address or name

You can find an example of the document with NamedRanges and how to work with it in the examples folder.

Requirements

pyoocalc runs on Python 3.

The only dependency is the Python-UNO library (imported as a module uno). It is often installed with the Office suite. On Debian based systems, it can be installed as python-uno or python3-uno package.

Obviously, you will also need OpenOffice or LibreOffice Calc. On Debian systems, it is available as libreoffice-calc package.

Install

You can copy the pyoocalc.py file somewhere to your PYTHONPATH.

Usage

Starting OpenOffice.org in Listening Mode

pyoocalc requires a running OpenOffice or LibreOffice instance which it can connect to. On Ubuntu, you can start LibreOffice from a command line using a command similar to:

$ soffice --accept="socket,host=localhost,port=2002;urp;" --norestore --nologo --nodefault # --headless

The LibreOffice will be listening for localhost connection on port 2002. Alternatively, a named pipe can be used:

$ soffice --accept="pipe,name=hello;urp;" --norestore --nologo --nodefault # --headless

If the --headless option is used, then no user interface is visible even when a document is opened.

For more information, run:

$ soffice --help

It is recommended to start directly the soffice binary. There can be various scripts (called for example libreoffice) which will run the soffice binary but you may not get the correct PID of the running program.

Using the Code

A brief description of how to use the library code is as follows:

import pyoocalc

# open document
doc = pyoocalc.Document()
file_name = os.getcwd() + "/example.ods"
doc.open_document(file_name)

# Get document fields
fields = doc.fields()

# Get field "HEADER"
field = fields.field("HEADER")
print ("Document header is: " + str(field.is_null()))

# Set values
field = fields.field("TABLE_NAME")
field.set_value("Test table name")
print ("New table name is: " + field.value())

# Insert 5 rows
field1 = fields.field("FIELD_1")
num_rows = 5
step = 2
if num_rows > 0:
    field1.insert_rows(num_rows=num_rows-1, step=step, columns_to_copy=200)
for i in range(1, num_rows + 1):
    field1.set_value("F1." + str(i), 0, i * step - (step - 1))

# Set value="value1" at column=1, row=1 (B1)
sheet = doc.sheets().sheet(0)
sheet.set_cell_value_by_index(1, 0, "value1")
print (sheet.cell_value_by_index(1, 0))
del doc

You can find an example file here:

./src/examples/example.py

Run example:

$ python3 example.py

Documentation

You can find the documentation here:

./doc/index.html

Testing

Automated integration unit tests cover most of the code.

The test suite assumes that OpenOffice or LibreOffice is running and it is listening on localhost port 2002.

Tests script path:

$ ./src/unit-tests/test.py

Run tests:

$ python3 test.py

The output must be like this:

$ python3 test.py 
............
----------------------------------------------------------------------
Ran 12 tests in 8.719s

OK