[Python] Python User Defined Function for LibreOffice Calc

I'm trying to create a Python UDF that operates on a range of spreadsheet values and returns a single value.

From what I've gleaned, I need a Basic wrapper that calls my Python script. Here is what I've got for the wrapper:

Function Call_mode_freq(range as string) as double
Dim oScriptProvider, oScript
oScriptProvider = ThisComponent.getScriptProvider()
oScript = oScriptProvider.getScript(_
"vnd.sun.star.script:stat_functions.py$mode_freq? language=Python&location=user")
Call_mode_freq = oScript.invoke(array(range), array(), array() )
End Function


I'm calling the UDF with this formula: =CALL_MODE_FREQ("A2:B11")

Passing a range as a string seems to be an issue. I created a module called stat_functions.py that has a function called mode_freq I've tried using the uno XSCRIPTCONTEXT and the ScriptForge interfaces but cant get either to work. Here's my incomplete Python function:

def mode_freq(arange):
# arange is a string e.g. "A3:B10"

# uno interface
oSheet = XSCRIPTCONTEXT.getDocument().getSheets().getByIndex(0)
oCell = oSheet.getCellByPosition(0, 0)
# I want to get the values from "arange"

# ScriptForge interface
doc = CreateScriptService("Calc")
# crange = doc.Sheets["Sheet1"][arange] # this fails

mode = 0 # dummy value to ensure something is returned to Calc cell with this UDF
return mode


When using crange = doc.Sheets["Sheet1"][arange], the error message says I'm supposed to specify a tuple of integers, not a string. I have no idea how to get the values from a range.

I cannot find any documentation on the object models for these interfaces that goes below the top level (i.e. Document or Sheet). The wrapper is calling the Python script and my dummy return value is getting written to the cell with the formula, so that part is working. Hoping someone can point me in the right direction.

Continue reading...