怎么在Python中利用接口自动化对excel文件进行读写

本篇文章为大家展示了怎么在Python中利用接口自动化对excel文件进行读写，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

一、xlrd、xlwt以及xlutils安装

1.使用pip安装

pip install xlrd
pip install xlwt
pip install xlutils

2.在PyCharm中安装

直接检索需要安装的模块名称即可，如xlrd:

二、xlrd操作excel文件的数据读取

新建一个excel文件，文件名称：excel_test.xlsx，文件编辑有两个sheet表，内容如下：

sheet1：

sheet2：

（1）打开excel文件，获取excel的sheet名

编辑如下代码：

import xlrd
file = xlrd.open_workbook("excel_test.xlsx")
all_sheet = file.sheet_names() # 获取所有的工作簿名
sheet_name1 = file.sheet_names()[0] # 通过sheet下标获取，第一个sheet下标为0
sheet_name2 = file.sheet_by_index(0).name # 通过sheet索引获取sheet名
print(all_sheet)
print(sheet_name1)
print(sheet_name2)
-----------------------------------------
# 返回结果
['员工信息表', 'api测试用例']
员工信息表
员工信息表

（2）获取sheet工作表页的对象

代码示例：

import xlrd
file = xlrd.open_workbook("excel_test.xlsx")
sheet_name1 = file.sheet_names()[0]
sheet1_obj = file.sheet_by_name(sheet_name1) # 通过sheet名获取sheet对象
sheet2_obj = file.sheet_by_index(1) # 通过sheet索引获取sheet对象
print(sheet1_obj)
print(sheet2_obj)
------------------------------------
# 返回结果
<xlrd.sheet.Sheet object at 0x0000000002AA09B0>
<xlrd.sheet.Sheet object at 0x0000000002AA0978>

（3）获取sheet工作表的行、列数，整行、整列数据，具体的单元格数据

代码示例：

import xlrd
file = xlrd.open_workbook("excel_test.xlsx")
sheet = file.sheet_by_index(0) # 通过sheet索引获取sheet对象
nrows = sheet.nrows # 获取行数
ncols = sheet.ncols # 获取列数
nrows_data = sheet.row_values(1) # 获取第二行数据，返回的是列表
ncols_data = sheet.col_values(0) # 获取第一列数据，返回的是列表
cell = sheet.cell(1,2)  # 获取单元格数据,如第二行，第三列数据
print(nrows)
print(ncols)
print(nrows_data)
print(ncols_data)
print(cell)
-------------------------------
# 返回结果
6
5
['王五', '男', 32883.0, 'java开发工程师', 233.0]
['姓名', '王五', '李四', '张三', '小红', '小明']
xldate:32883.0 # 这里日期数据直接返回成浮点数了

　常见读取excel不同数据类型的返回问题，如读取日期格式的数据

一般使用sheet.cell(rowx,colx)方法获取单元格数据，单元格数据类型判断可以使用如下代码：

print(sheet.cell(1,2).ctype)
------------
# 返回日期数据的结果
3

注：ctype : 0 empty,1 string, 2 number,3 date, 4 boolean, 5 error

读取单元格日期数据为浮点数的处理方式：

代码如下：

import xlrd
from datetime import date
file = xlrd.open_workbook("excel_test.xlsx")
sheet = file.sheet_by_index(0) # 通过sheet索引获取sheet对象
nrows_data = sheet.row_values(1) # 获取第二行数据，返回的是列表
ncols_data = sheet.col_values(0) # 获取第一列数据，返回的是列表
cell = sheet.cell(1,2)  # 获取单元格数据,如第二行，第三列数据，返回的是浮点数
data_value = xlrd.xldate_as_tuple(sheet.cell_value(1,2) ,file.datemode) # xldate_as_tuple()方法得到日期数据年月日时分秒的值并返回为元组
datatime2 = date(*data_value[:3]).strftime('%Y/%m/%d') # 截取元组中的前三位，即年月日的值传给data，并进行时间格式化
 
print(cell)
print(data_value)
print(datatime2)
 
-----------------------
# 返回结果
xldate:32883.0
(1990, 1, 10, 0, 0, 0)
1990/01/10

因此在读取excel单元格数据，如遇到是日期格式的数据，可以加上如下代码判断并处理：

if (sheet.cell(row,col).ctype == 3):
 date_value = xlrd.xldate_as_tuple(sheet.cell_value(row,col),file.datemode)
 date_tmp = date(*date_value[:3]).strftime('%Y/%m/%d')

三、xlwt向excel文件写入数据

xlwt一般用于向excel文件写入数据，简单示例如下：

import xlwt
 
workbook = xlwt.Workbook(encoding = 'utf-8') # 创建工作簿
sheet = workbook.add_sheet('api_test') # 添加一个sheet
data = sheet.write(0,0,'test')  # 向第一行第一列写入数据：test
workbook.save('book.xlsx')   # 保存到book.xlsx中

　运行完成后会在该py文件的同级目录下生成一个book.xlsx的excel文件，并新增了api_test的sheet表名，第一行第一列写入数据：test

　向excel写入数据时，可以设置单元格长宽、单元格合并、写入时间格式数据以及添加超链接等

代码示例：

import xlwt
import datetime
workbook = xlwt.Workbook(encoding = 'utf-8') # 创建工作簿
sheet = workbook.add_sheet('api_test') # 添加一个sheet
data = sheet.write(4,0,'test')
 
sheet.col(0).width = 5000   # 设置单元格宽度
 
style = xlwt.XFStyle()    # 初始化表格样式
style.num_format_str = 'M/D/YY'  # 设置时间格式，如：M/D/YY
sheet.write(5, 1, datetime.datetime.now(), style) # 写入时间格式数据
 
# 合并多列和和并多行
# 表示合并的行数是：按行索引，从0行到第0行，按列索引从0列到第3列，合并后并写入数据:test1
sheet.write_merge(0, 0, 0, 3, 'test1')
# 表示合并的行数是：按行索引，从1行到第2行，按列索引从0列到第3列，合并后并写入数据:test2
sheet.write_merge(1, 2, 0, 3, 'test2')
 
# 向单元格添加超链接
sheet.write(6, 0, xlwt.Formula('HYPERLINK("https://www.baidu.com/";"baidu")'))
 
workbook.save('book.xlsx')   # 保存到book.xlsx中

　运行后输出excel效果如下：

四、xlutils操作excel文件

（1）拷贝excel表格

xlutils模块下的copy可以复制拷贝excel文件，代码示例：

import xlrd
from xlutils.copy import copy
excel_file = xlrd.open_workbook("book.xlsx")
new_file = copy(excel_file) # 拷贝文件对象
new_file.save("book1.xlsx") # 保存为excel文件

　运行以上代码会在同级目录下生成一个book1.xlsx的excel文件，该文件和book.xlsx一样，只是文件名称不一样而已，也就是复制excel文件了

（2）修改excel文件内容

除了copy为其他excel文件外，也可以直接copy文件修改后，保存为同名的文件，那修改的内容将直接覆盖原excel文件，达到修改的目的

示例：

import xlrd
from xlutils.copy import copy
excel_file = xlrd.open_workbook("book.xlsx")
new_file = copy(excel_file)
sheet = new_file.get_sheet(0) # 获取表格的第一个sheet
sheet.write(0,1,"测试") # 第一行第二列写入：测试
sheet.write(1,1,"测试1") # 第二行第二列写入：测试1
new_file.save("book.xlsx")

　运行后book.xlsx表会修改更新

五、封装操作excel读和写的类

通过上面介绍，基本具备使用xlrd、xlwt、xlutils模块进行excel文件数据读取和向excel文件中写入数据，在进行接口测试时，我们说到需要：测试数据和测试脚本分离，后续的接口测试用例数据，我们统一写入excel表格中，然后通过操作excel来读取测试数据并将测试结果回填到excel中。因此，咱们需要对向excel读取数据和向excel写入数据的操作进行封装。

我们操作上面的api测试用例这个sheet，封装读取excel数据的类，代码示例：

from xlrd import open_workbook
class Readexcel():
 
 def excel_data_list(self, filename, sheetname):
 '''
 :param filename: excel文件名称
 :param sheetname: excel中表格sheet名称
 :return: data_list
 '''
 data_list = []
 wb = open_workbook(filename) # 打开excel
 sh = wb.sheet_by_name(sheetname) # 定位工作表
 header = sh.row_values(0) # 获取标题行的数据
 for i in range(1, sh.nrows): # 跳过标题行，从第二行开始获取数据
  col_datas = dict(zip(header, sh.row_values(i))) # 将每一行的数据，组装成字典
  data_list.append(col_datas) # 将字典添加到列表中 ，列表嵌套字典，每个元素就是一个字典
 return data_list
 
if __name__ == '__main__':
 Readexcel()

　上面代码封装了读取excel数据的类，将每一行数据读取出来组装成字典并添加到列表中

实例化运行：

 data_list = Readexcel().excel_data_list('excel_test.xlsx','api测试用例')
 print(data_list)
-----------------------
# 返回结果
[
 {'module': '视频安防', 'url': 'http://imp-t.tfgreenroad.com:18092/console_api/recep/tv/list', 'id': 1.0, 'params': '{ "queryMsg":"","regionCode":"","devtypeCode":"","online":"","offset":"","limit":1,"type":""}', 'method': 'get', 'actual_res': '', 'data': '', 'expect_res': '', 'test_res': '', 'case_name': '分页查询视频安防设备列表', 'files': ''},
 {'module': '平台管理', 'url': 'http://imp-t.tfgreenroad.com:18092/console_api/manage/appsys/info', 'id': 2.0, 'params': '', 'method': 'post', 'actual_res': '', 'data': '{"appName": "hahh","appId": "34434343","appUrl": "http://12306.com","appCode": "89","remark":""}', 'expect_res': '{"code": 200,"errMsg": ""}', 'test_res': '', 'case_name': '应用管理-单个应用系统添加', 'files': ''},
 {'module': '平台管理', 'url': 'http://imp-t.tfgreenroad.com:18092/console_api/manage/appsys/info/upappid/89', 'id': 3.0, 'params': '', 'method': 'put', 'actual_res': '', 'data': '{"appId": "3232327676888"}', 'expect_res': '{"code": 200,"errMsg": ""}', 'test_res': '', 'case_name': '应用管理-修改应用appId', 'files': ''},
 {'module': '平台管理', 'url': 'http://imp-t.tfgreenroad.com:18092/console_api/manage/devtype/list', 'id': 4.0, 'params': '{ "queryMsg":"15002","offset":"","limit":""}', 'method': 'get', 'actual_res': '', 'data': '', 'expect_res': '', 'test_res': '', 'case_name': '设备分类-分页获取设备类型', 'files': ''}
]

　封装向excel写入数据的类，代码示例：

from xlutils.copy import copy
from xlrd import open_workbook
 
class Write_excel():
 def write_result(self, filename, row, col1,col2,actual_res, test_res,sheet_name):
 '''
 :param filename: 文件名
 :param row: 要写回的行
 :param col1: 要写回的列
 :param col2: 要写回的列
 :param actual_res: 实际结果
 :param test_res: 测试结果 ：pass/failed
 :param sheet_name:指定的sheet表索引
 :return:
 '''
 old_workbook = open_workbook(filename)
 # 将已存在的excel拷贝进新的excel
 new_workbook = copy(old_workbook)
 # 获取sheet
 new_worksheet = new_workbook.get_sheet(sheet_name) # 第n个sheet,0表示第一个sheet
 # 写入数据
 new_worksheet.write(row, col1, actual_res)
 new_worksheet.write(row, col2, test_res)
 # 保存
 new_workbook.save("book.xlsx")
 
if __name__ == '__main__':
 Write_excel()

上述内容就是怎么在Python中利用接口自动化对excel文件进行读写，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。