python处理Excel文件的方法集合及性能对比

有用请点赞，没用请差评。

欢迎分享本文，转载请保留出处。

 

最近工作需要批量处理excel文本，写个小总结。

• 一、xlwt、xlrd处理excel文件

Python中一般使用xlrd库来读取Excel文件，使用xlwt库来生成Excel文件，使用xlutils库复制和修改Excel文件。

注意xlwt只支持到Excel2003，即xls文件；xlrd对xls、xlsx文件都可以读取。

1. xlwt创建excel文件

   
   """
   使用xlwt创建xls文件
   """
   import xlwt
   
   # 文件保存路径
   save_dir="D:\\python3_anaconda3\\学习\\test\\excel文件处理\\"
   
   #创建workbook和sheet对象
   workbook = xlwt.Workbook()
   sheet1 = workbook.add_sheet('sheet1',cell_overwrite_ok=True)
   
   # #向sheet中写入数据,通过单元格坐标位置写入,第一个单元格位置为（0,0）
   for i in range(0,100):
       sheet1.write(i,0,i)
   
   #保存该excel文件,有同名文件时直接覆盖
   save_filename="xlwt_test.xls"
   workbook.save(save_dir+save_filename)
   print("finish")

    

2. xlrd读取excel文件

 以手边的漏洞扫描器AWVS插件表格为例，该excel表格共有10列，638行，通过xlrd读取。

xls文件如图：

代码：

"""
使用xlrd打开xls文件,
"""

import xlrd

save_dir="D:\\python3_anaconda3\\学习\\test\\excel文件处理\\"
open_filename="AWVS漏洞信息.xls"

# 打开xls文件
workbook = xlrd.open_workbook(save_dir+open_filename)

sheet1 = workbook.sheet_by_index(0) # 通过sheet位置打开第一张表
# sheet=workbook.sheet_by_name("web漏洞") #通过表名打开sheet

# 获取总列数
print(sheet1.ncols)
 # 获取对应列的数据
col_0 = sheet1.col_values(0)
print(len(col_0))

# 获取总行数
print(sheet1.nrows)
 # 获取对应行的数据
row_0 = sheet1.row_values(0)
print(len(row_0))

# 通过单元格位置获取数据
cell_00 =sheet1.cell_value(0,0)
print(cell_00)

运行结果：

  3.修改已有excel文件

xlwt库只能用来生成Excel文件，不能对已有的xls文件进行修改操作。通过xlutils.copy模块复制一个xlrd.Book对象，生成一个xlwt.Workbook对象，可以对xlwt.Workbook进行修改。

"""
使用xlrd读取文件，使用xlwt生成Excel文件
但是用xlrd读取excel是不能对其进行操作的；而xlwt生成excel文件是不能在已有的excel文件基础上进行修改的，如需要修改文件就要使用xluntils模块。
"""

import xlrd
import xlutils.copy

save_dir="D:\\python3_anaconda3\\学习\\test\\excel文件处理\\"
open_filename="xlwt_test.xls"

#打开一个workbook
workbook = xlrd.open_workbook(save_dir+open_filename)
# 复制
workbook2 = xlutils.copy.copy(workbook)

#获取sheet对象，注意通过sheet_by_index()获取的sheet对象没有write()方法
sheet2 = workbook2.get_sheet(0)

sheet2.write(0,0,"xlutils test")

# 利用保存时同名覆盖达到修改excel文件的目的,注意未被修改的内容保持不变
workbook2.save(save_dir+open_filename)

• 二、使用openpyxl处理xlsx文件

openpyxl官方文档的介绍： A Python library to read/write Excel 2010 xlsx/xlsm files

openpyxl则主要针对Excel2007之后的版本（.xlsx）。

1.使用openpyxl创建xlsx文件

注意，openpyxl中为了和Excel中的表达方式一致，并不和编程语言的习惯一样以0表示第一个值，在openpyxl中以1表示第一个值。

import openpyxl

save_dir="D:\\python3_anaconda3\\学习\\test\\excel文件处理\\"
open_filename="openpyxl_test.xlsx"

out_workbook=openpyxl.Workbook()
out_sheet=out_workbook.create_sheet(index=0,title="openpyxl_new")

# 注意，openpyxl中为了和Excel中的表达方式一致，并不和编程语言的习惯一样以0表示第一个值，在openpyxl中以1表示第一个值。
for i in range(1,100):
    out_sheet.cell(row=i ,column=1).value=i

# openpyxl
out_workbook.save(open_filename)

2.使用openpyxl读取及修改xlsx文件

使用openpyxl修改已有文件比较简单，可以直接对原文件进行修改。

import openpyxl

save_dir="D:\\python3_anaconda3\\学习\\test\\excel文件处理\\"
open_filename="openpyxl_test.xlsx"

out_workbook=openpyxl.load_workbook(save_dir+open_filename)

sheet_names=out_workbook.sheetnames
print(sheet_names)

# 通过表坐标来获取表对象
out_sheet=out_workbook.worksheets[0]

# 新方法：通过表名获取表对象。等同于get_sheet_by_name("sheetname")
# out_sheet=out_workbook["openpyxl_new"]

# 老方法:通过表名获取表对象，
# out_sheet=out_workbook.get_sheet_by_name(sheet_names[0])

print(out_sheet.cell(row=1 ,column=1).value)
out_sheet.cell(row=1 ,column=1).value="openpyxl_change"

out_workbook.save(open_filename)

注意：使用get_sheet_by_name()时会出现DeprecationWarning，提示这是个旧方法（如下图），建议使用wb[sheetname]。虽然代码没有错误，但是出现一条红色的DeprecationWarning就是很不完美啊。那我们就进入源码看看。

进入源码workbook.py中看get_sheet_by_name方法，如下图。可以看到在装饰器上面建议使用wb[sheetname]，因此对于有强迫症的同学最好还是听从建议。

•  三、其他模块

除了上面介绍的几个模块之外，还有其他几个可以处理excel的模块。

xlwings：简单强大，可替代VBA

pandas：使用需要结合其他库，数据处理是pandas立身之本

win32com：不仅仅是excel，可以处理office;

Xlsxwriter：丰富多样的特性，直接创造一份美观大方的excel

DataNitro：作为插件内嵌到excel中，可替代VBA，在excel中优雅的使用python

对于这部分模块暂不详述。

 

• 四 模块对比

openpyxl、xlrd、xlwt读写速度对比

xlwt针对Ecxec2007之前的版本，即.xls文件，其要求单个sheet不超过65535行，而openpyxl则主要针对Excel2007之后的版本（.xlsx），它对文件大小没有限制。另外就是两者读写速度存在差异，为了比较它们的差异，特别做了以下测试：

测试环境：windows 10 X64，i7 4600U，Pycharm

1、写入测试

分别用xlwt与openpyxl生成一个65535行，1列，单元格内为整型数值的.xls与xlsx文件：

#使用xlwt生成文件
import xlwt
import time

time.clock()
f = xlwt.Workbook()
sheet1 = f.add_sheet('sheet_1')
for j in range(10):
    for i in range(65535):
        sheet1.write(i,j,i)

f.save('xlwt_write.xls')
t = time.clock()
print(t)

#使用openpyxl生成文件

import openpyxl
import time

time.clock()
out_workbook = openpyxl.Workbook()
sheet1 = out_workbook.create_sheet(index=0,title="sheet1")

# 从1开始
for j in range(1,11):
    for i in range(1,65536):
        sheet1.cell(row = i,column = j,value = i)

out_workbook.save('openpyxl_write.xlsx')
t = time.clock()
print(t)

结果：

	openpyxl	xlwt
写入(s)	10.85681415	6.231883997
类型	xlsx	xls
限制	对文件大小无限制	最大行数65535行
速度	慢	快

2、读取测试

openpyxl和xlrd都支持xlsx文件的读取，这里就以xlsx文件为例。

#使用xlrd进行读取
import xlrd
import time

time.clock()
f = xlrd.open_workbook('openpyxl_write.xlsx')
sheet1 = f.sheet_by_index(0)

print(sheet1.cell(65534,9).value)
t = time.clock()
print(t)

#使用openpyxl进行读取
import openpyxl
import time

time.clock()
wb = openpyxl.load_workbook('openpyxl_write.xlsx')
sheet1 = wb.worksheets[0]
print(sheet1.cell(row = 65535 ,column = 10).value)
t = time.clock()
print(t)

结果：

	openpyxl	xlrd
读取(s)	11.22823392	4.608229207
类型	xlsx	xlsx、xls
限制	无限制
速度	慢	快

3、测试结果

整体而言，两种包对小文件的读写速度差别不大，而面对较大文件，xlrd/xlwt速度明显优于openpyxl，但因为xlwt无法生成xlsx，所以想要尽量提高效率又不影响结果时，可以考虑用xlrd读取，用openpyxl写入，同时openpyxl可以支持对原始文件的修改，更方便。

 

注：内容原创，部分文字来源于网络。