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.
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:
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.
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 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.).
- Opening and creation of spreadsheet documents
- Saving documents to all formats available in OpenOffice
- Insert remove sheets
- Insert rows
- Set/get value by
- 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.
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
Obviously, you will also need OpenOffice or LibreOffice Calc. On Debian systems, it is available as
You can copy the pyoocalc.py file somewhere to your
Starting OpenOffice.org in Listening Mode
pyoocalc requires a running
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
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
--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:
doc = pyoocalc.Document()
file_name = os.getcwd() + "/example.ods"
fields = doc.fields()
field = fields.field("HEADER")
print ("Document header is: " + str(field.is_null()))
field = fields.field("TABLE_NAME")
field.set_value("Test table name")
print ("New table name is: " + field.value())
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))
sheet = doc.sheets().sheet(0)
sheet.set_cell_value_by_index(1, 0, "value1")
print (sheet.cell_value_by_index(1, 0))
You can find an example file here:
$ python3 example.py
You can find the documentation here:
Automated integration unit tests cover most of the code.
The test suite assumes that
LibreOffice is running and it is listening on localhost port 2002.
Tests script path:
$ python3 test.py
The output must be like this:
$ python3 test.py
Ran 12 tests in 8.719s