LibreOffice - Macro - APSO

Preface

Goal: LibreOffice Calc Macro using Python with APSO.

There is already a few APSO tutorial. I do not want to make it just another APSO tutorial. But the thing is, we require APSO introduction, to run these script. At least APSO make my life easier.

Why Python?

Why Python, and why not BASIC?

It simply is a common language, with large user base.

Why Macro?

When it comes with ODS, there is a few approach. Each has advantages, and disadvantages.

1. openpyxl, a good library that could process xlsx fast. But it does not have anything to do with ODS.

2. pyexcel, Sure it can access ODS. But this has very few formatting capability.

3. python macro, It can natively process ODS. But it require either LibreOffice or OpenOffice.

Of course I can use xls in LibreOffice. I just want native open document stuff from the document foundation.

What is APSO

Alternative Script Organizer for Python

• APSO Link

APSO is an LO (LibreOffice) extension that can make your life easier. APSO simply manage python module, so it can be edited easily. And also APSO has its own console, so you can test your code, and print the output just as you are in CLI mode.

APSO comes light. APSO does not have its own text editor. So you still need to edit python code with your favorite text editor.

Using APSO

Install

First you have to do is download the oxt file.

Install, and check your extension manager.

Macros Menu

Check The Menu - Tools - Macros.

Use Ctrl+Alt+F11 for shortcut.

Scripts Dialog

You can examine your scripts with APSO.

Feature

Click the Menu, and you can see all the feature.

Console

REPL in console is a must have tool for developer.

Source Location

This should be somewhere in you LO config suc as:

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

Editor

No embedded editor.

Good Tutorial

Where to Learn?

This tutorial is good for beginner.

• tutolibro.tech: Free online tutorials

Discussion Group

• t.me/LibreOfficeScripting

Google Search Keyword

In LibreOffice we use UNO API.

• UNO: Universal Networks Objects

• API: Application Programming Interface

We should do this a lot.

Calc Example

We can use APSO for any LibreOffice Application. When it comes to data, I use Calc a lot.

Header

The header is simple.

# coding: utf-8
from __future__ import unicode_literals

Bootstrap

This a common code required to access worksheet in Calc. We can wrap them in a function, so we do not have to write the same inityialization, over and over again.

def context_test():
  # set global variables for context
  global desktop
  global model
  global active_sheet

  # get the doc from the scripting context
  # which is made available to all scripts
  desktop = XSCRIPTCONTEXT.getDesktop()
  model = desktop.getCurrentComponent()

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

# Main
context_test()

This is just an example. For real life example, do not use context_test() in global declaration, or you will get error, if there is no active sheet.

Simple Example

Consider open a new sheet. And rename the script using macro.

def rename_sheet_test():
  active_sheet.Name = "Blewah"

Open APSO menu, and execute the script.

The sheet name will be changed immediately as below:

I know this looks lame. But we have to start somewhere simple.

REPL Console

Just like any other mediocre coder, I like to debug in CLI with console.

Example Script: Query All Sheets

Consider have a look at this simple script below.

def query_sheet():
  all_sheets = model.Sheets
  print("Total of %d sheets" % len(all_sheets))
  sheetnames = [s.Name for s in all_sheets]
  print(sheetnames)

This script query all sheets, and display all the sheet title in Console.

Example Workbook

For this demonstration to work, We need any workbook with more than one sheet.

I can utilize my annual accounting workbook. This monthly.ods consist 24 worksheets.

• 12 BS (balance sheet)
• 12 PL (profil loss)

APSO Console: Python REPL

Finally we can print all the sheet names in console.

Note that you can exit the console using CTRL+D, or simply type exit() method.

What is Next 🤔?

We have the proper tool to work with macro. It is a good time to dive into real world example.

Consider continue reading [ LibreOffice Calc - Python Macro - Split ].