大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

testlink用例导入导出平台小脚本：Excel和xml相互转换

公司启用testlink测试用例平台，发现测试用例的导入导出只支持xml，在testlink上写用例又比较麻烦，效率不高，百度了下，改了源文件测试导入导出直接报错，想着还是自己写个小脚本吧

testlink导出xml在导入

在testlink平台写几条测试用例后导出，然后观察xml文件内容

1. 删除节点不必要的属性 ，各个节点的id属性全部删除，只保留name属性；
2. 删除不必要的节点：node_order、details、externalid 、version、execution_type、estimated_exec_duration、status、is_open、active、execution_type；

xml转Excel

from openpyxl.workbook import Workbook
from xml.dom import minidom
file_name = "首页.xml"
excel = Workbook()
file = file_name.split(".")[0]
excel_file = file + ".xlsx"
dom = minidom.parse(file_name)
root = dom.documentElement
print("root根节点名字：",root.nodeName+"\t"+"root根节点属性name的值：",root.getAttribute('name'))
root_Nodes = root.childNodes    # 获取某个节点下所有子节点，是个列表
rowss = 2
def set_workbook_sheet1_name(sheet_name):
excel_sheets_name = excel.worksheets[0]
excel_sheets_name.title = sheet_name
return excel_sheets_name
def set_sheet_row_init(sheet_name):
row = 1
column =1
for x in range(1,10):
sheet_data_row_1 = set_workbook_sheet1_name(sheet_name)
if x == 1:
sheet_data_row_1.cell(row=row, column=column,value="测试用例集名称")
if x == 2:
sheet_data_row_1.cell(row=row, column=column,value="测试用例名称")
if x == 3:
sheet_data_row_1.cell(row=row, column=column,value="摘要")
if x == 4:
sheet_data_row_1.cell(row=row, column=column,value="前置条件")
if x == 5:
sheet_data_row_1.cell(row=row, column=column,value="重要性")
if x == 6:
sheet_data_row_1.cell(row=row, column=column,value="测试步骤")
if x == 7:
sheet_data_row_1.cell(row=row, column=column,value="预期结果")
if x == 8:
sheet_data_row_1.cell(row=row, column=column,value="实际结果")
if x == 9:
sheet_data_row_1.cell(row=row, column=column,value="备注")
column = column+1
return sheet_data_row_1
def excel_save_close(file_name):
excel.save(file_name)
excel.close()
rows = 2
def atestcase_if_list(testcasts_codes_names,sheet_data_cell,rowss):
for z in range(0, len(testcasts_codes_names)):
# print(testcasts_codes_names)
testcasts_codes_name = testcasts_codes_names[z]
testcasts_codes_name1 = testcasts_codes_name.nodeName
if testcasts_codes_name1 != '#text' and testcasts_codes_name1 == 'summary':
print(rowss,"zhesihi")
sheet_data_cell.cell(row=rowss,column=3,value=testcasts_codes_name.childNodes[0].nodeValue)
print('\n\t\t\t\t', "summary摘要的值：", testcasts_codes_name.childNodes[0].nodeValue)
elif testcasts_codes_name1 != '#text' and testcasts_codes_name1 == 'preconditions':
sheet_data_cell.cell(row=rowss, column=4, value=testcasts_codes_name.childNodes[0].nodeValue)
print('\n\t\t\t\t', "preconditions前提的值：", testcasts_codes_name.childNodes[0].nodeValue)
elif testcasts_codes_name1 != '#text' and testcasts_codes_name1 == 'importance':
sheet_data_cell.cell(row=rowss, column=5, value=testcasts_codes_name.childNodes[0].nodeValue)
print('\n\t\t\t\t', "importance重要性的值：", testcasts_codes_name.childNodes[0].nodeValue)
elif testcasts_codes_name1 != '#text' and testcasts_codes_name1 == 'steps':
steps = testcasts_codes_name.childNodes
for a in range(0, len(steps)):
_steps_name = steps[a]
__steps_name = _steps_name.nodeName
# print(__steps_name)
if __steps_name == "step":
step = _steps_name.childNodes
for j in range(0, len(step)):
step_names = step[j]
step_name = step_names.nodeName
if step_name != '#text' and step_name == 'actions':
sheet_data_cell.cell(row=rowss, column=6, value=step_names.childNodes[0].nodeValue)
print('\n\t\t\t\t\t', "actions测试步骤", step_names.childNodes[0].nodeValue)
elif step_name != '#text' and step_name == 'expectedresults':
sheet_data_cell.cell(row=rowss, column=7, value=step_names.childNodes[0].nodeValue)
print('\n\t\t\t\t\t', "expectedresults预期结果",step_names.childNodes[0].nodeValue)
rowss = rowss + 1
global rows
rows = rowss
print(rows,"zheshi zuihou de rows")
def xml_Du():
for x in range(0,len(root_Nodes)):
root_Nodes_name = root_Nodes[x]
root_Node_sheet_name = root_Nodes_name.nodeName
if (root_Node_sheet_name != '#text' and root_Node_sheet_name == "testsuite") or (root_Node_sheet_name != '#text' and root_Node_sheet_name == "testcase"):
if root_Node_sheet_name == 'testcase':
testcasts_codes_names = root_Nodes_name.childNodes
cast_nodesa_name = root_Nodes_name.getAttribute('name')
atestcase_if_list(testcasts_codes_names)
sheet_testsuite = set_sheet_row_init(cast_nodesa_name)
print("\n\t", "cast--子节点的名字：", root_Node_sheet_name, "sheet--子节点的name属性值：", cast_nodesa_name)
print(rows)
elif root_Node_sheet_name == 'testsuite':
root_Nodes_attributes_name = root_Nodes[x].getAttribute('name')
sheet_testsuite = set_sheet_row_init(root_Nodes_attributes_name)
print("\n\t","sheet--子节点的名字：",root_Node_sheet_name,"sheet--子节点的name属性值：",root_Nodes_attributes_name)
testsuite_nodes_name = root_Nodes_name.childNodes
for y in range(0,len(testsuite_nodes_name)):
testsuite_nodes_case_name = testsuite_nodes_name[y]
testsuite_node_name = testsuite_nodes_case_name.nodeName
if (testsuite_node_name != '#text' and testsuite_node_name == "testsuite") or (testsuite_node_name != '#text' and testsuite_node_name == "testcase"):
if testsuite_node_name == 'testcase':
print("\n\t\t", "用例集--子节点的名字：", testsuite_node_name, "用例集--子节点的name属性值：",testsuite_nodes_name[y].getAttribute('name'))
sheet_testsuite.cell(row=rows, column=1, value=testsuite_nodes_case_name.getAttribute('name'))
sheet_testsuite.cell(row=rows, column=2,
value=testsuite_nodes_case_name.getAttribute('name'))
testcasts_codes_names1 = testsuite_nodes_case_name.childNodes
atestcase_if_list(testcasts_codes_names1,sheet_testsuite,rows)
elif testsuite_node_name == 'testsuite':
sheet_testsuite.cell(row=rows,column=1,value=testsuite_nodes_case_name.getAttribute('name'))
# sheet_testsuite.cell(row=rows, column=2, value=testsuite_nodes_case_name.getAttribute('name'))
tescast_nodes_name = testsuite_nodes_case_name.childNodes
for i in range(0,len(tescast_nodes_name)):
testcasts_code_name = tescast_nodes_name[i]
testcast_code_name = testcasts_code_name.nodeName
if (testcast_code_name != '#text' and testcast_code_name == "testsuite") or (testcast_code_name != '#text' and testcast_code_name == "testcase"):
print("\n\t\t\t","用例名称--子节点的名字：",testcast_code_name,"用例名称--子节点的name属性值：",testcasts_code_name.getAttribute('name'))
testcasts_codes_names13 = testcasts_code_name.childNodes
sheet_testsuite.cell(row=rows, column=2, value=testcasts_code_name.getAttribute('name'))
atestcase_if_list(testcasts_codes_names13,sheet_testsuite,rows)
if __name__ == '__main__':
xml_Du()
# set_sheet_row_init()
excel_save_close(excel_file)

excel 转xml

from xml.dom import minidom
from openpyxl import load_workbook
file_name = "在么测试用例.xlsx"
doc = minidom.Document()
root = doc.createElement("testsuite")
def set_Attribute_root(root_name):
root.setAttribute('name', root_name)
doc.appendChild(root)
def create_Element_node(node_name,roots,attribute_name= None):
node_s = doc.createElement(node_name)
if attribute_name is not None:
print(attribute_name)
node_s.setAttribute('name',attribute_name)
roots.appendChild(node_s)
return node_s
def create_CDATA_Section_node(node_name,roots):
nodes = doc.createCDATASection(node_name)
roots.appendChild(nodes)
return nodes
def actions_CDATA_p(text):
p = "<p>"
p_1 = "</p>"
text =p+text+p_1
return text
def file_names():
return  file_name.split(".")[0]
testcast = load_workbook(file_name)
print("创建root根数的testsuite")
set_Attribute_root(file_names())
def get_sheet_names():
sheet_names = testcast.sheetnames
return sheet_names
def file_name_xml():
xml_name = file_names()+".xml"
return xml_name
def excel_convert_xml():
for i in get_sheet_names():
sheet_testsuite = create_Element_node("testsuite",root,i)
print(i)
cast_sheet_name = testcast[i]
man_row = cast_sheet_name.max_row
max_column = cast_sheet_name.max_column - 2
for x in range(2, man_row + 1):
for y in range(1, max_column + 1):
value = cast_sheet_name.cell(x, y).value
print(value)
if y == 1:
if value is not None:
#写入测试用例集
tests_testsuite = create_Element_node("testsuite",sheet_testsuite,value)
if y == 2:
if value is not None:
# 写入测试用例
setps = 1
test_case = create_Element_node("testcase",tests_testsuite,value)
test_case_steps = create_Element_node("steps", test_case)
if y == 3:
if value is not None:
# 写入摘要
test_case_summary = create_Element_node("summary",test_case)
create_CDATA_Section_node(actions_CDATA_p(value),test_case_summary)
if y == 4:
if value is not None:
# 写入前置条件
test_case_preconditions = create_Element_node("preconditions",test_case)
create_CDATA_Section_node(actions_CDATA_p(value),test_case_preconditions)
if y == 5:
if value is not None:
# 写入重要性
test_case_importance = create_Element_node("importance",test_case)
create_CDATA_Section_node(str(value), test_case_importance)
if y == 6:
if value is not None:
# 写入测试步骤
test_case_steps_step = create_Element_node("step", test_case_steps)
test_case_steps_step_number = create_Element_node("step_number",test_case_steps_step)
create_CDATA_Section_node(str(setps), test_case_steps_step_number)
test_case_steps_step_actions = create_Element_node("actions",test_case_steps_step)
create_CDATA_Section_node(actions_CDATA_p(value), test_case_steps_step_actions)
setps= setps+1
if y == 7:
if value is not None:
# 写入预期结果
test_case_steps_step_expectedresults = create_Element_node("expectedresults", test_case_steps_step)
create_CDATA_Section_node(actions_CDATA_p(value), test_case_steps_step_expectedresults)
with open(file_name_xml(), "w", encoding="utf-8") as f:
excel_convert_xml()
doc.writexml(f, indent='', addindent='\t', newl='\n', encoding="utf-8")

Excel格式如下