Python in LibreOffice Macros

Are macros still useful? I think yes. Macros give the users ability to programmatically script the content. This can be really useful in many situations to automate the stuff. Of course there are many reasons not use them. Historically they have been proven very dangerous. Specifically the documents that you receive through email or download from the web that have embedded macros. By default I consider any macro I receive from others as dangerous and wont run it. I usually use macros that only I have written or checked out from "official" git repo of the institution that I am involved with. With that caution, let's proceed.

Macros are useful but writing macos in Basic is hard. Hence Python. In this how-to we will learn about the ways to

Macros and Security

You can control the macro security from Tools-Options-Security-Macro Security. I always have the security at Very High or High. Very rarely I have it medium. And ⚠️ never at Low ⚠️. Even if I have the security level at High, Once I am done with the work, I make it Very High again. Also like I said I prefer manually running the macro than auto run and hence Very High works.

Screenshot of Libreoffice Option Settings

Libreoffice Option Settings

Display Screenshot of Libreoffice Macro Security settings

LibreOffice Macro Security settings

Libreoffice also allows you to have trusted paths on your computer. If a macro is opened from these paths then they are always executed. Its a good option if you like to run the macros often. This way you can have Very High Security level and also can run Macros without issues.

Screenshot showing macro trusted paths

Macro trusted paths

Install

LibreOffice comes with Basic Scripting built in. But to support any other scripting language you need to install the specific packaage. For python install

sudo apt-get install libreoffice-script-provider-python
Screenshot Python Macros Menu

Python Macros Menu

Once you install restart your LibreOffice (Calc or Writer) and Go to Tools and Macros, you will find Python Macros.

Screenshot of Python Run Macro

Python Run Macro Screen

Create python directory

Create python directory inside the user folder. Where your all system level scripts will be store.

~/.config/libreoffice/4/user/Scripts/python

Now you should be ready to write your first script. Whatever you write in the Scripts folder should appear in the Menu to run. But these default screens are not useful enough to write any practical scripts. We need access to python REPL, IDE and Debugger. This is where the community is great. There are great tools available as extensions. We will install couple of extensions to make it developer friendly.

APSO - Alternative Script Organizer for Python

APSO - Alternative Script Organiser for Python. Its a great tool for developing and managing the python scripts. It gives you console and debugger access etc

  • Create module (python file, each function is a macro) or library
  • Standard Edit, rename, delete a module or library
  • Embed module from application/local into current document. So it can be sent
  • Export an embedded module for local editing
  • Launch Python Console for REPL
  • Debug the Scripts

First install the APSO Extension from LibreOffice website. Restart the libreOffice to see the menu item Organize Python Scripts (ALT+Shift+F11) in Tools - Macros folder.

Screenshot of APSO dialog

APSO dialog

Now you are ready to develop.

Write your script live

Let's start developing like a Pythoner in REPL mode. Create a new LibreOffice Calc sheet. Save it some where. Create a sheet called welcome sheet. Now we will start a console and try to get the sheet name.

import uno
context = uno.getComponentContext()  
desktop = context.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", context)  
document = desktop.getCurrentComponent()  
sheet = document.Sheets[0]
sheet.getName()

The code is above but we can run in REPL mode so we can experiment as we go. Launch APSO and click on Menu and Launch console. It should launch your standard Python console. Launch will also print Python Version. Make a note of this. This is the version you will develop for.

Screenshot of Python Cosole

Python Console

Save your script and debug

Now that you have a script. Create a module - Edit and save it as def hello_world. APSO usually opens your default editor (GEDIT) for you to edit.

# -*- coding: utf-8 -*-
from __future__ import unicode_literals
import uno


def hello_world():
	context = uno.getComponentContext()  
	desktop = context.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", context)  
	document = desktop.getCurrentComponent()  
	sheet = document.Sheets[0]
	sheet.getName()

You can also start debugger from APSO menu. It opens a window and works just like any other debugger. Its simple and easy to use.

Screenshot of APSO debugger

APSO debugger is simple and very friendly to use.

Also if you like to use a different editor then you can go to Extensions-APSO-Options and give the path of your favourite editor. I use sublime text.

APSO custom editor settings screen

APSO custom editor settings

What next?

That's it for this tutorial. You can also refer LibreOffice Wiki for more details. In the next tutorial we will explore the uno objects and document structure. We will also develop a real and useful Python macro.

3 Responses

  1. bfris says:

    Nice article. I would strongly recommend another extension for LibreOffice: MRI. This is an inspection tool that displays/executes properties and methods of various objects. For example, if you have a cell selected in Calc, you can see that it has methods setString, setValue, and many others.

  2. Jorge Hygino B Sampaio Jr says:

    How can I call a function written in Python?
    For example, define a function to add two numbers
    def MyAddPy (a, b)
    return a + b

    From within a cell type
    =MyAddPy(1, 2)

    to obtain 3 in the cell.

  1. October 16, 2020

    […] can see the example usage in the below blog […]