因工作需要将Testlink用例导出为Excel文件,借鉴网上部分代码后实现。
功能为:从TestLink导出的用例或用例集,通过工具转换为Excel文件,界面显示转换的每条用例,统计每次转换的用例数。
已编译为EXE文件,可直接下载: TestLink导出XML用例转换为Excel工具-IT管理文档类资源-CSDN下载
源代码如下:
# -*- coding: utf-8 -*-
"""
-------------------------------------------------
File Name: xml2xlsx.py
Author : 曾良均
QQ: 277099728
Date: 8/9/2022 8:49 AM
Description :
-------------------------------------------------
Change Activity:
-------------------------------------------------
"""
import os, base64
import time
import logging
import xml
from fav import img
import re
try:
from lxml import etree
except ImportError:
import xml.etree.cElementTree as etree
import tkinter
from tkinter import messagebox
from tkinter import filedialog
from tkinter import *
import xlwt
import xml.dom.minidom
t1 = []
root = None
# 获取当前有时间,用于输出日志
fmt = time.strftime("%m/%d %H:%M:%S", time.localtime()) + '>> '
__author__ = {
'name': '曾良均',
'QQ': '277099728',
'Email': 'zlj-316731@163.com',
'Blog': 'https://blog.csdn.net/zljun8210',
'Created': '2017-07-03'}
# 设置logger配置
logging.basicConfig(level=logging.DEBUG,
encoding='utf-8',
format='%(asctime)s %(message)s',
datefmt='%a, %d %b %Y %H:%M:%S',
filename='./test.log',
filemode='w')
class Converter():
path = "./"
def __init__(self, rt):
if rt is None:
self.t = tkinter.Tk()
else:
self.t = tkinter.Toplevel(rt)
self.t.title("xml2xls转换器")
# 设置窗口图标
self.setIcon()
self.t.geometry('600x400')
self.lab_input = Label(self.t, font=("微软雅黑", 9, "bold"), text=" 源文件: ")
self.lab_input.place(x=2, y=20)
self.ent = Entry(self.t, bd=1)
self.ent.place(x=80, y=20, width=250)
self.btn = Button(self.t, font=("微软雅黑", 9, "bold"), text=" 打开 ", command=self.callback)
self.btn.place(x=350, y=20)
self.btn_exe1 = Button(self.t, font=("微软雅黑", 9, "bold"), text=" 转换用例 ", command=self.tcConvert)
self.btn_exe1.place(x=410, y=20)
self.btn_exe2 = Button(self.t, font=("微软雅黑", 9, "bold"), text=" 转换用例集 ", command=self.tcConverts)
self.btn_exe2.place(x=500, y=20)
self.lab_info = Label(self.t, fg='red', font=("微软雅黑", 7, "bold underline"), text="注意:转换用例集,因TestLink导出的两级测试用例集,其xml文件最外层testsuite,须手动改为testsuites")
self.lab_info.place(x=15, y=55)
self.st = Text(self.t, wrap='word')
self.st.place(x=5, y=80, width=560, height=260)
self.st.configure(state=tkinter.DISABLED)
sb = Scrollbar(self.st, orient=VERTICAL)
sb["command"] = self.st.yview()
self.st["yscrollcommand"] = sb.set
sb.pack(side=RIGHT, fill='both')
self.labinfo = Label(self.t, font=("微软雅黑", 7, "bold"), text=" Testlink软件之XML转Excel工具 \n 作者:曾良均 \n Ver: 0.1 (20220809) ")
self.labinfo.place(x=420, y=350)
def setIcon(self):
tmp = open("tmp.ico", "wb+")
tmp.write(base64.b64decode(img))
tmp.close()
self.t.iconbitmap("tmp.ico")
os.remove("tmp.ico")
# 选取文件路径
def callback(self):
self.ent.delete(0, END)
filepath = filedialog.askopenfilename()
if filepath:
self.ent.insert(0, filepath) # 将选择好的路径加入到entry里面
@staticmethod
def openfilename():
filename = filedialog.asksaveasfilename(filetypes=[("打开文件", "*.xml")])
if filename:
return open(filename, 'w', encoding='utf8')
# 转换测试用例
def tcConvert(self):
path = self.ent.get()
logging.debug('转换的XML文件为 ' + path)
self.st.configure(state=tkinter.NORMAL)
# 用于清空文本框内容
self.st.insert(tkinter.END, '转换的XML文件为 ' + path + '\n')
tfn = path.split("/")[-1]
ofn = tfn.split(".")[0]
# print("文件名是: " + ofn)
self.st.insert(tkinter.END, '转换后的文件名是: ' + ofn + '.xls')
self.st.see(tkinter.END)
logging.debug('转换后的文件名: ' + ofn + '.xls')
if path == "":
tkinter.messagebox.showinfo("Messages", "请打开有效的xml文件!")
dom = xml.dom.minidom.parse(path)
# 写入excel中
workbook = xlwt.Workbook(encoding="utf-8")
# 创建sheet名称
booksheet = workbook.add_sheet(ofn)
# 设置excel宽度
booksheet.col(0).width = 5120
booksheet.col(1).width = 5120
booksheet.col(2).width = 5120
booksheet.col(3).width = 5120
booksheet.col(4).width = 5120
booksheet.col(5).width = 5120
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
# 设置头部文件字体格式等
title = xlwt.easyxf(
u'font:name 仿宋,height 240 ,colour_index black, bold on, italic off; align: wrap on, vert centre, horiz center;pattern: pattern solid, fore_colour light_orange;')
# 设置写入文件的格式
style = xlwt.easyxf('align: wrap on,vert centre, horiz center')
if dom:
logging.debug(" ---- 开始转换 ---- ")
self.st.insert(tkinter.END, "\n ---- 开始转换 ---- \n" + '\n')
self.st.update()
# 设置excel字段
item = '模块/任务'
Subitem = '用例名称'
CaseTitle = '描述'
Condition = '前置条件'
actions = '操作步骤'
Result = '预期结果'
booksheet.write(0, 0, item, title)
booksheet.write(0, 1, Subitem, title)
booksheet.write(0, 2, CaseTitle, title)
booksheet.write(0, 3, Condition, title)
booksheet.write(0, 4, actions, title)
booksheet.write(0, 5, Result, title)
k = 0
try:
testcases_tag = dom.getElementsByTagName('testsuite')
fun = testcases_tag[0].getAttribute('name')
except:
testcases_tag = dom.getElementsByTagName('testcases')
fun = '功能测试' # 此时没有父级名称,直接设置为功能测试
finally:
testcase_tag_ = testcases_tag[0].getElementsByTagName('testcase')
for j in range(len(testcase_tag_)):
step_tag_ = testcases_tag[0].getElementsByTagName('testcase')[j].getElementsByTagName('steps')[
0].getElementsByTagName('step')
# print(step_tag_)
for i in range(len(step_tag_)):
# k的值向上递增
k = k + 1
# 获取用例名称
try:
testcase_tag_name = testcases_tag[0].getElementsByTagName('testcase')[j].getAttribute('name')
# 写入excel中
logging.debug(f'第 {k} 条用例: {testcase_tag_name}')
self.st.insert(tkinter.END, fmt + f'第 {k} 条用例: {testcase_tag_name}' + '\n')
self.st.see(tkinter.END)
self.st.update()
except:
testcase_tag_name = ''
booksheet.write(k, 1, testcase_tag_name, style)
# 获取用例描述
try:
testcase_tab_summary = \
testcases_tag[0].getElementsByTagName('testcase')[j].getElementsByTagName('summary')[
0].firstChild.data
testcase_tab_summary = re.sub(r'<(p|/br|br|/p|br/)*>', '\n', testcase_tab_summary)
except:
testcase_tab_summary = ''
booksheet.write(k, 2, testcase_tab_summary, style)
# 获取前置条件
try:
testcase_tag_preconditions = \
testcases_tag[0].getElementsByTagName('testcase')[j].getElementsByTagName('preconditions')[
0].firstChild.data
testcase_tag_preconditions = re.sub(r'<(p|/br|br|/p|br/)*>', '\n', testcase_tag_preconditions)
except:
testcase_tag_preconditions = ''
booksheet.write(k, 3, testcase_tag_preconditions, style)
# 获取操作步骤
try:
step_number = \
testcases_tag[0].getElementsByTagName('testcase')[j].getElementsByTagName('steps')[
0].getElementsByTagName('step')[i].getElementsByTagName('step_number')[0].firstChild.data
except:
step_number = 0
# 进行替换
step_number = re.sub(r'<(p|/br|br|/p|br/)*>', '\n', step_number)
# 获取用例步骤
try:
step = testcases_tag[0].getElementsByTagName('testcase')[j].getElementsByTagName('steps')[
0].getElementsByTagName('step')[i].getElementsByTagName('actions')[0].firstChild.data
step = re.sub(r'<(p|/br|br|/p|br/)*>', '\n', step)
except:
step = ''
# 将得到文件写入excel中
booksheet.write(k, 4, step, style)
booksheet.write(k, 0, fun, style)
# 获取预期结果
try:
expectedresults = \
testcases_tag[0].getElementsByTagName('testcase')[j].getElementsByTagName('steps')[
0].getElementsByTagName('step')[i].getElementsByTagName('expectedresults')[
0].firstChild.data
expectedresults = re.sub(r'<(p|/br|br|/p|br/)*>', '\n', expectedresults)
except:
expectedresults = ''
booksheet.write(k, 5, expectedresults, style)
# 保存至本地
pathlist = path.split("/")
pathlist.pop()
newpath = '/'.join(pathlist)
workbook.save('{}.xls'.format(newpath + "/" + ofn))
logging.debug('测试用例转换完成!!\n')
logging.debug(f'此次转换了{k} 条用例\n\n')
self.st.insert(tkinter.END, '\n' + '测试用例转换完成!!' + '\n')
self.st.insert(tkinter.END, fmt + f'此次转换了{k} 条用例' + '\n\n')
self.st.see(tkinter.END)
self.st.update()
self.st.configure(state=tkinter.DISABLED)
tkinter.messagebox.showinfo("Messages", "Convert Successfully.\n Save file to " + ofn + ".xls .")
# 转换测试用例集
def tcConverts(self):
path = self.ent.get()
logging.debug('转换的XML文件为 ' + path)
self.st.configure(state=tkinter.NORMAL)
# 用于清空文本框内容
self.st.delete(1.0, tkinter.END)
self.st.insert(tkinter.END, '转换的XML文件为 ' + path + '\n')
tfn = path.split("/")[-1]
ofn = tfn.split(".")[0]
# print("文件名是: " + ofn)
self.st.insert(tkinter.END, '转换后的文件名是: ' + ofn + '.xls')
self.st.see(tkinter.END)
logging.debug('转换后的文件名: ' + ofn + '.xls')
if path == "":
tkinter.messagebox.showinfo("Messages", "请打开有效的xml文件!")
dom = xml.dom.minidom.parse(path)
# 写入excel中
workbook = xlwt.Workbook(encoding="utf-8")
# 创建sheet名称
booksheet = workbook.add_sheet(ofn)
# 设置excel宽度
booksheet.col(0).width = 5120
booksheet.col(1).width = 5120
booksheet.col(2).width = 5120
booksheet.col(3).width = 5120
booksheet.col(4).width = 5120
booksheet.col(5).width = 5120
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
# 设置头部文件字体格式等
title = xlwt.easyxf(
u'font:name 仿宋,height 240 ,colour_index black, bold on, italic off; align: wrap on, vert centre, horiz center;pattern: pattern solid, fore_colour light_orange;')
# 设置写入文件的格式
style = xlwt.easyxf('align: wrap on,vert centre, horiz center')
if dom:
logging.debug(" ---- 开始转换 ---- ")
self.st.insert(tkinter.END, "\n ---- 开始转换 ---- \n" + '\n')
self.st.update()
# 设置excel字段
item = '模块/任务'
Subitem = '用例名称'
CaseTitle = '描述'
Condition = '前置条件'
actions = '操作步骤'
Result = '预期结果'
booksheet.write(0, 0, item, title)
booksheet.write(0, 1, Subitem, title)
booksheet.write(0, 2, CaseTitle, title)
booksheet.write(0, 3, Condition, title)
booksheet.write(0, 4, actions, title)
booksheet.write(0, 5, Result, title)
# TestLink导出的两级测试用例集,其xml文件最外层testsuite需手动改为testsuites
try:
testsites_tag = dom.getElementsByTagName('testsuites')
try:
testcases_tag = testsites_tag[0].getElementsByTagName('testsuite')
except:
testcases_tag = testsites_tag[0].getElementsByTagName('testcases')
except:
tkinter.messagebox.showinfo("Messages", "XML文件最外层对象不是testsuites,请修改后再重试。")
else:
k = 0
for m in range(len(testcases_tag)):
testcase_tag_ = testcases_tag[m].getElementsByTagName('testcase')
fun = testcases_tag[m].getAttribute('name')
for j in range(len(testcase_tag_)):
step_tag_ = testcases_tag[m].getElementsByTagName('testcase')[j].getElementsByTagName('steps')[
0].getElementsByTagName('step')
# print(step_tag_)
for i in range(len(step_tag_)):
# k的值向上递增
k = k + 1
# 获取用例名称
try:
testcase_tag_name = testcases_tag[m].getElementsByTagName('testcase')[j].getAttribute('name')
# 写入excel中
logging.debug(f'第 {k} 条用例: {testcase_tag_name}')
self.st.insert(tkinter.END, fmt + f'第 {k} 条用例: {testcase_tag_name}' + '\n')
self.st.see(tkinter.END)
self.st.update()
except:
testcase_tag_name = ''
booksheet.write(k, 1, testcase_tag_name, style)
# 获取用例描述
try:
testcase_tab_summary = \
testcases_tag[m].getElementsByTagName('testcase')[j].getElementsByTagName('summary')[
0].firstChild.data
testcase_tab_summary = re.sub(r'<(p|/br|br|/p|br/)*>', '\n', testcase_tab_summary)
except:
testcase_tab_summary = ''
booksheet.write(k, 2, testcase_tab_summary, style)
# 获取前置条件
try:
testcase_tag_preconditions = \
testcases_tag[m].getElementsByTagName('testcase')[j].getElementsByTagName('preconditions')[
0].firstChild.data
testcase_tag_preconditions = re.sub(r'<(p|/br|br|/p|br/)*>', '\n', testcase_tag_preconditions)
except:
testcase_tag_preconditions = ''
booksheet.write(k, 3, testcase_tag_preconditions, style)
# 获取操作步骤
try:
step_number = \
testcases_tag[m].getElementsByTagName('testcase')[j].getElementsByTagName('steps')[
0].getElementsByTagName('step')[i].getElementsByTagName('step_number')[0].firstChild.data
except:
step_number = 0
# 进行替换
step_number = re.sub(r'<(p|/br|br|/p|br/)*>', '\n', step_number)
# 获取用例步骤
try:
step = testcases_tag[m].getElementsByTagName('testcase')[j].getElementsByTagName('steps')[
0].getElementsByTagName('step')[i].getElementsByTagName('actions')[0].firstChild.data
step = re.sub(r'<(p|/br|br|/p|br/)*>', '\n', step)
except:
step = ''
# 将得到文件写入excel中
booksheet.write(k, 4, step, style)
booksheet.write(k, 0, fun, style)
# 获取预期结果
try:
expectedresults = \
testcases_tag[m].getElementsByTagName('testcase')[j].getElementsByTagName('steps')[
0].getElementsByTagName('step')[i].getElementsByTagName('expectedresults')[
0].firstChild.data
expectedresults = re.sub(r'<(p|/br|br|/p|br/)*>', '\n', expectedresults)
except:
expectedresults = ''
booksheet.write(k, 5, expectedresults, style)
# 保存至本地
pathlist = path.split("/")
pathlist.pop()
newpath = '/'.join(pathlist)
workbook.save('{}.xls'.format(newpath + "/" + ofn))
logging.debug('测试用例转换完成!!\n')
logging.debug(f'此次转换了{k} 条用例\n\n')
self.st.insert(tkinter.END, '\n' + '测试用例转换完成!!' + '\n')
self.st.insert(tkinter.END, fmt + f'此次转换了{k} 条用例' + '\n\n')
self.st.see(tkinter.END)
self.st.update()
self.st.configure(state=tkinter.DISABLED)
tkinter.messagebox.showinfo("Messages", "Convert Successfully.\n Save file to " + ofn + ".xls .")
if __name__ == '__main__':
root = None
t1.append(Converter(root))
root = t1[0].t
root.mainloop()
运行界面:
转换后的Excel文件如下:
如需其他字段,可自行调整源码。
关于icon即logo图标实现,代码如下:
#! encoding:utf-8
import base64
__author__ = {
'name': '曾良均',
'QQ': '277099728',
'Email': 'zlj-316731@163.com',
'Blog': 'https://blog.csdn.net/zljun8210',
'Created': '2017-07-03'}
open_icon = open("favicon.ico", "rb") # 要放入的图标文件
b64str = base64.b64encode(open_icon.read()) # 以Base64的格式读出
open_icon.close()
write_data = "img=%s" % b64str
f = open("fav.py", "w+")
f.write(write_data)
f.close()