如何用Python增强Excel，减少处理复杂数据的痛苦？

最新推荐文章于 2024-04-16 14:45:00 发布

wulishinian

最新推荐文章于 2024-04-16 14:45:00 发布

阅读量1k

点赞数

本文链接：https://blog.csdn.net/wulishinian/article/details/105249303

版权

Excel既是一种祝福，也是一种诅咒。

当涉及到足够小的数据和足够简单的操作时，Excel是王道。然而，一旦发现自己在努力走出这些区域，它就会变成一种痛苦。

当然，可以使用ExcelVBA来解决这些问题，但是在2020年，幸运的你不必这么做了！

如果有办法把Excel和Python集成在一起，Excel……就会插上翅膀！

现在有了。一个名为xlwings的python库允许用户通过VBA调用python脚本并在两者之间传递数据。

为什么要将Python与ExcelVBA集成？

事实上，用户可以在VBA中做任何事情。所以，如果是这样，为什么要使用Python？嗯，有很多原因。

1.在Excel中可以创建自定义函数，而不必学习VBA（如果读者还不知道的话）

2.用户对Excel很满意

3.使用Python可以显著加快数据操作

4.在Python中，几乎所有东西都有库（机器学习、数据科学等）

5.因为你可以！

准备使用xlwings

要做的第一件事，和想使用的任何新库一样，就是安装它。这是非常容易做到的；有了这两个命令，很快就能准备就绪。所以，把命令输入终端：

pipinstall xlwings

下载并安装库后，需要安装Excel集成部分。确保已关闭所有Excel实例和任何终端类型：

xlwings addin install

假设没有遇到错误，应该能够继续。然而，在Win10 的 Excel2016上，人们经常会看到以下错误：

xlwings0.17.0

[Errno 2] No such file or directory:'C:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART\\\xlwings.xlam'

如果走运地遇到了上述错误，需要做的就是创建丢失的目录。通过使用mkdir命令，可以很容易地做到这一点。就笔者而言，笔者做到了：

mkdirC:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART

假设excel与python库的集成安装成功，则可以立即注意到excel的主要区别：

file

为xlwings启用用户定义函数

首先，需要加载Excel加载项。点击Alt，L，H，然后导航到上面的目录来加载插件。完成后，应该能够看到以下内容：

file

最后，需要启用对VBA工程对象模型的信任访问。可以通过导航到“文件>选项>信任中心>信任中心设置>宏设置”来执行此操作。

xlwings入门

从Excel到Python（Python到Excel）有两种主要方式。第一种是直接从VBA调用Python脚本，另一种是通过用户定义的函数。快速看一下两者。

为了避免任何混淆，每次都能正确设置，xlwings提供了创建Excel电子表格的功能，准备就绪。下面来使用这个功能。

使用终端，导航到喜欢的目录并键入：

xlwingsquickstart ProjectName

把这称之为MyFirstPythonXL。上面的命令将在预先导航的目录中创建一个新文件夹，其中包含一个Excel工作表和一个python文件。

file

打开.xlsm文件时，可以立即注意到一个名为xlwings.conf的新Excel工作表。如果要覆盖xlwings的默认设置，只需重命名此工作表并删除起始下划线。这样，准备工作就完成了，下面开始使用xlwings。

VBA到Python

在开始编码之前，首先确保我们都在同一页上。要打开ExcelVBA编辑器，请按Alt+F11。这将返回以下屏幕：

file

带xlwings的VBA编辑器

这里关键要注意的是，此代码将执行以下操作：

1.在与电子表格相同的位置查找Python脚本

2.查找与电子表格同名的Python脚本（但扩展名为.py）

3.从Python脚本调用函数“main（）”

言归正传，来看看几个使用的实例。

例1：在Excel之外操作，并返回输出

在本例中，将看到如何在Excel之外执行操作，但随后将结果返回到电子表格中。这可以有无限多的用例。

从CSV文件中获取数据，对数据进行修改，然后将输出传递给Excel。这一操作很简单：

首先，VBA代码：

它与默认设置完全保持不变。

然后，Python代码：

importxlwings as xw
import 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

结果如下：

file

示例2：使用Excel输入来驱动操作

在本例中，从Excel读取输入，用Python对其进行处理，然后将结果传递回Excel。

更具体地说，要读一个问候语，一个名字和一个文件位置，在那里可以找到笑话。然后，Python脚本将从文件中随机抽取一行，并返回一个笑话。

首先，VBA代码：

它与默认设置完全保持不变。

然后，Python代码：

importxlwings as xw
import randomdef random_line(afile):
    line = next(afile)
    for num, aline in enumerate(afile,2):
      if random.randrange(num): continue
      line = aline
    return line
'Function from: stackoverflowdef 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)

结果为：

file

带xlwigs的用户定义函数

用与以前几乎相同的方式更改python文件中的代码。为了将某些内容转换为Excel用户定义函数，我们只需在函数所在的行之前包含“@xw.func”：

Python代码：

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

结果是：

file

如果你更喜欢使用Python而不是VBA，但又需要使用电子表格，这个工具是你的不二选择，你可以把它当作一个漂亮的小型数据库。

原文链接：读芯术 https://www.toutiao.com/a6795726551326392843/ 文源网络，仅供学习之用，如有侵权，联系删除。

我将优质的技术文章和经验总结都汇集在了我的公众号【Python圈子】里。

在学习Python的道路上肯定会遇见困难，别慌，我这里有一套学习资料，包含40+本电子书，600+个教学视频，涉及Python基础、爬虫、框架、数据分析、机器学习等，不怕你学不会！还有学习交流群，一起学习进步~