python|pandas、openpyxl、xlrd&xlwt&xlutils耗时对比、使用踩坑 xlrd|python|自动化测试|读写exe

一、问题背景：写数据时因为多加了两个字段，结果程序运行总耗时10s多，简直不能忍。开始分析各阶段耗时，发现写execl就用了10s多，几乎所有耗时都在这。
二、问题解决：读写execl有三种基本方式：
??pandas、openpyxl、xlrd&xlwt&xlutils
openpyxl、xlrd&xlwt&xlutils的对比
??在自己写的脚本中做了实验，对于读取小文件，openpyxl用时0.009s，xlrd用时0.003s.
当想比较下写入速度时，发现xlrd只能读，不能写，所以要结合xlutils使用。
以下是xlrd&xlwt&xlutils各模块的功能
??xlrd：读取Excel文件数据
??xlwt：写入Excel数据，只能是在新建的表中写入。
??xlutils：xlutils模块的功能是作为xlrd和xlwt的桥梁，解决了xlrd中book对象无法编辑的问题。通过copy模块将xlrd.Book对象转换为 xlwt.Workbook对象，从而实现了原始excel文件的编辑功能
??通过xlrd打开的execl文件不能直接写，需要使用xlutils.copy复制一份工作薄，再获取工作表。get_sheet()可以写int索引，也可写表名。再写入数据的时候直接写到work_book_copy中，并保存。
以下是写入相同数据时，两种方式的比较
数据：

a = {29: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 26: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 27: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 3: {17: 1, 18: '', 19: {'move_status': ['松开']}, 20: 'iOS', 21: '10107'}, 5: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 28: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 7: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 11: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 10: {17: 2, 18: "错误的枚举值:[[338, 'interact_name', 'NPC兑换']]；", 19: {}, 20: 'iOS', 21: '10107'}, 13: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 15: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 14: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 12: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 23: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 8: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 2: {17: 1, 18: '', 19: {'user_gender': ['2'], 'is_live': ['1']}, 20: 'iOS', 21: '10107'}, 22: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}, 21: {17: 1, 18: '', 19: {}, 20: 'iOS', 21: '10107'}}

时间比：

openpyxl :0.13587851s

xlrd&xlwt&xlutils : 0.01033256s

第二种方式比第一种方式减少了约92.4%.
??好，那就进行下一步，将自动下载的execl保存为xls格式的。文件通过urllib.request.urlretrieve(url, filename=None, reporthook=None, data=https://www.it610.com/article/None)函数保存，这个函数可以将URL表示的网络对象复制到本地文件。filename的后缀直接写成.xls的。ok，文件保存到本地确实成了.xls文件。结果发现这样保存并不行，需要打开文件，另存为xls才可以。
高版本的xlrd能不能保留样式呢？答案是：不能。
??低版本的xlrd==1.2.0仅支持.xls格式文件，但是可以使用formatting_info保留文件样式；
??高版本的xlrd可支持.xls和.xlsx格式文件，但是使用formatting_info就会报错。raise NotImplementedError(“formatting_info=True not yet implemented”)。高版本不再支持formatting_info的可能原因就是为了节省内存和时间，只去识别表中有内容的部分。没有内容但是有样式的单元格会被判断为空白。所以想要用xlrd读写execl文件，要不就不要样式，要不就把文件另存为.xls。
pandas:
??pandas和matplotlib、numpy是数据分析的基础模块，广泛用于大数据分析，而大数据往往用csv格式保存（csv行数列数没有上限，execl最大行数1048576，最大列数16384）。另外pandas写入引擎就是openpyxl和xlwt。
??已有的实验显示，通过for循环遍历12000行相同数据时, pandas耗时2.6s，openpyxl耗时0.47s，xlrd耗时0.35s。所以，对于埋点工具来说，pandas没啥优势。
总结：
??xlrd读取速度优于openpyxl，低版本xlrd对读取xlsx会出现版本不兼容的问题，它的功能没有openpyxl全面，大部分情况需要结合xlutils来使用。
??如果只是读取，没有格式要求的话，建议使用xlrd。
??如果是需要在原文件上修改的话，建议使用openpyxl。
另注：
??openpyxl的根据行号列号读取的时候，是从第一行第一列开始遍历，直到行号等于指定行号，列号等于指定列号，所以要读取的行号列号越多就越慢。写入数据时，也需要先找到对应的单元格。（也可能是从第一个有数据的行或列）。而xlrd则是类似与数组一样，我们要取第几个元素，直接根据下标找到内存中对应地址的元素即可，所以无论excel总量多少，速度变化并不明显
附录：
Python 通过xlutils实现在保留execl原格式的情况下写数据

#coding=utf-8import xlrd,xlwtfrom xlutils.copy import copy #读取文件 read_file = xlrd.open_workbook(file_path,formatting_info=True)#参数注释： #file_path：文件路径，包含文件的全名称 #formatting_info=True：保留Excel的原格式 #将文件复制到内存 write_data = https://www.it610.com/article/copy(read_file) #读取复制后文件的sheet1 write_save = write_data.get_sheet(0)#可以通过表名索引 #写入数据 write_save.write(x,y,value)#参数注释： #x,y:写入目标格的位置坐标 #value：写入数据 #保存写入数据后的文件到原文件路径 write_data.save(self.file_path)

通过xlrd读取Excel数据：

import xlrd#通过open_workbook读取Excel文件 data_r = xlrd.open_workbook("资产盘点记录表.xls") #通过索引将sheet表赋值给变量 table_r = data_r.sheets()[0] #获取整列数据(列索引) col_value = https://www.it610.com/article/table_r.col_values(1) #获取整行数据(行索引) row_valu e= table_r.row_values(1) #获取单个单元格数据(行索引，列索引) value = sheet1.cell(1,2).value

通过xlwt创建新表写入数据

import xlwt # 创建Excel文件对象、表格页sheet workbook = xlwt.Workbook(encoding='utf-8') worksheet = workbook.add_sheet('sheet名称')# sheet页中写入数据 worksheet.write(0, 0, label='ID') worksheet.write(0, 1, label='姓名')# 保存Excel文件 workbook.save(file_name)

【python|pandas、openpyxl、xlrd&xlwt&xlutils耗时对比、使用踩坑】xlutils参考资料：https://www.cnblogs.com/machangwei-8/p/10739115.html
openpyxl参考资料：https://geek-docs.com/python/python-tutorial/python-openpyxl.html