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,只需要通过其他标签区分就行。