In this episode of my LibreOffice Calc & Python Programming tutorial, we are going to write our first Python Macro that will write ‘Hello World’ in cell A1 of a LibreOffice Calc document. How exciting is that!?!
Here is the plan for this part 2 of the tutorial:
- Get your workflow right: deciding between your macro embedded in the file or saved in a macro repository.
- First line of code and writing “Hello World!” in Cell A1.
- Assigning the macro to a LibreOffice Control Button.
Get your workflow right
With the APSO add-on (see previous article) you can embed your macro with the LibreOffice File which can be useful when you want to send your file with the Python Macro. But the Python macro (also named Python Script) itself is not accessible outside of the LibreOffice file. So you’ll need to open the LibreOffice file and open the Python Macro organiser in order to edit the Python program.
Whereas if you choose to not embed the Python program but to put it in an organised directory(still using APSO), you can re-use it for any other LibreOffice file and you also can edit the program without opening the LibreOffice file if you open it from the relevant folder. The trade-off is that if you want to send your file and the Python macro to go with it you need to send both of them, not just the file.
I am in favor of keeping my programs in a tidy manner to be able to re-use them across many different files but the choice is entirely up to you.
To illustrate these 2 cases let’s have a look at an example:
I have 1 file called “HelloWorld.ods” and 2 Python files 1 called “HelloWorld.py” and 1 called “Testing.py”. The first python file is embedded in the LibreOffice file, the second is in shared Python Scripts directory.
In the first case scenario, after opening HelloWorld.ods, I can go to the Python Script Organizer, in Tools>Macro>Organize Python Scripts (or using shortcut Alt+Shift+F11) from the APSO extension, or use the LibreOffice Standard Macro Organiser provided.
In the Python Scripts Organiser you will see that your Python Macro is listed within the LibreOffice File’s name.
And when you choose to edit the Python Macro (Choosing ‘Menu>Edit’ from the ‘Python scripts’ window), the edited file’s path is a temporary path only used for the time of editing this Python program.
So where is the Python Script saved on your harddrive?
As I wrote earlier, it’s embedded in the LibreOffice File. If you don’t believe me, here is a trick to find out:
If I rename my ‘HelloWorld.ods’ in ‘HelloWorld.zip’ I can then unzip it to see the archived files and folders that constitute the LibreOffice File itself (with e.g. Archive Manager on Linux, winzip on Windows, or Archive Utility on Mac). In the list of folders I can then spot a folder called ‘Scripts’, then a sub-folder called ‘python’, and OH SURPRISE! the python script is there!
If you want to do that too, when you’re done don’t forget to rename the file back with its ‘.ods’ extension.
In the second case scenario, our Python script ‘Testing.py’ is not listed in the Macro manager within the LibreOffice File, but this time I created it as part of my Library ‘MyPythonLibrary’ within another Library called ‘Tutorial’.
And this time if I edit ‘Testing’, I can see the path is not a temporary one and I can access it even when LibreOffice is closed.
In fact your Python programs can be directly accessed on your computer at the following location:
(to see folder ‘.config’ you need to ‘Show hidden files’ , the shortcut CTRL+h).
(I think it’s)/Users/<user name>/Library/Application Support/LibreOffice/4/user\Scripts\python
(Please let me know in a comment if you know for sure what the folder is for Mac OS).
First line of code!
I know you can’t wait anymore! So let’s write our first line of code in Python, within a LibreOffice Calc Document.
So after creating a new Calc document, go in the Macro manager (if you have installed the APSO extension: ALT+Shift+F11), then as explained earlier in this lesson you can your program either in calc document or in a library outside the document. To do so, choose between ‘My Macros’, ‘LibreOffice Macros’ or the name of the document itself.
I do prefer saving my python macros in the ‘My Macros’ Library.
You can also create sub-folders, called libraries, or directly create your program by choosing ‘Create Module’ in the ‘Menu’.
So go ahead and create a Module, name it “HelloWorld”, then once the module is listed and selected, click again on ‘Menu’ and choose ‘edit’.
And then you get either a default editor or the one you have linked with APSO to pop up with a new program that content nothing but a couple of lines like these:
The first line is a comment.
A comment in a program is a line that is not processed when the program is executed. So it’s not a line a code, it is usually to help the person who is going to read your program (most of the time, yourself) to understand what the following code is aiming to do.
So write comments in your Pyhton programs simply add a ‘#’ at the start of the line.
Don’t worry about the second line, in fact you can delete these two lines for now.
Instead you could like me write your name, the date and what the program is for:
# Created by Super Busy Daddy 16/08/2019
# This program displays 'Hello World!" in cell A1 of the
# current Calc document.
Then Finally here is the code for writing “Hello World!” in cell A1:
# Created by Super Busy Daddy 16/08/2019 # This program displays 'Hello World!" in cell A1 of the # current Calc document. def HelloWorld(): """Write 'Hello World!' in Cell A1""" # 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 # write 'Hello World' in A1 active_sheet.getCellRangeByName("A1").String = "Hello World!"
You can either copy this block of code above or better type it manually yourself. It is longer to type it yourself but it’s the best way to remember this new language for you.
When you type it make sure that you leave 4 empty spaces to indent the lines that are not starting at the beginning of their lines, like:
desktop = XSCRIPTCONTEXT.getDesktop()
If you don’t do that it will create an error because the Python interpreter will not recognise the line as part of the function define by:
So the instruction ‘def’ is to ‘define a function’. A function is a set of written instructions that can be run on its own or ‘called’ by other functions to be executed.
Our function here is called ‘HelloWorld’ and the part between parenthesis ‘()’ is where you write the arguments to be used by the function but in our case we are not ‘passing’ any argument to the function ‘HelloWorld’ for now, that is why we have nothing within the parenthesis ‘()’.
You need to know that when we will assign our macro to a button we will need to pass a specific argument for our macro to work when we press the button.
Lines 1,2,3,8,9,13,16: these lines start with a ‘#’ so they are comments.
Lines 4,7,12,15,18: these empty lines are only for making the program easier to read,by separating the different set of instructions within the program. The Python interpreter does not need them but these empty lines make the code more readable for us.
Define a new function called ‘HelloWorld’ that has no arguments.
The ‘:’ at the end is essential, it’s to say that what is following is the actual code written for this function. And every line that follow this function’s 1st line should start with 4 spaces.
The function finishes when either there is nothing after, or when a new line starts from the beginning with no space to start with (excluding comment lines).
"""Write 'Hello World!' in Cell A1"""
This line is simply a description of the function, it starts and ends with 3 quotation marks. It is not really useful for us right now but it is a good practice to have a short description for each of your function. It’s different from a comment because this description can be displayed from outside of the program as a ‘help’ note.
desktop = XSCRIPTCONTEXT.getDesktop()
This line is what allows to use Python language to manipulate LibreOffice’s components. It is a bit complicated to explained exactly what this line does but in short:
We define ‘desktop’ to hold the ‘
XSCRIPTCONTEXT‘ interface provided by LibreOffice for our scripts (programs). The ‘getDesktop()’ function is used to obtain a reference for our scripts to operate on. (It is not a reference to a computer desktop!).
model = desktop.getCurrentComponent()
In the same way we define here ‘model’ to be the current component of ‘desktop’, so effectively we are referencing to the current document open, which should be a Calc document.
active_sheet = model.CurrentController.ActiveSheet
Here we are defining ‘active_sheet’ to be the current spreadsheet you are looking.
active_sheet.getCellRangeByName("A1").String = "Hello World!"
Finally! Now we have a reference to the current LibreOffice Context, the current LibreOffice Calc Document (component) and the current spreadsheet. So now we can access the cell ‘A1’ from that current spreadsheet and assign to its ‘String’ parameter the text “Hello World!”.
Add to function HelloWorld a line of code to display “Bye Bye World!” in cell ‘B2’. I am sure you can handle that!
Assign the macro to a LibreOffice Control Button.
You will quickly realise when you program macros within libreOffice that it’s not really convenient to execute them through the Macro Manager because you need to access that menu and it takes a few clicks to do so (Quickest way to access the APSO Macro manager being [Alt] + [Shift] + [F11]). It is easy to create a button on your spreadsheet and to assign it to your macro and when you’ll click on that button it will execute the macro.
So first we’ll create a button on the spreadsheet then we’ll assign the python macro to this button.
Create a button:
Simply by clicking on Menu ‘Insert>Form Controls>Push Button’:
Then once you have the little cross to insert the button, just click for upper left corner of the button, then hold and release where you want the bottom right corner of the button.
Assign the python macro:
Once you have the button displayed, right click on it and select ‘Control…’
Then in the window ‘Properties’ select the ‘Events’ tab, scroll down a bit to find in the list of events the one called ‘Mouse button pressed’. On the right of this ‘Mouse button pressed’ entry click on the button ‘[…]’.
A window named “Assign Action’ pops up, click on the button ‘[Macro]’ on the right hand side of this window.
You should then get the ‘Macro Selector’ with your Macro Libraries. Simply go to the library where your macro is saved and select the macro ‘HelloWorld’,then press ‘OK’.
Then ‘OK’ again in ‘Assign Macro’ window, than close the ‘Properties’ window.
Last thing to do is to come out the ‘Design’ mode to be able to click on the button, do that by going to ‘Tools>Forms>Design Mode’.
And now when you click on the button… tada…
This is because to be able to work, the function HelloWorld need all information from LibreOffice Context in the form of an argument. So we need to pass the argument ‘*args’ in our function HelloWorld. So in your code in line 5 instead of:
You should have:
Save your program again and then when you press on the button the cell A1 will display ‘Hello World!’ !!!
So there is the final version of the macro:
# Created by Super Busy Daddy 16/08/2019 # This program displays 'Hello World!" in cell A1 of the # current Calc document. def HelloWorld(*args): """Write 'Hello World!' in Cell A1""" # 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 # write 'Hello World' in A1 active_sheet.getCellRangeByName("A1").String = "Hello World!"
You can clone or download this code from gitHub here.