libreofficecalc

How to embed python scripts into LibreOffice Calc

I have a successful .py script that can open an existing LO Calc doc (saved as .xlsx so that I can use openpyxl) which appends information into the file which saves the file and closes it on termination of the script. The .xlsx file cannot already be open when the script runs, or the file gets corrupted and all data is lost.

But what I really need is to be able to see the data populating the spreadsheet as it happens so I can have a dynamic chart visually displaying the information.

I installed APSO (tq!) which I believed would let me to embed editable python user scripts. I want the script to run when I click on a programmable button I made in my sheet.

I saved the script at /home/USER/.config/libreoffice/4/user/Scripts/python which I made in accordance to instructions from the ScriptForge 'code assistant' software from the LO Conference 2021.

In the macro selector window, under "My Macros", I see my script, but access to the "OK" button is denied to me (grayed out), yet I am able to select a script from the "LibOCon_2021.py" ScriptForge file.

Comparing my .py script to the ones in "LibOCon_2021.py", It seemed the difference was that my script didn't have a definition, so at the top of my script I added "def to_be_embedded(args=None):" and saved it, but the "OK" button is still not available so I cannot tie it to the button press.

So how do I actually embed my script and get it to run when I click on the button?

Solution

  • Here are steps to create a button that runs an embedded python macro.

    1. Create a new Calc spreadsheet. It doesn't even need to be saved for this example to work, but if you do save then use .ods format.
    2. With APSO installed, go to Tools -> Macros -> Organize python scripts.
    3. Select "Untitled 1" to embed the macro there, and in the Menu, select Create module and OK. (This is embedded in the spreadsheet file, unlike in your question where the script was placed in the LibreOffice user directory, which is what "My Macros" means).
    4. Select the newly created file called "Module" and the menu will now contain different options. Select Edit. If this doesn't work, configure APSO to use the correct text editor.
    5. Enter the following code and save (for example :w if using vim).
    def test(args=None):
    raise Exception("Hello from Python!")
    1. From the Form Controls toolbar, create a Push Button. Calc must be in Design Mode to do this.
    2. Right-click on the push button and choose Control Properties. Under Events, select the dots next to Execute action and then press Macro. Expand "Untitled 1" and select "Module", where "test" will show up as the macro name. Press OK.

    Now turn off Design mode and click on the button. We can see that it runs because it produces the following message:

    com.sun.star.uno.RuntimeException: Error during invoking function test in
module vnd.sun.star.tdoc:/1/Scripts/python/Module.py (<class 'Exception'>:
Hello from Python!