testlink用例导入导出平台小脚本：Excel和xml相互转换[通俗易懂]

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)

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")