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': '[email protected]',
    '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': '[email protected]',
    '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()