User Defined Functions (UDFs)

本教程指导大家快速着手写用户定义函数。

备注

  • 目前仅能在Windows系统中使用用户定义函数(UDF)

  • 如何控制参数和返回值的方法，请查看 转换器及选项.

  • 关于全部装饰器及其功能的全面的综述，请查询相应的API文档: UDF装饰器.

Excel里的一次性准备工作

  1. Enable Trust access to the VBA project object model under File > Options > Trust Center > Trust Center Settings > Macro Settings. You only need to do this once. Also, this is only required for importing the functions, i.e. end users won’t need to bother about this.

  2. Install the add-in via command prompt: xlwings addin install (see Add-in & Settings).

准备工作簿

最简单的方法是在命令行用 xlwings quickstart myproject 来创建一个新的项目(参见 Command Line Client (CLI))。这样会在创建的工作簿中自动添加xlwings引用。

一个简单的UDF

加载项缺省设置和用 ``quickstart``创建的项目一样，像下面这样找Python源文件：

  • 在工作簿所在的相同目录下

  • 和工作簿同名，但是文件后缀是 .py 而不是 .xlsm 。

另外一种方法是在xlwings功能区通过 UDF Modules 来指明是哪一个文件。

  • The Image below shows the correct input for the “UDF Modules” field in the xlwings ribbon with a module called “my_udf.py”:

_images/udf_modules.png

  • If the module is not within the same directory as the Excel file, you point to it via the “PYTHONPATH” field. The image below shows input for if the module was in a folder under “C:\py_folder” (just an example so it fits in the field window):

_images/pythonpath.png

  • For reference, with those changes, this is how your xlwings.conf file should look:

_images/pythonpath_conf.png

假设你有一个工作簿 myproject.xlsm ，可以把下列代码输入 myproject.py:

import xlwings as xw

@xw.func
def double_sum(x, y):
    """Returns twice the sum of the two arguments"""
    return 2 * (x + y)

  • 在xlwings工具功能区点击 Import Python UDFs ，把在 myproject.py 中修改的内容同步进来。

  • 在一个单元格里输入公式 =double_sum(1, 2) ，可以看到正确结果：

    _images/double_sum.png

  • 文档字符串(三重双引号内的文字)在Excel中会被显示为函数说明。

备注

  • 只有修改了函数参数或者函数名称，才需要重新导入。

  • 实际函数中的改变会被自动更新(也就是说在公式下次计算的时候，比如被 Ctrl-Alt-F9 触发的时候)，但是导入模块中的变化不是这样。 导入模块的行为和Python导入模块的行为一致。 如果需要确保所有公式都在最新状态，需要单击 Restart UDF Server 。[译者注：在Excel2013，建议修改公式内容后，每个使用到该公式的单元格都重新运行一下。]

  • 装饰器 @xw.func 只在函数被导入Excel时被xlwings模块用到。它告诉xlwings该为哪个函数创建一个VBA的封装函数，除此之外，它不影响函数在Python中的行为。

数组公式：提高效率

在Excel中，调用一个大数组的公式比调用多个单元格的公式效率高。所以，用大数组公式是个很好的办法，特别是要解决性能问题的时候。

可以把一个区域而不是一个单元格作为公式的参数。区域在Python中被表示为嵌套列表。

例如，可以写下面一个公式来为区域里面的每个单元格加1:

@xw.func
def add_one(data):
    return [[cell + 1 for cell in row] for row in data]

要在Excel中用这个公式，可以：

  • 先再一次单击 Import Python UDFs

  • 在区域 A1:B2 中填一些数字

  • 选中 D1:E2

  • 输入公式 =add_one(A1:B2)

  • 按 Ctrl+Shift+Enter 组合键创建数组公式。如果上面几个动作都做对了，就会看到下面截屏中显示的公式外面有一对花括号：

_images/array_formula.png

数组的维数: ndim

上面的公式存在一个问题：它需要一个类似嵌套列表 [[1, 2], [3, 4]] 的“2维”的输入。所以如果你把这个公式用于一个单元格，会得到下面的错误信息： 类型错误: 'float' 对象不可迭代 。

要强制Excel不管输入参数是单元格、单行/单列还是2维区域都把它转化为2维数组，可以把上面的公式做如下扩展:

@xw.func
@xw.arg('data', ndim=2)
def add_one(data):
    return [[cell + 1 for cell in row] for row in data]

与NumPy和Pandas合用数组公式

通常会在UDF中用到NumPy array或者Pandas DataFrame，因为这样能把Python的整个科学计算体系的能力都发挥出来。

要用numpy array来定义一个矩阵运算公式，可以定义下面的公式:

import xlwings as xw
import numpy as np

@xw.func
@xw.arg('x', np.array, ndim=2)
@xw.arg('y', np.array, ndim=2)
def matrix_mult(x, y):
    return x @ y

备注

如果版本不满足Python >= 3.5 并且 NumPy >= 1.10，请用 x.dot(y) 替代 x @ y 。

把Pandas用于实际工作的一个很棒的例子是创建基于数组的 CORREL 公式。Excel版的 CORREL 必须用在2个数据集上，而且在求时间序列的相关系数矩阵时用起来很复杂。Pandas使得创建数组相关的 CORREL2 公式简单到一行搞定:

import xlwings as xw
import pandas as pd

@xw.func
@xw.arg('x', pd.DataFrame, index=False, header=False)
@xw.ret(index=False, header=False)
def CORREL2(x):
    """Like CORREL, but as array formula for more than 2 data sets"""
    return x.corr()

装饰器@xw.arg和@xw.ret

装饰器之于UDF就如 options 方法之于 Range 对象：他们允许对函数的参数( @xw.arg ) 和返回值(@xw.ret )使用转换器和各种选项。比如，要把参数x转换成pandas DataFrame并且在返回时抑制索引，可以这样做:

@xw.func
@xw.arg('x', pd.DataFrame)
@xw.ret(index=False)
def myfunction(x):
   # x is a DataFrame, do something with it
   return x

更多的细节可以参考 转换器及选项 。

动态数组公式

备注

如果你用的Excel版本支持原生的动态数组，就不需要做特别的处理，并且不要使用 expand 装饰器！ 检查版本是否支持的方法是看看 =UNIQUE() 公式是否可用。原生动态数组将在2018年9月底最先在Office 365 Insider项目的Fast通道中发布。

就像在前面看到的那样，使用Excel的数组公式的时候，要预先通过选定结果区域来确定范围，然后输入公式，最好敲组合键 Ctrl-Shift-Enter 。这在实际操作中常常弄得很复杂，特别是在处理与时间序列相关的动态数组时更是如此。从v0.10版本开始，xlwings提供了动态UDF扩展：

下面这个简单的例子展示了UDF扩展的语法和效果：

import numpy as np

@xw.func
@xw.ret(expand='table')
def dynamic_array(r, c):
    return np.random.randn(int(r), int(c))
_images/dynamic_array1.png
_images/dynamic_array2.png

备注

  • 扩展数组公式的输出会直接覆盖结果区域，没有预先提示;

  • v0.15.0之前的版本中，易变函数不能做为参数。例如，不能使用 =TODAY() 之类的函数作为参数。从v0.15.0开始，可以用易变函数作为输入，不过UDF的调用次数会增加1倍多;

  • 从v0.15.0开始重构了动态数组使得它和传统数组一致： 从xlwings >= v0.15.0开始， 编辑动态数组的时候，光标定位在动态数组区域的左上角，编辑完成后要需要敲组合键 Ctrl-Shift-Enter 。请注意，在第一次输入公式的时候，是用不着敲组合键的。

文档字符串

下面的例子演示了怎样在函数中加上函数及参数x、y的文档字符串，这些文档字符串能在Excel的函数向导里面显示出来：

import xlwings as xw

@xw.func
@xw.arg('x', doc='This is x.')
@xw.arg('y', doc='This is y.')
def double_sum(x, y):
    """Returns twice the sum of the two arguments"""
    return 2 * (x + y)

The “caller” argument

You often need to know which cell called the UDF. For this, xlwings offers the reserved argument caller which returns the calling cell as xlwings range object:

@xw.func
def get_caller_address(caller):
    # caller will not be exposed in Excel, so use it like so:
    # =get_caller_address()
    return caller.address

Note that caller will not be exposed in Excel but will be provided by xlwings behind the scenes.

“vba”关键字

By using the vba keyword, you can get access to any Excel VBA object in the form of a pywin32 object. For example, if you wanted to pass the sheet object in the form of its CodeName, you can do it as follows:

@xw.func
@xw.arg('sheet1', vba='Sheet1')
def get_name(sheet1):
    # call this function in Excel with:
    # =get_name()
    return sheet1.Name

Note that vba arguments are not exposed in the UDF but automatically provided by xlwings.

宏

On Windows, as an alternative to calling macros via RunPython, you can also use the @xw.sub decorator:

import xlwings as xw

@xw.sub
def my_macro():
    """Writes the name of the Workbook into Range("A1") of Sheet 1"""
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = wb.name

After clicking on Import Python UDFs, you can then use this macro by executing it via Alt + F8 or by binding it e.g. to a button. To do the latter, make sure you have the Developer tab selected under File > Options > Customize Ribbon. Then, under the Developer tab, you can insert a button via Insert > Form Controls. After drawing the button, you will be prompted to assign a macro to it and you can select my_macro.

从VBA调用UDF

导入的函数同样可以在VBA中使用。例如，一个返回2维数组的函数:

Sub MySub()

Dim arr() As Variant
Dim i As Long, j As Long

    arr = my_imported_function(...)

    For j = LBound(arr, 2) To UBound(arr, 2)
        For i = LBound(arr, 1) To UBound(arr, 1)
            Debug.Print "(" & i & "," & j & ")", arr(i, j)
        Next i
    Next j

End Sub

异步UDF

备注

This is an experimental feature

Added in version v0.14.0.

xlwings提供了一个在Excel中写异步函数的很容易的方法。异步函数立即返回 #N/A waiting... 。在函数等待返回值的时候，可以用Excel处理其他事情。一旦有了返回值，相关单元格的数值会被更新。

目前的唯一可用模式是 async_mode='threading' ，这意味着对于类似使用API通过网络获得数据的这类I/O绑定的任务很有用。

可以简单地通过设定其函数装饰器的参数中来实现一个异步函数。下面这个例子中，耗时的I/O绑定任务是用 time.sleep 来模拟的:

import xlwings as xw
import time

@xw.func(async_mode='threading')
def myfunction(a):
    time.sleep(5)  # long running tasks
    return a

You can use this function like any other xlwings function, simply by putting =myfunction("abcd") into a cell (after you have imported the function, of course).

注意：xlwings没有使用从Excel 2010开始引入的原生异步函数，所以xlwings的异步支持所有的Excel版本。