python ui自动化excel管理用例_python自动化测试报告（excel篇）

川长思鸟来 2023-01-09 14:23 137阅读 0赞

转：https://www.jianshu.com/p/6c5d328bf390

\# -\*- coding: utf-8 -\*-

import xlsxwriter

def get\_format(wd, option=\{\}):

return wd.add\_format(option)

\# 设置居中

def get\_format\_center(wd,num=1):

return wd.add\_format(\{‘align‘: ‘center‘,‘valign‘: ‘vcenter‘,‘border‘:num\})

def set\_border\_(wd, num=1):

return wd.add\_format(\{\}).set\_border(num)

\# 写数据

def \_write\_center(worksheet, cl, data, wd):

return worksheet.write(cl, data, get\_format\_center(wd))

workbook = xlsxwriter.Workbook(‘report.xlsx‘)

worksheet = workbook.add\_worksheet("测试总况")

worksheet2 = workbook.add\_worksheet("测试详情")

def init(worksheet):

\# 设置列行的宽高

worksheet.set\_column("A:A", 15)

worksheet.set\_column("B:B", 20)

worksheet.set\_column("C:C", 20)

worksheet.set\_column("D:D", 20)

worksheet.set\_column("E:E", 20)

worksheet.set\_column("F:F", 20)

worksheet.set\_row(1, 30)

worksheet.set\_row(2, 30)

worksheet.set\_row(3, 30)

worksheet.set\_row(4, 30)

worksheet.set\_row(5, 30)

\# worksheet.set\_row(0, 200)

define\_format\_H1 = get\_format(workbook, \{‘bold‘: True, ‘font\_size‘: 18\})

define\_format\_H2 = get\_format(workbook, \{‘bold‘: True, ‘font\_size‘: 14\})

define\_format\_H1.set\_border(1)

define\_format\_H2.set\_border(1)

define\_format\_H1.set\_align("center")

define\_format\_H2.set\_align("center")

define\_format\_H2.set\_bg\_color("blue")

define\_format\_H2.set\_color("\#ffffff")

\# Create a new Chart object.

worksheet.merge\_range(‘A1:F1‘, ‘测试报告总概况‘, define\_format\_H1)

worksheet.merge\_range(‘A2:F2‘, ‘测试概括‘, define\_format\_H2)

worksheet.merge\_range(‘A3:A6‘, ‘这里放图片‘, get\_format\_center(workbook))

\_write\_center(worksheet, "B3", ‘项目名称‘, workbook)

\_write\_center(worksheet, "B4", ‘接口版本‘, workbook)

\_write\_center(worksheet, "B5", ‘脚本语言‘, workbook)

\_write\_center(worksheet, "B6", ‘测试网络‘, workbook)

data = \{"test\_name": "智商", "test\_version": "v2.0.8", "test\_pl": "android", "test\_net": "wifi"\}

\_write\_center(worksheet, "C3", data\[‘test\_name‘\], workbook)

\_write\_center(worksheet, "C4", data\[‘test\_version‘\], workbook)

\_write\_center(worksheet, "C5", data\[‘test\_pl‘\], workbook)

\_write\_center(worksheet, "C6", data\[‘test\_net‘\], workbook)

\_write\_center(worksheet, "D3", "接口总数", workbook)

\_write\_center(worksheet, "D4", "通过总数", workbook)

\_write\_center(worksheet, "D5", "失败总数", workbook)

\_write\_center(worksheet, "D6", "测试日期", workbook)

data1 = \{"test\_sum": 100, "test\_success": 80, "test\_failed": 20, "test\_date": "2018-10-10 12:10"\}

\_write\_center(worksheet, "E3", data1\[‘test\_sum‘\], workbook)

\_write\_center(worksheet, "E4", data1\[‘test\_success‘\], workbook)

\_write\_center(worksheet, "E5", data1\[‘test\_failed‘\], workbook)

\_write\_center(worksheet, "E6", data1\[‘test\_date‘\], workbook)

\_write\_center(worksheet, "F3", "分数", workbook)

worksheet.merge\_range(‘F4:F6‘, ‘60‘, get\_format\_center(workbook))

pie(workbook, worksheet)

\# 生成饼形图

def pie(workbook, worksheet):

chart1 = workbook.add\_chart(\{‘type‘: ‘pie‘\})

chart1.add\_series(\{

‘name‘: ‘接口测试统计‘,

‘categories‘:‘=测试总况!$D$4:$D$5‘,

‘values‘: ‘=测试总况!$E$4:$E$5‘,

\})

chart1.set\_title(\{‘name‘: ‘接口测试统计‘\})

chart1.set\_style(10)

worksheet.insert\_chart(‘A9‘, chart1, \{‘x\_offset‘: 25, ‘y\_offset‘: 10\})

def test\_detail(worksheet):

\# 设置列行的宽高

worksheet.set\_column("A:A", 30)

worksheet.set\_column("B:B", 20)

worksheet.set\_column("C:C", 20)

worksheet.set\_column("D:D", 20)

worksheet.set\_column("E:E", 20)

worksheet.set\_column("F:F", 20)

worksheet.set\_column("G:G", 20)

worksheet.set\_column("H:H", 20)

worksheet.set\_row(1, 30)

worksheet.set\_row(2, 30)

worksheet.set\_row(3, 30)

worksheet.set\_row(4, 30)

worksheet.set\_row(5, 30)

worksheet.set\_row(6, 30)

worksheet.set\_row(7, 30)

worksheet.merge\_range(‘A1:H1‘, ‘测试详情‘, get\_format(workbook, \{‘bold‘: True, ‘font\_size‘: 18 ,‘align‘: ‘center‘,‘valign‘: ‘vcenter‘,‘bg\_color‘: ‘blue‘, ‘font\_color‘: ‘\#ffffff‘\}))

\_write\_center(worksheet, "A2", ‘用例ID‘, workbook)

\_write\_center(worksheet, "B2", ‘接口名称‘, workbook)

\_write\_center(worksheet, "C2", ‘接口协议‘, workbook)

\_write\_center(worksheet, "D2", ‘URL‘, workbook)

\_write\_center(worksheet, "E2", ‘参数‘, workbook)

\_write\_center(worksheet, "F2", ‘预期值‘, workbook)

\_write\_center(worksheet, "G2", ‘实际值‘, workbook)

\_write\_center(worksheet, "H2", ‘测试结果‘, workbook)

data = \{"info": \[\{"t\_id": "1001", "t\_name": "登陆", "t\_method": "post", "t\_url": "http://XXX?login", "t\_param": "\{user\_name:test,pwd:111111\}",

"t\_hope": "\{code:1,msg:登陆成功\}", "t\_actual": "\{code:0,msg:密码错误\}", "t\_result": "失败"\}, \{"t\_id": "1002", "t\_name": "商品列表", "t\_method": "get", "t\_url": "http://XXX?getFoodList", "t\_param": "\{\}",

"t\_hope": "\{code:1,msg:成功,info:\[\{name:123,detal:dfadfa,img:product/1.png\},\{name:456,detal:dfadfa,img:product/1.png\}\]\}", "t\_actual": "\{code:1,msg:成功,info:\[\{name:123,detal:dfadfa,img:product/1.png\},\{name:456,detal:dfadfa,img:product/1.png\}\]\}", "t\_result": "成功"\}\],

"test\_sum": 100,"test\_success": 20, "test\_failed": 80\}

temp = 4

for item in data\["info"\]:

\_write\_center(worksheet, "A"+str(temp), item\["t\_id"\], workbook)

\_write\_center(worksheet, "B"+str(temp), item\["t\_name"\], workbook)

\_write\_center(worksheet, "C"+str(temp), item\["t\_method"\], workbook)

\_write\_center(worksheet, "D"+str(temp), item\["t\_url"\], workbook)

\_write\_center(worksheet, "E"+str(temp), item\["t\_param"\], workbook)

\_write\_center(worksheet, "F"+str(temp), item\["t\_hope"\], workbook)

\_write\_center(worksheet, "G"+str(temp), item\["t\_actual"\], workbook)

\_write\_center(worksheet, "H"+str(temp), item\["t\_result"\], workbook)

temp = temp -1