如何构造Basic包装器调用LibreOffice Calc非整数区域的Python UDF

问题描述 #

要调用LibreOffice Calc中使用非整数电子表格区域的Python用户自定义函数（UDF），应如何构造Basic包装器调用？
目前不清楚如何正确声明values和excel_date数组对应的函数参数，这两个数组均存储实数，在Basic术语中对应Variant类型。
此前测试发现整数类型的区域无需额外定义即可传递，但实数值区域会出现调用异常。本次传入的两个数组均为一维结构，不存在多维数组适配的额外处理需求。

现有Basic代码 #

Function xnpv_helper(rate as Double, values() as Variant, excel_date() as Variant) as Double
    Dim scriptPro As Object, myScript As Object
    scriptPro = ThisComponent.getScriptProvider()
    myScript = scriptPro.getScript("vnd.sun.star.script:MyPythonLibrary/Develop/math.py$xnpv_helper?language=Python&location=user")
    xnpv_helper = myScript.invoke(Array(rate, values, excel_date), Array(), Array() )
end function

测试用电子表格数据 #

Date    Amount  Rate    xnpv
31/12/19    100 -0.1    
31/12/20    -110

Python函数实现 #

# Demo will run in python REPL - uncomment last line
import scipy.optimize

DAYS_PER_YEAR = 365.0

def xnpv(valuesPerDate, rate):
    '''Calculate the irregular net present value.

    >>> from datetime import date
    >>> valuesPerDate = {date(2019, 12, 31): -100, date(2020, 12, 31): 110}
    >>> xnpv(valuesPerDate, -0.10)
    22.257507852701295
    '''
    if rate == -1.0:
        return float('inf')
    t0 = min(valuesPerDate.keys())
    if rate <= -1.0:
        return sum([-abs(vi) / (-1.0 - rate)**((ti - t0).days / DAYS_PER_YEAR) for ti, vi in valuesPerDate.items()])
    return sum([vi / (1.0 + rate)**((ti - t0).days / DAYS_PER_YEAR) for ti, vi in valuesPerDate.items()])


from datetime import date
def excel2date(excel_date):
    return date.fromordinal(date(1900, 1, 1).toordinal() + int(excel_date) - 2)

def xnpv_helper(rate, values, excel_date):
    dates = [excel2date(i) for i in excel_date]
    valuesPerDate = dict(zip(dates, values))
    return xnpv(valuesPerDate, rate)

# valuesPerDate = {date(2019, 12, 31): -100, date(2020, 12, 31): 110}
# xnpv_helper(-0.10, [-100, 110], [43830.0, 44196.0])

解决方案 #

你不需要修改参数的类型声明，核心问题是LibreOffice Calc向Basic UDF传递区域参数时，无论选中的是单行还是单列，都会默认封装为二维数组（单列区域为N行1列结构，单行区域为1行N列结构），直接传给Python会变成嵌套列表，导致遍历逻辑出错，只需要对数组做一次降维处理即可：

修改后的Basic代码 #

Function xnpv_helper(rate as Double, values() as Variant, excel_date() as Variant) as Double
    Dim scriptPro As Object, myScript As Object
    Dim flat_values As Variant, flat_dates As Variant
    Dim i As Integer
    
    ' 将二维数组降为一维
    ReDim flat_values(LBound(values, 1) To UBound(values, 1))
    ReDim flat_dates(LBound(excel_date, 1) To UBound(excel_date, 1))
    For i = LBound(values, 1) To UBound(values, 1)
        ' 单列区域取值用(i,1)，如果是单行区域改为(1,i)即可
        flat_values(i) = values(i, 1)
    Next
    For i = LBound(excel_date, 1) To UBound(excel_date, 1)
        flat_dates(i) = excel_date(i, 1)
    Next
    
    scriptPro = ThisComponent.getScriptProvider()
    myScript = scriptPro.getScript("vnd.sun.star.script:MyPythonLibrary/Develop/math.py$xnpv_helper?language=Python&location=user")
    xnpv_helper = myScript.invoke(Array(rate, flat_values, flat_dates), Array(), Array() )
end function

使用说明 #

• Variant类型可以正确保留Double精度的实数值，传递到Python后会自动转为float类型，无需额外转换
• 表格中调用公式写法为：=xnpv_helper(C2, B2:B3, A2:A3)，直接选中对应列区域即可正常返回结果

内容的提问来源于stack exchange，提问作者flywire