从VBA到Python,Excel工作效率如何提高?
编译:1+1=6
1
前言
如果有一种方法可以将Excel与Python集成起来,该多好啊!现在有了:
xlwings库允许我们通过VBA调用Python脚本来进行两者的交互!
2
为什么要将Python与Excel VBA集成?
事实上,你可以在VBA中做任何事情。那么为什么要使用Python呢?原因有很多:
1、你可以在Excel中创建一个自定义函数,而不需要学习VBA。
2、使用Python可以显著加快数据操作的速度。
3、Python中有各种各样的库(机器学习、数据科学等)、
4、因为你可以!!!
3
xlwings安装
第一步安装:
pip install xlwings
接下来,我们需要安装Excel集成部分:
xlwings addin install
在使用 Excel 2016的 Win10上,人们经常会看到以下错误:
你可以通过使用 mkdir 命令解决这个问题:
安装好一切:
4
启用xlwings的用户定义函数
首先我们需要加载 Excel 外接程序:
最后,我们需要启用对 VBA 项目对象模型的信任访问。你可以通过导航到文件选项信任中心设置宏来做到这一点:
5
具体操作
有两种主要的方法可以使我们从 Excel 转换到 Python(以及转换回来)。第一种是直接从 VBA 调用 Python 脚本,另一种是通过用户定义函数调用。
为了我们每次都能正确设置,xlwings提供了创建Excel电子表格的功能:
xlwings quickstart ProjectName
上面的命令将使用 Excel 工作表和 Python 文件在预导航目录中创建一个新文件夹。
打开.xlsm文件,你会立即注意到一个名为_xlwings.conf的新Excel工作表。如果你希望覆盖xlwings的默认设置,只需重命名该工作表并删除开始的下划线即可。通过这些,我们就可以开始使用xlwings了。
6
从VBA到Python
在我们开始编码之前,让我们首先确保在同一个页面上。打开Excel VBA 编辑器,点击 Alt + F11。返回以下屏幕:
这里要注意的关键事情是,这段代码将做以下工作:
1、在与电子表格相同的位置查找Python脚本。
2、查找与电子表格名称相同的Python脚本(扩展名为.py)。
3、在Python脚本中,调用函数main()。
让我们看几个例子,看看如何使用它。
例1:在Excel外部操作,并返回输出。
在本例中,我们将看到如何在Excel之外执行操作,然后在电子表格中返回结果。
我们将从CSV文件中获取数据,对这些数据进行修改,然后将输出传递到Excel:
首先,VBA代码。
然后,Python代码:
import xlwings as xwimport pandas as pddef main(): wb = xw.Book.caller() df = pd.read_csv(r'C:\temp\TestData.csv') df['total_length'] = df['sepal_length_(cm)'] + df['petal_length_(cm)'] wb.sheets[0].range('A1').value = df
结果如下:
例2:从Excel中读取,用Python对其进行处理,然后将结果传递回Excel。
更具体地说,我们将读取一个 Greeting,一个 Name 和一个我们可以找到jokes的文件位置。 然后,我们的 Python 脚本将从文件中随机抽取一行,并返回一个jokes。
首先,VBA代码。
然后,Python代码:
import xlwings as xw
import random
def random_line(afile):
line = next(afile)
for num, aline in enumerate(afile, 2):
if random.randrange(num): continue
line = aline
return line
def main():
wb = xw.Book.caller()
listloc = str(wb.sheets[0].range('B3').value)
fhandle = open(listloc, encoding = 'utf-8')
wb.sheets[0].range('A5').value = wb.sheets[0].range('B2').value + ' ' + wb.sheets[0].range('B1').value + ' here is a joke for you'
wb.sheets[0].range('A6').value = random_line(fhandle)
结果如下:
7
用户定义函数与xlwings
我们将以与以前几乎相同的方式更改python文件中的代码。为了把一个东西变成一个Excel用户定义函数,我们需要做的就是
与前面的方式大致相同,我们将更改 Python 文件中的代码,使其变成一个 Excel 用户定义函数,我们所需要做的就是包含@xw.func:
import xlwings as xw@xw.funcdef joke(x): wb = xw.Book.caller() fhandle = open(r'C:\Temp\list.csv') for i, line in enumerate(fhandle): if i == x: return(line)
结果如下:
希望大家可以有所收获!
2020年第25篇文章