pythonlibreoffice-calclibreoffice-basic

Libreoffice basic: how to pass CellRange variables to python scripts


I'm working on my first python script for Libre office calc.

Following various guides I installed APSO and successfully created a Basic wrapper that calls the python script.
This is its signature:
Function python(functionName As String, ParamArray params) As Variant
Where:
functionName is the name of the python method to call.
params are the parameters to pass.
(NOTE: As the python method varies basing on functionName the params array has not a predefined size and elements types)

This works quite well except if a parameter is a cells range. In this case the cells range is passed to python as a tuple, missing the information about the rows and columns references.

Why is basic converting the cells range into a tuple instead of a CellRange object?
Is there a way to detect the types of the params elements so that I can manage the cells ranges differently? (I tried to use TypeName(params(0)) but it seems to work only with base types like "Strings", if the param is a cells range it returns Variant()

EDIT 1

A bit more details as my question was not clear enough
Here it is the full wrapper that I wrote in basic:

REM  *****  BASIC  *****
option compatible

Function python(functionName As String, ParamArray params) As Variant
    Dim scriptPro As Object, myScript As Object
    scriptPro = ThisComponent.getScriptProvider()
    scriptPath = "vnd.sun.star.script:development.py$" & functionName & "?language=Python&location=user"
    On Error GoTo ErrorHandler 
    
    myScript = scriptPro.getScript(scriptPath)
   
    Dim outResult As Variant

    outResult = myScript.invoke(params, Array(), Array())
    python = outResult(0)
    
    Exit Function
     
ErrorHandler:
    python = CVErr(xlErrName)
End Function 

I execute the wrapper from the calc formula bar, for example:

=python("custom_concatenate", "Hello ", "world")
Calls the python method custom_concatenate passing it the 2 strings "Hello " and "world" and shows in the cell "Hello world"

or

=python("sum_all", A1:A10)
Calls the python method sum_all passing the TUPLE OF VALUES contained in the cells from A1 to A10 and shows the sum in the cell.

Till here it works perfectly.

Now, imagine that I want to create a method that takes a range and returns the sum of its first row number and its last row number.
For example
=python("sum_row_numbers", B13:C24)
the method should do the sum of the first row number, 13, and the last one 24, and return 37

This is the signature of the python method:
def sum_row_numbers(range):
The problem is that the range parameter passed by the wrapper is just a tuple of tuples, and any reference to the rows and columns is unknown at this point.

How can I make sure that the wrapper pass all the information about the range (included the cells references) to the python method?


Solution

  • To get the row numbers used in a spreadsheet formula, we can extract them from getFormula(). Include the address of the cell that has the formula.

    import re
    
    def sum_row_numbers(*params):
        sCellAddress = params[-1]
        oDoc = XSCRIPTCONTEXT.getDocument()
        oSheet = oDoc.getCurrentController().getActiveSheet()
        oCell = oSheet.getCellRangeByName(sCellAddress)
        sFormula = oCell.getFormula()
        match = re.search(r'[A-Z]+(\d+):[A-Z]+(\d+)', sFormula)
        if match:
            row1, row2 = map(int, match.groups())
            return row1 + row2
        return 0
    

    For example, enter the following formula in cell A1 and fill down.

    =PYTHON("sum_row_numbers"; B13:C24; CELL("address"))
    

    Result:

    37 39 41 43 45 47 49 51 53 55 57 59
    

    If the cell isn't on the active sheet, then include CELL("sheet") and do:

    oSheet = oDoc.getSheets().getByIndex(iSheetNumber - 1)