TestLink导出用例转换工具(XML2Excel)

    因工作需要将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()