Python User Defined Function for LibreOffice Calc

Question

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.

How about CALL_MODE_FREQ(TEXTJOIN(",";0;"A2:B11")) and then parse the arg as csv in python? Like serializing the range. — LMC
– LMC, Commented Nov 30, 2024 at 16:34

Jim K · Accepted Answer · 2024-11-30 18:51:59Z

1

Sounds like you've got most of it working. Here's a python example that sums a range string, based on Pitonyak chapter 6 and MRI introspection.

def sum_range():
    oSheet = XSCRIPTCONTEXT.getDocument().getSheets().getByIndex(0)
    oCellRange = oSheet.getCellRangeByName("A3:B10")
    oDataArray = oCellRange.getDataArray()
    the_sum = 0
    for row_tuple in oDataArray:
        for cell_val in row_tuple: 
            if cell_val:
                the_sum += cell_val
    oCell = oSheet.getCellByPosition(0, 0)
    oCell.setValue(the_sum)

1 Comment

AlanD Dec 4, 2024 at 12:21

Thank you. Now if I could just get a working debugger with introspection for Xmas I'd be happy.

Collectives™ on Stack Overflow

Python User Defined Function for LibreOffice Calc

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related