python 之将xmind转为excel用例文件
1.xmind文件模板如下所示（最后一个子级为预置条件）
2.excel用例模板
3.获取xmind文件数据并转成字典形式
from xmindparser import xmind_to_dict #xmind_to_dict可读取xmind文件并转成字典形式展示 def get_xmind(filename1): """ 获取思维导图数据 :return: """ data = xmind_to_dict(filename1)[0]["topic"] return data if __name__ == '__main__': print(get_xmind(r"E:\集成资料\测试项目\项目资料\用例模板.xmind"))
"D:\Program Files\Python\Python37-32\python.exe" E:/pythonproject/work_pro/complete_case_export.py
{'title': '用例模板', 'topics': [{'title': '登录', 'topics': [{'title': '成功登录', 'makers': ['priority-1'], 'topics': [{'title': '点击账号输入框输入账号', 'topics': [{'title': '点击密码输入框输入密码', 'topics': [{'title': '点击提交菜单', 'topics': [{'title': '登录成功，进入到系统首页'}]}]}]}]}, {'title': '账号为空', 'makers': ['priority-3'], 'topics': [{'title': '不输入账号\r\n点击密码输入框输入密码\r\n点击提交菜单', 'topics': [{'title': '登录失败，提示账号密码错误'}]}]}, {'title': '密码为空', 'makers': ['priority-3'], 'topics': [{'title': '点击账号输入框输入账号\r\n不输入密码\r\n点击提交菜单', 'topics': [{'title': '登录失败，提示账号密码错误'}]}]}, {'title': '账号错误', 'makers': ['priority-3'], 'topics': [{'title': '点击账号输入框输入错误的账号', 'topics': [{'title': '点击密码输入框输入密码', 'topics': [{'title': '点击提交菜单', 'topics': [{'title': '登录失败，提示账号密码错误'}]}]}]}]}, {'title': '密码错误', 'makers': ['priority-3'], 'topics': [{'title': '点击账号输入框输入账号', 'topics': [{'title': '点击密码输入框输入错误的密码', 'topics': [{'title': '点击提交菜单', 'topics': [{'title': '登录失败，提示账号密码错误'}]}]}]}]}]}, {'title': '设备管理', 'topics': [{'title': '设备列表', 'topics': [{'title': '列表展示', 'makers': ['priority-1'], 'topics': [{'title': '查看列表数据展示是否与实际一致', 'topics': [{'title': '列表字段包含：设备名称等'}]}]}, {'title': '组合搜索', 'makers': ['priority-2'], 'topics': [{'title': '输入设备名称、设备编号、寄存器号搜索', 'topics': [{'title': '搜索出的结果数据同时满足以上条件'}]}]}]}, {'title': '设备新增', 'topics': [{'title': '成功新增设备', 'makers': ['priority-1'], 'topics': [{'title': '查看列表数据展示是否与实际一致', 'topics': [{'title': '列表字段包含：设备名称等'}]}]}, {'title': '必填项不输入时新增', 'makers': ['priority-4'], 'topics': [{'title': '输入设备名称、设备编号、寄存器号搜索', 'topics': [{'title': '搜索出的结果数据同时满足以上条件'}]}]}]}, {'title': '设备删除', 'topics': [{'title': '删除不在使用设备', 'makers': ['priority-1'], 'topics': [{'title': '查看列表数据展示是否与实际一致', 'topics': [{'title': '列表字段包含：设备名称等'}]}]}, {'title': '删除已在使用的设备', 'makers': ['priority-3'], 'topics': [{'title': '输入设备名称、设备编号、寄存器号搜索', 'topics': [{'title': '搜索出的结果数据同时满足以上条件'}]}]}]}, {'title': '设备编辑', 'topics': [{'title': '成功编辑设备', 'makers': ['priority-1'], 'topics': [{'title': '查看列表数据展示是否与实际一致', 'topics': [{'title': '列表字段包含：设备名称等'}]}]}, {'title': '编辑正在使用的设备', 'makers': ['priority-3'], 'topics': [{'title': '输入设备名称、设备编号、寄存器号搜索', 'topics': [{'title': '搜索出的结果数据同时满足以上条件'}]}]}]}]}]}
Process finished with exit code 0
4.用代码实现excel用例模板
def __init__(self, filename1, filename2): """ 数据初始化 :param filename: :param data: """ self.str1 = "" self.str2 = "" self.step = "" self.i = 1 self.k = 1 self.data = get_xmind(filename1) if os.path.isfile(filename2): self.filename = filename2 else: self.filename = filename2 + "\\" + self.data["title"] + ".xlsx" self.wb = openpyxl.Workbook(self.filename) for s in self.data['topics']: self.wb.create_sheet(s["title"]) self.wb.save(self.filename) self.title = ["所属模块", "相关研发需求", "用例标题", "前置条件", "步骤", "预期", "关键词", "优先级", "用例类型", "适用阶段", "用例状态"] self.work = openpyxl.load_workbook(self.filename) self.sheetnames = self.work.sheetnames for name in self.sheetnames: self.sheet = self.work[name] for i in range(1, len(self.title) + 1): self.sheet.cell(1, i).value = self.title[i - 1] # 写入表头 self.sheet.cell(1, i).font = Font(bold=True, color=colors.BLACK) # 将表头字体设置为黑色，加粗 self.sheet.cell(1, i).fill = PatternFill(fill_type="solid", fgColor="003399") # 将表头填充颜色 row_name = get_column_letter(i) # 将数字编号转为字母编号 if i in [3, 5, 6]: self.sheet.column_dimensions[row_name].width = 40 # 设置宽度 else: self.sheet.column_dimensions[row_name].width = 20 self.work.save(self.filename)
5.将获取的数据按规则写入excel
def set_value(self, infos): """ 获取用例并写入excel :return: """ global work , sheet, title, lever for info in infos["topics"]: if info["title"] in self.sheetnames: self.i = 1 workbook = self.open_sheet(info["title"]) sheet = workbook[1] work = workbook[0] if info.get("topics"): if info.get("makers"): self.k = 0 title = info["title"] lever = info["makers"][0].split("-")[1] self.str1 = infos["title"] else: if self.k == 0: self.step += info["title"] + "\n" if isinstance(info["topics"], list): self.set_value(info) else: self.i += 1 data = "【" + self.str1 + "】" + title sheet.cell(self.i, 3).value = data sheet.cell(self.i, 5).value = self.step.rstrip("\n") sheet.cell(self.i, 6).value = info["title"] sheet.cell(self.i, 8).value = lever sheet.cell(self.i, 9).value = "功能测试" sheet.cell(self.i, 10).value = "功能测试阶段" sheet.cell(self.i, 11).value = "正常" sheet.cell(self.i, 3).alignment = Alignment(wrapText=True) sheet.cell(self.i, 5).alignment = Alignment(wrapText=True) sheet.cell(self.i, 6).alignment = Alignment(wrapText=True) self.step = "" self.k += 1 work.save(self.filename)
6.为了方便传参操作，引入easygui，通过界面传入
filename1 = g.fileopenbox(msg="请选择需要转换的xmind文件", title="文件", filetypes=["*.xmind"], multiple=False, default=r"E:\\") filename2 = g.diropenbox(msg="请选择保存的文件路径", title="文件", default=r"E:\\")
7.完整实现代码
import openpyxl from openpyxl.styles import Font from openpyxl.styles import colors from openpyxl.styles import PatternFill from openpyxl.styles import Alignment from openpyxl.utils import get_column_letter # 将数字转为字母 from xmindparser import xmind_to_dict import os import easygui as g def get_xmind(filename1): """ 获取思维导图数据 :return: """ data = xmind_to_dict(filename1)[0]["topic"] return data class CaseXmind(): """ 思维导图转用例 """ def __init__(self, filename1, filename2): """ 数据初始化 :param filename: :param data: """ self.str1 = "" self.str2 = "" self.step = "" self.i = 1 self.k = 1 self.data = get_xmind(filename1) if os.path.isfile(filename2): self.filename = filename2 else: self.filename = filename2 + "\\" + self.data["title"] + ".xlsx" self.wb = openpyxl.Workbook(self.filename) for s in self.data['topics']: self.wb.create_sheet(s["title"]) self.wb.save(self.filename) self.title = ["所属模块", "相关研发需求", "用例标题", "前置条件", "步骤", "预期", "关键词", "优先级", "用例类型", "适用阶段", "用例状态"] self.work = openpyxl.load_workbook(self.filename) self.sheetnames = self.work.sheetnames for name in self.sheetnames: self.sheet = self.work[name] for i in range(1, len(self.title) + 1): self.sheet.cell(1, i).value = self.title[i - 1] # 写入表头 self.sheet.cell(1, i).font = Font(bold=True, color=colors.BLACK) # 将表头字体设置为黑色，加粗 self.sheet.cell(1, i).fill = PatternFill(fill_type="solid", fgColor="003399") # 将表头填充颜色 row_name = get_column_letter(i) # 将数字编号转为字母编号 if i in [3, 5, 6]: self.sheet.column_dimensions[row_name].width = 40 # 设置宽度 else: self.sheet.column_dimensions[row_name].width = 20 self.work.save(self.filename) def open_sheet(self, name): """ 打开指定工作表 :param name: :return: """ work = openpyxl.load_workbook(self.filename) sheet = work[name] return work, sheet def set_value(self, infos): """ 获取用例并写入excel :return: """ global work , sheet, title, lever for info in infos["topics"]: if info["title"] in self.sheetnames: self.i = 1 workbook = self.open_sheet(info["title"]) sheet = workbook[1] work = workbook[0] if info.get("topics"): if info.get("makers"): self.k = 0 title = info["title"] lever = info["makers"][0].split("-")[1] self.str1 = infos["title"] else: if self.k == 0: self.step += info["title"] + "\n" if isinstance(info["topics"], list): self.set_value(info) else: self.i += 1 data = "【" + self.str1 + "】" + title sheet.cell(self.i, 3).value = data sheet.cell(self.i, 5).value = self.step.rstrip("\n") sheet.cell(self.i, 6).value = info["title"] sheet.cell(self.i, 8).value = lever sheet.cell(self.i, 9).value = "功能测试" sheet.cell(self.i, 10).value = "功能测试阶段" sheet.cell(self.i, 11).value = "正常" sheet.cell(self.i, 3).alignment = Alignment(wrapText=True) sheet.cell(self.i, 5).alignment = Alignment(wrapText=True) sheet.cell(self.i, 6).alignment = Alignment(wrapText=True) self.step = "" self.k += 1 work.save(self.filename) if __name__ == '__main__': filename1 = g.fileopenbox(msg="请选择需要转换的xmind文件", title="文件", filetypes=["*.xmind"], multiple=False, default=r"E:\\") filename2 = g.diropenbox(msg="请选择保存的文件路径", title="文件", default=r"E:\\") CaseXmind(filename1, filename2).set_value(get_xmind(filename1))
所属模块、相关研发需求、预置条件等字段都可以内置在xmind，只需要通过其他标签区分就行。
