打破Excel与Python的隔阂，xlwings最佳实践

前言能生成 pandas 代码的数据浏览工具工具安装加载数据直觉理解运行机制进一步完善充分利用 Excel 功能最后

更多 Python 数据处理的干货，敬请关注！！！！

本系列将结合实际应用，教会你如何利用xlwings，把Excel与Python的各自优势充分发挥

前言

说到必需学习的数据工具，Excel 无疑是唯一的答案 ，各种基本操作、函数公式、透视表，这些都是非常好用的功能，加上 vba 可以实现自动化需求。

自动化控制 Excel，我认为 vba 是目前最好的平台。但是 vba 的数据处理能力实在有限(别把表格处理与数据处理混淆)。

而 Python 之所以在数据领域受宠，完全是因为他有一些非常好用的库(numpy、pandas等)，如果没有这些库，实际上 Python 与 vba 没有多大区别(只是在数据处理方面)。但是 Python 做数据任务有个不太好的地方，没有一个舒服的操作界面(虽然有许多第三方库做界面，但是实在太麻烦)

Excel 就是一个很好的操作界面，为何不结合他们？

本系列文章我将完成一些小工具的制作，通过 Excel 完成各种输出格式的自动化，而把数据处理交给 pandas 完成。

能生成 pandas 代码的数据浏览工具

这是一个能让你通过简单操作，即可对数据进行各种操作的小工具，如下动图：

• 可以指定文件路径
• 可以指定各种基本操作，比如筛选、分组、统计等等
• 当然也可以让你编写代码
• 这个工具的一个特点是，你的操作最后都能转化成 pandas 代码

此工具界面完全使用 Excel 制作(大部分情况下不需要编写任何 vba 代码)，后台处理使用 Python(大部分情况使用 pandas)。

工具安装

首先安装 xlwings：

pip install xlwings

xlwings 是 Python 的一个第三方库，主要用于让你的 Python 代码可以在 Excel 上被调用。

我们要借助 xlwings 的一个开发工具，因此执行如下命令行：

xlwings addin install

这个工具只是方便你开发使用，实际使用时并不需要安装此工具

此时你打开 Excel ，应该会看到 xlwings 的加载项

暂且不解释他的原理，稍后在实践中再讲解其中的机制。

接下来，我们需要生成一个项目，说白了，就是生成一些必要的文件：

首先导航到你的项目文件夹中，执行如下命令：

xlwings quickstart myproject --standalone

• 其中的 "myproject" 是你的项目名字， 你可以修改为任何合法的名字，

此时你会发现项目文件夹中生成了一个名为 "myproject" 的文件夹，其中有文件：

• myproject.xlsm，这是带 vba 代码的 Excel 文件
• myproject.py，这是带 Python 代码的文件

接下来开始我们的小工具制作

加载数据

从一个简单任务开始，当我们在一个 Excel 单元格上输入文件路径与工作表名字，下方显示文件中的数据。

这通过动态数组公式完成这个任务。首先打开 myproject.py 文件，自定义一个函数：

• 行1，2：导入需要的库
• 行6-11：加载数据的自定义函数，其中的逻辑非常简单，使用 pandas 加载数据，返回结果即可
• 行4：@xw.func ， xlwings 的装饰器，标记此函数是一个公式，这会让函数成为 Excel 中的函数公式
• 行5：@xw.ret(expand='table') ， xlwings 的装饰器 ， "ret" 应该是 "return" 的意思 ，他是函数返回值相关的设置。这里参数 expand='table' ，这会让公式变成一个自动扩展范围的动态数组公式(结果是一个表，行列数都是动态的)

Python 的代码已经有了，但是 Excel 是不可能直接识别你定义的函数。

幸运的是，Excel 可以识别 vba 定义的函数。因此，我们需要 xlwings 帮我们自动生成 vba 代码。

打开 Excel 文件 myproject.xlsm(注意要启动宏)：

• 在 xlwings 页中，点击 import Functions 的大按钮，意思是"导入 Python 文件中的函数"

此时我们输入函数公式时，就能从提示中看到函数：

按照参数，选择对应的单元格引用即可：

回车后发现返回一段错误信息：

• 显然，我们还没有输入文件路径

输入完整的文件路径即可：

• 你会发现公式自动变成了数组公式

如果文章只是简单列出操作步骤，那么这是一个不合格的教程。你在不懂原理的情况下，很多问题都无法自己解决。

接下来我将讲解其运行机制的直觉理解。

直觉理解运行机制

目前为止，我们没有编写一句 vba 代码，只是简单定义出一个加载数据的 Python 自定义函数，就可以在 Excel 上使用公式实现效果。

从步骤上来说：

1. 定义 Python 自定义函数(在文件 myproject.py 中)
2. 点击"导入函数"的按钮(在 Excel 文件 myproject.xlsm 中)

那么为什么需要点击 "导入函数" 按钮？

如果我修改了 Python 代码，需要重新点击这个按钮吗？

首先，我们之所以能在 Excel 上输入公式时，出现我们的自定义函数，是因为在这个 Excel 文件中，存在 vba 代码，定义了同名的方法：

• 从 vbe 界面中可以看到，当我们点击"导入函数"按钮时，xlwings 在按照 Python 文件中定义的函数，生成了对应的 vba 代码
• 其中也能看见，调用时需要 Python 文件名，函数名字，和其他的参数

vbe 是 编写 vba 代码的界面。通过快捷键 alt + F11 即可打开

因此，Excel 公式执行时，会转而执行 Python 文件中的同名函数。

理解这点非常重要，从中可以得知：

• 如果 Python 中的函数名字或参数数量有增减，则需要重新点击"导入函数"按钮
• 如果只是函数中的实现代码有变动，无须点击"导入函数"按钮

例如，修改 myproject.py 中的代码：

• 我们希望返回结果的前10行

修改后，保存一下此 Python 文件，在 Excel 上无须点击"导入函数"按钮，只要公式有刷新(比如修改公式引用到的单元格的值)，就能看到最新结果：

• 只有10行结果

你可能会觉得每次修改都点一下导入按钮，万无一失。

如果你是制作工具给别人使用的情况下，就需要知道代码变动后，哪些文件(Excel或Python文件)需要更新

进一步完善

首先，我们希望返回结果不要把 DataFrame 的行索引输出：

这可以通过装饰器的参数设置：

• 行5：设置 ret 装饰器的参数 index 为 False 即可
• 此外通过参数 header 也能控制是否输出表头

此次修改中，函数名字，参数数量没有变化，因此不需要点击"导入函数"按钮

你会发现即使数据文件就在项目文件夹中，使用相对路径是读取不到文件。

但是，每次输入全路径太麻烦了，如果能输入相对路径就很好了。

这问题由于 xlwings 在执行 vba 的方法时，会启动一个 Python 进程(只有首次运行的时候)，然后加载 myproject.py 中的代码。但他没有设置启动目录。

因此默认情况下启动目录是 Python.exe 所在目录。

我们只需要在 myproject.py 中修改启动目录即可：

现在可以支持相对路径：

文件名字如果可以下拉选择就好了！

我们来看看如何实现

充分利用 Excel 功能

首先，我们需要一个能找出 myproject.py 文件所在目录的所有 Excel 文件，我们使用 Python 实现这功能(这不是 vba 擅长的)：

• 保存文件后，记得"导入函数"

在 Excel 文件 myproject.xlsm 中，创建一个新的工作表(示例中名字为 Sheet2)，执行这个公式：

到界面工作表，为 B1 单元格设置数据有效性：

• 其中来源选择 Sheet2 的A列

现在可以下拉选择文件：

目前这个功能无法随时监控文件夹的文件变化，后面我们再想办法解决

最后

今天内容已经比较多了，以后我们将继续完善和添加其他功能到此工具上，过程中将讲解更多相关机制：

• 文件下拉选项动态变化
• 工作表名字的下来选择
• 支持更多数据源(csv，数据库等)
• 异步加载数据
• 数据操作，并生成对应的 pandas 代码
• 文件缓存：只有在数据文件被修改后，才重新读取文件(现在是每次执行都加载)
• 锁定 xlwings 版本，如何能在没有安装 xlwings 的电脑上使用工具

你还有其他的功能建议吗？留言给我吧

敬请关注！