如何在OpenOffice.Org Calc单元格公式中调用Python宏？

Question

为了扩展OpenOffice的功能，我在一个文件里定义了一些Python宏，这个文件放在用户脚本目录中（对我来说是 ~/Library/Application Support/OpenOffice.org/3/user/Scripts/python/）。这些宏在Python宏管理器中是可见的。但是，当我在单元格公式中使用这些函数时，结果却是"#NAME?"（这表示有错误，具体是OO.org错误 525）。

假设我定义了以下函数：

def pytype(val):
    return str(type(val))

我该如何在单元格公式中调用pytype（例如=PYTYPE("string")）呢？

背景

我正在将一些数据从Authorize.net导入到MySQL数据库中进行分析。MySQL无法将Authorize.net使用的日期和时间格式解析为DATETIME或TIMESTAMP字段，所以我想在导入之前把数据处理成MySQL能识别的格式。OpenOffice也不把这些数据当作日期和时间，而且据我所知，OO.org没有通用的日期解析函数。因此，我在扩展OO.org的功能。

解决这个大问题还有其他方法。例如，我也可以在数据导入后，利用额外的列在MySQL中修复数据。实际上，我第一次就是这么做的；不过现在表里已经有了现存的数据需要处理。因为这个原因，还有未来我希望通过在公式中使用宏来完成的其他任务，所以现在我主要想知道如何在公式中调用Python宏。

Answer 1

outis - 感谢你的精彩回答。如果没有你，我现在可能还在为写基本的宏而烦恼呢！

不过我有几点想说：

调用invokePyFunc时，最后两个参数总是空的 - 直接用这个就行：

const filename = "your_file"

Function pyFunc(func as String, args as Array)
    pyFunc = invokePyFunc(filename, func, args, Array(), Array())
End Function

多维数组的返回有点麻烦。如果你返回 ((1,2,3), (4,5,6))，calc会把它当作两格里有未知对象的内容。

这是因为基本语言和Python对待多维数组的方式不同。

如果你把这样的结构返回给基本语言，你需要像 data(row)(col) 这样访问，而calc则期待用 data(row, col) 来处理多维数组。

因此，你需要使用一个转换函数来处理返回值：

' 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

所以我的Python和基本语言的宏桥接看起来是这样的：

' 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

使用方法是这样的：

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

Answer 2

在以前的OO.org论坛上，一位用户Villeroy分享了一个示例，讲述如何从OO.org Basic调用Python函数，然后可以在公式中使用这些函数。关键是要使用com.sun.star.script.provider.MasterScriptProviderFactory这个服务作为桥梁。下面是他解决方案的一个改编版，适用于调用任意模块中的任意函数：

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

这样就可以创建一个可以在公式中调用的OO.org Basic函数。以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

另一种可能的实现方式是创建一个Python插件。不过，这种方式比较复杂，因为需要安装OpenOffice SDK，而且我不太确定这种方法是否适用于自由函数，还是仅适用于类。