python读写excel之xlrd&xlwt&xlutils组合

---

前言

python中能操作Excel的库主要有以下9种：

常用的主要有：

1、xlrd： 读取 xls 格式Excel文件数据；
2、xlwt： 将数据写入 xls 格式Excel文件；
3、openpyxl： 读取、写入 xlsx 格式Excel文件；
4、pandas： 通过 xlrd 与 xlwt 模块实现xls 格式Excel文件的读写操作；
5、win32com： 获取 Excel 应用接口，实现Excel 文件的读写。

一 . 简介

python中通过xlwt、xlrd和xlutils三个模块操作xls文件。这三个模块的区别如下：

• xlwt：xlwt用于在内存中生成一个xlsx/xls对象，增加表格数据，并把内存中的xls对象保存为本地磁盘xls文件。
• xlrd：xlrd用于把本地xls文件加载到内存中，可以读取xls文件的表格数据，查询xls文件的相关信息。
• xlutils：xlutils模块是xlrd和xlwt之间的桥梁，读取xls文件可以使用xlrd，新建创建文件可以使用xlwt，而修改文件则使用xlutils；可以使用xlutils模块中的copy模块拷贝一份通过xlrd读取到内存中的xls对象，就可以在拷贝对象上像xlwt中那样修改xls表格的内容，并保存到本地。
• 除此之外，还有一些其他的模块也可以用于操作excel文件，如xlwings、openyxl、xlsxwriter、win32com和pandas库

xlrd&xlwt

xlrd和xlwt两个模块，一个负责“读”取 xls 和 xlsx 格式Excel文件的库（较新的版本将不再支持xlsx格式的读取），一个负责“写”入 xls 格式Excel文件的库。但两个模块操作的对象并不是一类。原因是用xlrd模块打开一个Excel工作表后，返回一个xlrd.Book对象实例；用xlwt模块创建工作簿时，会返回一个xlwt.Workbook对象实例。两者其实是不同的对象。这样做会有：

xlrd和xlwt主要支持早期的Excel版本，但优点是操作更自然，模拟人们对Excel表格的手动操作，且适用性强，可以实现多层表头的结构（而不像pandas把第一行视为表头，剩下的行都视为数据）。

两者其实是不同的对象。这样做会有：

优点：在处理Excel数据时，流程更加清晰。

缺点：，数据量大时，由于“读”、“写”操作的对象并不同，需要折中处理为Python对象，这是一个繁琐的转化过程，很浪费资源。

弥补缺点的思路可以是直接将xlrd.Book对象转化为一个xlwt.Workbook对象，将xlrd读取到的Excel文件直接转化为可写的Workbook对象实例，这样，写入后直接保存即可完成整个读写操作（去中介）。

解决方法：使用xlutils

xlrd、xlwt二者均无法直接修改excel文件，xlutils库可提供辅助和衔接，使用户可以同时读写一个 .xls 文件。

xlrd官方文档：https://xlrd.readthedocs.io/en/latest/
xlwt官方文档：https://xlwt.readthedocs.io/en/latest/
xlrd开源库：https://github.com/python-excel/xlrd
xlwt开源库：https://github.com/python-excel/xlwt

xlutils

xlutils模块相当于在xlrd和xlwt之间搭建了一座桥，最核心的作用是将xlrd的Book对象复制转换为xlwt 的Workbook对象，具体使用时，通常导入模块中的copy子模块中的copy函数来实现

xlutils官方文档：https://xlutils.readthedocs.io/en/latest/
xlutils开源库：https://github.com/python-excel/xlutils

python其他常用Excel读写库：

openpyxl：实现对xlsx格式Excel文件的读写和修改操作。
pandas：数据处理最常用的分析库之一，可以读写xls和xlsx格式的Excel文件，一般输出dataframe格式，功能强大。
更多的Excel文件操作的Python开源库：https://www.python-excel.org/

二 . 下载

1. 官网下载

xlrd
官网：https://pypi.org/project/xlrd/

xlwt
官网：https://pypi.org/project/xlwt/

xlutils
官网：https://pypi.org/project/xlutils/

2. Windows黑窗口

xlrd
推荐使用：在cmd命令提示符窗口用pip install xlrd进行安装，如果想安装指定版本比如2.1.0，则输入：pip install xlrd==2.1.0。

xlwt
推荐使用：在cmd命令提示符窗口用pip install xlwt进行安装，如果想安装指定版本比如1.3.0，则输入：pip install xlwt==1.3.0。

xlutils
推荐使用：在cmd命令提示符窗口用pip install xlutils进行安装，如果想安装指定版本比如2.0.0，则输入：pip install xlutils==2.0.0。

3. 查看版本

xlrd
在cmd窗口输入pip show xlrd，查看安装的xlrd版本
xlwt
在cmd窗口输入pip show xlwt，查看安装的xlwt版本
xlutils
在cmd窗口输入pip show xlutils，查看安装的xlutils版本

三 . xlrd

在使用xlrd获取Excel文件内容之前，需要先准备好两个Excel文件(.xls文件、.xlsx文件)，为了演示效果，我将按照下面这两个内容作为演示文件内容：


使用xlrd对Excel进行读操作的流程和手动操作Excel文件一样：打开工作簿(Workbook) --> 选择工作表(sheet) --> 操作单元格(cell)

1. 打开工作簿

xlrd.open_workbook(excel文件路径) - 打开指定路径对应的excel文件，返回excel文件对应的工作簿对象。

import xlrd

# filepath为文件路径名
book = xlrd.open_workbook(filepath)



print(book)
1
2
3
4
5
6
7
8

运行结果：

xlrd.book.Book object at 0x0000013C076207F0
1

也就是说我们返回了一个xlrd.book.Book对象。

2. workbook中的工作表（sheet）操作

sheetCount = book.nsheets  #返回工作簿中sheet的数量

sheets = book.sheets()  #返回所有sheet对象
names = book.sheet_names() #返回所有sheet的名称

sheets = book.sheets()[sheet_index]  #通过索引顺序获取一个sheet对象
sheet = book.sheet_by_index(sheet_index))  #通过索引顺序获取一个sheet对象
sheet = book.sheet_by_name(sheet_name)  #通过名称获取一个sheet对象
1
2
3
4
5
6
7
8

返回的结果是一个xlrd.sheet.Sheet对象。

如果工作表的内容很多时，要检查一下这个工作表是否已经完全导入了。检查的方法如下:

check = book.sheet_loaded(sheet_name) #通过工作表名称指定工作表进行检查
check = book.sheet_loaded(sheet_index) #通过工作表索引顺序指定工作表进行检查
1
2

检查工作表是否导入完毕，完毕则返回true

3. 获取行列信息

sheet中的行操作

1. 获取该sheet中的有效行数

   rowCount = sheet.nrows
   1

2. 获取指定行所有单元格对象组成的列表
   有两种表达方式：其中row_number是指定的行数（从0开始计算），返回的列表里是键值对。

   row_object = work_sheet.row(row_number)  
   row_object = work_sheet.row_slice(row_number)
   1
   2

3. 获取指定行单元格内容组成的列表
   获取指定行所有单元格内容组成的列表，row_number为行数（从0开始计算）：

   row_content = work_sheet.row_values(row_number)
   1

   也可以用切片的方式获取指定行里指定从开始列（start_colx，包括）到结束列（end_colx，不包括）的内容组成的列表。

   如果没有设置，默认start_colx=0，end_colx=None，end_colx为None表示结束没有限制。

   row_content = work_sheet.row_values(row_number, start_colx=0, end_colx=None)
   1

4. 获取指定行单元格数据类型组成的列表
   其中：row_number为行数（从0开始计算），返回的列表是个逻辑值列表，同样可以用切片方式指定列数范围。

   row_type = work_sheet.row_types(row_number)
   1

   至于逻辑值是什么，我们首先来了解一下常用的单元格的数据类型。

   0：empty

   1：text

   2：number

   3：date

   4 ：boolean

   5：error

   也就是说若单元格数据类型为empy（空）则逻辑值为0。

5. 获取指定行有效单元格的长度
   即获取这一行有多少个数据，row_number为行数（从0开始计算）。

   row_length = work_sheet.row_len(row_number)
   1

6. 获取工作表所有行的生成器

   rows_generator = work_sheet.get_rows()
   1

   知识点：生成器对象是一个可迭代的一个对象，可以用list函数把它转换成列表。

7. 示例

   import xlrd #导入模块
   data= xlrd.open_workbook("xlrd实例文件.xlsx")   #打开excel文件
   work_sheet = data.sheet_by_name('绩效成绩')  #获取工作表
   
   all_rows = work_sheet.nrows  #获取工作表中的有效行数
   print(all_rows)
    
   row_object = work_sheet.row_slice(1)  #获取第2行所有单元格对象组成的列表
   print(row_object)
    
   row_content = work_sheet.row_values(1) #获取第2行所有单元格内容组成的列表
   print(row_content)
    
   row_content = work_sheet.row_values(1, start_colx=1, end_colx=2) #获取第2行里第2列单元格内容组成的列表
   print(row_content)
    
   row_type = work_sheet.row_types(1)  #获取第2行单元格数据类型组成的列表
   print(row_type)
    
   row_length = work_sheet.row_len(1)  #获取第2行有效单元格的长度
   print(row_length)
    
   rows_generator = work_sheet.get_rows() #获取工作表所有行的生成器对象
   print(rows_generator)
    
   list_generator = list(rows_generator)  #把生成器转换成列表
   print(list_generator)
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27

   运行结果：

   11
   [text:'小红', number:2021001.0, number:98.0]
   ['小红', 2021001.0, 98.0]
   [2021001.0]
   array('B', [1, 2, 2])
   3
   <generator object Sheet.get_rows.<locals>.<genexpr> at 0x00000242CFE988B8>
   [[text:'姓名', text:'工号', text:'成绩'], [text:'小红', number:2021001.0, number:98.0], [text:'小橙', 	number:2021002.0, number:100.0], [text:'小黄', number:2021003.0, number:90.0], [text:'小绿', 	number:2021004.0, number:72.0], [text:'小青', number:2021005.0, number:88.0], [text:'小蓝', number:2021006.0, 	number:60.0], [text:'小紫', number:2021007.0, number:86.0], [text:'小黑', number:2021008.0, number:60.0], [	text:'小白', number:2021009.0, number:76.0], [text:'小彩', number:2021010.0, number:100.0]]
   1
   2
   3
   4
   5
   6
   7
   8

sheet中的列操作

列(column)的操作和行的操作是类似的,只是列的操作由row变成了col,我们直接来看例子:

import xlrd #导入模块
data= xlrd.open_workbook("xlrd实例文件.xlsx")   #打开excel文件
work_sheet = data.sheet_by_name('绩效成绩')  #获取工作表
 
all_cols = work_sheet.ncols  #获取工作表中的有效列数
print(all_cols)
 
col_object = work_sheet.col_slice(1)  #获取第2列所有单元格对象组成的列表
print(col_object)
 
col_content = work_sheet.col_values(1) #获取第2列所有单元格内容组成的列表
print(col_content)
 
col_content = work_sheet.col_values(1, start_rowx=1, end_rowx=2) #获取第2列里第2行单元格内容组成的列表
print(col_content)
 
col_type = work_sheet.col_types(1)  #获取第2列单元格数据类型组成的列表
print(col_type)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

运行结果：

3
[text:'工号', number:2021001.0, number:2021002.0, number:2021003.0, number:2021004.0, number:2021005.0, number:2021006.0, number:2021007.0, number:2021008.0, number:2021009.0, number:2021010.0]
['工号', 2021001.0, 2021002.0, 2021003.0, 2021004.0, 2021005.0, 2021006.0, 2021007.0, 2021008.0, 2021009.0, 2021010.0]
[2021001.0]
[1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2]
1
2
3
4
5

sheet中的单元格（cell）操作

1. 获取指定单元格内容

   sheet.cell(rowx,colx)   #返回单元格对象
   
   sheet.cell(rowx,colx).value   #返回单元格中的数据类型
   sheet.cell(rowx,colx).ctype   #返回单元格中的数据
   
   sheet.cell_type(rowx,colx)    #返回单元格中的数据类型
   sheet.cell_value(rowx,colx)   #返回单元格中的数据
   1
   2
   3
   4
   5
   6
   7

   cell_value = work_sheet.row(rowx)[colx].value
   1

   其中rowx为指定行，colx为指定列，(rowx, colx)为rowx+1行和colx+1列构成的单元格。

   比如(1,1)是第2行第2列这个位置的单元格(B2单元格)，即下图红框所示位置。

   

2. 获取指定单元格对象
   返回的是一个列表,列表里是键值对:

   cell_object = work_sheet.cell(rowx, colx)
   1

3. 获取指定单元格数据类型
   数据类型同上:

   0 - empty, 1 - text, 2 - number, 3 - date, 4 - boolean, 5 - error

   cell_type = work_sheet.cell_type(rowx, colx)
   1

4. 获取指定单元格内容的类型
   注意这是单元格内容的类型，比如内容是数值，则是判断这个数值的类型:

   cell_content_type = type(work_sheet.cell_value(rowx, colx))
   1

5. 示例
   我们以B2这个单元格为例来综合看看:

   import xlrd #导入模块
   data= xlrd.open_workbook("xlrd实例文件.xlsx")   #打开excel文件
   work_sheet = data.sheet_by_name('绩效成绩')  #获取工作表
    
   cell_value = work_sheet.cell_value(1, 1) #获取B2单元格内容
   print(cell_value)
    
   cell_object = work_sheet.cell(1, 1)  #获取B2单元格对象
   print(cell_object)
    
   cell_type = work_sheet.cell_type(1, 1)  #获取B2单元格数据类型 
   print(cell_type)
    
   cell_content_type = type(work_sheet.cell_value(1, 1))  #获取B2单元格内容的类型
   print(cell_content_type)
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15

   运行结果：

   2021001.0
   number:2021001.0
   2
   <class 'float'>
   1
   2
   3
   4

4. 示例

1. 文件

用我们之前创建的：xlrd实例文件.xlsx，文件内容:

2. 目标

我们如果认真看了文件的话，会发现每个员工的绩效工资是其对应的绩效成绩的10倍。100分的绩效成绩就是1000的绩效工资。

假如我们现在只有绩效成绩这一个表，要如何得出绩效工资表的内容？可以自己先想想你要如何做哦~

想法：

读取绩效成绩工作表除表头以外的数据

把绩效成绩*10得到绩效工资

把绩效工资和对应的员工信息组成一组新的数据

写入表头后继续把新得到的数据写入，得到一个新的工作表，然后保存。

3. 代码实现

import xlrd #导入模块
data= xlrd.open_workbook("xlrd实例文件.xlsx")   #打开excel文件
work_sheet = data.sheet_by_name('绩效成绩')  #获取绩效成绩工作表
 
# 获取除表头外全部行内容
rows_generator = list(work_sheet.get_rows())[1:]
 
#创建个列表来储存名字、工号、绩效工资数据
row_list=[]
 
#利用for循环，得到名字、工号、绩效成绩
for rows in rows_generator:
    name, number, score = rows[0].value, rows[1].value, rows[2].value
    
    #利用绩效成绩得出绩效工资
    money = score*10 
    
    #把名字、工号和新得的绩效工资一起组成一个元组
    row_tuple=(name,number,money) 
 
    #把元组添加到之前建的列表里
    row_list.append(row_tuple) 
 
print(row_list) #打印列表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

运行结果：

[('小红', 2021001.0, 980.0), ('小橙', 2021002.0, 1000.0