pandas、openpyxl、xlrd&xlwt&xlutils耗时对比、使用踩坑

一、问题背景：

写数据时因为多加了两个字段，结果程序运行总耗时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=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 = 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 = 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)

xlutils参考资料：https://www.cnblogs.com/machangwei-8/p/10739115.html
openpyxl参考资料：https://geek-docs.com/python/python-tutorial/python-openpyxl.html