自动化测试Excel记录表格
import openpyxl
from openpyxl.workbook import Workbook
from openpyxl.styles import Font
import os
import time
import imghdr
import logging
logger = logging.getLogger("log")
logger.setLevel(logging.DEBUG)
# logger的setLevel是最根本的
fh = logging.FileHandler('log' + time.strftime("%Y%m%d%H%M%S", time.localtime(time.time())) + '.log')
# 如果没有这个,就不会输出到文件
fh.setLevel(logging.INFO)
ch = logging.StreamHandler()
ch.setLevel(logging.INFO)
formatter = logging.Formatter("%(asctime)s - %(name)s - %(levelname)s - %(message)s")
ch.setFormatter(formatter)
fh.setFormatter(formatter)
logger.addHandler(ch)
logger.addHandler(fh)
def CardTest(rootdir,database ):
now = time.strftime("%Y%m%d%H%M%S", time.localtime(time.time()))
filepath = 'xx准确率测试'+now+'.xlsx'
wb = openpyxl.Workbook()
#默认表sheet1
ws1 = wb.active
#更改表名
ws1.title = '测试记录'
sheet_names = wb.sheetnames # 得到工作簿的所有工作表名 结果: ['Sheet1', 'Sheet2', 'Sheet3']
sheet1 = wb[sheet_names[0]] # 打开第一个 sheet 工作表
sheet1.cell(row=1, column=1, value="序列") .font = Font(bold=True) # 序列
sheet1.cell(row=1, column=2, value="测试时间").font = Font(bold=True) # 模板
sheet1.cell(row=1, column=3, value="检索模板").font = Font(bold=True) # 模板
sheet1.cell(row=1, column=4, value="测试图片") .font = Font(bold=True) # 测试图片
sheet1.cell(row=1, column=5, value="期望检索结果").font = Font(bold=True) # 期望检索结果
sheet1.cell(row=1, column=6, value="实际检索结果") .font = Font(bold=True) # 实际检索结果
sheet1.cell(row=1, column=7, value="检索时间").font = Font(bold=True) # 检索时间
sheet1.cell(row=1, column=8, value="判断结果") .font = Font(bold=True) # 判断结果
sheet1.cell(row=1, column=15, value="测试图路径").font = Font(bold=True) # 测试图路径
A=1
passs=[]
fails=[]
errors=[]
for i in Getimage(rootdir):
image = i.split("\\")[-1]#测试图片
B = time.strftime("%Y%m%d%H%M%S", time.localtime(time.time()))#测试时间
try:
n = image.split(".")[0].split("-")
C=str(image.split(".")[0].split("-")[-1]+"号database")#匹配模板
D=n[0]+"-"+n[1]+"-"+n[2]#预期检索结果
E=Reddll(i,image.split(".")[0].split("-")[-1],database )#实际检索结果
F="" #检索时间
if D==E:
G="PASS"
color="006400"
writedata(sheet1, A + 1, A, B, C,image, D, E, F,G ,i,color)
passs.append("PASS")
else:
G="FAIL"
color = "8B0000"
writedata(sheet1, A + 1, A, B, C, image, D, E, F, G, i, color)
fails.append("FAIL")
except Exception as e:
logger.error("出错:"+str(e))
color="FFBB00"
writedata(sheet1, A + 1, A, B, "error",image, "error", "error", "error", "error", i,color)
errors.append("error")
A=A+1
sheet1.cell(row=1, column=12, value="数据汇总").font = Font(bold=True)
sheet1.cell(row=2, column=12, value="测试总数:" + str(len(passs)+len(fails)+len(errors)))
sheet1.cell(row=3, column=12, value="通过数:"+str(len(passs)))
sheet1.cell(row=4, column=12, value="失败数:"+str(len(fails)))
sheet1.cell(row=5, column=12, value="出错数:" + str(len(errors)))
sheet1.cell(row=6, column=12, value="准确率:"+str(len(passs)/(len(passs)+len(fails))))
wb.save(filepath)
def writedata(sheet1,row,A,B,C,D,E,F,G,H,J,color):
ft = Font(color=color) # color="FFBB00",颜色编码也可以设定颜色
sheet1.cell(row=row, column=8, value=H).font = ft
logger.info("《"+D+"》"+"检索结果为:"+F+",测试结果:"+H)
sheet1.cell(row=row, column=1, value=A) # 序列
sheet1.cell(row=row, column=2, value=B) # 测试时间
sheet1.cell(row=row, column=3, value=C) # 检索模板
sheet1.cell(row=row, column=4, value=D) # 测试图片
sheet1.cell(row=row, column=5, value=E) # 期望检索结果
sheet1.cell(row=row, column=6, value=F) # 实际检索结果
sheet1.cell(row=row, column=7, value=G) # 检索时间
sheet1.cell(row=row, column=8, value=H) # 判断通过
sheet1.cell(row=row, column=15, value=J) # 测试图路径
def Getimage(rootdir):
_files = []
list = os.listdir(rootdir) # 列出文件夹下所有的目录与文件
for i in range(0, len(list)):
path = os.path.join(rootdir, list[i]) # 合并路径,将rootdir和list合并
if os.path.isdir(path):
_files.extend(Getimage(path)) # 递归调用函数
if os.path.isfile(path):
if imghdr.what(path) in ["bmp", "jpg", "png", "gif", "jpeg"]:
_files.append(path)
return _files
def Reddll(imgagepath,n,database ):
a = imgagepath.split("\\")[-1].split(".")[0].split("-")
databasepath=database+"/"+n
#logger.info(str(databasepath))
return a[0]+"-"+a[1]+"-"+a[2]
path = r"F:\python\xxdll\20副xxxx验"
database = r"F:\python\xxdll\20副xxxx验\database"
CardTest(path,database)
input('Press Enter to exit...')