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()
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?
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)