1、处理一个excel基本方法
单独写一个handle_excel.py
from xlutils.copy import copy
import xlrd
class HandleExcel:
def __init__(self,file='D:\\Users\\wqa\\Desktop\\demo.xls',sheet_id=0):
self.file = file
self.sheet_id = sheet_id
self.data = self.get_data()
def get_data(self):
data = xlrd.open_workbook(self.file)
sheet = data.sheet_by_index(self.sheet_id)
return sheet
def get_rows(self):
rows=self.data.nrows
return rows
def get_value(self, row, col):
value = self.data.cell_value(row, col)
return value
def write_value(self, row, col, value):
data = xlrd.open_workbook(self.file)
data_copy = copy(data)
sheet = data_copy.get_sheet(0) # 取得复制文件的sheet对象
sheet.write(row, col, value) # 在某一单元格写入value
data_copy.save(self.file)
# 封装excel的列名常量
def get_no():
no = 0
return no
def get_apitype():
apitype = 1
return apitype
def get_url():
url = 2
return url
def get_method():
"""获取method"""
method = 3
return method
def get_header():
"""获取header"""
header = 4
return header
def get_purpose():
purpose = 5
return purpose
def get_params():
"""获取params"""
params = 6
return params
def get_expectvalue():
"""获取expectValue"""
expectvalue = 7
return expectvalue
def get_resultValue():
"""获取resultValue"""
resultValue = 8
return resultValue
2、 跑excel中用例
建一个run_handle_excel.py
from customerivr.run_method import RunMain
from customerivr.handle_excel import *
import jsonpath
import json
class RunTestCase:
def __init__(self):
self.Runmain = RunMain()
self.data = HandleExcel()
def go_run(self):
rows_count = self.data.get_rows()
for i in range(1, 2):
url = self.data.get_value(i, get_url())
method = self.data.get_value(i, get_method())
data = self.data.get_value(i, get_params())
expect = self.data.get_value(i, get_expectvalue())
res = self.Runmain.run_main(url, method, data) # 调用get/post主函数
txt = jsonpath.jsonpath(res, '$..text')
stxt = " ".join(txt)
print(stxt)
self.data.write_value(i, get_resultValue(), txt)
if expect in stxt:
print('测试通过')
self.data.write_value(i, get_resultValue(), txt)
else:
print('测试失败')
self.data.write_value(i, get_resultValue(), "测试不通过")
if __name__ == '__main__':
run = RunTestCase()
run.go_run()
# run = RunMain()
# print(run.run_main(url, 'POST', data))