Testlink导出用例转化为excel用例
当前项目使用的是testlink平台管理测试用例,该平台用例导入导出文件均为xml格式
1.导入
平时做需求测试分析时,我总是习惯用xmind梳理测试点,而且现在敏捷测试下,很多公司都不局限于测试用例,都在使用xmind做测试点执行,个人感觉思路清晰,就是执行结果不好跟踪。
所以我xmind写的用例需要支持导入到testlink,网上找了下,直接使用前辈写的xmind2testlink脚本就好了
2.导出
交付的时候需要给客户excel格式的测试用例,这下就需要xml文件转为excel,网上找了下,没有好的直接可以用的资源,我照猫画虎写了一个,第一次用python写,可能写的比较挫,但是也完成了转换用例的需求。
3.使用方法
D:\work>python D:\work\xmlToexcel.py ceshiyongli.xml
导出文件列是下面这个样子:
大家可以根据需要修改脚本
# coding=utf-8
from xml.etree import ElementTree
import win32com.client
import sys, os
import time
class easy_excel:
def __init__(self, filename=None):
self.xlApp = win32com.client.Dispatch('Excel.Application')
if filename:
self.filename = filename
self.xlBook = self.xlApp.Workbooks.Open(self.filename)
else:
self.xlBook = self.xlApp.Workbooks.Add()
self.filename = ''
def save(self, newfilename=None):
if newfilename:
self.filename = newfilename
self.xlBook.SaveAs(newfilename)
else:
self.xlBook.Save()
def close(self):
self.xlBook.Close(SaveChanges=0)
def getCell(self, sheet, row, col):
sht = self.xlBook.Worksheets(sheet)
return sht.Cell(row, col).Value
def setCell(self, sheet, row, col, value):
sht = self.xlBook.Worksheets(sheet)
sht.Cells(row, col).Value = value
sht.Cells(row, col).HorizontalAlignment = 3
sht.Rows(row).WrapText = True
def mergeCells(self, sheet, row1, col1, row2, col2):
sht = self.xlBook.Worksheets(sheet)
sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Merge()
def setBorder(self, sheet, row, col):
sht = self.xlBook.Worksheets(sheet)
sht.Cells(row, col).Borders.LineStyle = 1
def set_col_width(self, sheet):
sht = self.xlBook.Worksheets(sheet)
sht.Columns("E:H").ColumnWidth = 30
def SetTextWithGt(str):
strnew=str
if (str.find(">") != -1):
strnew = strnew.replace(">", ">")
if (str.find("“") != -1):
strnew = strnew.replace("“", "\"")
if (str.find("”") != -1):
strnew = strnew.replace("”", "\"")
if (str.find("") != -1):
strnew = strnew.replace("", "")
") != -1):
if (str.find("
strnew = strnew.replace("", "")
if (str.find("
") != -1):
strnew = strnew.replace("
", "")
return strnew
pass
def combineCells(startRow,endRow,cloumStart,cloumEnd):
for clo in range(cloumStart,cloumEnd):
xls.mergeCells('sheet1', startRow, clo, endRow, clo)
pass
def convertTestCase(caseinTestsuite,row_flag,rootName="",subName=""):
for sub1TestCase in caseinTestsuite:
row_flag = row_flag + 1
title = SetTextWithGt(sub1TestCase.get("name"))
externalid = sub1TestCase.find("externalid").text
preconditions = ""
if (sub1TestCase.find("summary").text != None):
preconditions = SetTextWithGt(sub1TestCase.find("summary").text)
if (sub1TestCase.find("preconditions").text != None):
preconditions = preconditions+SetTextWithGt(sub1TestCase.find("preconditions").text)
xls.setCell('Sheet1', row_flag, 1, rootName+'/'+subName)
xls.setCell('Sheet1', row_flag, 2, externalid)
xls.setCell('Sheet1', row_flag, 3, title)
xls.setCell('Sheet1', row_flag, 4, preconditions)
stepsNode = sub1TestCase.find("steps")
num = 0
if sub1TestCase.find("steps"):
stepCount = len(stepsNode.findall("step"))
for stepNode in stepsNode.findall("step"):
stepExpt = ""
stepText = ""
if (stepNode.find('actions').text != None):
stepText = SetTextWithGt(stepNode.find('actions').text.strip())
if (stepNode.find('expectedresults').text != None):
stepExpt = SetTextWithGt(stepNode.find('expectedresults').text.strip())
xls.setCell('Sheet1', row_flag, 5, stepText)
xls.setCell('Sheet1', row_flag, 6, stepExpt)
if (num + 1 < stepCount):
num += 1
row_flag += 1
combineCells(row_flag - num, row_flag, 1, 5)
return row_flag
pass
if __name__ == '__main__':
if (len(sys.argv) == 1):
print("Please specified a xml file")
os.system("pause")
sys.exit(0)
else:
tmpInFile = os.getcwd() + "\\" + sys.argv[1]
inFile = tmpInFile
if not tmpInFile.endswith(".xml"):
outFile = tmpInFile + "-tcexp"+str(int(time.time()))+".xlsx"
inFile = tmpInFile + ".xml"
else:
outFile = tmpInFile[:-4] + "-tcexp"+str(int(time.time()))+".xlsx"
print(f'outFile--{outFile}')
xls = easy_excel()
xls.setCell('Sheet1', 1, 1, u"测试用例集")
xls.setCell('Sheet1', 1, 2, u"用例编号")
xls.setCell('Sheet1', 1, 3, u"测试标题")
xls.setCell('Sheet1', 1, 4, u"预置条件")
xls.setCell('Sheet1', 1, 5, u"操作步骤")
xls.setCell('Sheet1', 1, 6, u"预期结果")
xls.set_col_width('Sheet1')
tree = ElementTree.parse(inFile)
root = tree.getroot()
row_flag = 1
# 一级目录
if(len(root.findall("testsuite"))>0):
sub1SuiteName = root.get("name")
for sub1Testsuite in root.findall("testsuite"):
sub1SuiteId = sub1Testsuite.get("id")
sub2SuiteName = sub1Testsuite.get("name")
row_flag = convertTestCase(sub1Testsuite.findall("testcase"), row_flag,sub1SuiteName,sub2SuiteName)
print(f'{sub2SuiteName}---convert success')
print(f'row_flag---{row_flag}')
elif(len(root.findall("testcase"))>0):
row_flag = convertTestCase(root.findall("testcase"),row_flag,root.get("name"),"")
for row in range(2, row_flag):
for col in range(1, 9):
xls.setBorder('Sheet1', row, col)
xls.save(outFile)
xls.close()
print("finished.")
sys.exit(0)