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

量化投资与机器学习微信公众号

发布于 2020-02-20 17:33:40

11.2K1

发布于 2020-02-20 17:33:40

文章被收录于专栏：量化投资与机器学习量化投资与机器学习

作者：Costas

编译：1+1=6

前言

如果有一种方法可以将Excel与Python集成起来，该多好啊！现在有了：

xlwings库允许我们通过VBA调用Python脚本来进行两者的交互！

为什么要将Python与Excel VBA集成？

事实上，你可以在VBA中做任何事情。那么为什么要使用Python呢？原因有很多：

1、你可以在Excel中创建一个自定义函数，而不需要学习VBA。

2、使用Python可以显著加快数据操作的速度。

3、Python中有各种各样的库（机器学习、数据科学等）、

4、因为你可以！！！

xlwings安装

第一步安装：

pip install xlwings

接下来，我们需要安装Excel集成部分：

xlwings addin install

在使用 Excel 2016的 Win10上，人们经常会看到以下错误：

你可以通过使用 mkdir 命令解决这个问题：

安装好一切：

启用xlwings的用户定义函数

首先我们需要加载 Excel 外接程序：

最后，我们需要启用对 VBA 项目对象模型的信任访问。你可以通过导航到文件选项信任中心设置宏来做到这一点：

具体操作

有两种主要的方法可以使我们从 Excel 转换到 Python（以及转换回来）。第一种是直接从 VBA 调用 Python 脚本，另一种是通过用户定义函数调用。

为了我们每次都能正确设置，xlwings提供了创建Excel电子表格的功能：

xlwings quickstart ProjectName

上面的命令将使用 Excel 工作表和 Python 文件在预导航目录中创建一个新文件夹。

打开.xlsm文件，你会立即注意到一个名为_xlwings.conf的新Excel工作表。如果你希望覆盖xlwings的默认设置，只需重命名该工作表并删除开始的下划线即可。通过这些，我们就可以开始使用xlwings了。

从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)

结果如下：

用户定义函数与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)

结果如下：