-----------------------------------------------------------
author:wangshinan
description:读取excel表格中某一sheet的全部数据
param:1、data_path excle表格路径,单独使用需传入绝对路径
2、sheetname excle表格内sheet名
remarks:输出格式:AllData = [[第一行数据],[第二行数据],[第三行数据]]
-----------------------------------------------------------
def read_excel(self,data_path, sheetname):
# 打开excel表格
data = xlrd.open_workbook(data_path)
# 切换到相应sheet
table = data.sheet_by_name(sheetname)
# 获取表格行数
rowNum = table.nrows
AllData=[]
if rowNum < 2:
print("excle内数据行数小于2")
else:
# 从第二行(数据行)开始取数据
for i in range(1, rowNum):
# 获取每一行的数据,返回一个列表
list = table.row_values(i)
# 将每行数据添加到一个列表中
AllData.append(list)
return AllData,rowNum
-----------------------------------------------------------
author:wangshinan
description:处理excel中读取出来的数据AllData
param:1、data_path:excle表格路径,单独使用需传入绝对路径
2、sheetname:excle表格内sheet名
3、Get_data_Mode:处理第四列参数的方式,API或GUI
remarks:输出格式:[[list1],[list1],[list1]]
1、list1 GUI格式:[‘参数值1’,‘参数值2’,‘某项目_登录模块’, ‘url’, ‘get/post’, ‘success’]
2、list1 API格式:[{‘键1’: ‘参数值1’, ‘键2’: ‘参数值2’}, ‘某某项目_登录模块’, ‘url’, ‘get/post’, ‘success’]
-----------------------------------------------------------
def Get_data(self, data_path, sheetname,Get_data_Mode):
list = []
(AllData,rowNum) = self.read_excel(data_path, sheetname)
for index in range(0, len(AllData)):
datas = []
if index <= len(AllData):
modularName = AllData[index][0]
url = AllData[index][1]
requestMode = AllData[index][2]
hope = AllData[index][4]
if Get_data_Mode == 'API':
test_data_last = self.Get_API_data(AllData[index][3])
datas.append( test_data_last)
else:
test_data_last = self.Get_GUI_data(AllData[index][3])
for i in test_data_last:
datas.append(i)
index += 1
datas.append(modularName)
datas.append(url)
datas.append(requestMode)
datas.append(hope)
list.append(datas)
return list
-----------------------------------------------------------
author:wangshinan
description:API_处理excel中参数数据
param:1、TestData:excle第四列数据
remarks:处理之前:‘username=admin\npassword=123456’
处理之后:{‘username’: ‘admin’, ‘password’: ‘123456’}
-----------------------------------------------------------
def Get_API_data(self,TestData):
list = TestData.split('\n')
test = {}
for i in list:
td = i.split(':')
test[td[0]] = td[1]
return test
-----------------------------------------------------------
author:wangshinan
description:GUI_处理excel中参数数据
param:1、TestData:excle第四列数据
remarks:处理之前:‘username=admin\npassword=123456’
处理之后:[‘admin’, ‘123456’]
-----------------------------------------------------------
def Get_GUI_data(self, TestData):
# 处理行数据中AllData[index][3]的参数数据
list = TestData.split('\n')
test = []
for i in list:
td = i.split('=')[1]
test.append(td)
return test