"Selfie tutorial 65" Python Testlink use case export tool xml2excel

Case story: next two: Excel 2xml: Python Testlink case import tool
There are imports, there must be exports. Unfortunately, only Xml format use case exports are supported.

We need to convert the test cases in Xml format into the test cases in Excel version for easy reading.

Preparation stage
  1. For the operation of Xml module, I suggest that ElementTree is preferred. This time, the official automatic ElementTree can be used
  2. openpyxl is always the preferred module for Excel operation

Python object oriented class form

Because the code involved in this case is somewhat difficult and relatively long,
Directly in the form of object-oriented classes to model and program design.

Modeling: first imagine a blank world and what kinds of things (nouns) the world needs.
We need two classes. One is the ExcelParser class, which is used to parse Excel to get Excel data,
One is the XMLWriter class, which is used to write the Excel data obtained above into the Xml file.

# coding=utf-8

import os
import re
import shutil
from openpyxl import Workbook
from openpyxl.styles import Alignment
from xml.etree.ElementTree import ElementTree

class XmlReader():
    '''read XML File and get detailed fields of all use cases'''

    def __init__(self, xml_file):
        self.xml_file = xml_file
        self.all_case_list = []
        self.tree = ElementTree()
        self.xmlroot = self.tree.getroot()

    def parse_xml(self):
        '''Parse and finally write all the data self.all_case_lit'''
        if self.xmlroot.tag == "testcases":
            for node_1 in list(self.xmlroot):
                if (node_1.tag == "testcase"):
                    temp_casedict = {}
                    temp_casedict["summary"] = node_1.attrib["name"]
                    for node_2 in list(node_1):
                        if (node_2.tag == "steps"):
                            action_list = []
                            result_list = []
                            action_count = 1
                            result_count = 1
                            for node_3 in node_2.iter():
                                if (node_3.tag == "actions"):
                                        action_list.append("Step" + str(action_count) + ":" + self.data_format(
                                                node_3.text) + "\n")
                                        action_list.append( "Step" + str(action_count) + ":" + "" + "\n")
                                    action_count = action_count + 1
                                if (node_3.tag == "expectedresults"):
                                        result_list.append("Result" + str(result_count) + ":" + self.data_format(
                                                        node_3.text) + "\n")
                                        result_list.append("Result" + str(result_count) + ":" + "" + "\n")
                                    result_count = result_count + 1
                            action_liststr = "".join(action_list)
                            result_list_str = "".join(result_list)
                            temp_casedict["step"] = action_liststr
                            temp_casedict["expectResult"] = result_list_str
                        elif (node_2.tag == "preconditions"):
                            temp_casedict[node_2.tag] = self.data_format_fummary_precondition(node_2.text)
                        elif (node_2.tag == "execution_type"):
                            if (node_2.text == "1"):
                                temp_casedict[node_2.tag] = u"Manual"
                            elif (node_2.text == "2"):
                                temp_casedict[node_2.tag] = u"automatic"
                        elif (node_2.tag == "importance"):
                            if (node_2.text == "1"):
                                temp_casedict[node_2.tag] = "Low"
                            elif (node_2.text == "2"):
                                temp_casedict[node_2.tag] = "Medium"
                            elif (node_2.text == "3"):
                                temp_casedict[node_2.tag] = "High"
                        elif (node_2.tag == "keywords"):
                            for node_3 in node_2.iter():
                                if (node_3.tag == "keyword"):
                                    temp_casedict[node_2.tag] = node_3.attrib["name"]
        return self.all_case_list

    def data_format(self, inputdata):
        '''Filter out (delete) some unnecessary html Characters of'''
        inputdata = inputdata.strip()
        inputdata = inputdata.replace('<p>', '').replace('</p>', '').replace('\n', '').replace('\t', '').replace(
                '</div>', ''). \
            replace('<div>', '').replace('&nbsp;', ' ').replace('&gt;', ''). \
            replace('<br />', '').replace("&ldquo;", "", ).replace("&rdquo;", "")
        return inputdata

    def data_format_fummary_precondition(self, inputdata):
        '''Filter out (delete) some unnecessary html Characters of'''
        if inputdata != None:
            inputdata = inputdata.strip()
            inputdata = inputdata.replace('<p>', '').replace('</p>', '').replace('\n', '').replace('\t', '').replace(
                    '</div>', ''). \
                replace('<div>', '').replace('&nbsp;', ' ').replace('&gt;', '').replace('<br />', '')
            inputdata = ""
        return inputdata

class ExcelWriter():
    '''Get XML Cases and Generate Excel Cases'''

    def __init__(self, all_case_list):
        self.all_case_list = all_case_list
        self.wb = Workbook()

    def write_excel(self, save_path):
        ws = self.wb.active
        alignment = Alignment(horizontal="left", vertical="top", wrap_text=True)
        ws.alignment = alignment
        first_row = ["Use case title", "Preset conditions", "Execution mode", "priority", "testing procedure", "Expected results", "keyword"]
        for case in self.all_case_list:
            temp_row = [(case["summary"]), case["preconditions"], case["execution_type"], case["importance"],
            if "keywords" in case:

if __name__ == '__main__':
    curpath = os.getcwd()
    xml_dir = os.path.join(curpath, "XML_Input") # Input folder
    xml_list = os.listdir(xml_dir)

    output_dir = os.path.join(curpath, "Excel_Output") # Output folder

    if not os.path.exists(output_dir):

    for each_xml in xml_list:
        print("*" * 60)
        print("Processing%s" % each_xml)
        print("*" * 60)
        xml_name, posfix = os.path.splitext(each_xml)
        exch_xml_path = "%s%s%s" % (xml_dir, os.sep, each_xml)

        x_obj = XmlReader(exch_xml_path)
        all_case_list = x_obj.parse_xml()

        e_obj = ExcelWriter(all_case_list)
        excel_save_path = "%s%s%s.xlsx" % (output_dir, os.sep, xml_name)
        print("XML turn Excel Finished and saved to %s" % excel_save_path)

Download the case materials

Including: exported test case xml, Python script
Jump to the official website to download this material
Wusanren products, please feel free to download!

Operation mode and effect video

Go to the official website to view this video

For more and better original articles, please visit the official website: www.zipython.com
Selfie course (Python course of automatic test, compiled by Wu Sanren)
Original link: https://www.zipython.com/#/detail?id=2e0f9c3c677045b1b2e9e81891db9387
You can also follow the wechat subscription number of "wusanren" and accept the article push at any time.

Tags: Python xml Excel

Posted on Thu, 14 May 2020 00:03:33 -0400 by kf