用户定义函数（UDFs）¶

本教程快速介绍如何编写用户定义函数。

注意

UDFs 目前仅在 Windows 上可用。
若要了解如何控制参数和返回值的行为，请参阅转换器和选项.
若要全面了解可用的装饰器及其选项，请查阅相应的 API 文档：UDF 装饰器.

一次性Excel准备¶

启用Trust access to the VBA project object model在File > Options > Trust Center > Trust Center Settings > Macro Settings下。你只需要做一次。此外，这只是导入函数所需的，最终用户无需为此烦恼。
通过命令提示符安装加载项：xlwings addin install（参见插件与设置)。

工作簿准备¶

开始一个新项目的最简单方法是运行xlwings quickstart myproject在命令提示符上（参见命令行客户端（CLI））。这会自动将 xlwings 引用添加到生成的工作簿中。

简单的UDF¶

默认的加载项设置期望在与 Excel 文件相同的目录下找到 Python 源文件quickstart:

并且文件名与 Excel 文件相同，但扩展名是
而不是.py ending instead of .xlsm.

或者，您可以通过UDF Modules在 xlwings 功能区中指向特定模块。

下图显示了在 xlwings 功能区中“UDF Modules”字段的正确输入，模块名为“my_udf.py”：

如果模块不在与 Excel 文件相同的目录中，则可以通过“PYTHONPATH”字段指向它。下图显示了如果模块位于“C:\py_folder”文件夹中的配置（只是一个示例，以便它适合字段窗口）：

参考而言，这是使用这些设置时你的 xlwings.conf 文件的样子：

假设你有一个工作簿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)

中编写以下代码Import Python UDFs现在点击功能区中的myproject.py.
来接收对=double_sum(1, 2)的更改，并在一个单元格中输入公式，你将看到正确的结果：
文档字符串（三引号内）将作为 Excel 中的函数描述显示。

注意

如果你更改了函数参数或函数名称，则只需重新导入你的函数。
实际函数中的代码更改会在下次计算公式时自动拾取（例如由Ctrl-Alt-F9触发），但导入模块中的更改不会。这就是 Python 导入工作的行为。如果你想确保一切都是新鲜状态，请点击Restart UDF Server.
The @xw.func装饰器仅在函数导入到 Excel 时由 xlwings 使用。它告诉 xlwings 为哪些函数创建 VBA 包装函数，否则它对函数在 Python 中的行为没有影响。

数组公式：提高效率¶

在 Excel 中调用一个大的数组公式比调用许多单胞公式要高效得多，所以通常使用它们是个好主意，特别是当你遇到性能问题时。

你可以将 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以创建数组公式。如果你一切操作都正确，你会看到公式被大括号包围，如下图所示：

数组维度的数量：ndim¶

上述公式的问题在于它期望“二维”输入，例如嵌套列表的形式[[1, 2], [3, 4]]。因此，如果你将该公式应用于单个单元格，你会得到以下错误：TypeError: 'float' object is not iterable.

为了强制 Excel 始终给你一个二维数组，无论参数是一个单元格、一列/一行还是二维范围，你可以将上述公式扩展如下：

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

使用类型提示，可以这样写：

from typing import Annotated

@xw.func
def add_one(data: Annotated[list[list[float], {"ndim": 2}]]):
    return [[cell + 1 for cell in row] for row in data]

如果你想在其他函数中重用该类型提示，可以简化如下：

from typing import Annotated

List2d = Annotated[list[list[float], {"ndim": 2}]]

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

使用NumPy和Pandas的数组公式¶

通常，你希望在 UDF 中使用 NumPy 数组或 Pandas DataFrame，因为这能解锁 Python 科学计算生态系统的全部功能。

要定义一个使用 numpy 数组的矩阵乘法公式，你可以定义以下函数：

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

并再次使用类型提示：

from typing import Annotated
import xlwings as xw
import numpy as np

Array2d = Annotated[np.ndarray, {"ndim": 2}]

@xw.func
def matrix_mult(x: Array2d, y: Array2d):
    return x @ y

注意

如果你不使用 Python >= 3.5 和 NumPy >= 1.10，请使用x.dot(y)表示，而不是x @ y.

一个很好的例子是如何使用 Pandas 创建基于数组的CORREL公式。Excel 版本的CORREL仅适用于两个数据集，如果你想要快速获取几个时间序列的相关矩阵时，使用起来很麻烦。Pandas 使得创建基于数组的CORREL2公式基本变成了一行代码：

import xlwings as xw
import pandas as pd

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

并再次使用类型提示：

from typing import Annotated
import xlwings as xw
import pandas as pd

@xw.func
def CORREL2(df: Annotated[pd.DataFrame, {"index": False, "header": False}]):
    """Like CORREL, but as array formula for more than 2 data sets"""
    return df.corr()

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

这些装饰器对于 UDF 就像options方法对于Range对象一样：它们允许你将转换器及其选项应用于函数参数（@xw.arg）和返回值（@xw.ret）。例如，要将参数x转换为 pandas DataFrame 并在返回时不显示索引，你可以这样做：

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

有关更多详细信息，请参阅转换器和选项文档。

使用类型提示而不是装饰器¶

在版本 0.32.0 中新增。

自 v0.32.0 起，xlwings 支持类型提示，你可以使用它们代替或与装饰器结合使用：

import xlwings as xw
import pandas as pd

@xw.func
def myfunction(df: pd.DataFrame) -> pd.DataFrame:
     # df is a DataFrame, do something with it
    return df

在这个例子中，返回类型（-> pd.DataFrame）是可选的，因为 xlwings 会自动检查返回对象的类型。

如果你需要提供额外的转换参数，你可以通过带注解的类型提示或通过装饰器提供它们。请注意，当你同时使用类型提示和装饰器时，装饰器会优先于类型提示进行转换。

为了设置index=False对于参数和返回值，您可以这样注解类型提示：

from typing import Annotated
import xlwings as xw
import pandas as pd

@xw.func
def myfunction(
    df: Annotated[pd.DataFrame, {"index": False}]
) -> Annotated[pd.DataFrame, {"index": False}]:
    # df is a DataFrame, do something with it
    return df

因为这可能有点难以阅读，您可以提取类型定义，这也允许您重用它，如下所示：

from typing import Annotated
import xlwings as xw
import pandas as pd

Df = Annotated[pd.DataFrame, {"index": False}]

@xw.func
def myfunction(df: Df) -> Df:
    # df is a DataFrame, do something with it
    return df

或者，您还可以将类型提示与装饰器结合使用：

from typing import Annotated
import xlwings as xw
import pandas as pd

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

遗留动态数组¶

注意

如果您的Excel版本支持新的原生动态数组，则您不需要做任何特殊操作，并且不应该使用expand装饰器！要检查您的Excel版本是否支持它，请查看您是否有=UNIQUE()公式。原生动态数组最早是在2018年底引入的。

如上所述，为了使用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))

以及带有类型提示的相同方法：

from typing import Annotated
import numpy as np

@xw.func
def dynamic_array(r: int, c: int) -> Annotated[np.ndarray, {"expand": "table"}]:
    return np.random.randn(int(r), int(c))

注意

扩展的数组公式将在不提示的情况下覆盖单元格
在v0.15.0之前不允许将易失性函数作为参数使用，例如您不能使用类似=TODAY()的函数作为参数。从v0.15.0开始，您可以将易失性函数作为输入，但UDF将被调用多次。
动态数组在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)

以及带有类型提示的相同方法：

from typing import Annotated
import xlwings as xw

@xw.func
def double_sum(
    x: Annotated[float, {"doc": "This is x."}],
    y: Annotated[float, {"doc": "This is y."}],
):
    """Returns twice the sum of the two arguments"""
    return 2 * (x + y)

“caller”参数¶

您通常需要知道哪个单元格调用了UDF。为此，xlwings提供了保留参数caller，它以xlwings范围对象的形式返回调用单元格：

@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

请注意，caller将不会在Excel中暴露，而是由xlwings在后台提供。

“vba”关键字¶

通过使用vba关键字，您可以以pywin32对象的形式访问任何Excel VBA对象。例如，如果您想以CodeName形式传递工作表对象，可以按以下方式操作：

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

请注意，vba参数不会在UDF中暴露，而是由xlwings自动提供。

宏¶

在Windows上，作为通过RunPython调用宏的替代方案，您还可以使用@xw.sub装饰器：

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

点击Import Python UDFs后，您可以通过执行它来使用此宏Alt + F8或将其绑定到按钮。为了后者，请确保在Developer下选择了File > Options > Customize Ribbon标签。然后，在Developer标签下，您可以通过Insert > Form Controls插入一个按钮。在绘制按钮后，系统将提示您为按钮分配一个宏，您可以选择my_macro.

从VBA调用UDF¶

导入的函数也可以在VBA中使用。例如，对于返回二维数组的函数：

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¶

注意

这是一项实验性功能

在版本v0.14.0中添加。

xlwings提供了一种在Excel中编写异步函数的简单方法。异步函数会立即返回#N/A waiting...。当函数等待其返回值时，您可以使用Excel做其他事情，并且只要返回值可用，单元格值就会更新。

当前唯一可用的模式是async_mode='threading'，这意味着它适用于I/O密集型任务，例如当您通过网络从API获取数据时。

只需在函数装饰器中给出相应的参数，就可以使一个函数变为异步。在此示例中，耗时的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

来模拟。=myfunction("abcd")您可以像使用其他xlwings函数一样使用此函数，只需在单元格中放入

请注意，xlwings不使用Excel 2010引入的原生异步函数，因此xlwings异步函数支持任何版本的Excel。