How can I call a Python macro in a cell formula in OpenOffice.Org Calc?

Question

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.

Answer 1

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.

Answer 2

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