请选择 进入手机版 | 继续访问电脑版
 找回密码
 立即注册

QQ登录

只需要一步,快速开始

搜索
开启左侧

请教下python高手,如何用python自动化操作excel?

马上注册,分享更多源码,享用更多功能,让你轻松玩转云大陆。

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
目前需要自动化完成一些excel的重复性工作,涉及排序,插入数据,数据透视表等等,是学vba简单,还是用python容易,python的话需要用到什么样的模块呢,如果有文档可否提供下链接,谢谢。
回复

使用道具 举报

大神点评18

井底燕雀傥 2019-7-20 21:23:41 显示全部楼层
选择 VBA 还是 Python 取决于你的需求。
如果你只在 Excel 里做,并且数据量不超过它的限制的话,可以用 VBA 或 Excel 自有的功能。
如果你想把 Excel 与数据库、爬虫、微信、邮件等连接,或者要处理的数据量比较大的话,那用 Python 是更合适的,在 Excel 里面处理可能会“卡成 PPT“……
用 Python 处理 Excel 可以试下这几个库,我们整理了常用的功能,给学员做了速查表,也给你看下
请教下python高手,如何用python自动化操作excel?-1.jpg 就像玩积木一样,用这些基本的功能去组合,就可以实现很多事情。
今天大促比较忙,先写到这里了,回头我来更新几个案例,看能不能启发到你。
另外我们另一个回答下,不少学员分享了自己做的事情,可以先看看。
用Python自动化办公能做到哪些有趣或有用的事情? 文档链接:
xlrd documentation
xlwt documentation
https://xlutils.readthedocs.io/en/latest/
xlwt documentation
xlutils documentation
Creating Excel files with Python and XlsxWriter
openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files - openpyxl 2.6.1 documentation
xlwings - Python For Excel. Free & Open Source.
Python Data Analysis Library
关注微信公众号「麻瓜编程」,回复「小白书」获得20万读者喜爱的 《编程小白的第一本Python入门书》
回复

使用道具 举报

贺老师 2019-7-20 22:02:46 显示全部楼层
如果熟悉VBA 就用VBA,熟悉Python就用Python。
当前建议用pandas  这个第三方库,这里有例子
Common Excel Tasks Demonstrated in Pandas很早以前,我帮网友答疑的代码,请参考
有哪些python处理excel的教程? - 黄哥的回答 - 知乎 https://www.zhihu.com/question/35904647/answer/65023560
回复

使用道具 举报

123456790 2019-7-20 22:57:14 显示全部楼层
以前用VBA,现在用python,vba胜在比较方便写个界面出来吧。但是明显python好用。
其实很多情况下,excel内置的公式可以解决相当多的问题。
VBA我觉得不方便之处在于对于excel,word甚至ppt,相同或者相似元素的各种属性之类的,有时候名称和用法非常不同,也许是因为我没有系统的学过VBA,全都是碰见问题百度这样学来的缘故。
Python依赖两个库,xlrd和xlwt,一个负责把数据读出来,一个负责把数据写进去。数据怎么处理全靠python本身。当然这一点也决定了功能很强大了。而且本身python的数据处理能力不弱,加上各种库就更强了。你会发现写了10来行就完成了相对比较麻烦的运算
综上,推荐python
回复

使用道具 举报

123456811 2019-7-20 23:41:45 显示全部楼层
先回答题主的问题
看题主提问的时候谈到了排序、插入、数据透视等excel中常见的字眼,说明还是纠结于excel内的环境,这种情况下选择python的话就要考虑自身的需求问题了,实际上excel自带的东西就能解决99%的自动化问题了,indirect,名称,甚至极端情况搬出数组和宏表函数eval再或是powerpivot,在我看来必须上python的理由只有大量的计算、行列变换、极端复杂的逻辑关系;如果愿意接受python与excel在数据思想上微小的差距,并且稍微花点时间的话,还是强烈建议python的,毕竟python是真的很强大。


再说说库的问题
用什么库还是取决于自己的需求吧,要对整个工作流程有个清晰的认识。我之前试过好几个库,最后选择了xlwings,在此强烈安利一下官方文档,中文的教程网上也有,不过内容不够完整。
Quickstart - xlwings dev documentation与其他的库相比的异同优劣:
1、会运行excel进程
算是一个限制,xlwings在链接到一个excel文件时一定会打开它(可以不显示,但后台会有进程),如果你的工作流程中本来就需要打开excel程序(题主貌似应该符合)就不会有什么影响;但是本身如果想脱离excel的环境,只是使用excel中储存的数据,或是需要读取的excel文件非常多,xlwings可能不是最好的解决方案(这时用用pandas自带的read_excel就很舒服)。本人的工作流程中就要打开excel用一下com加载项,所以完全不会觉得很突兀。
2、运行速度快
快意味着什么就不用解释了,万行级别的数据,读取在百毫秒级别,写入只要几十毫秒,和pywin32库应该是平分秋色。(快不一定是每个人的刚需)
3、有清晰的对象,且对象逻辑及其的符合excel使用者的习惯
第一级对象App,代表excel程序(只处理一个文件时可以忽略它...)
第二级对象workbook,代表excel文件
第三级对象sheets,代表sheet
第四级对象range,代表区域(四级对象还有形状shape,图表chart,图片picture,个人用的不多,因为这些用excel程序就足够处理了)
对象清晰的好处就不多说了,严谨,有助于思考,查help时也省力,第一次用的时候真的惊艳到了。
4、对pandas支持好
pandas自带的excel工具就不说了,xlwings应该是几个三方库中对pandas支持最好的,支持数据直接读取成DataFrame格式,相比其他库要用好几步来转换的不知道高到哪里去了。
5、支持excel的语法
是的,你甚至能在python里写excel公式(虽然我从没这么干过),看似鸡肋,但这一点最关键的地方其实在于,能够使用excel内的名称,不需要再去看这个表是从A1:到A多少,直接告诉python我需要表名为xxx的表的数据(对于本身excel素质尚可的人来说,简直如虎添翼)。
6、不会覆盖文件
这一点及其的重要,某些库会在workbook中写入数据的时候清除所有的原数据。比如说,我要读取sheet1和sheet2里的数据,处理完成后生成一个表格,写到同一个workbook下的sheet3中,然后你会惊奇的发现sheet1和sheet2就不见了,印象中,不打开excel程序的库都有这个毛病而且无法解决,如果有在同一个workbook下读写的需求,请当仁不让的选择xlwings!
7、能把python写入VBA
这个功能没用过,感觉也不是很有必要,感兴趣可以读读官方文档。




最后,如果需要一行代码解决数据读取的问题,请直接用pandas自带的工具,什么库都是扯淡
回复

使用道具 举报

无人岛屿颈 2019-7-21 00:34:26 显示全部楼层
最近迷上了高效处理数据的pandas,其实这个是用来做数据分析的,如果你是做大数据分析和测试的,那么这个是非常的有用的!!
但是其实我们平时在做自动化测试的时候,如果涉及到数据的读取和存储,那么而利用pandas就会非常高效,基本上3行代码可以搞定你20行代码的操作!该教程仅仅限于结合柠檬班的全栈自动化测试课程来讲解下pandas在项目中的应用,这仅仅只是冰山一角,希望大家可以踊跃的去尝试和探索!
一、安装环境:

1:pandas依赖处理Excel的xlrd模块,所以我们需要提前安装这个,安装命令是:pip install xlrd

  2:安装pandas模块还需要一定的编码环境,所以我们自己在安装的时候,确保你的电脑有这些环境:Net.4 、VC-Compiler以及winsdk_web,如果大家没有这些软件~可以咨询我们的辅导员索要相关安装工具。

  3:步骤1和2 准备好了之后,我们就可以开始安装pandas了,安装命令是:pip install pandas

一切准备就绪,就可以开始愉快的玩耍咯!
ps:在这个过程中,可能会遇到安装不顺利的情况,万能的度娘有N种解决方案,你这么大应该要学着自己解决问题。
二、pandas操作Excel表单

数据准备,有一个Excel文件:lemon.xlsx有两个表单,表单名分别为:Python 以及student,
Python的表单数据如下所示:
请教下python高手,如何用python自动化操作excel?-1.jpg student的表单数据如下所示:
请教下python高手,如何用python自动化操作excel?-2.jpg 1:在利用pandas模块进行操作前,可以先引入这个模块,如下:
import  pandas  as pd2:读取Excel文件的两种方式:
#方法一:默认读取第一个表单
df=pd.read_excel('lemon.xlsx')#这个会直接默认读取到这个Excel的第一个表单 data=df.head()#默认读取前5行的数据 print("获取到所有的值:\n{0}".format(data))#格式化输出得到的结果是一个二维矩阵,如下所示:
请教下python高手,如何用python自动化操作excel?-3.jpg #方法二:通过指定表单名的方式来读取
df=pd.read_excel('lemon.xlsx',sheet_name='student')#可以通过sheet_name来指定读取的表单
data=df.head()#默认读取前5行的数据
print("获取到所有的值:\n{0}".format(data))#格式化输出
得到的结果如下所示,也是一个二维矩阵:
请教下python高手,如何用python自动化操作excel?-4.jpg #方法三:通过表单索引来指定要访问的表单,0表示第一个表单
#也可以采用表单名和索引的双重方式来定位表单
#也可以同时定位多个表单,方式都罗列如下所示
df=pd.read_excel('lemon.xlsx',sheet_name=['python','student'])#可以通过表单名同时指定多个
# df=pd.read_excel('lemon.xlsx',sheet_name=0)#可以通过表单索引来指定读取的表单
# df=pd.read_excel('lemon.xlsx',sheet_name=['python',1])#可以混合的方式来指定
# df=pd.read_excel('lemon.xlsx',sheet_name=[1,2])#可以通过索引 同时指定多个
data=df.values#获取所有的数据,注意这里不能用head()方法哦~
print("获取到所有的值:\n{0}".format(data))#格式化输出具体结果是怎样的,同学们可以自己一个一个的去尝试,这个结果是非常有意思的,但是同时同学们也发现了,这个数据是一个二维矩阵,对于我们去做自动化测试,并不能很顺利的处理,所以接下来,我们就会详细的讲解,如何来读取行号和列号以及每一行的内容 以及制定行列的内容。
三、pandas操作Excel的行列

1:读取指定的单行,数据会存在列表里面
#1:读取指定行
df=pd.read_excel('lemon.xlsx')#这个会直接默认读取到这个Excel的第一个表单
data=df.ix[0].values#0表示第一行 这里读取数据并不包含表头,要注意哦!
print("读取指定行的数据:\n{0}".format(data))得到的结果如下所示:
请教下python高手,如何用python自动化操作excel?-5.jpg 2:读取指定的多行,数据会存在嵌套的列表里面:
df=pd.read_excel('lemon.xlsx')
data=df.ix[[1,2]].values#读取指定多行的话,就要在ix[]里面嵌套列表指定行数
print("读取指定行的数据:\n{0}".format(data))3:读取指定的行列:
df=pd.read_excel('lemon.xlsx')
data=df.ix[1,2]#读取第一行第二列的值,这里不需要嵌套列表
print("读取指定行的数据:\n{0}".format(data))4:读取指定的多行多列值:
df=pd.read_excel('lemon.xlsx')
data=df.ix[[1,2],['title','data']].values#读取第一行第二行的title以及data列的值,这里需要嵌套列表
print("读取指定行的数据:\n{0}".format(data))
5:获取所有行的指定列
df=pd.read_excel('lemon.xlsx')
data=df.ix[:,['title','data']].values#读所有行的title以及data列的值,这里需要嵌套列表
print("读取指定行的数据:\n{0}".format(data))
6:获取行号并打印输出
df=pd.read_excel('lemon.xlsx')
print("输出行号列表",df.index.values)

输出结果是:
输出行号列表 [0 1 2 3]
7:获取列名并打印输出
df=pd.read_excel('lemon.xlsx')
print("输出列标题",df.columns.values)

运行结果如下所示:
输出列标题 ['case_id' 'title' 'data']
8:获取指定行数的值:
df=pd.read_excel('lemon.xlsx')
print("输出值",df.sample(3).values)#这个方法类似于head()方法以及df.values方法

输出值
[[2 '输入错误的密码' '{"mobilephone":"18688773467","pwd":"12345678"}']
[3 '正常充值' '{"mobilephone":"18688773467","amount":"1000"}']
[1 '正常登录' '{"mobilephone":"18688773467","pwd":"123456"}']]9:获取指定列的值:
df=pd.read_excel('lemon.xlsx')
print("输出值\n",df['data'].values)
四:pandas处理Excel数据成为字典

我们有这样的数据,
,处理成列表嵌套字典,且字典的key为表头名。
实现的代码如下所示:


请教下python高手,如何用python自动化操作excel?-7.jpg df=pd.read_excel('lemon.xlsx')
test_data=[]
for i in df.index.values:#获取行号的索引,并对其进行遍历:
    #根据i来获取每一行指定的数据 并利用to_dict转成字典
    row_data=df.ix[i,['case_id','module','title','http_method','url','data','expected']].to_dict()
    test_data.append(row_data)
print("最终获取到的数据是:{0}".format(test_data))
最后得到的结果是:
最终获取到的数据是:
[{'title': '正常登录', 'case_id': 1, 'data': '{"mobilephone":"18688773467","pwd":"123456"}'},
{'title': '输入错误的密码', 'case_id': 2, 'data': '{"mobilephone":"18688773467","pwd":"12345678"}'},
{'title': '正常充值', 'case_id': 3, 'data': '{"mobilephone":"18688773467","amount":"1000"}'},
{'title': '充值输入负数', 'case_id': 4, 'data': '{"mobilephone":"18688773467","amount":"-100"}'}] 关于pandas的学习,今天就告一段落啦!赶紧打开pycharm跑起来!!!
除了本文,我准备了 《python自动化处理excel的学习视频》

希望以上的回答对你有用
作者:华妹陀
来源:博客园
回复

使用道具 举报

python与excle交互vba无缝链接库  :
xlwings

其他那些xlrd,xlwt,openpyxl,xlwriter做自动化报表还是有些麻烦的
pandas,matplotlib都可以与xlwings嵌套用,其他的库暂时还没有类似方法。
用好这个库,excel自动化真的小case
回复

使用道具 举报

素色流年783 2019-7-21 03:28:29 显示全部楼层
python简单……你都不用装excel就可以完成……
xlwt,xlrd这两个库一个写一个读就可以完成很多需求了。
问题只是不能改,只能读和写新的,但是看起来能满足你的需求。
其他的库xlwings,pandas就更强大了。
回复

使用道具 举报

风来时狂放 2019-7-21 04:13:29 显示全部楼层
vba和python都用过。早期用vba,后来感觉vba不适合复杂的程序,现在改为python(公司里有个系统就是用vba实现的,一个文件不输入任何用户数据就有10m,用起来太痛苦)。python操作excel主要用pywin32或者openpyxl。
pywin32可以理解为在python中操作excel,只要vba能实现的功能pywin32都可以实现。并且直接可以操作excel已经打开的文件,也就是说可以把一个excel文件作为用户界面,通过python改变表格中的内容。缺点是速度比较慢,如果数据很多可能就不适合了。当然只能在windows平台上使用,需要安装有excel。
另一种我经常用的方法是openpyxl,支持xlsx文件,能读能写,可以在非windows平台上使用。
回复

使用道具 举报

123456790 2019-7-21 05:08:51 显示全部楼层
VBA 已经在淘汰边缘了,国外的生产力工具目前都往 Spreadsheet 和 Apps Script走。
鉴于国内大环境(和谐),推荐还是使用Pyhton开发搭配xlwings 包,用过的都说好。
如果需要生成excel表格搭配 xlswriter 使用最佳。
非常复杂的工业级 Excel 才可能用得上VBA。
回复

使用道具 举报

123456825 2019-7-21 05:24:24 显示全部楼层
pandas模块 pandas.read_excel 转化为dataframe格式,具体需求请查阅pandas官方文档,英文不好查csdn。http://pandas.pydata.org/pandas-docs/stable/user_guide/index.html
回复

使用道具 举报

十二音阶囤 2019-7-21 06:46:59 显示全部楼层
win32com   试试就知道了  很强大    还有openpyxl   等  pandas这种处理很大的文件效率很低  不建议使用
回复

使用道具 举报

同样遇到这个问题,但是因为是门外汉,学习python实在难入门,所以只能手工了
回复

使用道具 举报

建议用python的pywin32库。
Python的语法更灵活,相关文档及库也多。
pywin32库操作方便,适用于各种版本excel文件的读写等各种操作,有不会写的地方还可以用Excel本身录制宏来参考宏行成的代码。
同时也适用于word文档的读写,替换,格式设置等。
详见Github项目,
https://github.com/mhammond/pywin32
用法示例
https://www.jianshu.com/p/518acd3d9f14
回复

使用道具 举报

主要用到两个库。
openpyxl,功能完备。但该库不适用部分xlsx文件,比如带有复杂条件格式的文档,处理后格式容易乱掉。
其他excel相关的库要么仅支持xls,要么只能读或只能写不能改,一律不推荐。
win32com。该库能够帮你调用windows api操作excel,功能强大,不存在兼容性问题。但该库说明文档语焉不详,得自己一个一个试。
回复

使用道具 举报

123456819 2019-7-21 09:39:40 显示全部楼层
如果单纯是实现Excel自动化的话,VBA肯定更有优势。Python是万金油啥都可以干,有些第三方库也可以自动化Excel,但是主要是数据处理上。反正结合自身需求来吧。如果有兴趣可以加我微信交流学习,学了这么多年的Python和VBA有些课程很经典,可以帮你指点迷津,不走弯路!
回复

使用道具 举报

123456825 2019-7-21 10:40:50 显示全部楼层
曾经写过python操作excel的程序,当时vba不会,项目时间要求短。对于熟悉python的人来说还是用py调包吧,除了上面 说的一些,还有一个叫xlwings也非常好用,不过中午文档较少,国内资料也比较少,当时用的时候费了死劲。
回复

使用道具 举报

宇宙无限 2019-7-21 10:59:17 显示全部楼层
逻辑不复杂的话,也可以考虑powershell
回复

使用道具 举报

十二音阶囤 2019-7-21 11:57:59 显示全部楼层
vba比较简单
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

广告招商