图形化界面的Xmind用例转excel工具

界面

convert.py

xmind转换为excel

# !/Desktop/codearea/pytestlearn2/venv/bin/python3.9
"""
@Author:Zhming
@Project:XmindtoExcelGUI 
@File:convert
@Software:PyCharm 
@Date:2022/3/3
"""

import xlwt
from xmindparser import xmind_to_dict


def handle_xmind(filename):
    out = xmind_to_dict(filename)
    return out[0]['topic']['topics']


def handle_path(topic, topics_lists, title):
    """
    遍历解析后的xmind数据
    :param topic:xmind解析后的数据
    :param topics_lists:从topic提取的数据存放的列表
    :param title:从topic提取的title
    :return:
    """
    # title去除首尾空格
    title = title.strip()
    # 如果调用本方法，则concatTitle赋值为第一个层级的名称（所属模块名称）
    if len(title) == 0:
        concatTitle = topic['title'].strip()
    else:
        concatTitle = title + '|' + topic['title'].strip()
    # 如果第一层下没有数据了，那么就把第一层级的名称填加到topics_lists里
    if topic.__contains__('topics') == False:
        topics_lists.append(concatTitle)
    # 如果有，那么就遍历下一个层级......这样就把所有title都写到topics_lists里了
    else:
        for d in topic['topics']:
            handle_path(d, topics_lists, concatTitle)


def handle_title(topics):
    """
    判断是title的类别：所属模块，所属子模块，用例，操作步骤，预期结果，备注
    :param topics: 传入topic的列表
    :return: 经过处理的字典，{'model': '...', 'sub_model': '...', 'case': '...', 'step': '...', 'expect': '...'}

    """
    list = []
    for l in topics:
        dict = {}
        for i in l:
            if "模块" in i.split("_"):
                dict["model"] = i[3:]
            elif "子模块" in i.split("_"):
                dict["sub_model"] = i[4:]
            elif "测试用例:" in i or "测试用例：" in i:
                dict["case"] = i[5:]
            elif "步骤：" in i or "步骤:" in i:
                dict["step"] = i[3:]
            elif "预期：" in i or "预期:" in i:
                dict["expect"] = i[3:]
            elif "备注：" in i or "备注:" in i:
                dict["remark"] = i[3:]
            else:
                try:
                    dict["case"] = dict["case"] + "_" + i
                except:
                    pass
        list.append(dict)
    return list


def handle_topics(topics):
    index = 0
    title_lists = []
    # 遍历第一层级的分支（所属模块）
    for h in topics:
        topics_lists = []
        handle_path(h, topics_lists, '')
        # print("这个是lists,", topics_lists)
        # 取出topics下所有的title放到1个列表中
        for j in range(0, len(topics_lists)):
            title_lists.append(topics_lists[j].split('|'))
    return title_lists


def write_to_temp1(list, excelname):
    """
    把解析的xmind文件写到xls文件里
    使用基础模板
    :param list: 经过处理的字典，格式 [{'model': '...', 'sub_model': '...', 'case': '...', 'step': '...','expect': '.'},{...},...]
    :param excelname: 输出的excel文件路径，文件名固定为：xmind文件名.xls
    :return:
    """
    f = xlwt.Workbook()
    # 生成excel文件
    sheet = f.add_sheet('测试用例', cell_overwrite_ok=True)
    row0 = ['序号', '所属模块', '所属子模块', '测试用例', '执行步骤', '预期结果', '备注']
    # 生成第一行中固定表头内容
    for i in range(0, len(row0)):
        sheet.write(0, i, row0[i])
    # 把title写入xls
    for n in range(0, len(list)):
        # 第一列的序号
        sheet.write(n + 1, 0, n + 1)
    for index, d in enumerate(list):
        # 第二列及之后的用例数据
        try:
            sheet.write(index + 1, 1, d["model"])
        except:
            pass
        try:
            sheet.write(index + 1, 2, d["sub_model"])
        except:
            pass
        try:
            sheet.write(index + 1, 3, d["case"])
        except:
            pass
        try:
            sheet.write(index + 1, 4, d["step"])
        except:
            pass
        try:
            sheet.write(index + 1, 5, d["expect"])
        except:
            pass
        try:
            sheet.write(index + 1, 6, d["remark"])
        except:
            pass
    f.save(excelname)


def write_to_temp_jira(list, excelname):
    """
    把解析的xmind文件写到xls文件里
    使用JIRA模板
    :param list: 经过处理的字典，格式 [{'model': '...', 'sub_model': '...', 'case': '...', 'step': '...','expect': '.'},{...},...]
    :param excelname: 输出的excel文件路径，文件名固定为：xmind文件名.xls
    :return:
    """
    f = xlwt.Workbook()
    # 生成excel文件
    sheet = f.add_sheet('测试用例', cell_overwrite_ok=True)
    row0 = ['Team', 'TCID', 'Pre_Condition', '任务分类-TOC', 'Issue_type', 'Test-Set6','Summary', 'Component', 'Description',
            'Fix_Version', 'Priority', 'Lables', 'Test_type', 'assignee', 'Step', 'Data', 'Expected_Result']
    # 生成第一行中固定表头内容
    for i in range(0, len(row0)):
        sheet.write(0, i, row0[i])
    # 把title写入xls
    for index, d in enumerate(list):
        # 第二列及之后的用例数据
        sheet.write(index + 1, 3, "用例设计")
        sheet.write(index + 1, 4, "Test")
        try:
            sheet.write(index + 1, 6, d["case"])
        except:
            pass

        if d.get("model") and d.get("sub_model"):
            sheet.write(index + 1, 11, d["model"] + '_' + d["sub_model"])
        elif d.get("model") and d.get("sub_model") == None:
            sheet.write(index + 1, 11, d["model"])
        elif d.get("model") == None and d.get("sub_model"):
            sheet.write(index + 1, 11, d["sub_model"])
        else:
            pass
    try:
        sheet.write(index + 1, 12, "Manual")
    except:
        pass
    try:
        sheet.write(index + 1, 14, d["step"])
    except:
        pass
    try:
        sheet.write(index + 1, 16, d["expect"])
    except:
        pass

    f.save(excelname)

untitled.py

Qt5Design生成的图形界面的代码

# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file 'untitled.ui'
#
# Created by: PyQt5 UI code generator 5.15.4
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again.  Do not edit this file unless you know what you are doing.

import os
from convert import handle_xmind,handle_title,handle_topics,write_to_temp1,write_to_temp_jira
from PyQt5 import QtCore, QtWidgets



class Ui_MainWindow(object):
    fileName = ''
    filePath = ''
    template = ''

    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 450)
        MainWindow.setMinimumSize(QtCore.QSize(800, 450))
        MainWindow.setMaximumSize(QtCore.QSize(800, 450))
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.pushButton = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton.setGeometry(QtCore.QRect(410, 150, 341, 31))
        self.pushButton.setObjectName("pushButton")
        self.lineEdit = QtWidgets.QLineEdit(self.centralwidget)
        self.lineEdit.setGeometry(QtCore.QRect(160, 41, 471, 31))
        self.lineEdit.setTabletTracking(False)
        self.lineEdit.setStatusTip("")
        self.lineEdit.setWhatsThis("")
        self.lineEdit.setInputMask("")
        self.lineEdit.setText("")
        self.lineEdit.setFrame(True)
        self.lineEdit.setEchoMode(QtWidgets.QLineEdit.Normal)
        self.lineEdit.setDragEnabled(False)
        self.lineEdit.setReadOnly(True)
        self.lineEdit.setObjectName("lineEdit")
        self.textEdit = QtWidgets.QTextEdit(self.centralwidget)
        self.textEdit.setGeometry(QtCore.QRect(40, 240, 711, 131))
        self.textEdit.setObjectName("textEdit")
        self.pushButton_2 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_2.setGeometry(QtCore.QRect(660, 90, 91, 31))
        self.pushButton_2.setObjectName("pushButton_2")
        self.lineEdit_2 = QtWidgets.QLineEdit(self.centralwidget)
        self.lineEdit_2.setGeometry(QtCore.QRect(160, 90, 471, 31))
        self.lineEdit_2.setInputMask("")
        self.lineEdit_2.setReadOnly(True)
        self.lineEdit_2.setObjectName("lineEdit_2")
        self.label = QtWidgets.QLabel(self.centralwidget)
        self.label.setGeometry(QtCore.QRect(40, 40, 81, 31))
        self.label.setObjectName("label")
        self.label_2 = QtWidgets.QLabel(self.centralwidget)
        self.label_2.setGeometry(QtCore.QRect(40, 90, 81, 31))
        self.label_2.setObjectName("label_2")
        self.label_3 = QtWidgets.QLabel(self.centralwidget)
        self.label_3.setGeometry(QtCore.QRect(40, 210, 81, 31))
        self.label_3.setObjectName("label_3")
        self.pushButton_3 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_3.setGeometry(QtCore.QRect(660, 40, 91, 31))
        self.pushButton_3.setObjectName("pushButton_3")
        self.label_4 = QtWidgets.QLabel(self.centralwidget)
        self.label_4.setGeometry(QtCore.QRect(40, 150, 101, 31))
        self.label_4.setObjectName("label_4")
        self.comboBox = QtWidgets.QComboBox(self.centralwidget)
        self.comboBox.setGeometry(QtCore.QRect(160, 150, 191, 31))
        self.comboBox.setEditable(False)
        self.comboBox.setDuplicatesEnabled(False)
        self.comboBox.setObjectName("comboBox")
        self.comboBox.addItem("基础模板")
        self.comboBox.addItem("JIRA模板")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 21))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

        # 信号，选择xmind文件
        self.pushButton_3.clicked.connect(self.open_file)
        # 信号，选择导出路径
        self.pushButton_2.clicked.connect(self.open_filepath)
        # 信号，把取到的值传给convert槽进行转换用例
        self.template = self.comboBox.currentText()
        self.comboBox.activated.connect(self.select_template)
        self.pushButton.clicked.connect(lambda: self.run(self.fileName, self.filePath, self.template))

    # 槽函数
    def open_file(self):
        fileName, fileType = QtWidgets.QFileDialog.getOpenFileName(self, "选取xmind文件", os.getcwd())
        self.fileName = fileName
        self.lineEdit.setText(fileName)

    def open_filepath(self):
        filePath = QtWidgets.QFileDialog.getExistingDirectory(self, "选取输出文件夹", os.getcwd())
        self.filePath = filePath
        self.lineEdit_2.setText(filePath)

    def select_template(self):
        template = self.comboBox.currentText()
        self.template = template

    def run(self, xmind_file, out_filepath, temp):
        if xmind_file == "":
            QtWidgets.QMessageBox.warning(self, '警告', '源文件路径不能为空', QtWidgets.QMessageBox.Yes, QtWidgets.QMessageBox.Yes)
        elif xmind_file.endswith('.xmind') is False:
            QtWidgets.QMessageBox.warning(self, '警告', '请选择xmind格式的用例', QtWidgets.QMessageBox.Yes, QtWidgets.QMessageBox.Yes)
        else:
            xmind_name = os.path.basename(xmind_file)[:-6]
            # print("xmind文件路径:", xmind_file)
            if out_filepath == '':
                excel_file = os.path.join(os.path.dirname(xmind_file), xmind_name + '.xls')
            else:
                excel_file = os.path.join(out_filepath, xmind_name + '.xls')
            # print("excel输出路径:", excel_file)
            res = handle_topics(handle_xmind(xmind_file))
            if temp == "基础模板":
                write_to_temp1(handle_title(res), excel_file)
            elif temp == "JIRA模板":
                write_to_temp_jira(handle_title(res), excel_file)
            # print("转换完成！")
            self.textEdit.setText(f"xmind文件路径:{xmind_file}" + '\n' + f"excel输出路径:{excel_file}" + '\n' + "转换成功")

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
        self.pushButton.setText(_translate("MainWindow", "开始转换"))
        self.pushButton_2.setText(_translate("MainWindow", "选择"))
        self.label.setText(_translate("MainWindow", "源文件路径："))
        self.label_2.setText(_translate("MainWindow", "输出路径："))
        self.label_3.setText(_translate("MainWindow", "运行结果："))
        self.pushButton_3.setText(_translate("MainWindow", "选择"))
        self.label_4.setText(_translate("MainWindow", "选择输出模板："))
        self.comboBox.setItemText(0, _translate("MainWindow", "基础模板"))
        self.comboBox.setItemText(1, _translate("MainWindow", "JIRA模板"))

run.py

主程序

# !/Desktop/codearea/pytestlearn2/venv/bin/python3.9
"""
@Author:Zhming
@Project:XmindtoExcelGUI 
@File:run
@Software:PyCharm 
@Date:2022/2/27
"""
import sys
from PyQt5 import QtWidgets
from untitled import Ui_MainWindow

class MyWindow(QtWidgets.QMainWindow, Ui_MainWindow):
    def __init__(self, parent=None):
        super(MyWindow, self).__init__(parent)
        self.setupUi(self)
        self.setWindowTitle("Xmind用例转excel")


if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    myWin = MyWindow()
    myWin.show()
    sys.exit(app.exec_())

完整项目可以去Gitee下载
https://gitee.com/zhaim/xmind2-excel.git