python：将excel转换xml，testlink
最近公司内部开始推行使用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!")