接口中导入excel测试数据（详）

workBook = xlrd.open_workbook(filePath)#路径

第一种方法：
workSheet = workBook.get_sheet(sheet_index)#第几个工作表（索引）
第二种方法：
workSheet = workBook.sheet_by_name(‘工作表名‘)
第三种方法：
workSheet = workBook.sheet_by_index(sheet_index)
第四种方法：
workSheet=workBook.sheets()[索引]

nrows = workSheet.nrows

row=workSheet.row_values(行号)

retList=[]
for i in range(1,nrows): #range左包含右不包含 读45条记录
#返回的是一个list,得到的是第几行数据
OneRow= workSheet.row_values(i)
retList.append(oneRow)
return retList

List=readExcel(‘测试用例路径‘,0)
print（list）

import json
from API项目.lib.courseLIb import add,delete,modify
from API项目.lib.excelManger import readExcel
import time
import sleep
def SendCourseRequest(row):
# print(row) # 传入的是一行数据，也就是一个测试用例
colus5 = row[4] # 第5列的值
# # 第6列的值，用json.loads方法，把字符串转化为字典格式
colus6 = json.loads(row[5])
# ret = None
if colus5 == ‘add‘:
# print(colus6[‘name‘])# 课程名称
courseName = colus6[‘name‘] # 获取课程名称
# 把关键字：{{courseName}} 替换成时间戳
courseName = courseName.replace(‘{{courseName}}‘, str(int(time.time() * 100)))
ret = add(courseName, colus6[‘desc‘], colus6[‘display_idx‘])
# print(ret)
test = json.loads(row[6])
if (ret[‘retcode‘]==test[‘code‘]):
print(row[0],‘测试通过‘)
# elif colus5 == ‘list‘:
# ret = list(colus6[‘pagenum‘], colus6[‘pagesize‘])
# elif colus5 == ‘delete‘:
# ret = delete(colus6[‘id‘])
# return ret

list=readExcel(r‘E:\bijizongjie\API项目\data\教管系统-测试用例V1.2.xls‘,0)
# print(list)
for i in range(0,len(list)):
SendCourseRequest(list[i])
time.sleep(1)

from API项目.lib.excelManger import readExcel
from API项目.lib.sendCourseRequest import SendCourseRequest
import time
#1-读取测试用例
list=readExcel(r‘E:\bijizongjie\API项目\data\教管系统-测试用例V1.2.xls‘,0)
# print(list)
#2-执行测试用例
dictBody=[]
for i in range(0,len(list)):
row=list[i]
dictBody=SendCourseRequest(row)
dictBody.append(dictBody)
time.sleep(1)

def getNewExcel(filePath):
#1-1打开Excel，得到workBook对象
workBook = xlrd.open_workbook(filePath)
#1-2 复制一个全新的工作簿
workBookNew = copy(workBook)
return workBookNew
调用
workBookNew = getNewExcel（‘路径’）

workBook = xlrd.open_workbook(filePath,formattings_info = True)

workSheetNew= workBookNew.sheet_by_index(0)

for i in range(0,len(list)):
row=list[i]
dictBody=SendCourseRequest(row)
time.sleep(1)
test = json.loads(row[6])
if (dictBody[‘retcode‘] == test[‘code‘]):
print(row[0], ‘测试通过‘)
workSheetNew.write(i+1,7,‘PASS‘)
else:
print(‘row[0]‘,‘测试不通过‘)
workSheetNew.write(i + 1,7, ‘FAIL‘)
workSheetNew.write(i + 1,8, dictBody[‘reason‘])

workBookNew.save(‘路径‘)