python代替excel vba_'Python替代Excel Vba'系列（终）：vba中调用Python

系列文章

前言

有小伙伴向我反映到，本系列前面的章节主要还是在讲 pandas ，几乎与 xlwings 没有啥关系。

本系列一直强调要善用各种工具，作为本系列的最后一节，那么这次就用一例子说明如何让Python结合Vba，直接在Excel中动态获取各种处理条件，输出结果。

日后也会不定期分享 pandas 的处理案例，但不一定非要与 Excel 挂钩。比如直接结合 power bi 做处理分析。

本文主要效果如下图：

处理数据的过程在 Python 中进行。

输入条件，输出结果的过程在 Vba 进行。

可以随意修改汇总方式(求和、平均等)与汇总字段。

可以随意修改汇总字段和过滤条件。

所有的修改都无需改动代码。

数据源文件与显示文件是独立分开的。

本文要点：使用 xlwings 注册 Python 方法到 Vba 模块

Vba 调用 Python 方法，输出结果到 Excel

注意：虽然本文是'Python替代Excel Vba'系列，但希望各位读者明白，工具都是各有所长，选择适合的工具，才是最好的。

案例

本次数据来自于微软官方提供的财务数据。如下图：

数据大致表示每个部门每个月的销售情况

Units Sold 列是销售额

本文所用到的 pandas 技巧都在之前的章节已有详细介绍，因此本文只对重点细节做讲解

导入包

本文所需的包，安装命令如下:

pip install pandaspip install numpypip install xlwings

建议你安装 anaconda ，那么最难安装的 pandas 和 numpy 都不会是问题。

脚本中导入

定义 Python 方法

首先定义一个对 pandas 的 DataFrame 进行过滤的方法。如下图:

df.query(where_exp) ， 这个是主要的方法。DataFrame 的 query 方法支持用文本表达查询，因此这里直接传入外部的字符串即可。

其他的语句是为了防止没有输入任何查询的情况。

然后再定义一个做汇总的方法。如下图：

由于 DataFrame 几乎所有的方法都可以传入字符串表示，因此非常方便把这些汇总条件通过外部传入。

pd.Grouper(key='Date',freq=date_freq) ，这是 pandas 为处理时间分组提供的处理方式。只需要在 freq 参数传入字母即可表达你希望按日期的哪个部分进行分组。比如:'M' 表示按月，'Y' 表示按年。

最后，定义一个方法，让 vba 调用。如下图:

这个方法的上方套上一个 xlwings 的装饰器 @xw.func。表示这个方法需要注册到 Vba 模块中。

外部传入的是字符串，比如参数 groups 可以是 'col1,col2'，因此需要对 groups 和 values 参数调用 split 分裂成列表。

然后就顺序调用之前定义的2个方法 where_df 和 group_df。

接着把 DataFrame 的 columns 与 values 合并成一个 numpy 数组，即可返回。

使用 xlwings 生成项目文件

打开命令行，执行以下语句，即可安装 xlwings 的加载项：xlwings addin install实际上，你在使用 pip 安装 xlwings时，已经有一个 xlwings.xlam文件。

而上述命令行只是把这个 xlam 文件放入你的 excel 加载项目录中而已。

然后，在你的任意目录中打开命令行。执行以下语句，即会生成一个 py 文件和一个 带宏的 excel 文件。

xlwings quickstart myproject --standalone其中 myproject ，可以自定义任何名字，这个名字是生成的目录名字

此刻你会发现在当前目录会有一个 myproject 的文件夹，打开后会看到如下2个文件：

我们需要往其中的 py 文件写入处理代码。

打开 myproject.xlsm 文件，你会看到一个教 xlwings 的功能区页。如下图：

点一下上图红框部分，即可注册你的 py 文件中的自定义方法到 vba 中。

他大致原理是读取 py 文件中的方法，然后相应在 vba 中生成名字和参数一样的 vba 方法。

因此，假如你的 py 文件的方法定义改动了，记得要在这里点击一下，重新导入定义。如果只是方法里面的语句改动，则无需重导入。

按 alt + f11，打开 vbe(vba的编辑器)。发现其中有3个模块，分别是 Module1 和 xlwings。Module1 是需要我们自己写入所需的 vba 代码。

xlwings 模块是 xlwings 自动生成的，我们不需要去改动。

xlwings_udfs 模块就是你在功能区点击导入按钮时生成的。同样不需要去改动。

到此为止，即可编写 vba 代码去调用。如下：

注意红线部分，返回结果的 numpy 数组索引是从0开始计数。因此这里需要在最大索引+1才是行和列的数目。

其他就不细说了，会 vba 的小伙伴应该一看就懂。

最后

你发现这样做的一个好处是，无需重复启动 Python ，因为每次启动 Python 都需要不少时间(大概2、3秒的样子)。而本文的做法，可以让其 Python 进程一直存在。

总结使用 xlwings 可以让 Vba 调用 Python 。

把复杂的汇总处理流程让给 Python 处理。

Vba 处理 Excel输出结果等，别再让 vba 做他不擅长的事情。

[源码地址](https://github.com/CrystalWindSnake/Creative/tree/master/python/excel_pandas/5)