testlink1.9x导入用例功能支持xml文件导入,所以如果测试用例能够按照一定格式写入excel再用脚本生成xml文件然后导入testlink的话,这样就可以避免在公司加班到很晚,完全可以晚上回去慢慢写excel。
首先是一个操作excel的类,这个从网上找的源码,然后做了一些修改,其实这个功能里只用到了getCell这个函数,也就是读excel单元格中的内容。
#coding=utf-8
from xml.etree import ElementTree
from win32com.client import Dispatch
import win32com.client
import os
class easy_excel:
def __init__(self,filename=None):
self.xlApp=win32com.client.Dispatch('Excel.Application')
if filename:
self.filename=os.getcwd()+"\\"+filename
#self.xlApp.Visible=True
self.xlBook=self.xlApp.Workbooks.Open(self.filename)
else:
#self.xlApp.Visible=True
self.xlBook=self.xlApp.Workbooks.Add()
self.filename=''
def save(self,newfilename=None):
if newfilename:
self.filename=os.getcwd()+"\\"+newfilename
#if os.path.exists(self.filename):
#os.remove(self.filename)
self.xlBook.SaveAs(self.filename)
else:
self.xlBook.Save()
def close(self):
self.xlBook.Close(SaveChanges=0)
self.xlApp.Quit()
def getCell(self,sheet,row,col):
sht=self.xlBook.Worksheets(sheet)
return sht.Cells(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):
start_coloum=int(dic_config["start_coloum"])
#如果这列不存在就不合并单元格
if col2!=start_coloum-1:
sht=self.xlBook.Worksheets(sheet)
sht.Range(sht.Cells(row1,col1),sht.Cells(row2,col2)).Merge()
#else:
#print 'Merge cells coloum %s failed!' %col2
def setBorder(self,sheet,row,col):
sht=self.xlBook.Worksheets(sheet)
sht.Cells(row,col).Borders.LineStyle=1
def set_col_width(self,sheet,start,end,length):
start+=96
end+=96
msg=chr(start)+":"+chr(end)
#print msg
sht=self.xlBook.Worksheets(sheet)
sht.Columns(msg.upper()).ColumnWidth=length
主要的思路是先读excel里面的内容,然后存到一个特定的数据结构中,再从数据结构中取值生成xml文件。这里row_flag用来标记当前读到的行数。
class operate():
def __init__(self):
self.temp=easy_excel('test.xlsx')
self.dic_testlink={}
self.row_flag=2
self.testsuite = self.temp.getCell('Sheet1',1,1)
self.dic_testlink[self.testsuite]={"node_order":"13","details":"","testcase":[]}
self.content = ""
self.content_list = []
def xlsx_to_dic(self):
while True:
#print 'loop1'
#list_testcase = dic_testlink[testsuite].["testcase"]
#1.用例名2.关键字3.摘要4.前提5.步骤6.预期结果
testcase = {"name":"","node_order":"100","externalid":"","version":"1","summary":"","preconditions":"","execution_type":"1","importance":"3","steps":[],"keywords":"P1"}
testcase["name"] = self.temp.getCell('Sheet1',self.row_flag,1)
testcase["summary"] = self.temp.getCell('Sheet1',self.row_flag,3)
testcase["preconditions"] = self.temp.getCell('Sheet1',self.row_flag,4)
#print self.temp.getCell('Sheet1',self.row_flag,3)
step_number=1
testcase["keywords"] = self.temp.getCell('Sheet1',self.row_flag,2)
print testcase["keywords"]
while True:
#print 'loop2'
step = {"step_number":"","actions":"","expectedresults":"","execution_type":""}
step["step_number"] = step_number
step["actions"] = self.temp.getCell('Sheet1',self.row_flag,5)
step["expectedresults"] = self.temp.getCell('Sheet1',self.row_flag,6)
testcase["steps"].append(step)
step_number += 1
self.row_flag += 1
if self.temp.getCell('Sheet1',self.row_flag,1) is not None or self.temp.getCell('Sheet1',self.row_flag,5) is None:
break
#print testcase
self.dic_testlink[self.testsuite]["testcase"].append(testcase)
#print self.row_flag
if self.temp.getCell('Sheet1',self.row_flag,5) is None and self.temp.getCell('Sheet1',self.row_flag+1,5) is None:
break
self.temp.close()
#print self.dic_testlink
def content_to_xml(self,key,value=None):
if key == 'step_number' or key == 'execution_type' or key == 'node_order' or key == 'externalid' or key == 'version' or key == 'importance':
return "<"+str(key)+"><![CDATA["+str(value)+"]]></"+str(key)+">"
elif key == 'actions' or key == 'expectedresults' or key == 'summary' or key == 'preconditions':
return "<"+str(key)+"><![CDATA[<p> "+str(value)+"</p> ]]></"+str(key)+">"
elif key == 'keywords':
return '<keywords><keyword name="'+value+'"><notes><![CDATA[ 每个版本都会执行的用例 ]]></notes></keyword></keywords>'
elif key == 'name':
return '<testcase name="'+str(value)+'">'
else:
return '##########'
def dic_to_xml(self):
testcase_list = self.dic_testlink[self.testsuite]["testcase"]
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["expectedresults"])
self.content += self.content_to_xml("execution_type",step["execution_type"])
self.content += "</step>"
self.content = "<steps>" + self.content + "</steps>"
self.content = self.content_to_xml("importance",testcase["importance"]) + self.content
self.content = self.content_to_xml("execution_type",testcase["execution_type"]) + self.content
self.content = self.content_to_xml("preconditions",testcase["preconditions"]) + self.content
self.content = self.content_to_xml("summary",testcase["summary"]) + self.content
self.content = self.content_to_xml("version",testcase["version"]) + self.content
self.content = self.content_to_xml("externalid",testcase["externalid"]) + self.content
self.content = self.content_to_xml("node_order",testcase["node_order"]) + self.content
self.content = self.content + self.content_to_xml("keywords",testcase["keywords"])
self.content = self.content_to_xml("name",testcase["name"]) + self.content
self.content = self.content + "</testcase>"
self.content_list.append(self.content)
self.content = ""
self.content = "".join(self.content_list)
self.content = '<testsuite name="'+self.testsuite+'">'+self.content+"</testsuite>"
self.content = '<?xml version="1.0" encoding="UTF-8"?>' + self.content
self.write_to_file()
def write_to_file(self):
cp = open("test.xml","w")
cp.write(self.content)
cp.close()
test=operate()
test.xlsx_to_dic()
test.dic_to_xml()
excel的格式必须跟下图中的格式一样,否则会无法正确读取。
这样导出的xml文件导入后的结果如下图。
testlink中的用例结果如下图。
如果需要编译好的文件,由于文件大于2M,请到我的下载空间下载。目标文件为test.xlsx,一定要严格按照格式,不该写数据的地方千万留空,生成的xml文件为test.xml。