Macro Programming in OpenOffice/LibreOffice with using Python[EN]

The open-source world is growing incredibly fast. To take a step into this, I have started to learn about macro programming in LibreOffice. In the first two or three story that I will write about macro programming, I will use this tutorial mainly. But it has some missing parts and a little complicated to start from zero. So you can check this out if you have any problems that I did not mention.

What Is LibreOffice?

LibreOffice is equivalent to Microsoft Office in the open-source software world. There is another office like LibreOffice which is called OpenOffice. OpenOffice is almost the same with LibreOffice and what I have done here with LibreOffice work in OpenOffice too. You can see the differences between them from here if you wish.

Components of LibreOffice

LibreOffice has four main parts. Writer is a word processor, Calc is a spreadsheet program, Impress is a presentation program, and Draw is a program like Microsoft’s Visio.

Starting to Use Macros

To start using macros in LibreOffice, we need to make settings first. Let’s make these now.

We should go to LibreOffice > Tools> Options> LibreOffice > Security

and change Macro Security > Medium

In this way, LibreOffice will allow us to use our macros, but will also protect us from macros that we do not know the source.

Why Python?

Macros are pieces of programming code that runs in office suites and helps automate routine tasks. Specifically, in LibreOffice API these codes can be written with so many programming languages thanks to the Universal Network Objects (UNO). Among them are: Open/LibreOffice Basic (Thanks to Foad S Farimani for the correction :) ), Java, C/C++, Javascript, Python.

So which language should we use? Since LibreOffice is multi-platform we can use our documents at different platforms like Mac, Windows, and Linux. So we need a cross-platform language to run our macros at different platforms. We can eliminate Visual Basic because of that.

Java and C/C++ require compilation, are much more complex and verbose. So we can eliminate these too.

Probably we will have some problems while working with numbers if we choose JavaScript. For example it has rounding errors ( 0.1 + 0.2 does not equals 0.3 in Javascript). So we can eliminate this too. But Python is very powerful at numeric computation thanks to its libraries. Libraries likeNumpy and Numexpr is excellent for this job. So we should choose Python 3 for macro programming.

Connecting to LibreOffice from Command Line

Before we create our own macro, let’s see how we can manipulate LibreOffice with Python by connecting to LibreOffice from the command line.

First, launch LibreOffice Calc with an open socket to communicate with from the shell.

For Linux:

soffice --calc --accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

Update: After I updated my Libreoffice, soffice command does not work anymore. To reach soffice you can write this:

/opt/libreoffice$VERSION/program/soffice --calc --

$VERSION is your libreoffice version.

For Mac OS:

/Applications/LibreOffice.app/Contents/MacOS/soffice --calc \
--accept="socket,host=localhost,port=2002;urp;StarOffice.ServiceManager"

For Windows:

"C:\\Program Files (x86)\LibreOffice 5\program\soffice.exe" --calc --accept="socket,host=localhost,port=2002;urp;"

Write these commands to the comment line and this should open Calc.

(If you have any problem, have a look at the proposed workarounds.)

Now we have to open Python Shell.

For Linux:

We need to open another Terminal because this one is busy with keeping open the LibreOffice. In the new Terminal write this.

python3

Update: This may give this error when you trying next command:

ModuleNotFoundError: No module named ‘uno

If this happens you should use Python Interpreter that comes along LibreOffice. You can find it at:

/opt/libreoffice$VERSION/program/python

For Mac OS:

/Applications/LibreOffice.app/Contents/MacOS/python

(Update) The correct path for Mac OS (Thanks to Foad S Farimani):

/Applications/LibreOffice.app/Contents/Resources/python

For Windows:

"C:\\Program Files (x86)\LibreOffice 5\program\python.exe"

Now we write these codes at the opened Python Shell.

import socket  # only needed on win32-OOo3.0.0
import uno
# 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()

These lines are common for every documents (Text, Spreadsheet, Presentation, Drawing).

Now let’s interact with the document.

Since we open LibreOffice with --calc the parameter, let’s try the spreadsheet commands.

# access the active sheet
active_sheet = model.CurrentController.ActiveSheet

# access cell C4
cell1 = active_sheet.getCellRangeByName("C4")

# set text inside
cell1.String = "Hello world"

# other example with a value
cell2 = active_sheet.getCellRangeByName("E6")
cell2.Value = cell2.Value + 1

If we open LibreOffice with the --writer parameter, we can also do the following operations.

# access the document's text property
text = model.Text

# create a cursor
cursor = text.createTextCursor()

# insert the text into the document
text.insertString( cursor, "Hello World", 0 )

Congratulations! Now you can control LibreOffice from outside with Python. I’m finishing this here. In my next article, I will talk about embedded macros. Goodbye :)

To see more advanced examples you can check this repository that I created for this blog series.

GitHub - rebahozkoc/libreofficepythonmacros: LibreOffice Python Macro Samples

Important resources I refer to when creating this article series:

Muhammet Kara

Interface-oriented programming in OpenOffice / LibreOffice : automate your office tasks with Python…

www.pitonyak.org/oo.php