最近因为工作需要,需要将excel里的测试用例归档到TestLink中,由于平台上的转换Xml工具不好使用,本人自己写了个python脚本工具,方便将用力转换为xml文件,最后导入到TestLink中。
代码如下:
# -*- coding: UTF-8 -*-
from openpyxl import load_workbook
import time
import os
import tkinter as tk
import tkinter.messagebox
from datetime import datetime
import windnd
"""测试步骤处理"""
def bz(str,res):
str_list = str.split("\n")
res_list = res.split("\n")
if len(str_list) != len(res_list):
return "预期与结果行数不匹配"
else:
str_n = ''
for i in range(len(str_list)):
if i == 0:
str_n = str_n + '<step>' + '\n<step_number><![CDATA[1]]></step_number>' + '\n<actions><![CDATA[<p>%s</p>]]></actions>' % str_list[i] + '\n<expectedresults><![CDATA[<p>%s</p>]]></expectedresults>' % res_list[i] + '\n</step>'
else:
str_n = str_n + '\n<step>' + '\n<step_number><![CDATA[%s]]></step_number>' % (i+1) + '\n<actions><![CDATA[<p>%s</p>]]></actions>' % str_list[i] + '\n<expectedresults><![CDATA[<p>%s</p>]]></expectedresults>' % res_list[i] + '\n</step>'
str_w = "<steps>" + "\n%s" % str_n + "\n</steps>"
return str_w
"""读取功能excel表"""
def read_excel(path):
wb = load_workbook(path)
ws = wb[wb.sheetnames[0]]
row = ws.max_row
cl = ws.max_column
str_w = '<?xml version="1.0" encoding="UTF-8"?>'
if row ==1 and cl ==1:
return "no data"
else:
a = 0
for i in ws.values:
if i[0] == "模块" or i[0] == None:
pass
else:
tit_name = i[2].replace('\t', '').replace('\n', '')
zy_name = i[3].replace('\t', '').replace('\n', '') if i[3] != None else ''
qz_name = i[4].replace('\t', '').replace('\n', '') if i[4] != None else ''
bz_name = i[5]
res_name = i[6]
# 终稿
status = '<status>7</status>'
#摘要及其相关
zy = '<summary><![CDATA[<p>%s</p>]]></summary>' % zy_name
# 前置条件
pre = '<preconditions><![CDATA[<p>%s</p>]]></preconditions>' % qz_name
# 测试步骤与测试结果处理
br = bz(bz_name, res_name)
if br == "预期与结果行数不匹配":
return "预期与结果行数不匹配"
else:
a += 1
if a == 1:
str_w = str_w+'\n<testcases>'+'\n<testcase name="%s">' % tit_name+'\n<node_order><![CDATA[%s]]></node_order>' % a+'\n%s' % zy+'\n%s' % status+'\n%s' % pre+'\n%s' % br
else:
str_w = str_w+'\n</testcase>'+'\n<testcase name="%s">' % tit_name+'\n<node_order><![CDATA[%s]]></node_order>' % a+'\n%s' % zy+'\n%s' % status + '\n%s' % pre+'\n%s' % br
str_w = str_w + '\n</testcase>' + '\n</testcases>'
# print(str_w)
return str_w, a
"""写入xml文件"""
def wr_xml(path,data):
time_w = str(datetime.now().strftime('%Y%m%d%H%M%S')) + ".txt"
path_n = "\\".join(path.split("\\")[:-1]) + "\\" + time_w
with open(path_n, 'w', encoding='utf-8') as f:
f.write(data)
time.sleep(1)
path_x = "\\".join(path.split("\\")[:-1]) + "\\" + str(datetime.now().strftime('%Y%m%d%H%M%S')) + '.xml'
os.rename(path_n, path_x)
if __name__ == '__main__':
# path = "E:\\2022.01例行_用例.xlsx"
# data = read_excel(path)
# wr_xml(path, data[0])
window = tk.Tk()
window.title('转化xml工具')
window.geometry('400x300')
"""登陆界面"""
tk.Label(window, text='测试文件地址:').place(x=10, y=128)
tk.Label(window, text='可拖拽文件至输入框!').place(x=100, y=100)
var_usr_name = tk.StringVar()
enter_usr_name = tk.Entry(window, textvariable=var_usr_name)
enter_usr_name.place(x=100, y=130, width=280)
# 拖拽文件函数
def func(files):
for i in files:
enter_usr_name.insert("end", i.decode("gbk")+'\n')
# 创建一个任务函数
def usr_log_in():
# 输入框内容
usr_name = var_usr_name.get().strip()
# 用例表
if usr_name == '':
tk.messagebox.showerror(message='请输入测试用例表格地址!')
else:
if not os.path.exists(usr_name):
tk.messagebox.showerror(message='测试用例表格路径不正确!')
else:
try:
data = read_excel(usr_name)
if data == "预期与结果行数不匹配":
tk.messagebox.showerror(message='预期与结果行数不匹配!')
else:
wr_xml(usr_name, data[0])
tk.messagebox.showinfo(title="运行完成", message='共转化%s条用例!'%str(data[1]))
except:
tk.messagebox.showerror(message='运行失败!')
# 按钮
windnd.hook_dropfiles(enter_usr_name.winfo_id(), func)
enter_usr_name.place(x=100, y=130, width=280)
bt_login = tk.Button(window, text='开始', command=usr_log_in)
bt_login.place(x=170, y=200, width=80)
window.mainloop()
如果不会复制代码,可以去下载这个工具: