Python让Excel飞起来：使用Python xlwings实现Excel自动化

hercules028 2021-11-07

展开全文

excelperfect

标签：Python与Excel,xlwings

本文将向你展示如何使用Python xlwings库自动化Excel。毋庸置疑，Excel是一款非常棒的软件，具有简单直观的用户界面，而Python是一种强大的编程语言，在数据分析方面非常高效。xlwings就像胶水一样，将两者连接到一起，让我们能够同时拥有两者最好的一面。

你可以使用xlwings+Python执行下列任务：

1.使用Python自动化Excel，例如生成报告。

2.使用Python编写宏，并通过单击按钮从Excel运行。

3.使用Python编写用户定义的函数，并像调用任何Excel内置函数一样从Excel中调用这些函数。

听起来很刺激？让我们开始吧！

第一部分：安装xlwings

安装xlwings有两个部分：Python库和Excel加载项。

先安装Python库：

pip install xlwings

然后从xlwings的官方Github存储库下载这个Excel加载项，即页面上的xlwings.xlam文件。（或者，你可以到知识星球中的完美Excel社群下载）

将xlwings.xlam文件放入Excel加载项文件夹，该文件夹所在位置为：

C:\用户\xxxx\AppData\Roaming\Microsoft\AddIns

xxxx是计算机上自己的用户名。

然后，打开Excel，选择“文件->选项->加载项”。单击“管理：Excel加载项”旁边的“转到”按钮，如下图1所示。

图1

在“加载宏”对话框中，选取Xlwings前的复选框，如下图2所示，单击“确定”按钮。

图2

现在，Excel功能区中将出现一个名为“xlwings”的选项卡，如下图3所示。

图3

至此，设置已完成，我们可以使用用Python自动化Excel了！

第二部分：自动化Excel

运行以下Python脚本，它将打开一个新的Excel实例。

import xlwings as xw

wb = xw.Book()

将数据写入Excel

这里，wb引用新的（且打开的）Excel文件，同时它也是一个Python对象，这意味着我们可以在Python中操作它（Excel文件）。尝试下面的代码，它将允许你将值从Python输入到Excel。

sheet = wb.sheets['Sheet1']

sheet.range('A1').value ='Hi,Excel,我来自Python'

图4

我们也可以使用.range((x，y))表示法来引用Excel中的单个单元格，其中x表示行，y表示列。因此，.range((3，2))表示单元格B3。

sheet.range((3,2)).value = 'x轴'

sheet.range((3,3)).value = 'y轴'

for i in range(5):

sheet.range((i+4,2)).value = i

图5

也可以使用Python在Excel中编写公式。基本上，我们是在向单元格中写入字符串。这里，我们要在另一列中计算x轴的指数值。在下面的代码中，我们使用了“f-string”，这是从Python 3.6开始的一种改进的字符串格式语法。

for i in range(5):

sheet.range((i+4,3)).value = f'=exp(B{i+4})'

图6

从Excel中读取数据

从Excel读取数据同样简单，下面的代码将Excel数据作为列表读取到Python中。

data = sheet.range('B3:C8').value

图7

如果要将Excel数据作为pandas数据框架读入Python，代码如下。

import pandas as pd

df = xw.Range('B3').expand().options(pd.DataFrame).value

df.reset_index(inplace=True)

.expand()自动检测数据的维度，.options()指定我们需要pandas数据框架。我们在末尾重置了索引，因此x轴将被视为列，而不是数据框架索引。

图8

数据已经读入到Python，我们可以生成一个图形，然后将其放入Excel文件中。为了绘制图形，我们将使用matplotlib库。

import matplotlib.pyplot as plt

fig = plt.figure()

plt.plot(df['x轴'],df['y轴'])

plt.xlabel('x-axis')

plt.ylabel('y-axis')

sheet.pictures.add(fig,name='MyPlot',update=True)

图9

最后，正如我们对每个Excel电子表格所做的那样，我们必须保存我们的工作并关闭文件。

wb.save('auto_excel_with_python.xlsx')

wb.close()

第三部分：在Python中编写宏并在Excel中运行

澄清一下，这里的“宏”不是指VBA编写的宏，而是Python程序，可以从Excel执行。然而，它需要一点VBA来允许Excel调用Python函数。

Python脚本

让我们首先编写一个简单的Python函数，该函数生成10个随机数，然后将它们放在Excel工作表单元格A1中。注意，xw.Book.caller()引用当前工作簿。

将以下脚本另存为“rand_10.py”。

import numpy as np

import xlwings as xw

def generate():

wb = xw.Book.caller()

wb.sheets[0].range('A1').value = np.random.rand(10)

Excel VBA & 链接到Python脚本

好了，现在有了Python脚本。接着，在Excel中按Alt+F11组合键，打开VBA编辑器。

在VBA编辑器中，单击菜单“工具->引用”，找到并选取“xlwings”前的复选框，如下图10所示，然后单击“确定”按钮。

图10

接下来，单击“插入——模块”，插入一个标准模块。在右侧的代码窗口，输入以下VBA代码。

Sub Rand_10()

RunPython ('import rand_10; rand_10.generate()')

End Sub

这里需要注意的是:rand_10是Python脚本文件名。我们刚刚将脚本保存为”rand_10.py”。VBA代码RunPython (”import rand_10；rand_10.generate()”)基本上是说：导入名为”rand_10”的Python脚本，然后使用Python运行函数generate()。

接下来，保存VBA代码，现在我们要在Excel工作表中创建一个按钮。返回Excel界面，在“开发工具”选项卡，单击“插入->按钮”，并指定刚创建的宏Rand_10。

单击该按钮，将在单元格A1至J1中填充10个随机数，如下图11所示。

图11

注意：rand_10.py文件应放置在下面的文件夹中：

C:\Users\xxxx\AppData\Local\Programs\Python\Python39\Lib\site-packages

xxxx是计算机上自己的用户名。

或者：是否可以直接在代码中指定该文件放置的位置，而不必非得将文件放在上述文件夹。

第四部分：在Python中编写用户定义的函数并在Excel中调用该函数

高级Excel用户都知道，我们可以在VBA中创建用户定义的函数。这项功能很棒，因为并非所有内置的Excel函数都适合我们的需要。然而，VBA功能有限，使用xlwings，我们可以在Python中创建自己的用户定义函数。我们所需要的只是一个Python脚本，并在Excel中进行一些设置来实现。

Python脚本

编写一个简单的Python函数，计算数字的平方。

import xlwings as xw

@xw.func

def square(x):

return x ** 2

这里需要注意两件事：

@xw.func是一个装饰器。必须将其添加到def之前，以让xlwings知道这是一个用户定义的函数。
该函数必须返回某些内容，以便将返回的值传递到Excel中。

Excel设置

默认设置预计Python代码和Excel文件为：

在同一目录中
名称相同，但Python文件以.py结尾，Excel文件以.xlsm（或.xlsb）结尾

为了演示，我将把文件命名为“square.py”和“square.xlsm”。在Excel中打开square.xlsm，转到xlwings选项卡，然后单击Import Functions（导入函数）。

如果一切正常，意味着成功导入了Python函数，我们就可以直接在工作表中使用square()函数了，如下图12所示，在单元格中输入=square(A1)。

图12

注意到，当键入函数时，square实际上会显示在函数列表中——我们可以像使用Excel内置函数一样使用Python函数，并且可以将单元格引用传递到函数中。我们似乎在使用Excel函数，但其实在后台，Python正在进行所有计算，然后通过Excel向用户显示结果。这意味着，由于Python的强大功能，我们可以创建非常复杂的函数。

要说明的是，在某些情况下，可能会弹出“Automatio error 440”错误消息提示，则需要进行一些设置。

首先，到“信任中心”的“宏设置”选项卡中，选取“信任对VBA工程对象模型的访问(V)”前的复选框，如下图13所示。

图13

然后，到VBA编辑器中，设置对“xlwings”的引用，如上文中图10所示。

调试

刚开始可能会遇到的两个常见错误是：

1.“Automatio error 440”（自动化错误404）。上文中已讨论了如何修复此错误，确保Excel宏设置正确。

2.键入用户定义的函数时，单元格中会显示“Object Require”（对象要求）。确保在VBA编辑器菜单“工具->引用”中选取了“xlwings”，并将更改保存到相应的Excel文件中。有时，当打开多个Excel工作表时，我们可能会无意中将此更改应用于另一个文件。

注：本文学习整理自pythoninoffice.com。