outis - thanks for your awesome answer. If it weren't for you I'd be mad by now still writing basic macros!
I just have some remarks, though:
Last 2 arguments to invokePyFunc are always empty - just use this:
const filename = "your_file"
Function pyFunc(func as String, args as Array)
pyFunc = invokePyFunc(filename, func, args, Array(), Array())
End Function
Multi-dimensional arrays are tricky to return. If you return ((1,2,3), (4,5,6)) calc treats that as 2 cells in a row containing unknown objects.
This is because basic and python treat multi-dimensional arrays differently.
If you return such structure to basic, you have to access it like data(row)(col) and calc expects data(row, col) for multi-dimensional arrays.
Because of that you need to use a converter function for return values:
' Converts python multidimensional arrays to basic arrays.
function convPy2Basic(pyvalue)
if isarray(pyvalue) then
dim lastRow as integer
lastRow = ubound(pyvalue)
if lastRow = -1 then
' empty array
convPy2Basic = ""
else
if isarray(pyvalue(0)) then
' Multi-dimensional array
dim maxCols as integer, lastCol as integer
maxCols = ubound(pyvalue(0))
dim res(lastRow, maxCols)
for rowIndex = 0 to lastRow
lastCol = ubound(pyvalue(rowIndex))
' Expand array if needed.
if lastCol > maxCols then
maxCols = lastCol
redim preserve res(lastRow, maxCols)
end if
for colIndex = 0 to lastCol
res(rowIndex, colIndex) = pyvalue(rowIndex)(colIndex)
next colIndex
next rowIndex
convPy2Basic = res
else
' Single-dimensional array - this is supported by libreoffice
convPy2Basic = pyvalue
end if
end if
else
convPy2Basic = pyvalue
end if
end function
Function invokeScriptFunc(file AS String, lang, ext, func As String, args As Array, outIdxs As Array, outArgs As Array)
sURL = URL_Main & file & "." & ext & "$" & func & "?language=" & lang & "&location=user"
oMSP = getMasterScriptProvider()
oScript = oMSP.getScript(sURL)
invokeScriptFunc = oScript.invoke(args, outIdxs, outArgs)
end Function
Function invokePyFunc(file AS String, func As String, args As Array, outIdxs As Array, outArgs As Array)
res = invokeScriptFunc(file, "Python", "py", func, args, outIdxs, outArgs)
invokePyFunc = convPy2Basic(res)
end Function
So my python-basic macro bridge looks like this:
' Keep a global reference to the ScriptProvider, since this stuff may be called many times:
Global g_MasterScriptProvider as Object
' Specify location of Python script, providing cell functions:
Const URL_Main as String = "vnd.sun.star.script:"
' Converts python multidimensional arrays to basic arrays.
function convPy2Basic(pyvalue)
if isarray(pyvalue) then
dim lastRow as integer
lastRow = ubound(pyvalue)
if lastRow = -1 then
' empty array
convPy2Basic = ""
else
if isarray(pyvalue(0)) then
' Multi-dimensional array
dim maxCols as integer, lastCol as integer
maxCols = ubound(pyvalue(0))
dim res(lastRow, maxCols)
for rowIndex = 0 to lastRow
lastCol = ubound(pyvalue(rowIndex))
' Expand array if needed.
if lastCol > maxCols then
maxCols = lastCol
redim preserve res(lastRow, maxCols)
end if
for colIndex = 0 to lastCol
res(rowIndex, colIndex) = pyvalue(rowIndex)(colIndex)
next colIndex
next rowIndex
convPy2Basic = res
else
' Single-dimensional array - this is supported by libreoffice
convPy2Basic = pyvalue
end if
end if
else
convPy2Basic = pyvalue
end if
end function
Function invokeScriptFunc(file AS String, lang, ext, func As String, args As Array, outIdxs As Array, outArgs As Array)
sURL = URL_Main & file & "." & ext & "$" & func & "?language=" & lang & "&location=user"
oMSP = getMasterScriptProvider()
oScript = oMSP.getScript(sURL)
invokeScriptFunc = oScript.invoke(args, outIdxs, outArgs)
end Function
Function invokePyFunc(file AS String, func As String, args As Array, outIdxs As Array, outArgs As Array)
res = invokeScriptFunc(file, "Python", "py", func, args, outIdxs, outArgs)
invokePyFunc = convPy2Basic(res)
end Function
Function getMasterScriptProvider()
if isNull(g_MasterScriptProvider) then
oMasterScriptProviderFactory = createUnoService("com.sun.star.script.provider.MasterScriptProviderFactory")
g_MasterScriptProvider = oMasterScriptProviderFactory.createScriptProvider("")
endif
getMasterScriptProvider = g_MasterScriptProvider
End Function
const filename = "skaiciuokle"
Function pyFunc(func as String, args as Array)
pyFunc = invokePyFunc(filename, func, args, Array(), Array())
End Function
And is used like this:
function DamageToArmor(data, damageType as String, armorType as String, dmgPerGun as Integer, guns as Integer)
DamageToArmor = pyFunc("dmg2armor", Array(data, damageType, armorType, dmgPerGun, guns))
end function
