python读写excel文件

一、背景

python自动化测试中，有时候也需要使用到Excel文件，而此时要借用第三方库才能进行操作。在执行读execl文件时要用到xlrd库，要写入数据至excel文件时，要用到xlwt库，它们需要单独安装，安装方式也非常简单。当然也可以用其他的第三方库，比如openpyxl和xlutils库。最后，选择什么模块要根据实际情况而定。

二、各模块之间的区别

xlrd，xlwt和xlutils是用Python处理Excel文档(*.xls)的高效率工具。其中，xlrd只能读取xls，xlwt只能新建xls（不可以修改），xlutils能将xlrd.Book转为xlwt.Workbook，从而得以在现有xls的基础上修改数据，并创建一个新的xls，实现修改。openpyxl（可读写excel表）专门处理Excel2007及以上版本产生的xlsx文件，xls和xlsx之间转换容易。另外，还有以下特点。

• 读取文件速度，xlrd比openpyxl较快
• xlwt写入文件效率较高
• xlwt写入数据会有局限性，不能写入超过65535行、256列的数据（因为它只支持Excel 2003及之前的版本，在这些版本的Excel中行数和列数有此限制）
• xlrd，xlwt和xlutils是用Python处理Excel文档(*.xls)的高效率工具。其中，xlrd只能读取xls，xlwt只能新建xls（不可以修改），xlutils能将xlrd.Book转为xlwt.Workbook，从而得以在现有xls的基础上修改数据
• xlwt不支持07版以后的excel并创建一个新的xlsx（可以创建xls格式的excel文件），openpyxl支持07+的excel，一直有人在维护，文档清晰易读。

三、安装第三方库

这里选择xlrd，xlwt模块进行简单介绍。

安装xlrd库：

pip install xlrd

安装xlwt库：

pip install xlwt

也可以直接在pycharm编辑器中安装，方法也非常的简单：先打开settings，然后找到project interpreter，最后再添加想要安装的模块即可。

四、读取excel文件数据

这里准备了一个招聘人员信息表，名字叫zhaopin.xlsx的工作簿，其中有两个表，名字叫招聘人员和数据分析。如图：

下面我们用python第三方库xlrd来读取工作簿中的信息。

1、 sheet_names：获取所有的sheet的名字。

import xlrd

workbook = xlrd.open_workbook('zhaopin.xlsx') # 打开名字叫'zhaopin.xlsx'的工作簿
sheet_names = workbook.sheet_names() # 获得工作簿中的所有表明
print(sheet_names)

结果：

['招聘人员', '数据分析']

2、 sheet_by_index：根据索引获取sheet对象。

import xlrd

workbook = xlrd.open_workbook('zhaopin.xlsx') # 打开名字叫'zhaopin.xlsx'的工作簿
sheet0 = workbook.sheet_by_index(0) # 获取索引为0的表对象，0对应的是excel工作簿中的第一张表
sheet1 = workbook.sheet_by_index(1) # 获取索引为1的表对象，1对应的是excel工作簿中的第二张表
print(sheet0.name)
print(sheet1.name)

结果：

招聘人员
数据分析

3、 sheet_by_name：根据名字获取sheet对象。

import xlrd

workbook = xlrd.open_workbook('zhaopin.xlsx') # 打开名字叫'zhaopin.xlsx'的工作簿
sheet = workbook.sheet_by_name('数据分析')
print(sheet.name)

结果：

数据分析

注意：sheet_by_name('数据分析')括号中的表名称一定要填写正确，否则无法找到对象，会报错。

4、sheets：获取所有的sheet对象。

import xlrd

workbook = xlrd.open_workbook('zhaopin.xlsx') # 打开名字叫'zhaopin.xlsx'的工作簿
sheets = workbook.sheets() # 获取所有的`sheet`对象
for sheet in sheets:
    print(sheet.name)

结果：

招聘人员
数据分析

5、sheet.nrows：这个sheet中的行数。

6、sheet.ncols：这个sheet中的列数。

import xlrd

workbook = xlrd.open_workbook('zhaopin.xlsx') # 打开名字叫'zhaopin.xlsx'的工作簿
sheet = workbook.sheet_by_index(0) # 获取索引为0的表对象，0对应的是excel工作簿中的第一张表
sheet_nrows = sheet.nrows # 这个`sheet`中的行数
sheet_ncols = sheet.ncols # 这个`sheet`中的列数
print('行数为：{}'.format(sheet_nrows))
print('列数为：{}'.format(sheet_ncols))

结果：

行数为：78
列数为：7

7、sheet.cell(row,col)：获取指定行和列的cell对象。

import xlrd

workbook = xlrd.open_workbook('zhaopin.xlsx') # 打开名字叫'zhaopin.xlsx'的工作簿
sheet = workbook.sheet_by_name('招聘人员')
cells = sheet.cell(0,1) # 获取指定0行和1列的cell对象。
print(cells.value) # 打印这个cell里面的值。

结果：

姓名

8、sheet.row_slice(row,start_col,end_col)：获取指定行的某几列的cell对象。

import xlrd

workbook = xlrd.open_workbook('zhaopin.xlsx') # 打开名字叫'zhaopin.xlsx'的工作簿
sheet = workbook.sheet_by_name('招聘人员')
cells = sheet.row_slice(0,0,6) # 获取指定0行的0列到5列的cell对象。
print(cells)

结果：

[text:'序号', text:'姓名', text:'性别', text:'专业', text:'学历', text:'毕业院校']

9、sheet.col_slice(col,start_row,end_row)：获取指定列的某几行的cell对象。

import xlrd

workbook = xlrd.open_workbook('zhaopin.xlsx') # 打开名字叫'zhaopin.xlsx'的工作簿
sheet = workbook.sheet_by_index(0) # 获取索引为0的表对象，0对应的是excel工作簿中的第一张表
cells = sheet.col_slice(1,0,5)#获取1列0行到4行的cell对象，返回一个列表
for cell in cells:
    print(cell.value) # 打印这个cell里面的值。

结果：

姓名
冯涛
李佳楠
罗旭
张自航

10、sheet.cell_value(row,col)：获取指定行和列的值。

import xlrd

workbook = xlrd.open_workbook('zhaopin.xlsx') # 打开名字叫'zhaopin.xlsx'的工作簿
sheet = workbook.sheet_by_index(0)
cells = sheet.cell_value(0,5)
print(cells)

结果：

毕业院校

11、sheet.row_values(row,start_col,end_col)：获取指定行的某几列的值。

import xlrd

workbook = xlrd.open_workbook('zhaopin.xlsx') # 打开名字叫'zhaopin.xlsx'的工作簿
sheet = workbook.sheet_by_index(0)
cells = sheet.row_values(0,0,6) # 获取0行从0列到5列的值。
print(cells)

结果：

['序号', '姓名', '性别', '专业', '学历', '毕业院校']

注意：它与sheet.row_slice(row,start_col,end_col)的区别，一个是获取对象，一个是获取值。

12、sheet.col_values(col,start_row,end_row)：获取指定列的某几行的值。

import xlrd

workbook = xlrd.open_workbook('zhaopin.xlsx') # 打开名字叫'zhaopin.xlsx'的工作簿
sheet = workbook.sheet_by_index(0)
cells = sheet.col_values(1,0,5)
print(cells)

结果：

['姓名', '冯涛', '李佳楠', '罗旭', '张自航']

注意：它与sheet.col_slice(col,start_row,end_row)的区别，一个是获取对象，一个是获取值。

五、写入数据至excel文件

1、cell的数据类型

• xlrd.XL_CELL_TEXT（Text）：文本类型。
• xlrd.XL_CELL_NUMBER（Number）：数值类型。
• xlrd.XL_CELL_DATE（Date）：日期时间类型。
• xlrd.XL_CELL_BOOLEAN（Bool）：布尔类型。
• xlrd.XL_CELL_BLANK：空白数据类型。

2、写入数据至excel文件步骤

• 首先，导入xlwt模块。
• 然后，创建一个Workbook对象。
• 然后，创建一个Sheet对象。
• 接着，使用sheet.write(row,col,data)方法把数据写入到Sheet下指定行和列中。如果想要在原来workbook对象上添加新的cell，那么需要调用put_cell来添加。
• 最后，保存成Excel文件。

3、实例

比如，我想在原来的‘zhaopin.xlsx’表中添加"总分"一列，并在其中随机填充面试得分。需要注意，如果想要在原来已经存在的Excel文件中添加新的行或者新的列，那么需要采put_cell(row,col,type,value,xf_index)来添加进去，最后再放到xlwt创建的workbook中，然后再保存进去。示例代码如下：

import xlwt
import xlrd
import random

workbook = xlrd.open_workbook('zhaopin.xlsx')  # 打开名字叫'zhaopin.xlsx'的工作簿
rsheet = workbook.sheet_by_index(0)
rsheet.put_cell(0, 6, xlrd.XL_CELL_TEXT, '总分', None)  # 在0行6列处添加“总分”列，列属性为文本

for row in range(1, rsheet.nrows):
    rsheet.put_cell(
        row,
        6,
        xlrd.XL_CELL_TEXT,
        random.randint(
            70,
            99),
        None)  # 随机向每行6列添加70~99之间的数

write_workbook = xlwt.Workbook(encoding='utf8')  # 重新创建一个工作簿
wsheet = write_workbook.add_sheet('招聘人员')  # 工作簿中创建一个名字为“招聘人员”的表
for row in range(rsheet.nrows):
    for col in range(rsheet.ncols):
        wsheet.write(row, col, rsheet.cell_value(row, col))  # 将原表中的数据写入新创建的表中

write_workbook.save('zhaopin_new.xls')  # 保存

结果：