pythonuser-defined-functionslibreoffice-basic

LO Calc BASIC wrapper for python array functions


How can I construct simple LibreOffice basic wrapper, that could read an arbitrary array from LO calc spreadsheet to preform some python function on it, say add all the values?

Here is example of some function. What I want is to do say sum of arrays, not cell values as it does. So, what changes should I implement?

Function readpy(a, b, funcfilename As String, funcname As String) As Variant
    Dim oScriptProvider As Object
    Dim oScript As Object
    Dim result As Variant

    oScriptProvider = ThisComponent.getScriptProvider()

    oScript = oScriptProvider.getScript(_
        "vnd.sun.star.script:" & funcfilename & "$" & funcname & "?language=Python&location=document")

    result = oScript.invoke(Array(a, b), Array(), Array())

    readpy = result
End Function

Solution

  • This is not really a question about Python. The reason I say this is because the following routine is straightforward:

    Sub sumLists
        Dim oScriptProvider As Object
        Dim oScript As Object
        Dim result As Variant
        oScriptProvider = ThisComponent.getScriptProvider()
        oScript = oScriptProvider.getScript(_
            "vnd.sun.star.script:custom_functions.py$sum_lists?language=Python&location=user")
        result = oScript.invoke(Array(Array(1,2), Array(3,4)), Array(), Array())
        MsgBox result(0) & ", " & result(1)
    End Sub
    

    This correctly displays 4, 6 with Python code:

    def sum_lists(a, b):
        return [
            a_val + b_val
            for a_val, b_val in zip(a, b)
            ]
    

    Rather, the question is how to pass an array from Calc to a Basic user-defined function (UDF). The answer to that question is given in Make a macro to sort a row using a custom list in LibreOffice Calc :

    First of all, we need to figure out a way to transfer the range of cells to be sorted to the macro. There are different ways - write the address directly in the macro code, pass it as a parameter to the UDF, get it from the current selection.