背景
由于习惯使用xmind梳理test point，但有些场景描述较长且不是每个人都安装xmind，为了方便于是写了个xmind转Excel格式的Python脚本。
效果
xmind层级关系
xmind格式test point.png
转为Excel样式
summary.png
Test point1.png
Test point2.png
脚本代码及解释
- 主要使用到xmindparser, json, xlwt这三个依赖包，思路是解释xmind文件得到一个json, 然后解释json对象，并写入到Excel文件。
- 实际使用中可根据需要调整样式或者需要的列，献上源码
# coding=utf-8
import time
from xmindparser import xmind_to_dict
import json
import xlwt
def writerows(worksheet):
sizes = [15, 20, 40, 50, 70, 70, 8, 8, 8, 10, 20]
columns = ["No.", 'Test title', 'Description', 'Prerequisite', 'Test step', 'Expect result',
'Level', 'Type', 'Result', 'Tracker', 'Comment'] # 写成excel表格用例的要素
for col in range(len(columns)):
worksheet.write(1, col, columns[col], title_styles())
widths(worksheet, col, size=sizes[col])
heights(worksheet, 1, 2)
def title_styles():
"""设置单元格标题样式的基础方法"""
style = xlwt.XFStyle()
style.pattern = patterns(46)
style.font = fonts('Times New Roman', True)
style.borders = borders()
dicts = {"horz": "CENTER", "vert": "CENTER"}
style.alignment = alignments(**dicts)
style.borders = borders()
return style
def normalstyles():
"""设置单元格标题样式的基础方法"""
style = xlwt.XFStyle()
style.font = fonts('Arial', False, False, False, "black", 10)
style.borders = borders()
dicts = {"horz": "LEFT", "vert": "CENTER"}
style.alignment = alignments(**dicts)
style.borders = borders()
return style
def borders(status=1):
"""设置单元格的边框
细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13"""
border = xlwt.Borders()
border.left = status
border.right = status
border.top = status
border.bottom = status
return border
def heights(worksheet, line, size=4):
"""设置单元格的高度"""
worksheet.row(line).height_mismatch = True
worksheet.row(line).height = size * 256
def widths(worksheet, line, size=11):
"""设置单元格的宽度"""
worksheet.col(line).width = size * 256
def alignments(**kwargs):
"""设置单元格的对齐方式
status有两种：horz（水平），vert（垂直）
horz中的direction常用的有：CENTER（居中）,DISTRIBUTED（两端）,GENERAL,CENTER_ACROSS_SEL（分散）,RIGHT（右边）,LEFT（左边）
vert中的direction常用的有：CENTER（居中）,DISTRIBUTED（两端）,BOTTOM(下方),TOP（上方）"""
alignment = xlwt.Alignment()
if "horz" in kwargs.keys():
alignment.horz = eval(f"xlwt.Alignment.HORZ_{kwargs['horz'].upper()}")
if "vert" in kwargs.keys():
alignment.vert = eval(f"xlwt.Alignment.VERT_{kwargs['vert'].upper()}")
alignment.wrap = 1 # 设置自动换行
return alignment
def fonts(name='Times New Roman', bold=False, underline=False, italic=False, colour='black', height=11):
"""设置单元格中字体的样式
默认字体为宋体，不加粗，没有下划线，不是斜体，黑色字体"""
font = xlwt.Font()
# 字体
font.name = name
# 加粗
font.bold = bold
# 下划线
font.underline = underline
# 斜体
font.italic = italic
# 颜色
font.colour_index = xlwt.Style.colour_map[colour]
# 大小
font.height = 20 * height
return font
def patterns(colors=19):
"""设置单元格的背景颜色，该数字表示的颜色在xlwt库的其他方法中也适用，默认颜色为白色
0 = Black, 1 = White,2 = Red, 3 = Green, 4 = Blue,5 = Yellow, 6 = Magenta, 7 = Cyan,
16 = Maroon, 17 = Dark Green,18 = Dark Blue, 19 = Dark Yellow ,almost brown), 20 = Dark Magenta,
21 = Teal, 22 = Light Gray,23 = Dark Gray, the list goes on..."""
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = colors
return pattern
def main(xmind_file):
xm = xmind_to_dict(xmind_file)[0]['topic']
# indent is format json, ensure_ascii not show Chinese, not show ASCII code
print(json.dumps(xm, indent=2, ensure_ascii=False))
workbook = xlwt.Workbook(encoding='utf-8') # 创建workbook对象
# Add Summary
summary_sheet = workbook.add_sheet("Summary")
widths(summary_sheet, 0, 30)
summary_sheet.write(0, 0, "Test Point List", title_styles())
for i in range(len(xm["topics"])):
test_suit = xm["topics"][i]
summary_sheet.write(i + 1, 0, str(i + 1).__add__(". ").__add__(test_suit["title"]), normalstyles())
worksheet = workbook.add_sheet(test_suit["title"], cell_overwrite_ok=True) # 创建工作表，并设置可以重写单元格内容
worksheet.write(0, 0, "Comment", title_styles())
worksheet.write_merge(0, 0, 1, 5, test_suit["note"])
heights(worksheet, 0, 3)
writerows(worksheet)
x = 2 # 写入数据的当前行数
for j in range(len(test_suit["topics"])):
test_point_title = test_suit["topics"][j]
for k in range(len(test_point_title["topics"])):
test_prerequisite = test_point_title["topics"][k]
for n in range(len(test_prerequisite["topics"])):
test_step = test_prerequisite["topics"][n]
for m in range(len(test_step["topics"])):
test_except = test_step["topics"][m]
for q in range(len(test_except["topics"])):
test_type_and_tracker = test_except["topics"][q]
worksheet.write(x, 0, x - 1, normalstyles()) # No.
worksheet.write(x, 1, test_point_title["title"], normalstyles()) # 执行title
worksheet.write(x, 2, test_point_title["note"], normalstyles()) # 执行prerequisite
worksheet.write(x, 3, test_prerequisite["title"], normalstyles()) # 执行prerequisite
worksheet.write(x, 4, test_step["title"], normalstyles()) # 执行步骤
worksheet.write(x, 5, test_except["title"], normalstyles()) # 预期结果
level = "High"
if test_point_title["makers"][0] == "priority-2":
level = "Medium"
if test_point_title["makers"][0] == "priority-3":
level = "Low"
worksheet.write(x, 6, level, normalstyles()) # Level
# testtype = "UT"
# if test_point_title["makers"][1] == "tag-orange":
# level = "CT"
# if test_point_title["makers"][1] == "tag-dark-blue":
# level = "FT"
# if test_point_title["makers"][1] == "tag-green":
# level = "MT"
# testtype = "UT"
# if test_type_and_tracker["makers"][1] == "tag-orange":
# # level = "CT"
# if test_point_title["makers"][1] == "tag-dark-blue":
# level = "FT"
# if test_point_title["makers"][1] == "tag-green":
# level = "MT"
optype = test_type_and_tracker["title"].split(":", 1)[0]
worksheet.write(x, 7, optype, normalstyles()) # Type
worksheet.write(x, 8, "", normalstyles()) # Result
worksheet.write(x, 9, "", normalstyles()) # Tracker
worksheet.write(x, 10, "", normalstyles()) # Comment
x += 1
# time.sleep(1)
print(x)
# print(test_point_title)
workbook.save(xm["title"] + ".xls") # xls名称取xmind主题名称
if __name__ == "__main__":
main("../testPointFile/TestPointSample.xmind")
