案例故事： Testlink是我们常用的用例管理工具，很多公司其实都在用，
Testlink 支持制定测试计划，支持多人在线同时管理维护/执行测试用例，自动生成测试报告等。
我个人也非常非常不推荐Excel线下管理测试用例，
但是官方提供的Testlink版本，是不支持Excel导入的，只能进行Xml导入。
没这个Excel导入功能，确实有点可惜了,
但是秉着Python是万能的，只是将Excel转Xml本也不是什么难事。
准备阶段
- 操作Excel的模块，一直首选openpyxl, 直接pip install openpyxl即可
- 操作Xml的模块，我建议首选ElementTree, 但是需要修改其源代码，原因如下3，4步。
- 根据Testlink官方提供的文档，如果单独一条用例，其xml如下，看着晕乎，别怂，就是干！
<?xml version='1.0' encoding='utf-8'?>
<testcases>
<testcase internalid="1" name="VTS测试——arm64-v8a VtsFastbootVerification">
<externalid />
<version>
<![CDATA[1]]>
</version>
<summary>
<![CDATA[VTS测试——arm64-v8a VtsFastbootVerification]]>
</summary>
<preconditions>
<![CDATA[Android设备已经通过USB连接上Ubuntu系统]]>
</preconditions>
<execution_type>
<![CDATA[2]]>
</execution_type>
<importance>
<![CDATA[1]]>
</importance>
<status>
<![CDATA[7]]>
</status>
<steps>
<step>
<step_number>
<![CDATA[1]]>
</step_number>
<actions>
<![CDATA[Step1: 运行vts-tradefed进入vts控制台]]>
</actions>
<expectedresults>
<![CDATA[Result1: 成功进入vts控制台]]>
</expectedresults>
<execution_type>
<![CDATA[2]]>
</execution_type>
</step>
<step>
<step_number>
<![CDATA[2]]>
</step_number>
<actions>
<![CDATA[Step2: 执行run vts -m VtsFastbootVerification]]>
</actions>
<expectedresults>
<![CDATA[Result2: 运行结果Pass，vts测试通过]]>
</expectedresults>
<execution_type>
<![CDATA[2]]>
</execution_type>
</step>
</steps>
<keywords>
<keyword name="Android 9">
<notes>
<![CDATA[]]>
</notes>
</keyword>
</keywords>
</testcase>
</testcases>
4.由以上Xml可以看到，有一个很奇怪的![CDATA[**]]，
被这个标记所包含的内容将表示为纯文本，不应被XML解析器进行解析，
但是这种这个标记，Python自带的ElementTree无法正常生成，
于是我多番研究后，修改了ElementTree的源代码，增加了901，902行，
可以到本素材案例处下载就是etree这个文件夹，
本案例import的etree可以理解成第三方的模块，非官方的ElementTree模块。
5. 我们需要设定测试用例Excel模板如下：
6. 我们本次需要考虑实现批量转换多个Excel, 输入是Excel_Input，
运行excel2xml.py后输出是XML_Output, 其文件目录如下：
Python面向对象类形式
由于本案例涉及的代码有些许难度，且相对较长，
直接以面向对象类的形式来进行建模及程序设计。
建模：先设想有这么一个空白的世界，这个世界需要哪些类型的事物（名词）。
我们需要两个类，一个是ExcelReader类用于读取Excel获取Cases数据，
一个是XMLWriter类，用于将以上获取的Excel数据写入Xml文件里去。
# coding=utf-8
import os
import re
import shutil
from openpyxl import load_workbook
from etree.ElementTree import ElementTree, Element, SubElement
SUMMARY_COL = 1 # 用例标题
PRECONDITION_COL = 2 # 预置条件
EXECUTETYPE_COL = 3 # 用例的执行方式,手动和自动两种
IMPORTANCE_COL = 4 # 用例的优先级
STEPS_COL = 5 # 用例的执行步骤
EXCEPTED_RESULT_COL = 6 # 用例的预期结果
KEYWORD_COL = 7 # 用例所属的项目
class ExcelReader(object):
"""Read Excel file and get all cases contents"""
def __init__(self, excel_file):
self.excel_file = excel_file
def __parse_steps(self, steps_str):
"""需要解析切割Step1:XXXXStep2:XXXXStep3:XXXXX并获取各执行步骤Step的具体文本"""
steps_str = steps_str + " "
new_steps_list = []
number_list = []
steps_count = 1
for i in range(1, 20):
if ("Step%s" % i in steps_str):
steps_count = i
else:
break
for x in range(1, steps_count + 1):
number_list.append(int(steps_str.find("Step%s" % x)))
number_list.append(-1)
for j in range(0, len(number_list) - 1):
new_steps_list.append(steps_str[number_list[j]:number_list[j + 1]])
return new_steps_list
def __parse_results(self, result_str):
"""需要解析切割Result1:XXXXResult2:XXXXResult3:XXXXX并获取各预期结果Result的具体文本"""
result_str = result_str + " "
new_result_list = []
number_list = []
steps_count = 1
for i in range(1, 20):
if ("Result%s" % i in result_str):
steps_count = i
else:
break
for x in range(1, steps_count + 1):
number_list.append(int(result_str.find("Result%s" % x)))
number_list.append(-1)
for j in range(0, len(number_list) - 1):
new_result_list.append(result_str[number_list[j]:number_list[j + 1]])
return new_result_list
def get_all_cases(self):
"""读取到Excel所有的测试用例并写入一个列表存储起来 """
all_case_list = []
excel = load_workbook(self.excel_file)
_, excel_name = os.path.split(self.excel_file)
sheet = excel.active
max_rows = sheet.max_row
for row_num in range(2, max_rows):
print("正在处理%s文件的第%s行" % (excel_name, row_num))
casedict = {}
summary = sheet.cell(row=row_num, column=SUMMARY_COL).value
# print(summary)
if summary:
precondition = sheet.cell(row=row_num, column=PRECONDITION_COL).value
execution_type = sheet.cell(row=row_num, column=EXECUTETYPE_COL).value
importance = sheet.cell(row=row_num, column=IMPORTANCE_COL).value
steps = sheet.cell(row=row_num, column=STEPS_COL).value
excepted_results = sheet.cell(row=row_num, column=EXCEPTED_RESULT_COL).value
keyword = sheet.cell(row=row_num, column=KEYWORD_COL).value
if keyword == None:
keyword = ""
casedict["internalid"] = "1"
casedict["summary"] = summary
casedict["status"] = "7"
casedict["preconditions"] = precondition
casedict["keyword"] = keyword
if (importance == "" or importance == None):
print(u"格式错误, 第%s行, \"优先级\"列, 不能为空!" % row_num)
return None
else:
importance = importance.strip()
importance = importance.capitalize() # 首字母大写
if (importance == "Medium" or importance == "M"):
casedict["importance"] = "2"
elif (importance == "High" or importance == "H"):
casedict["importance"] = "3"
elif (importance == "Low" or importance == "L"):
casedict["importance"] = "1"
else:
print(u"优先级错误, 第%s行, \"优先级\"列, 必须是High, Medium, Low 或者H, M, L!" % row_num)
return None
if (execution_type != '手动' and execution_type != "自动" and execution_type != "手工"):
print(u"格式错误, 第%s行, \"执行方式\"列, 必须为\"手动\"或\"自动\"!" % row_num)
return None
else:
if (execution_type == u"手动"):
casedict["execution_type"] = "1"
else:
casedict["execution_type"] = "2"
if ("Step1" not in steps):
print(u"格式错误, 第%s行, \"测试步骤\"列,必须以Step1:, Step2:, Step3:...等进行格式化!" % row_num)
return None
else:
steps_list = self.__parse_steps(steps)
for i in range(1, len(steps_list) + 1):
casedict["step" + str(i)] = steps_list[i - 1]
if not (re.match(r".*Result.*", excepted_results)):
print(u"格式错误, 第%s行, \"期望结果\"列,必须以Result1:, Result2:, Result3:...等进行格式化!" % row_num)
return None
else:
result_list = self.__parse_results(excepted_results)
for i in range(1, len(result_list) + 1):
casedict["result" + str(i)] = result_list[i - 1]
all_case_list.append(casedict)
else:
break
# print(allcase_list)
return all_case_list
class XmlWriter():
'''Write to XML'''
def __init__(self, all_cases_list, save_path):
self.all_cases_list = all_cases_list
self.save_path = save_path
def write_xml(self):
xml_file = ElementTree()
testcases_node = Element("testcases")
xml_file._setroot(testcases_node)
for eachcase in self.all_cases_list:
testcase_node = Element("testcase", {"internalid": eachcase["internalid"], "name": eachcase["summary"]})
try:
SubElement(testcase_node, "externalid").append(CDATA(eachcase["externalid"]))
except:
pass
try:
SubElement(testcase_node, "version").append(CDATA(eachcase["version"]))
except:
pass
SubElement(testcase_node, "summary").append(CDATA(eachcase["summary"]))
SubElement(testcase_node, "preconditions").append(CDATA(eachcase["preconditions"]))
SubElement(testcase_node, "execution_type").append(CDATA(eachcase["execution_type"]))
SubElement(testcase_node, "importance").append(CDATA(eachcase["importance"]))
SubElement(testcase_node, "status").append(CDATA(eachcase["status"]))
stepsnode = SubElement(testcase_node, "steps")
for i in range(1, 20):
try:
step = eachcase["step" + str(i)]
result = eachcase["result" + str(i)]
stepnode = SubElement(stepsnode, "step")
SubElement(stepnode, "step_number").append(CDATA(str(i)))
SubElement(stepnode, "actions").append(CDATA(self.__remove_step_num(step)))
SubElement(stepnode, "expectedresults").append(CDATA(self.__remove_result_num(result)))
SubElement(stepnode, "execution_type").append(CDATA(eachcase["execution_type"]))
except:
break
try:
keywords_node = SubElement(testcase_node, "keywords")
keyword_node = SubElement(keywords_node, "keyword", {"name": eachcase["keyword"]})
SubElement(keyword_node, "notes").append(CDATA(""))
except:
pass
testcases_node.append(testcase_node)
self.__indent(testcases_node)
xml_file.write(self.save_path, encoding="utf-8", xml_declaration=True)
return xml_file
def __remove_step_num(self, text=None):
"""# 剔除Step ：字符"""
step_text = re.sub(r"Step\s+:", "", text)
return step_text
def __remove_result_num(self, text=None):
"""# 剔除Result ：字符"""
result_text = re.sub(r"Result\s+:", "", text)
return result_text
def __indent(self, elem, level=0):
i = "\n" + level * " "
if len(elem):
if not elem.text or not str(elem.text).strip():
elem.text = i + " "
for e in elem:
# print e
self.__indent(e, level + 1)
if not e.tail or not e.tail.strip():
e.tail = i
if level and (not elem.tail or not elem.tail.strip()):
elem.tail = i
return elem
def CDATA(text=None): # 为了配合显示xml里的CDATA格式化，本处全部采用大写形式。
"""生成CDATA标签相关xml数据"""
element = Element("CDATA")
element.text = text
return element
if __name__ == '__main__':
curpath = os.getcwd()
excel_folder = os.path.join(curpath, "Excel_Input")
excel_list = os.listdir(excel_folder)
xml_folder = os.path.join(curpath, "XML_Output")
# 先删除之前的老的XML_Output文件夹
try:
shutil.rmtree(xml_folder)
except:
pass
if not os.path.exists(xml_folder):
os.mkdir(xml_folder)
# 批量逐个解析Excel
for each_excel in excel_list:
print("*" * 60)
print("正在处理%s" % each_excel)
print("*" * 60)
e_obj = ExcelReader("%s%s%s" % (excel_folder, os.sep, each_excel))
all_cases_list = e_obj.get_all_cases()
excel_name, posfix = os.path.splitext(each_excel)
x_obj = XmlWriter(all_cases_list, "%s%s%s.xml" % (xml_folder, os.sep, excel_name))
x_obj.write_xml()
print("\nExcel to XML全部处理完毕! XML生成到了XML_Output文件夹!")
os.system("pause")
本案例素材下载
包括：测试用例模板，etree包（含ElementTree），Python脚本
跳转到自拍教程官网下载
武散人出品，请放心下载！
Testlink导入效果
跳转到官网查看Excel转XML，并导入Testlink的视频。
更多更好的原创文章，请访问官方网站：www.zipython.com
自拍教程（自动化测试Python教程，武散人编著）
原文链接：https://www.zipython.com/#/detail?id=d5a9c3981b4b4d8ab560a09c428c39c1
也可关注“武散人”微信订阅号，随时接受文章推送。
