Use a Python function as formula in LibreOffice Calc cells

Question

Since LibreOffice Calc supports macros, and the builtin functions are somewhat limited, I'd like to write my own functions in Python and then use them as formulas in individual cells. Ideally, I would have a Python file like:

def VOL(a, b, c):
    v = a*b*c
    return v

either embedded in the document or as a custom_functions.py file in a straightforward location. I would then be able to write =VOL(A1, A2, B3) in a cell and have it calculate the result. Bad input or other errors should produce error text in the cell, same as how builtin calc functions do it.

I can go to Tools > Macros > Organize macros... > LibreOffice Basic. This gives me a dialog where I can apparently create a new function in the way I describe above (I haven't tested it). Unfortunately this appears to require writing it in Basic, not Python. I don't really want to learn Basic just for this, I want to leverage my knoweldge and experience in Python.

If I go to Tools > Macros > Organize macros... > Python, the options for creating new macros are grayed out. After digging a bit on the internet, I discovered that apparently LibreOffice (well, actually upstream OpenOffice) doesn't have its own Python IDE "yet", so they just put a grayed out button in the UI with no explanation as a placeholder. I say "yet" in quotes because I found people complaining about this in 2012 just to be told that it will be fixed any day now.

I also found some information about writing Python extensions using some sort of Python interop library. This appears to require importing a lot of hefty libraries, even the most basic examples require dozens of lines of complex boilerplate code, and debugging is probably a pain. It would be much easier to just write a normal Python script that reads data from the spreadsheet and then writes it back. However the functions I want are relatively trivial, and being able to see and navigate data effectively is very helpful to develop and debug my exploratory analysis, so being able to define the function in simple Python and play with it inside a Calc spreadsheet would be very convenient. Is there a way to do this?

I am on LibreOffice 5.2.2.2 and Windows 7, I can use either Python 2 or 3, but prefer 3.

Solution

Some things you asked about are easily solved, others not so much. First of all, the IDE. APSO is an extension that is now ready to use and provides a convenient menu for editing Python macros.

Once it is installed, go to Tools -> Macros -> Organize python scripts. Expand My Macros and go to Menu -> Create module. Name it "custom_functions.py".

Then select the file and go to Menu -> Edit. For this to work, you'll need to go into Tools -> Extension Manager and tell APSO the path to your favorite text editor. Mine is set to C:\Program Files (x86)\Vim\vim80\gvim.exe.

In the text editor, enter the following code.

def VOL(a, b, c):
    v = a*b*c
    return v

def call_vol():
    oSheet = XSCRIPTCONTEXT.getDocument().getSheets().getByIndex(0)
    cell_a = oSheet.getCellRangeByName("A1")
    cell_b = oSheet.getCellRangeByName("A2")
    cell_c = oSheet.getCellRangeByName("A3")
    cell_result = oSheet.getCellRangeByName("B1")
    cell_result.setValue(
        VOL(
            cell_a.getValue(),
            cell_b.getValue(),
            cell_c.getValue()))

g_exportedScripts = call_vol,

To run the code, use APSO again or go to the built-in Tools -> Macros -> Run Macro. Find and execute the call_vol function. For example, if A1 through A3 each contain 2, then this will produce 8 in B1 because 2 * 2 * 2 = 8.

Now, how to turn this macro into a spreadsheet function? The proper way is to create a spreadsheet add-in. I have found that such functions work well, but it does require several XML configuration files, and it sounds like you want to avoid this.

The alternative to creating add-ins is a user-defined function. These can only be called when written in Basic.

For a UDF where the core work is done in Python, put a wrapper function in Basic that calls Python from Basic using the Script Provider.

On Windows, recent versions of LibreOffice typically come with Python 3. Apache OpenOffice comes with Python 2 instead.

Comments

[flywire]

Better UDF basic wrapper calling python example: stackoverflow.com/questions/37336699/…

[Jim K]

@flywire: You stated that "Configuring non-default text editor is optional" in a rejected edit. However that was not true on my system. Most likely it depends on your system's file associations. Anyway, next time you have a doubt about the information in an answer, please add a comment rather than proposing an edit.

[Jim K]

No, it was not a good edit, and that was why two other people besides me rejected it. As stated in the site guidelines, you should not change information in someone's answer. Also I do not agree with the way you worded the edit. Yes, APSO looks for the default, but that is not quite how your proposed edit was worded. If you had simply added a comment then it would have been helpful additional information, but the attempted edit along with these follow-up comments has made it confusing instead.

[flywire]

"you'll need to" err No, I didn't quote it before but here it is: gitlab.com/jmzambon/apso#options "By default, APSO uses the system defined editor."