实现freemind 转excel方案一

"""
   @author:zhaocuixa
   @date:2024/10/30
   @function: freemind 转excel openpyxl可以对单元格重复写入
"""
# coding : utf-8
import tkinter
import tkinter.filedialog
import os
import xml.etree.ElementTree as ET
import xlwt
from datetime import datetime
from openpyxl import Workbook
# 选freemind文件
def select_file():
    global file_path
    file_path = tkinter.filedialog.askopenfilename(title='选择一个freemind文件', filetypes=[('freemind文件', '.mm')])
    return file_path


# 选Excel生成目录
def select_folder():
    global folder_path
    folder_path = tkinter.filedialog.askdirectory(title='选择一个文件夹')
    return folder_path


# 生成窗口
def create_frame():
    win = tkinter.Tk()
    win.geometry('350x130')
    win.title("选择freemind文件目录")
    file = tkinter.Button(win, text="选择freemind文件", height=2, width=20, fg="blue", bg="gray", command=select_file)
    file.pack()
    folder = tkinter.Button(win, text="选择生成的Excel目录", height=2, width=20, fg="blue", bg="gray", command=select_folder)
    folder.pack()
    excel = tkinter.Button(win, text="生成Excel文件", height=1, width=12, bg="gray", command=win.quit)
    excel.pack()
    win.mainloop()


# 设置excel标题
def set_excel_header():
    col = 1
    header = ['测试用例ID', '标题', '前置条件', '重要程度', '所属模块', '标签', '描述步骤', '预期结果',
              '类型', '终端', '场景类型', '可执行环境', '自动化是否覆盖', '自动化覆盖无法实现原因', '自动化负责人（工号）','期望完成自动化日期']
    for name in header:
        write_excel( 2, col,name)
        col += 1
    writemerge_excel('注(重要)：测试用例ID新增导入时无需填写，如导出之后需将测试用例导入其他模块下，需删除用例ID和用例步骤ID，否则请勿改动')

# 生成用例标题的模块名,格式为模块1__模块2__模块3__
def set_testcase_module(element, testcase_title):
    global g_row
    testcase_title = testcase_title + '__' + element.get('TEXT')
    for child in element:
        set_case(child, testcase_title)
        name = child.get('TEXT')
        if child.find('node') is None and name is not None:
            title_length = len(first_node.get('TEXT'))  # 计算根节点长度，用例标题的模块不必包含根节点
            # print(testcase_title[(4 + title_length):] + '__' + name)
            rnt.append(testcase_title[(4 + title_length):] + '__' + name)
            g_row += 1


# 组成用例
def set_case(element, name):
    global g_row
    if element is not None:
        set_testcase_module(element, name)

# 写入Excel文件
def write_excel(row, col, name):
    ws.cell(row, col, name)
def writemerge_excel(name):
    ws.merge_cells('A1:P1')
    ws['A1'] = name
def writeadd_excel(row, col, name):
    print(row, col)
    tmp = ws.cell(row=row, column=col).value
    print('here',tmp)
    ws.cell(row, col, str(tmp)+'\n'+name)


if __name__ == '__main__':
    rnt = []
    g_row = 1
    file_path = ''
    folder_path = ''
    now = datetime.now()
    file_name = 'freemind2excel_'+ str(now)[:-10].replace(' ','_').replace(':','')+ '.xlsx'
    create_frame()
    print('选择的freemind文件为：' + file_path)
    tree = ET.ElementTree(file=file_path)
    root = tree.getroot()
    first_node = root.find('node')
    wb = Workbook()
    # 激活工作表
    ws = wb.active
    set_excel_header()  # 写表头
    text_content = ''
    set_testcase_module(first_node, text_content)
    ceng = 3  # 划分层级
    api = ''  # 接口
    j = 2  # 目前该写入第几行
    old = ''  # 上一个标题
    index = 1  # 某个用例的第几步
    for i in range(len(rnt)):
        # print(rnt[i])
        tmp = rnt[i].split('__')
        if old != ','.join(tmp[:ceng]):
            j = j+1
            index = 1
            old = ','.join(tmp[:ceng])
            write_excel(j, 2,  ','.join(tmp[:ceng]))
            write_excel(j, 3, api)
            write_excel(j, 4, 'P0')
            write_excel(j, 7,  str(index)+'.'+','.join(tmp[ceng:len(tmp)-1]))
            write_excel(j, 8,  str(index)+'.'+tmp[len(tmp)-1])
            write_excel(j, 9, '功能用例')

        else:
            index = index + 1
            writeadd_excel(j, 7, str(index)+'.'+','.join(tmp[ceng:len(tmp)-1]))
            writeadd_excel(j, 8, str(index)+'.'+tmp[len(tmp)-1])


    wb.save(folder_path + '/' + file_name)
    if not os.path.exists(folder_path + '/' + file_name):
        print('Excel生成失败')
    else:
        print('Excel生成成功，路径为：' + folder_path + '/' + file_name)
        # print('测试用例条数为：' + str(g_row - 1))