【python】将Excel转换为xml文件，导入到TestLink

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

如果不会复制代码，可以去下载这个工具：

TestLink用例上传工具，python编写。-Python文档类资源-CSDN下载