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

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

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

[[315780]]

当涉及到足够小的数据和足够简单的操作时,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

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

  1. pipinstall xlwings 

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

  1. xlwings addin install 

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

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

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

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

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

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

为xlwings启用用户定义函数

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

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

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

xlwings入门

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

为了避免任何混淆,每次都能正确设置,xlwings提供了创建Excel电子表格的功能,准备就绪。下面来使用这个功能。使用终端,导航到喜欢的目录并键入: 

  1. xlwingsquickstart ProjectName 

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

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

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

VBA到Python

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

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

带xlwings的VBA编辑器

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

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

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

3.从Python脚本调用函数“main()”

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

例1:在Excel之外操作,并返回输出

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

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

首先,VBA代码:

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

然后,Python代码: 

  1. importxlwings as xw 
  2. import pandas as pddef main(): 
  3.     wb = xw.Book.caller() 
  4.     df =pd.read_csv(r'C:\temp\TestData.csv'
  5.     df['total_length'] =  df['sepal_length_(cm)'] +df['petal_length_(cm)'
  6.     wb.sheets[0].range('A1').value = df 

结果如下: 

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

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

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

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

首先,VBA代码:

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

然后,Python代码: 

  1. importxlwings as xw 
  2. import randomdef random_line(afile): 
  3.     line = next(afile) 
  4.     for num, aline in enumerate(afile,2): 
  5.       if random.randrange(num): continue 
  6.       line = aline 
  7.     return line 
  8. 'Function from: stackoverflowdef main(): 
  9.     wb = xw.Book.caller() 
  10.     listloc =str(wb.sheets[0].range('B3').value) 
  11.     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' 
  12.     wb.sheets[0].range('A6').value =random_line(fhandle) 

结果为: 

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

带xlwigs的用户定义函数

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

Python代码: 

  1. importxlwings as xw@xw.func 
  2. def joke(x): 
  3.     wb = xw.Book.caller() 
  4.     fhandle = open(r'C:\Temp\list.csv'
  5.     for i, line in enumerate(fhandle): 
  6.         if i == x: 
  7.             return(line) 

结果是: 

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

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

 

责任编辑:华轩 来源: 今日头条
相关推荐

2023-04-07 14:04:52

增强分析人工智能

2024-04-03 07:46:41

PythonReduce函数工具

2020-10-29 06:02:44

PythonPandasExcel

2024-11-14 08:00:00

Python迭代器

2023-04-21 16:06:33

2010-07-13 13:27:13

Perl复杂数据结构

2020-12-10 10:46:23

PythonExcel图片

2023-08-02 08:47:55

聚合框架MongoDB

2016-12-02 19:40:41

数据分析

2016-11-28 15:21:54

谷歌大数据

2024-01-09 07:34:28

Rust架构语言

2017-07-20 21:06:44

PythonExcelSQL

2011-07-13 14:02:42

OracleExcel

2022-07-14 07:12:09

PythonPandasVBA

2021-03-04 13:40:57

Python文件代码

2020-07-10 09:49:53

数据清理数据分析查找异常

2024-09-23 10:00:00

代码Python

2017-12-06 15:46:31

深度学习结构化数据NLP

2024-10-11 18:36:51

2011-12-31 09:24:29

减少PUE数据中心
点赞
收藏

51CTO技术栈公众号