Pytest集成excel

import os
l import NewExcel
l import OldExceldef get_reader(srcfile=''):reader = Noneif not os.path.isfile(srcfile):print("%s not exist!" % (srcfile))return dswith('.xls'):reader = OldExcel.Reader()reader.open_excel(srcfile)return dswith('.xlsx'):reader = NewExcel.Reader()reader.open_excel(srcfile)return readerdef get_writer(srcfile, dstfile):writer = Noneif not os.path.isfile(srcfile):print("%s not exist!" % (srcfile))return dswith('.xls'):writer = OldExcel.Writer()py_open(srcfile, dstfile)return dswith('.xlsx'):writer = NewExcel.Writer()py_open(srcfile, dstfile)return writer

NewExcle.py文件内容：

import os, openpyxl
from shutil import copyfile
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, Protectionclass Reader:def __init__(self):self.workbook = Noneself.sheet = ws = 0self.r = 0# 打开exceldef open_excel(self, srcfile):if not os.path.isfile(srcfile):print("%s not exist!" % (srcfile))ding = "utf8"self.workbook = openpyxl.load_workbook(filename=srcfile)self.sheet = self.workbook[self.workbook.sheetnames[0]]ws = self.sheet.max_rowself.r = 0returndef get_sheets(self):sheets = self.workbook.sheetnamesreturn sheetsdef set_sheet(self, name):self.sheet = self.workbook[ws = self.sheet.max_rowself.r = 0returndef readline(self):lines = []for row in ws:line = []for cell in row:if cell.value is None:line.append('')else:line.append(cell.value)lines.append(line)return linesclass Writer:def __init__(self):self.workbook = Noneself.wb = Noneself.sheet = Noneself.df = w = 0self.clo = 0def copy_open(self, srcfile, dstfile):if not os.path.isfile(srcfile):print(srcfile + " not exist!")returnif os.path.isfile(dstfile):print(dstfile + " file already exist!")self.df = dstfileself.workbook = openpyxl.load_workbook(filename=srcfile)copyfile(srcfile, dstfile)self.wb = openpyxl.load_workbook(filename=dstfile)returndef get_sheets(self):sheets = self.workbook.sheetnamesreturn sheetsdef set_sheet(self, name):self.sheet = self.wb[name]returndef write(self, r, c, value, color=None):d = ll(row=r + 1, column=c + 1, value=value)if color:if color == 0:color = "FF000000"elif color == 1:color = "FFFFFFFF"elif color == 2:color = "FFFF0000"elif color == 3:color = "FF00FF00"elif color == 4:color = "FF0000FF"elif color == 5:color = "FFFFFF00"else:color = "FF000000"font = Font(name='Arial',size=11,bold=False,italic=False,vertAlign=None,underline='none',strike=False,color=color)d.font = fontreturndef save_close(self):self.wb.save(self.df)return

OldExcle.py文件内容：

import os, xlrd, xlwt
py import copyclass Reader:def __init__(self):self.workbook = Noneself.sheet = ws = 0self.r = 0def open_excel(self, srcfile):if not os.path.isfile(srcfile):print("%s not exist!" % (srcfile))ding = "utf8"self.workbook = xlrd.open_workbook(filename=srcfile)self.sheet = self.workbook.sheet_by_index(ws = wsself.r = 0returndef get_sheets(self):sheets = self.workbook.sheet_names()return sheetsdef set_sheet(self, name):self.sheet = self.workbook.sheet_by_name(ws = wsself.r = 0returndef readline(self):lines = []while self.r < ws:row1 = Nonerow = w_values(self.r)self.r = self.r + 1i = 0row1 = rowfor strs in row:row1[i] = str(strs)i = i + 1lines.append(row1)return linesclass Writer:def __init__(self):self.workbook = Noneself.wb = Noneself.sheet = Noneself.df = w = 0self.clo = 0def copy_open(self, srcfile, dstfile):if not os.path.isfile(srcfile):(srcfile + " not exist!")returnif os.path.isfile(dstfile):print(dstfile + " file already exist!")self.df = dstfileself.workbook = xlrd.open_workbook(filename=srcfile, formatting_info=True)self.wb = copy(self.workbook)returndef get_sheets(self):sheets = self.workbook.sheet_names()return sheetsdef set_sheet(self, name):self.sheet = _sheet(name)returndef write(self, r, c, value, color=None):def _getCell(sheet, r, c):row = sheet._Worksheet__(r)if not row:return Nonecell = row._Row__(c)return cellcell = _getCell(self.sheet, r, c)if cell:idx = cell.xf_idxif color is None:self.sheet.write(r, c, value)if cell:ncell = _getCell(self.sheet, r, c)if ncell:ncell.xf_idx = idxelse:style = xlwt.XFStyle()font = xlwt.Font()  font.name = 'Arial'font.bold = True  # font.underline = True  # font.italic = lour_index = color style.font = fontborders = xlwt.Borders()borders.left = xlwt.Borders.THINborders.right = xlwt.p = xlwt.Borders.THINborders.bottom = xlwt.Borders.THINstyle.borders = bordersself.sheet.write(r, c, value, style)returndef save_close(self):self.wb.save(self.df)return

2.读取excel用例，执行用例，将结果写入excel
用例内容：

示例代码：

#关键字对象
web=Web()
def runcase(obj,line,i):""":param obj:关键字对象:param line:参数:param i: 写入的行:return:"""#思路，先判断哪一行是要进行反射的，#如果第一个或者第二个有值则不执行反射print(line)if len(line[0])>1 or len(line[1])>1:returntry:func=getattr(obj,line[3])print(line[3])writer.write(i,7,"PASS",3)except Exception as e:print('关键字%s不存在' % line[3])writer.write ( i, 7, "FALl", 2 )returnparams=line[4:]params=params[:params.index('')]if params:func(*params)else:func()
#打开文件
reader=get_reader("../lib/电商项目用例.xlsx")
#写入文件
writer=get_writer("../lib/电商项目用例.xlsx","../lib/result-电商项目用例.xlsx")
#获取内容，获取sheet页 然后输出每一个sheet页每一行的数据，在把每一行的数据进行遍历
#调用get_sheets方法调用
#执行成功写入pass
sheets&#_sheets()
for i in range (0,len(sheets)):#遍历sheets名称，挨个读取reader.set_sheet(sheets[i])#保证读取和写入都在同一个sheet页writer.set_sheet(sheets[i])lines&#adline()#输出每一行的信息for i in range(1,len(lines)):runcase(web,lines[i],i)#保存结果
writer.save_close()

2.10.3 pytest集成excel

思路：将excel中的用例保存到列表cases中，使用pytest参数化调用cases，再用反射的方式执行cases。就实现了excel驱动pytest。
代码供参考：

保存用例到cases：

class Conf:def __init__(self,reader_path="./../lib/电商项目用例.xlsx",writer_path="./../lib/result-电商项目用例.xlsx"):#读取excel文件ader=get_reader(reader_path)#self.writer=get_writer(reader_path,writer_path)#获取sheet名称self.sheetname&#_sheets()#设置cases 保存所有用例self.cases=[]_Case()def get_Case(self):#设置要读取的sheet页for i in range(0,len(self.sheetname)):ader.set_sheet(self.sheetname[i])case=[]#读取行lines&#adline()for i in range (1,len(lines)):line=lines[i]# 如果一个数据有值 我们不做任何操作if  len(line[0])>1:pass#如果第二个数据有值我们开始保存case，这是一个case的开始   登录失败的用例elif len(line[1])>1:#如果不是第一行 到了第二个用例的时候if i >2 :#我们要把上一个用例保存到cases里面self.cases.append(case)#将case置空保存第二条用例case=[]case.append(line)#如果没有值 就直接在case后面增加，直到下一次 len(line[1])>1else:case.append(line)#把最后一个用例保存到casesself.cases.append(case)#输出case# for i  in  range (0,len(self.cases)):#     print(self.cases[i])conf=None

用例部分代码：

class Test_web():"""web自动化测试类"""def setup_class(self):self.web=Web()allure.step()@pytest.mark.parametrize("case_params",f.cases)def test_case(self,case_params):for i  in range (0,len(case_params)):line=case_params[i]#判断那行执行if len(line[0]) > 1 or len(line[1]) > 1:continue#获取一行的数据try:func =getattr(self.web,line[3])params = line[4:]params = params[:params.index('')]func(*params)except:print(line[3]+"方法不存在")if __name__=='__main__':f=Conf()os.system('rd /s/q result')os.system('rd /s/q reports')pytest.main(['-s', 'pytest_example/test_web.py','--alluredir', 'result'])os.system('allure generate result -o reports --clean')

再使用pytest生成allure报告，这里就不介绍allure的设计了（我比较懒）。