#-*- coding: utf8 -*-
import xlrd
import xlwt
from Public import GlobalClass
def openexcel(path):
try:
data = xlrd.open_workbook(path)
return data
except Exception as e:
print (e)
#将测试结果写入一个Excel,传:保存路径,sheet名称,读取Excel时保存的Keys,要写入Excel的数据,行数,列数
def writeexcel(path,sheetname,keys,values,nrows,ncols):
Path = path
wb=xlwt.Workbook()
sheet=wb.add_sheet(sheetname)
Keys = keys
value = values
Len = len(value)
rows = nrows
cols = ncols
for k in range(0,cols):
sheet.write(0,k,Keys[k])
for i in range(0,Len): #由于第1行已经写入了表头,所以要从第2行开始写数据
for j in range(1,rows):
data = value[i]
for l in range(0,cols):
sheet.write(j,l,str(data[Keys[l]]))
wb.save(Path)
#将测试结果写入一个Excel(传入数组),传:保存路径,sheet名称,读取Excel时保存的Keys,执行统计时保存的SKeys,要写入Excel的数据,行数,列数
def writeexcellist(path,sheetnamelist,keys,skeys,valueslist,nrowslist,ncolslist):
Path = path
wb=xlwt.Workbook()
# print(sheetnamelist)
for s in range(len(sheetnamelist)):
sheet=wb.add_sheet(sheetnamelist[s])
Keys = keys
Skeys = skeys
value = valueslist[s]
rows = nrowslist[s]
cols = ncolslist[s]
if sheetnamelist[s] == "Result":
for k in range(0,cols):
sheet.write(0,k,Skeys[k])
for r in range(1,rows): #由于第1行已经写入了表头,所以要从第2行开始写数据
for c in range(0,cols):
sheet.write(r,c,str(value[r-1][Skeys[c]]))
else:
for k in range(0,cols):
sheet.write(0,k,Keys[k])
for r in range(1,rows): #由于第1行已经写入了表头,所以要从第2行开始写数据
for c in range(0,cols):
sheet.write(r,c,str(value[r-1][Keys[c]]))
wb.save(Path)
#获取sheet总行和总列
def GetRowAndCol(path,sheetname):
try:
data = openexcel(path)
table = data.sheet_by_name(sheetname)
nrows = table.nrows #行数
ncols = table.ncols #列数
list =[nrows,ncols]
except:
list = False
# list = (nrows,ncols)
# print(list)
return list
#根据用例状态获取sheet总行和总列
def GetRowByState(path,sheetname):
data = openexcel(path)
# print(sheetname)
table = data.sheet_by_name(sheetname)
nrows = table.nrows #行数
ncols = table.ncols #列数
srows = 1 #行数
# scols = 1 #列数
# list =[]
for rownum in range(1,nrows):
row = table.row_values(rownum) #获取某行数据
# print(row)
if row[8] == "Fail":
srows +=1
# scols +=1
# list = [srows,scols]
return srows
#根据用例等级获取sheet总行和总列
def GetRowByLevel(path,sheetname):
data = openexcel(path)
table = data.sheet_by_name(sheetname)
nrows = table.nrows #行数
ncols = table.ncols #列数
srows = 1 #行数
# scols = 1 #列数
levellist = GlobalClass.Level
for rownum in range(1,nrows):
row = table.row_values(rownum) #获取某行数据
# print(row)
if float(row[9]) in levellist:
srows +=1
# scols +=1
# list = [srows,scols]
# print(list)
return srows
#根据名称获取Excel表格中的数据
def exceltablebyname(path,sheetname):
data = openexcel(path)
table = data.sheet_by_name(sheetname)
nrows = table.nrows #行数
ncols = table.ncols
colnames = table.row_values(0) #某一行数据,0表示列名所有行的所引
list =[]
#把字典的Key存起来,后续通过这个Key按顺序读取
if not GlobalClass.keys:
for k in range(0,ncols):
GlobalClass.keys.append(colnames[k])
# print(GlobalClass.keys)
for rownum in range(1,nrows):
row = table.row_values(rownum)
# print(row)
if row:
app = {}
for i in range(ncols):
app[colnames[i]] = row[i]
list.append(app)
#按Key读取的方法
# for rownum in range(0,nrows-1):
# for i in range(ncols):
# data = list[rownum]
# print(data[keys[i]])
# print(keys)
# print(list)
return list
#根据用例状态为Fail获取Excel表格中的数据
def exceltablebystate(path,sheetname):
data = openexcel(path)
table = data.sheet_by_name(sheetname)
nrows = table.nrows #行数
ncols = table.ncols
colnames = table.row_values(0) #某一行数据,0表示列名所有行的所引
list =[]
#把字典的Key存起来,后续通过这个Key按顺序读取
if not GlobalClass.keys:
for k in range(0,ncols):
GlobalClass.keys.append(colnames[k])
# print(GlobalClass.keys)
for rownum in range(1,nrows):
row = table.row_values(rownum) #获取某行数据
# print(row)
if row[8] == "Fail":
if row:
app = {}
for i in range(ncols):
app[colnames[i]] = row[i]
list.append(app)
#按Key读取的方法
# for rownum in range(0,nrows-1):
# for i in range(ncols):
# data = list[rownum]
# print(data[keys[i]])
# print(keys)
# print(list)
return list
#根据用例等级获取Excel表格中的数据
def exceltablebylevel(path,sheetname):
levellist = GlobalClass.Level
data = openexcel(path)
table = data.sheet_by_name(sheetname)
nrows = table.nrows #行数
ncols = table.ncols
colnames = table.row_values(0) #某一行数据,0表示列名所有行的所引
list =[]
#把字典的Key存起来,后续通过这个Key按顺序读取
if not GlobalClass.keys:
for k in range(0,ncols):
GlobalClass.keys.append(colnames[k])
# print(GlobalClass.keys)
for rownum in range(1,nrows):
row = table.row_values(rownum) #获取某行数据
# print(row)
if float(row[9]) in levellist:
if row:
app = {}
for i in range(ncols):
app[colnames[i]] = row[i]
list.append(app)
#按Key读取的方法
# for rownum in range(0,nrows-1):
# for i in range(ncols):
# data = list[rownum]
# print(data[keys[i]])
# print(keys)
# print(list)
return list