当前位置:   article > 正文

python打开excel执行vba代码_“Python替代Excel Vba”系列(终):vba中调用Python

python 打开 xlsx 并执行 vb

5882b2b7d0a20cf48cf3565b78de4233adaf992b.jpeg?token=232dfa3a9e41756507bb65c4142c7d7b&s=7062D85A2153D5CE1A25625D03006077

请关注本号,后续会有更多相关教程。

系列文章

学Python还不会处理Excel数据?带你用pandas玩转各种数据处理“Python替代Excel Vba”系列(二):pandas分组统计与操作Excel“Python替代Excel Vba”系列(三):pandas处理不规范数据“Python替代Excel Vba”系列(四):课程表分析与动态可视化图表

前言

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

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

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

本文主要效果如下图:

9bc296ec3a7a6f6f9a66ea83a967cc8c6076.gif

处理数据的过程在 Python 中进行。输入条件,输出结果的过程在 Vba 进行。可以随意修改汇总方式(求和、平均等)与汇总字段。可以随意修改汇总字段和过滤条件。所有的修改都无需改动代码。数据源文件与显示文件是独立分开的。

本文要点:

使用 xlwings 注册 Python 方法到 Vba 模块Vba 调用 Python 方法,输出结果到 Excel注意:虽然本文是"Python替代Excel Vba"系列,但希望各位读者明白,工具都是各有所长,选择适合的工具,才是最好的。

08f790529822720e1fe9c31f741c0343f31fab0f.jpeg?token=e2f12f06dc9f19891667366ba79d8673&s=F21C7E8657A3D8E45A2B826E03007078

案例

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

838ba61ea8d3fd1f3e208c5420992c1a94ca5f8a.jpeg?token=ae981d0a961654ca19d67234001ecb16&s=D80A5E38170D514B1455A0DE020080B2

数据大致表示每个部门每个月的销售情况Units Sold 列是销售额本文所用到的 pandas 技巧都在之前的章节已有详细介绍,因此本文只对重点细节做讲解

导入包

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

pip install pandaspip install numpypip install xlwings

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

脚本中导入

eac4b74543a982266e4a58728555b0044b90eb3c.jpeg?token=2d1826138c523fdd4bffe8cf277ec125&s=A9E0D3028BA08E514A49740A0000E0C1

定义 Python 方法

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

8c1001e93901213feae56f205a303fd42d2e9557.jpeg?token=ec4c6571f9fb119ac368ff491792627e&s=61E09342C7F48B7056D5540F000030C1

df.query(where_exp) , 这个是主要的方法。DataFrame 的 query 方法支持用文本表达查询,因此这里直接传入外部的字符串即可。其他的语句是为了防止没有输入任何查询的情况。然后再定义一个做汇总的方法。如下图:

7a899e510fb30f24f369c77dc742d846ac4b033c.jpeg?token=2bbeee8c7c830ed5e8967e404eccf159&s=6BE08B4213F0BE694C74C00D0000E0C1

由于 DataFrame 几乎所有的方法都可以传入字符串表示,因此非常方便把这些汇总条件通过外部传入。pd.Grouper(key='Date',freq=date_freq) ,这是 pandas 为处理时间分组提供的处理方式。只需要在 freq 参数传入字母即可表达你希望按日期的哪个部分进行分组。比如:"M" 表示按月,"Y" 表示按年。最后,定义一个方法,让vba调用。如下图:

96dda144ad345982f99ada311c2338a8caef8458.jpeg?token=939f45c26f43e79b2b64168aa7976c59&s=63E09B425BA4874F1C59EC0F0000B0C2

这个方法的上方套上一个 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 ,可以自定义任何名字,这个名字是生成的目录名字

48540923dd54564e0e2d21d9bd099587d3584fe6.jpeg?token=41a760c2772963613058994ceed411e4&s=FCA08919414074E243D551C003005030

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

b812c8fcc3cec3fddc2c0614d85fdd3a86942798.jpeg?token=02fece50e37ce8d2263ade0f64e605b1&s=2592EC324B624D241AF584DA0000C0B2

我们需要往其中的 py 文件写入处理代码。打开myproject.xlsm文件,你会看到一个叫xlwings的功能区页。如下图:

8694a4c27d1ed21b342399a9a3b9d4c153da3feb.jpeg?token=0b66140e86aa46fe7b2b49fad6436598&s=7943CF104D62472056529CD9030080BB

点一下上图红框部分,即可注册你的 py 文件中的自定义方法到 vba 中。他大致原理是读取 py 文件中的方法,然后相应在 vba 中生成名字和参数一样的 vba 方法。因此,假如你的 py 文件的方法定义改动了,记得要在这里点击一下,重新导入定义。如果只是方法里面的语句改动,则无需重导入。按 alt + f11,打开 vbe(vba的编辑器)。发现其中有3个模块,分别是 Module1 和 xlwings。

Module1 是需要我们自己写入所需的 vba 代码。xlwings 模块是 xlwings 自动生成的,我们不需要去改动。xlwings_udfs 模块就是你在功能区点击导入按钮时生成的。同样不需要去改动。到此为止,即可编写vba代码去调用。如下:

a1ec08fa513d2697e79160c85b2cbbfe4116d8c5.jpeg?token=14af17886a58a5cc3f74f794f149890f&s=98AA5D3293B04C234C75D1CF000010B2

注意红线部分,返回结果的 numpy 数组索引是从0开始计数。因此这里需要在最大索引+1才是行和列的数目。其他就不细说了,会 vba 的小伙伴应该一看就懂。最后

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

总结

使用 xlwings 可以让 Vba 调用 Python 。把复杂的汇总处理流程让给 Python 处理。Vba 处理 Excel输出结果等,别再让 vba 做他不擅长的事情。

请关注本号,后续会有更多相关教程。

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/Gausst松鼠会/article/detail/661961
推荐阅读
相关标签
  

闽ICP备14008679号