从VBA到Python，Excel工作效率如何提高？

标星★


置顶


公众号     

爱你们
♥
   

作者：Costas 

编译：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 xw
import pandas as pd
def 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.func
def 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
篇文章

量化投资与机器学习微信公众号，是业内垂直于


Quant、MFE、
Fintech
、AI、ML


等领域的

量化类主流自媒体。

公众号拥有来自


公募、私募、券商、期货、银行、保险资管、海外


等众多圈内

18W+

关注者。每日发布行业前沿研究成果和最新量化资讯。

你点的每个“在看”，都是对我们最大的鼓励