Excel是很出色的工具,但有时候它不够好用。当数据集较小,需要处理的问题比较简单时,Excel最好用。但是一旦跳出这些舒适区域,就会发现Excel无法满足业务需求。当然,可以使用VBA来解决问题,但为什么不考虑Python呢,借助xlwings这个三方库,我们可以把Python和Excel完全集成到一起。
为什么要集成Python和Excel
Excel VBA能完成很多数据分析工作,包括自动化。那么为什么要使用Python?有以下几个重要的原因:
- 即便不用VBA,也可以在Excel中创建自定义函数。
- Python可以大幅提升数据操作的效率
- Python中有大量的三方库可用于爬取数据,操作数据库,机器学习,在数据领域几乎能完全你想要解决的任何问题
设置好环境,安装xlwings
先在终端安装xlwings三方库。
pip install xlwings
安装完毕后需要进一步安装xlwings excel集成插件。
xlwings addin install
在安装过程中可能会遇到以下错误:
xlwings 0.17.0
[Errno 2] No such file or directory: 'C:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART\\xlwings.xlam'
这时候只需要创建丢失的目录即可。
mkdir C:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART
安装成功后,打开Excel,会注意到导航栏多了xlwings addin.
为xlwings启用用户自定义函数
首先要添加Excel加载项,完成后,应该可以看到以下内容:
启用对VBA项目对象模型的信任访问。导航到文件>选项>信任中心>信任中心设置>宏设置来做到这一点:
xlwings入门示例
集成Python和Excel的方法主要有两种:1. 从VBA调用Python脚本,2. 用户自定义函数。
为了避免潜在的问题,xlwings提供了创建Excel表格的功能。我们尝试一下这个功能,在终端输入以下命令:
xlwings quickstart ProjectName
我们创建了一个简单项目:'MyFirstPythonXL',上面的命令将自动创建一个文件夹,里边包含1个excel工作表和1个python脚本。
从VBA到Python
在开始编码之前,先调出Excel VBA编辑器(Alt + F11),会返回如下UI:
上面的VBA代码将执行以下操作:
- 在与Excel表格相同的目录下查找Python脚本
- 寻找与Excel表同名的Python脚本(扩展名为.py)
- 在Python脚本中调用函数“main()”
让我们看一些简单的示例。
示例1:在Python中计算,将结果返回给Excel
该实例将会在Python中进行计算,并将结果返回给Excel。
我们将从本地CSV获取数据,对数据进行修改(这步用pandas实现),然后将输出传递给Excel。以下是python脚本中的代码。
结果如下图所示:
示例2:将excel单元格的输入作为参数
我们先读取Excel单元格的内容,在Python中对其进行处理,然后将结果返回给Excel。
以下是Python代码:
结果如下图所示:
示例3:用xlwings创建自定义函数
xlwings可以创建Excel函数,直接在单元格中使用,与普通的Excel函数几乎没有区别。
更改python脚本中的代码,为了将Python函数转换为Excel函数,需要使用装饰器'@xw.func'装饰函数。
以下是Python代码:
结果如下图所示:
结论
如果你喜欢Python和Excel,那么xlwings将称为集成两个强大工具的最佳途径。