前面文章记录了 testlink 的安装方法(),由于 testlink 仅支持 xml 格式的用例导入,研究了下 excel 转 xml 的方法,
从网上其他网友那里借用了部分代码,自己又补充修改了下,供大家参考,使用的时候要在 PC 上安装 python 2.7。
一、代码(有两个 py 文件):
easy_excel.py:
- 1 # coding:utf-8
- 2 import os
- 3 import sys
- 4 reload(sys)
- 5 sys.setdefaultencoding("utf-8")
- 6
- 7 from easy_excel import easy_excel
- 8 class operate():
- 9 def __init__(self, ExcelFileName, SheetName):
- 10 self.excelFile = ExcelFileName + '.xls'
- 11 self.excelSheet = SheetName
- 12 self.temp = easy_excel(self.excelFile)
- 13 self.dic_testlink = {}
- 14 self.row_flag = 3
- 15 self.testsuite = self.temp.getCell(self.excelSheet, 2, 1)
- 16 self.dic_testlink[self.testsuite] = {"node_order": "13", "details": "", "testcase": []}
- 17 self.content = ""
- 18 self.content_list = []
- 19
- 20 def xlsx_to_dic(self, SheetName):
- 21 while True:
- 22 # print 'loop1'
- 23 # list_testcase = dic_testlink[testsuite].["testcase"]
- 24
- 25 testcase = {"name": "", "node_order": "100", "externalid": "", "version": "1", "summary": "",
- 26 "preconditions": "", "execution_type": "1", "importance": "3", "steps": [], "keywords": "P1"}
- 27 testcase["name"] = self.temp.getCell(self.excelSheet, self.row_flag, 1)
- 28 testcase["summary"] = self.temp.getCell(self.excelSheet, self.row_flag, 3)
- 29 testcase["preconditions"] = self.temp.getCell(self.excelSheet, self.row_flag, 4)
- 30 execution_type = self.temp.getCell(self.excelSheet, self.row_flag, 7)
- 31 if execution_type == "自动":
- 32 testcase["execution_type"] = 2
- 33 # print self.temp.getCell('Sheet1',self.row_flag,3)
- 34 step_number = 1
- 35 testcase["keywords"] = self.temp.getCell(self.excelSheet, self.row_flag, 2)
- 36 # print testcase["keywords"]
- 37 while True:
- 38 # print 'loop2'
- 39 step = {"step_number": "", "actions": "", "expectedresults": "", "execution_type": ""}
- 40 step["step_number"] = step_number
- 41 step["actions"] = self.temp.getCell(self.excelSheet, self.row_flag, 5)
- 42 step["expectedresults"] = self.temp.getCell(self.excelSheet, self.row_flag, 6)
- 43 testcase["steps"].append(step)
- 44 step_number += 1
- 45 self.row_flag += 1
- 46 if self.temp.getCell(self.excelSheet, self.row_flag, 1) is not None or self.temp.getCell(self.excelSheet, self.row_flag, 5) is None:
- 47 break
- 48 # print testcase
- 49
- 50 self.dic_testlink[self.testsuite]["testcase"].append(testcase)
- 51 # print self.row_flag
- 52 if self.temp.getCell(self.excelSheet, self.row_flag, 5) is None and self.temp.getCell(self.excelSheet, self.row_flag + 1, 5) is None:
- 53 break
- 54 self.temp.close()
- 55 # print self.dic_testlink
- 56
- 57 def content_to_xml(self, key, value=None):
- 58 if key == 'step_number' or key == 'execution_type' or key == 'node_order' or key == 'externalid' or key == 'version' or key == 'importance':
- 59 return "<" + str(key) + "><![CDATA[" + str(value) + "]]></" + str(key) + ">"
- 60 elif key == 'actions' or key == 'expectedresults' or key == 'summary' or key == 'preconditions':
- 61 return "<" + str(key) + "><![CDATA[<p> " + str(value) + "</p> ]]></" + str(key) + ">"
- 62 elif key == 'keywords':
- 63 return '<keywords><keyword name="' + str(value) + '"><notes><![CDATA[ aaaa ]]></notes></keyword></keywords>'
- 64 elif key == 'name':
- 65 return '<testcase name="' + str(value) + '">'
- 66 else:
- 67 return '##########'
- 68
- 69 def dic_to_xml(self, ExcelFileName, SheetName):
- 70 testcase_list = self.dic_testlink[self.testsuite]["testcase"]
- 71 for testcase in testcase_list:
- 72 for step in testcase["steps"]:
- 73 self.content += "<step>"
- 74 self.content += self.content_to_xml("step_number", step["step_number"])
- 75 self.content += self.content_to_xml("actions", step["actions"])
- 76 self.content += self.content_to_xml("expectedresults", step["expectedresults"])
- 77 self.content += self.content_to_xml("execution_type", step["execution_type"])
- 78 self.content += "</step>"
- 79 self.content = "<steps>" + self.content + "</steps>"
- 80 self.content = self.content_to_xml("importance", testcase["importance"]) + self.content
- 81 self.content = self.content_to_xml("execution_type", testcase["execution_type"]) + self.content
- 82 self.content = self.content_to_xml("preconditions", testcase["preconditions"]) + self.content
- 83 self.content = self.content_to_xml("summary", testcase["summary"]) + self.content
- 84 self.content = self.content_to_xml("version", testcase["version"]) + self.content
- 85 self.content = self.content_to_xml("externalid", testcase["externalid"]) + self.content
- 86 self.content = self.content_to_xml("node_order", testcase["node_order"]) + self.content
- 87 self.content = self.content + self.content_to_xml("keywords", testcase["keywords"])
- 88 self.content = self.content_to_xml("name", testcase["name"]) + self.content
- 89 self.content = self.content + "</testcase>"
- 90 self.content_list.append(self.content)
- 91 self.content = ""
- 92 self.content = "".join(self.content_list)
- 93 self.content = '<testsuite name="' + self.testsuite + '">' + self.content + "</testsuite>"
- 94 self.content = '<?xml version="1.0" encoding="UTF-8"?>' + self.content
- 95 self.write_to_file(ExcelFileName, SheetName)
- 96
- 97 def write_to_file(self, ExcelFileName, SheetName):
- 98 xmlFileName = ExcelFileName + '_' + SheetName + '.xml'
- 99 cp = open(xmlFileName, "w")
- 100 cp.write(self.content)
- 101 cp.close()
- 102
- 103 if __name__ == "__main__":
- 104
- 105 fileName = raw_input('enter excel name:')
- 106 sheetName = raw_input('enter sheet name:')
- 107 sheetList = sheetName.split(" ")
- 108 for sheetName in sheetList:
- 109 test = operate(fileName, sheetName)
- 110 test.xlsx_to_dic(sheetName)
- 111 test.dic_to_xml(fileName, sheetName)
- 112 print "Convert success!"
- 113 os.system('pause')
operate.py:
- 1 # coding:utf-8
- 2 import os
- 3 import sys
- 4 reload(sys)
- 5 sys.setdefaultencoding("utf-8")
- 6
- 7 from easy_excel import easy_excel
- 8 class operate():
- 9 def __init__(self, ExcelFileName, SheetName):
- 10 self.excelFile = ExcelFileName + '.xls'
- 11 self.excelSheet = SheetName
- 12 self.temp = easy_excel(self.excelFile)
- 13 self.dic_testlink = {}
- 14 self.row_flag = 3
- 15 self.testsuite = self.temp.getCell(self.excelSheet, 2, 1)
- 16 self.dic_testlink[self.testsuite] = {"node_order": "13", "details": "", "testcase": []}
- 17 self.content = ""
- 18 self.content_list = []
- 19
- 20 def xlsx_to_dic(self, SheetName):
- 21 while True:
- 22 # print 'loop1'
- 23 # list_testcase = dic_testlink[testsuite].["testcase"]
- 24
- 25 testcase = {"name": "", "node_order": "100", "externalid": "", "version": "1", "summary": "",
- 26 "preconditions": "", "execution_type": "1", "importance": "3", "steps": [], "keywords": "P1"}
- 27 testcase["name"] = self.temp.getCell(self.excelSheet, self.row_flag, 1)
- 28 testcase["summary"] = self.temp.getCell(self.excelSheet, self.row_flag, 3)
- 29 testcase["preconditions"] = self.temp.getCell(self.excelSheet, self.row_flag, 4)
- 30 execution_type = self.temp.getCell(self.excelSheet, self.row_flag, 7)
- 31 if execution_type == "自动":
- 32 testcase["execution_type"] = 2
- 33 # print self.temp.getCell('Sheet1',self.row_flag,3)
- 34 step_number = 1
- 35 testcase["keywords"] = self.temp.getCell(self.excelSheet, self.row_flag, 2)
- 36 # print testcase["keywords"]
- 37 while True:
- 38 # print 'loop2'
- 39 step = {"step_number": "", "actions": "", "expectedresults": "", "execution_type": ""}
- 40 step["step_number"] = step_number
- 41 step["actions"] = self.temp.getCell(self.excelSheet, self.row_flag, 5)
- 42 step["expectedresults"] = self.temp.getCell(self.excelSheet, self.row_flag, 6)
- 43 testcase["steps"].append(step)
- 44 step_number += 1
- 45 self.row_flag += 1
- 46 if self.temp.getCell(self.excelSheet, self.row_flag, 1) is not None or self.temp.getCell(self.excelSheet,
- 47 self.row_flag,
- 48 5) is None:
- 49 break
- 50 # print testcase
- 51
- 52 self.dic_testlink[self.testsuite]["testcase"].append(testcase)
- 53 # print self.row_flag
- 54 if self.temp.getCell(self.excelSheet, self.row_flag, 5) is None and self.temp.getCell(self.excelSheet, self.row_flag + 1,
- 55 5) is None:
- 56 break
- 57 self.temp.close()
- 58 # print self.dic_testlink
- 59
- 60 def content_to_xml(self, key, value=None):
- 61 if key == 'step_number' or key == 'execution_type' or key == 'node_order' or key == 'externalid' or key == 'version' or key == 'importance':
- 62 return "<" + str(key) + "><![CDATA[" + str(value) + "]]></" + str(key) + ">"
- 63 elif key == 'actions' or key == 'expectedresults' or key == 'summary' or key == 'preconditions':
- 64 return "<" + str(key) + "><![CDATA[<p> " + str(value) + "</p> ]]></" + str(key) + ">"
- 65 elif key == 'keywords':
- 66 return '<keywords><keyword name="' + str(value) + '"><notes><![CDATA[ aaaa ]]></notes></keyword></keywords>'
- 67 elif key == 'name':
- 68 return '<testcase name="' + str(value) + '">'
- 69 else:
- 70 return '##########'
- 71
- 72 def dic_to_xml(self, ExcelFileName, SheetName):
- 73 testcase_list = self.dic_testlink[self.testsuite]["testcase"]
- 74 for testcase in testcase_list:
- 75 for step in testcase["steps"]:
- 76 self.content += "<step>"
- 77 self.content += self.content_to_xml("step_number", step["step_number"])
- 78 self.content += self.content_to_xml("actions", step["actions"])
- 79 self.content += self.content_to_xml("expectedresults", step["expectedresults"])
- 80 self.content += self.content_to_xml("execution_type", step["execution_type"])
- 81 self.content += "</step>"
- 82 self.content = "<steps>" + self.content + "</steps>"
- 83 self.content = self.content_to_xml("importance", testcase["importance"]) + self.content
- 84 self.content = self.content_to_xml("execution_type", testcase["execution_type"]) + self.content
- 85 self.content = self.content_to_xml("preconditions", testcase["preconditions"]) + self.content
- 86 self.content = self.content_to_xml("summary", testcase["summary"]) + self.content
- 87 self.content = self.content_to_xml("version", testcase["version"]) + self.content
- 88 self.content = self.content_to_xml("externalid", testcase["externalid"]) + self.content
- 89 self.content = self.content_to_xml("node_order", testcase["node_order"]) + self.content
- 90 self.content = self.content + self.content_to_xml("keywords", testcase["keywords"])
- 91 self.content = self.content_to_xml("name", testcase["name"]) + self.content
- 92 self.content = self.content + "</testcase>"
- 93 self.content_list.append(self.content)
- 94 self.content = ""
- 95 self.content = "".join(self.content_list)
- 96 self.content = '<testsuite name="' + self.testsuite + '">' + self.content + "</testsuite>"
- 97 self.content = '<?xml version="1.0" encoding="UTF-8"?>' + self.content
- 98 self.write_to_file(ExcelFileName, SheetName)
- 99
- 100 def write_to_file(self, ExcelFileName, SheetName):
- 101 xmlFileName = ExcelFileName + '_' + SheetName + '.xml'
- 102 cp = open(xmlFileName, "w")
- 103 cp.write(self.content)
- 104 cp.close()
- 105
- 106 if __name__ == "__main__":
- 107
- 108 fileName = raw_input('enter excel name:')
- 109 sheetName = raw_input('enter sheet name:')
- 110 sheetList = sheetName.split(" ")
- 111 for sheetName in sheetList:
- 112 test = operate(fileName, sheetName)
- 113 test.xlsx_to_dic(sheetName)
- 114 test.dic_to_xml(fileName, sheetName)
- 115 print "Convert success!"
- 116 os.system('pause')
二、转换方法:
1、将要转换的测试用例文件放置在与 py 文件的文件夹中,测试用例样式见下图,
将每个 "测试集" 放在一个 Sheet 中,每个 Sheet 的第二行为该 "测试集" 的名称,如下图,"运行环境测试" 为该测试集的名称,
Sheet 的名称,建议与测试集的名称一致,如下图:
双击 "operate.py" 文件,出现控制台窗口,输入 excel 文件名称,回车,输入要转换的 sheet 的名称,多个 sheet 之间以 "空格" 隔开,
再回车,出现 "Convert success!" 转换完成。
转换前后的 excel 及 xml 文件:
三、导入 testlink:
来源: