To extend OpenOffice's capabilities, I've defined some Python macros in a file in the user script directory (~/Library/Application Support/OpenOffice.org/3/user/Scripts/python/, in my case). The macros are visible in the Python macro organizer. However, using the functions in a cell formula results in "#NAME?" (OO.org error 525).
Suppose I've defined the following function :
def pytype(val):
return str(type(val))
How can I call pytype in a cell formula (e.g. =PYTYPE("string"))?
Background
I'm importing some data from Authorize.net into a MySQL database for analysis. MySQL can't parse the date & time format used by Authorize.net into a DATETIME or TIMESTAMP field, so I'm trying to massage the data into a format MySQL can handle before import. OpenOffice also doesn't recognize the data as a date & time and, as far as I've been able to determine, OO.Org doesn't have a generic date parsing function. Thus I'm extending OO.org's capabilities.
There are other approaches to the larger issue. For example, I could also try to fix up the data in MySQL post-import using additional columns. In fact, this is what I did the first time; however, there's now existing data in the table to contend with. Because of that, and because there are other tasks in the future I hope to accomplish by using macros in formulas, for now I'm mostly interested in calling Python macros in formulae.
On the old OO.org forums, (super)user Villeroy posted an illustration of how to call Python functions from OO.org Basic, which can then be used in formulae. The key is to use the com.sun.star.script.provider.MasterScriptProviderFactory service as a bridge. Here is an adaptation of his solution, generalized to call arbitrary functions in arbitrary modules:
REM Keep a global reference to the ScriptProvider, since this stuff may be called many times:
Global g_MasterScriptProvider as Object
REM Specify location of Python script, providing cell functions:
Const URL_Main as String = "vnd.sun.star.script:"
Const URL_Args as String = "?language=Python&location=user"
Function invokePyFunc(file AS String, func As String, args As Array, outIdxs As Array, outArgs As Array)
sURL = URL_Main & file & ".py$" & func & URL_Args
oMSP = getMasterScriptProvider()
On Local Error GoTo ErrorHandler
oScript = oMSP.getScript(sURL)
invokePyFunc = oScript.invoke(args, outIdxs, outArgs)
Exit Function
ErrorHandler:
Dim msg As String, toFix As String
msg = Error$
toFix = ""
If 1 = Err AND InStr(Error$, "an error occurred during file opening") Then
msg = "Couldn' open the script file."
toFix = "Make sure the 'python' folder exists in the user's Scripts folder, and that the former contains " & file & ".py."
End If
MsgBox msg & chr(13) & toFix, 16, "Error " & Err & " calling " & func
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
This can then be used to create a OO.org Basic function callable in a formula. Using the example pytype:
Const libfile as String = "util" REM functions live in util.py
Function pytype(value)
pytype = invokePyFunc(libfile, "pytype", Array(value), Array(), Array())
End Function
Another potential implementation is to create a Python add-in. However, this is a much heavier option as it requires installing the OpenOffice SDK, and it isn't obvious to me whether this approach would work for free functions or only works for classes.
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
