xlwings 在 excel 中使用自定义 python 函数

发表于 分类于 阅读次数： 本文字数： 849 阅读时长 ≈ 3 分钟

0. xlwings 简介

xlwings 提供了四种用法:

  • xlwings (Open Source)， 本地 python模块、excel插件，代码在本地，运行在本地
  • xlwings PRO， 商业版，收费
  • xlwings Lite， 代码内置在excel文件中、运行在云端（代码公开）
  • xlwings Server， 商业版，收费

1. xlwings (Open Source) 本地版，在 excel 中使用自定义 python 函数

1.1 excel 启用宏

文件 -> 选项 -> 信任中心 -> 信任中心设置 ->
宏设置 -> 勾选 启用VBA宏 和 信任对VBA工程对象模型的访问

1.2 安装 xlwings (Open Source)

1
2
3
4
5
6

pip install xlwings
xlwings addin install

# xlwings addin remove
# pip uninstall xlwings

1.3 快速使用

1
2
3
4
xlwings config create --force
# .xlwings\xlwings.conf 

xlwings quickstart xlwings_test

xlwings quickstart 命令会自动建立一个文件夹,内部有两个文件：

  • xlwings_test.xlsm
  • xlwings_test.py

简单看一下 xlwings_test.py 的代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
import xlwings as xw


def main():
    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    if sheet["A1"].value == "Hello xlwings!":
        sheet["A1"].value = "Bye xlwings!"
    else:
        sheet["A1"].value = "Hello xlwings!"

# 可以看出 main 函数的功能是：用于直接操作指定的单元格

@xw.func
def hello(name):
    return f"Hello {name}!"

# hello 函数则是一个方法函数，有输入输出：
# 输入是 name， 
# 输出是 返回一个拼接的字符串


if __name__ == "__main__":
    xw.Book("openmc_xlwings.xlsm").set_mock_caller()
    main()

1.4 测试 main 函数

打开 xlwings_test.xlsm, 菜单栏切换到 xlwings 标签：

点击 Run main: A1 表格 应该会出现 Hello xlwings!

再次点击 Run main: A1 表格 应该会出现 Bye xlwings!

1.5 测试 hello 函数

打开 xlwings_test.xlsm, 菜单栏切换到 xlwings 标签：

第一次导入 py 文件或有改动时, 点击 Import Functions （或 Restart UDF Server）。

在任单元格中输入 =hello , 应该能弹出函数提示。

假设 D1 单元格内容为： xlwings for excel, 在 D2 中填入： =hello(D1),

D2 最终返回 Hello xlwings for excel!

2. 自定义函数： 调用 numpy、pandas 插值函数

2.1 xlwings_test.py

自定义一个插值函数：xl_interp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
import xlwings as xw

import pandas as pd
from numpy import log, exp, interp
from scipy.interpolate import interp1d

@xw.func
def xl_interp(x_val: float, y_str: str):
    xy_cols = ["xx", "y1", "y2", "y3", "y4"],
    xy_data = [
            [0.1, 0.1509, 0.1674, 0.3425, 5.355],
            [0.15, 0.1341, 0.149, 0.1829, 1.921],
            [0.2, 0.1225, 0.1361, 0.1383, 0.9432],
            [0.3, 0.1063, 0.1183, 0.1064, 0.3772],
            [0.4, 0.09525, 0.1059, 0.092, 0.2172],
            [0.5, 0.08694, 0.09667, 0.08282, 0.1514],
            [0.6, 0.08041, 0.08943, 0.07611, 0.1178],
            [0.8, 0.07065, 0.07856, 0.06644, 0.08472],
            [1.0, 0.06349, 0.07062, 0.05957, 0.06844],
            [1.5, 0.05168, 0.05747, 0.04862, 0.05102],
            [2.0, 0.0444, 0.04932, 0.0425, 0.04536],
            [3.0, 0.03573, 0.03961, 0.03611, 0.04199],
            [4.0, 0.03072, 0.03395, 0.03303, 0.04175],
            [5.0, 0.02745, 0.03024, 0.03139, 0.04257],
            [6.0, 0.02516, 0.02763, 0.0305, 0.04379],
            [8.0, 0.0222, 0.02423, 0.02985, 0.04667],
            [10.0, 0.0204, 0.02214, 0.02989, 0.04965],
            [15.0, 0.01805, 0.01936, 0.03088, 0.05653],
            [20.0, 0.01702, 0.01808, 0.0322, 0.06202],
        ]

    df = pd.DataFrame(xy_data, columns=xy_cols)
    
    # 1. 线性插值
    y_interp = interp(x_val, df["xx"], df[y_str]) 
    # 2. 指数插值
    y_interp_log = exp(interp(log(x_val), log(df["xx"]), log(df[y_str])))
    # 3. 样条插值：
    # 'zero', 'slinear', 'quadratic', 'cubic' : 零阶、一阶、二阶或三阶样条插值
    y_interp_quad = scipy.interpolate.interp1d(df["xx"], df[y_str], kind="quadratic")(x_val)

    return y_interp

2.2 xlwings_test.xlsm

假设 G1 单元格内容为：2.5; G2 单元格内容为：y1,

在 G3 中填入： =xl_interp(G1, G2),

G3 最终返回：0.040065

3. xlwings Lite

xlwings Lite 也挺好用的

  • 不需要本地有 python 环境。（基于 Pyodide）
  • 代码内置在 excel 文档中。
  • 库管理功能挺完善，numpy pandas scipy等基本库都没问题
  • 第一次运行应该是要联网的
  • 速度比本地慢一点，刷新计算时能感觉到，但也基本不影响正常使用

使用时直接在侧边栏中编写 python 代码，有一些基本的功能

  • 配置安装 python 第三方库
  • 代码格式化： alt + shift + F
  • 简单的终端窗口