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, and why not BASIC?
It simply is a common language, with large user base.
When it comes with ODS, there is a few approach. Each has advantages, and disadvantages.
openpyxl, a good library that could process
xlsxfast. But it does not have anything to do with ODS.
pyexcel, Sure it can access ODS. But this has very few formatting capability.
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 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.
First you have to do is download the
Install, and check your extension manager.
Menu - Tools - Macros.
Ctrl+Alt+F11 for shortcut.
You can examine your scripts with APSO.
Click the Menu, and you can see all the feature.
REPL in console is a must have tool for developer.
This should be somewhere in you LO config suc as:
No embedded editor.
Where to Learn?
This tutorial is good for beginner.
Google Search Keyword
In LibreOffice we use UNO API.
UNO: Universal Networks Objects
API: Application Programming Interface
We should do this a lot.
We can use APSO for any LibreOffice Application. When it comes to data, I use Calc a lot.
The header is simple.
# coding: utf-8 from __future__ import unicode_literals
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.
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.
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.
For this demonstration to work, We need any workbook with more than one sheet.
I can utilize my annual accounting workbook.
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
or simply type
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 ].