最近公司内部开始推行使用testlink,打算使用excel编写转换为xml，自行上传，但是最终转换出来的文件还是不能被testlink所识别
最终解决方案：使用testlink convert进行相应的转换操作。
此处仅提供excel转xml的思路：使用xlrd读取excel并存为字典，再从字典解析成xml（一个是用xmldom解析，一种是通过拼接字符串的方式进行解析）
1、读取excel
# -*- coding: utf-8 -*- import xlrd import os class ExcelEg3: def __init__(self): self._sheet = '' self._cell = '' self._fileName = 'test001.xls' self._rows = 1 self._cols = 1 def get_sheet(self): workbook = xlrd.open_workbook(self._fileName) self._sheet = workbook.sheet_by_name('Sheet1') return self._sheet def get_cell_value(self, row, col): self._cell = self.get_sheet().cell(row, col).value return self._cell def get_rows(self): self._rows = self.get_sheet().nrows return self._rows def get_cols(self): self._cols = self.get_sheet().ncols return self._cols if __name__ == "__main__": excel1 = ExcelEg3() print(excel1.get_sheet()) print(excel1.get_cell_value(1, 1)) print('-----------------') print(type(excel1._sheet)) print('rows:', excel1.get_rows())
2、将excel的内容按 testlink组件形式进行存储
# -*- coding: utf-8 -*- from ExcelEg3 import ExcelEg3 import copy class TestStep: def __init__(self): self._temp = ExcelEg3() self._name = '' self._preconditions = '' self._actions = '' self._expected = '' self._steps = ['0'] self._testCases = [] self._rows = 1 self._step = {'step_number': '1', 'actions': '', 'expected': '', 'execution_type': 1 } self._testCase = {'name': '', 'summary': '', 'preconditions': '', 'execution_type': 1, 'importance': 2, # 'estimated_exec_duration': '', 'status': 1, 'steps': [] } def set_name(self, row, col): self._name = self._temp.get_cell_value(row, col) def get_name(self): return self._name def set_preconditions(self, row, col): self._preconditions = self._temp.get_cell_value(row, col) def get_preconditions(self): return self._preconditions def set_actions(self, row, col): self._actions = self._temp.get_cell_value(row, col) def get_actions(self): return self._actions def set_expected(self, row, col): self._expected = self._temp.get_cell_value(row, col) def get_expected(self): return self._expected def get_row_num(self): self._rows = self._temp.get_rows() return self._rows def set_step_value(self): self._step['actions'] = copy.deepcopy(self.get_actions()) self._step['expected'] = copy.deepcopy(self.get_expected()) def get_step(self): return self._step def add_step_to_steps(self, step4): self._steps[0] = step4 def get_steps(self): return self._steps def set_test_case(self, steps): self._testCase['name'] = self.get_name() self._testCase['preconditions'] = self.get_preconditions() self._testCase['steps'] = steps def get_test_case(self): return self._testCase def add_tc_to_tcs(self, test_case): self._testCases.append(test_case) def get_test_cases(self): return self._testCases def add_data(self): for row1 in range(1, self.get_row_num()): self.set_name(row1, 0) self.set_preconditions(row1, 1) self.set_actions(row1, 2) self.set_expected(row1, 3) self.set_step_value() step2 = copy.deepcopy(self.get_step()) self.add_step_to_steps(step2) steps = copy.deepcopy(self.get_steps()) self.set_test_case(steps) # 深赋值，更改字典的地址 test_case = copy.deepcopy(self.get_test_case()) self.add_tc_to_tcs(test_case) if __name__ == '__main__': step = TestStep() step.add_data() print('----------add get--------') print(step.get_test_cases()[0])
3-1：拼接字符串的形式,读取字段转xml
# -*- coding: utf-8 -*- from ExcelEg3 import ExcelEg3 from TestStep import TestStep import os import sys #sys.setdefaultencoding("utf-8") class Operate3: def __init__(self): self._ts = TestStep() self._testlist = [] self.content_list = [] self.content = "" def set_test_list(self): self._ts.add_data() self._testlist = self._ts.get_test_cases() def get_test_list(self): return self._testlist def content_to_xml(self, key, value=None): if key == 'step_number' or key == 'execution_type' or key == 'importance' or key == 'status' or key == 'summary': return "<" + str(key) + "><![CDATA[" + str(value) + "]]></" + str(key) + ">" elif key == 'actions' or key == 'expectedresults' or key == 'preconditions': return "<" + str(key) + "><![CDATA[<p> " + str(value) + "</p> ]]></" + str(key) + ">" elif key == 'name': return '<testcase name="' + str(value) + '">' else: return '##########' def dic_to_xml(self, ExcelFileName, SheetName): print('=--------into dic to xml-----') self.set_test_list() testcase_list = self.get_test_list() print('=--------into dic to xml-----') for testcase in testcase_list: for step in testcase["steps"]: self.content += "<step>" self.content += self.content_to_xml("step_number", step['step_number']) self.content += self.content_to_xml("actions", step['actions']) self.content += self.content_to_xml("expectedresults", step['expected']) self.content += "</step>" self.content = "<steps>" + self.content + "</steps>" print('-------add ing-------') self.content = self.content_to_xml("preconditions", testcase['preconditions']) + self.content self.content = self.content_to_xml("name", testcase['name']) + self.content self.content = self.content + "</testcase>" self.content_list.append(self.content) self.content = "" print('-------after-------') self.content = "".join(self.content_list) self.content = "<testcases>" + self.content + "</testcases>" self.content = '<?xml version="1.0" encoding="UTF-8"?>' + self.content self.write_to_file(ExcelFileName, SheetName) def write_to_file(self, ExcelFileName, SheetName): xmlFileName = ExcelFileName + '_' + SheetName + '.xml' cp = open(xmlFileName, "w") cp.write(self.content) Indent(doc, parent) cp.close() if __name__ == '__main__': test = Operate3() test.dic_to_xml('test', '0501') print("Convert success!") os.system('pause')
3-2：xmldom处理xml
# -*- coding: utf-8 -*- import html from ExcelEg3 import ExcelEg3 from TestStep import TestStep import os import xml.dom.minidom class Operate4: def __init__(self): self._ts = TestStep() self._testlist = [] self.content_list = [] self.content = "" def set_test_list(self): self._ts.add_data() self._testlist = self._ts.get_test_cases() def get_test_list(self): return self._testlist def dict_to_xml(self): doc = xml.dom.minidom.Document() testcases = doc.createElement("testcases") doc.appendChild(testcases) self.set_test_list() test_list = self.get_test_list() print(test_list) print() flag1 = '' flag2 = '' for testcase in test_list: tc = doc.createElement("testcase") tc.setAttribute('name', testcase['name']) flag1 = "<![CDATA[" flag1 = html.unescape(flag1) flag2 = "]]>" print(type(flag1)) print(type(testcase['steps'][0]['step_number'])) preconditions = doc.createElement("preconditions") preconditions.appendChild(doc.createTextNode(testcase['preconditions'])) tc.appendChild(preconditions) steps = doc.createElement('steps') step = doc.createElement('step') step_number = doc.createElement('step_number') step_number1 = testcase['steps'][0]['step_number'] step_number2 = flag1 + step_number1 + flag2 print(step_number1) print(step_number2) step_number.appendChild(doc.createTextNode(step_number2)) actions = doc.createElement('actions') actions.appendChild(doc.createTextNode(testcase['steps'][0]['actions'])) expected = doc.createElement('expectedresults') expected.appendChild(doc.createTextNode(testcase['steps'][0]['expected'])) step.appendChild(step_number) step.appendChild(actions) step.appendChild(expected) steps.appendChild(step) tc.appendChild(steps) testcases.appendChild(tc) f = open('test_0501.txt', 'w') doc.writexml(f) f.close() if __name__ == '__main__': test = Operate4() test.dict_to_xml() print("Convert success!")