How to embed Python script in ODS file

Description

LibreOffice Calc gives users the ability to create their own macros in the Python language, however, these macros can only be stored on the user's PC, i.e., in the program itself. Unfortunately, LO Calc does not have a built-in ability to store Python scripts in the *.ods file itself, which creates inconveniences when working with such scripts, as they cannot be transferred along with the data to another user.

This problem can only be solved manually, namely by repacking the *.ods file, by adding the necessary Python scripts.

So, let's find out how to embed Python scripts in an ODS file, so that it is possible to send them to other users.

Python Folder

All Python macros are stored in the following folder:

%APPDATA%\LibreOffice\4\user\Scripts\python

If the folders Scripts and python are missing, then you need to create them manually.

Python macro code

Below is the Python script for the HelloPython macro, which writes the phrase "Hello Python!" into cell "B8" in the LibreOffice Calc program.

To add a new macro, create the file myPyScripts.py and open it using Notepad++ (або Windows Notepad) and copy the text below into it:

from scriptforge import CreateScriptService

def HelloPython():
    doc = CreateScriptService("Calc")
    doc.SetValue("B8", "Hello Python!")
    return None

Then, close Notepad++ and return to LibreOffice Calc. To run this script, open the menu Tools - Macros - Organise Macros - Python..., select "My Macros", expand the folder "myPyScripts" and by selecting HelloPython click the button Run.

Transferring Python script to ODS file

In order to transfer the Python script to the *.ods file, you need to unpack it with any archiver (7-zip, WinRAR, etc.) into a separate folder. Yes, that's right, your *.ods file is simply an archive of a set of folders and files.

Next, in this unarchived folder, create a subfolder Scripts, and in it - a subfolder python. The next step is, copy the myPyScripts.py file to the python folder.

It is also necessary to explicitly specify the path to the file where your Python macro is located. To do this, in the META-INF folder, edit the manifest.xml file, by adding the following line:

<manifest:file-entry manifest:full-path="Scripts/python/myPyScripts.py" manifest:media-type="application/binary"/>

Now archive the content of the previously unarchived folder with your changes into a *.zip archive. Next, rename this archive, by changing its name (so as not to overwrite your original file) and the extension from *.zip to *.ods.

Using the extension

You can also use a ready-made set of functions and utilities written in Python by installing the extension YLC Utilities.

After that, this functionality will be available in all files that will be opened in LibreOffice Calc.