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
- For the operation of Xml module, I suggest that ElementTree is preferred. This time, the official automatic ElementTree can be used
- 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.tree.parse(self.xml_file)
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"):
try:
action_list.append("Step" + str(action_count) + ":" + self.data_format(
node_3.text) + "\n")
except:
action_list.append( "Step" + str(action_count) + ":" + "" + "\n")
action_count = action_count + 1
if (node_3.tag == "expectedresults"):
try:
result_list.append("Result" + str(result_count) + ":" + self.data_format(
node_3.text) + "\n")
except:
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"]
else:
pass
self.all_case_list.append(temp_casedict)
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(' ', ' ').replace('>', ''). \
replace('<br />', '').replace("“", "", ).replace("”", "")
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(' ', ' ').replace('>', '').replace('<br />', '')
else:
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"]
ws.append(first_row)
for case in self.all_case_list:
temp_row = [(case["summary"]), case["preconditions"], case["execution_type"], case["importance"],
case["step"],
case["expectResult"]]
if "keywords" in case:
temp_row.append(case["keywords"])
ws.append(temp_row)
self.wb.save(save_path)
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
try:
shutil.rmtree(output_dir)
except:
pass
if not os.path.exists(output_dir):
os.mkdir(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)
e_obj.write_excel(excel_save_path)
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.