学习Excel技术，关注微信公众号：
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:用户xxxxAppDataRoamingMicrosoftAddIns
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:UsersxxxxAppDataLocalProgramsPythonPython39Libsite-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。
