Search code examples
libreoffice-calc

Creating an user-defined function that uses external python code

Suppose we have a spreadsheet that, in A1:B5, has the following data:

ID  Value
5   10
2   20
5   15
2   25

Additionally, suppose we have a python code that, given a pandas dataframe df, calculates:

df.groupby('ID').mean()

Is it possible to use this python code to create an user-defined LibreOffice Calc function, by using Basic to call the python script? Thanks!

Solution

  • First of all, if you want to use a UDF, then the function would need to take an array of values as the argument. So the formula could look like =A2:B5, giving a two-dimensional array to the function.

    Next, an example of calling a python-UNO function is at Calling a python function from within LibreCalc. This will be much easier on Linux than other operating systems, because LibreOffice uses the system-wide python where pandas can easily be installed.

    On Windows, some libraries can be added to the bundled (not system-wide) python with get-pip.py, which installs a version of pip that can be used with LibreOffice. You would need to experiment to see whether the libraries you need can be handled this way.

    If that doesn't work, then instead of importing pandas, see if it wouldn't be too hard to write your own python-UNO code to do the calculation. I've done this before with parts of lxml.

    As a last resort, maybe you could use Basic's Shell statement to call a script using the system-wide python interpreter.

    Shell("python",, "C:/Users/YourUsername/Desktop/yourscript.py")

    Before executing the command, write the input to a file using Basic. Then the script could process the file and write the output to a different file. Back in the Basic function, sleep until the output file appears, then read the file and return the result from the function. Hopefully your UDF isn't volatile, because changes would cause frequent recalculating, potentially locking the UI until it finishes.

    File handling in Basic is a pain. There's the actual Basic way like at https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03020200.html, but using SimpleFileAccess from the UNO API is often better.

    One final idea: If you can't get this to work with python, it might be better in Java, because the integration with LibreOffice works differently. There are two downsides to Java, however — it isn't as easy to get it to work across multiple versions of LibreOffice, and the code required is a bit more complex, especially obtaining specific interfaces with queryInterface.