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

Question

To extend the capabilities of OpenOffice, I defined some Python macros in a file in the user script directory (~ / Library / Application Support /OpenOffice.org/3/user/Scripts/python/, in my case). Macros are visible in the Python Macro Organizer. However, using functions in the cell formula results in "#NAME?" ( Error OO.org 525).

Suppose I have 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 am importing some data from Authorize.net into a MySQL database for analysis. MySQL cannot parse the date and time format used by Authorize.net in the DATETIME or TIMESTAMP field, so I'm trying to massage the data in a format that MySQL can process before importing. OpenOffice also does not recognize data as date and time, and as far as I could determine, OO.Org does not have a common date parsing function. In this way, I am expanding OO.org.

There are other approaches to a larger problem. For example, I could also try to fix the data in the MySQL post-import using additional columns. In fact, this is what I did for the first time; however, there is currently existing data in the table that you can agree with. Because of this, and because of other tasks in the future, I hope to accomplish using macros in formulas, at the moment I'm mostly interested in calling Python macros in formulas.

Answer 1

In older OO.org (super) forums, Villeroy posted an illustration of how to invoke Python functions from OO.org Basic , which can then be used in formulas. 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 calling 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 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 the basic OO.org function called in the formula. Using the pytype example:

 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 more difficult option, since it requires the installation of the OpenOffice SDK, and for me it is not obvious whether this approach will work for free functions or only for classes.

Answer 2

outis - thanks for your wonderful answer. If it weren’t for you, I would have gone crazy, still writing basic macros!

I have only some comments:

The last 2 arguments of 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 

Multidimensional arrays are difficult to return. If you return ((1,2,3), (4,5,6)) calc treats as 2 cells in a row containing unknown objects.

This is because base and python handle multidimensional arrays differently.

If you return such a structure to the base one, you should access it like data(row)(col) , and calc expects data(row, col) for multidimensional arrays.

Because of this, you need to use the converter function for the 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 bridge macro 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 used as follows:

 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