您的位置:软件测试 > 开源软件测试 > 开源测试管理工具 > Testlink
Testlink用例转换小工具(excel转为xml,python版)
作者:网络转载 发布时间:[ 2017/2/24 15:02:30 ] 推荐标签:测试管理工具

 

前面文章记录了testlink的安装方法(CentOS7下安装xampp和testlink),由于testlink仅支持xml格式的用例导入,研究了下excel转xml的方法,从网上其他网友那里借用了部分代码,自己又补充修改了下,供大家参考,使用的时候要在PC上安装python2.7。
  所有文件在文章后面的百度网盘上。
  一、代码(有两个py文件):
easy_excel.py:
1#coding=utf-8
2fromxml.etreeimportElementTree
3fromwin32com.clientimportDispatch
4importwin32com.client
5importos
6importsys
7reload(sys)
8sys.setdefaultencoding("utf-8")
9
10classeasy_excel:
11def__init__(self,filename=None):
12self.xlApp=win32com.client.Dispatch('Excel.Application')
13
14iffilename:
15self.filename=os.getcwd()+"\"+filename
16#self.xlApp.Visible=True
17self.xlBook=self.xlApp.Workbooks.Open(self.filename)
18else:
19#self.xlApp.Visible=True
20self.xlBook=self.xlApp.Workbooks.Add()
21self.filename=''
22
23defsave(self,newfilename=None):
24ifnewfilename:
25self.filename=os.getcwd()+"\"+newfilename
26#ifos.path.exists(self.filename):
27#os.remove(self.filename)
28self.xlBook.SaveAs(self.filename)
29else:
30self.xlBook.Save()
31
32defclose(self):
33self.xlBook.Close(SaveChanges=0)
34self.xlApp.Quit()
35
36defgetCell(self,sheet,row,col):
37sht=self.xlBook.Worksheets(sheet)
38returnsht.Cells(row,col).Value
39
40defsetCell(self,sheet,row,col,value):
41sht=self.xlBook.Worksheets(sheet)
42sht.Cells(row,col).Value=value
43#设置居中
44sht.Cells(row,col).HorizontalAlignment=3
45sht.Rows(row).WrapText=True
46
47defmergeCells(self,sheet,row1,col1,row2,col2):
48start_coloum=int(dic_config["start_coloum"])
49#如果这列不存在不合并单元格
50ifcol2!=start_coloum-1:
51sht=self.xlBook.Worksheets(sheet)
52sht.Range(sht.Cells(row1,col1),sht.Cells(row2,col2)).Merge()
53#else:
54#print'Mergecellscoloum%sfailed!'%col2
55
56defsetBorder(self,sheet,row,col):
57sht=self.xlBook.Worksheets(sheet)
58sht.Cells(row,col).Borders.LineStyle=1
59
60defset_col_width(self,sheet,start,end,length):
61start+=96
62end+=96
63msg=chr(start)+":"+chr(end)
64#printmsg
65sht=self.xlBook.Worksheets(sheet)
66sht.Columns(msg.upper()).ColumnWidth=length
  operate.py:
1#coding:utf-8
2importos
3importsys
4reload(sys)
5sys.setdefaultencoding("utf-8")
6
7fromeasy_excelimporteasy_excel
8classoperate():
9def__init__(self,ExcelFileName,SheetName):
10self.excelFile=ExcelFileName+'.xls'
11self.excelSheet=SheetName
12self.temp=easy_excel(self.excelFile)
13self.dic_testlink={}
14self.row_flag=3
15self.testsuite=self.temp.getCell(self.excelSheet,2,1)
16self.dic_testlink[self.testsuite]={"node_order":"13","details":"","testcase":[]}
17self.content=""
18self.content_list=[]
19
20defxlsx_to_dic(self,SheetName):
21whileTrue:
22#print'loop1'
23#list_testcase=dic_testlink[testsuite].["testcase"]
24
25testcase={"name":"","node_order":"100","externalid":"","version":"1","summary":"",
26"preconditions":"","execution_type":"1","importance":"3","steps":[],"keywords":"P1"}
27testcase["name"]=self.temp.getCell(self.excelSheet,self.row_flag,1)
28testcase["summary"]=self.temp.getCell(self.excelSheet,self.row_flag,3)
29testcase["preconditions"]=self.temp.getCell(self.excelSheet,self.row_flag,4)
30execution_type=self.temp.getCell(self.excelSheet,self.row_flag,7)
31ifexecution_type=="自动":
32testcase["execution_type"]=2
33#printself.temp.getCell('Sheet1',self.row_flag,3)
34step_number=1
35testcase["keywords"]=self.temp.getCell(self.excelSheet,self.row_flag,2)
36#printtestcase["keywords"]
37whileTrue:
38#print'loop2'
39step={"step_number":"","actions":"","expectedresults":"","execution_type":""}
40step["step_number"]=step_number
41step["actions"]=self.temp.getCell(self.excelSheet,self.row_flag,5)
42step["expectedresults"]=self.temp.getCell(self.excelSheet,self.row_flag,6)
43testcase["steps"].append(step)
44step_number+=1
45self.row_flag+=1
46ifself.temp.getCell(self.excelSheet,self.row_flag,1)isnotNoneorself.temp.getCell(self.excelSheet,self.row_flag,5)isNone:
47break
48#printtestcase
49
50self.dic_testlink[self.testsuite]["testcase"].append(testcase)
51#printself.row_flag
52ifself.temp.getCell(self.excelSheet,self.row_flag,5)isNoneandself.temp.getCell(self.excelSheet,self.row_flag+1,5)isNone:
53break
54self.temp.close()
55#printself.dic_testlink
56
57defcontent_to_xml(self,key,value=None):
58ifkey=='step_number'orkey=='execution_type'orkey=='node_order'orkey=='externalid'orkey=='version'orkey=='importance':
59return"<"+str(key)+"><![CDATA["+str(value)+"]]></"+str(key)+">"
60elifkey=='actions'orkey=='expectedresults'orkey=='summary'orkey=='preconditions':
61return"<"+str(key)+"><![CDATA[<p>"+str(value)+"</p>]]></"+str(key)+">"
62elifkey=='keywords':
63return'<keywords><keywordname="'+str(value)+'"><notes><![CDATA[aaaa]]></notes></keyword></keywords>'
64elifkey=='name':
65return'<testcasename="'+str(value)+'">'
66else:
67return'##########'
68
69defdic_to_xml(self,ExcelFileName,SheetName):
70testcase_list=self.dic_testlink[self.testsuite]["testcase"]
71fortestcaseintestcase_list:
72forstepintestcase["steps"]:
73self.content+="<step>"
74self.content+=self.content_to_xml("step_number",step["step_number"])
75self.content+=self.content_to_xml("actions",step["actions"])
76self.content+=self.content_to_xml("expectedresults",step["expectedresults"])
77self.content+=self.content_to_xml("execution_type",step["execution_type"])
78self.content+="</step>"
79self.content="<steps>"+self.content+"</steps>"
80self.content=self.content_to_xml("importance",testcase["importance"])+self.content
81self.content=self.content_to_xml("execution_type",testcase["execution_type"])+self.content
82self.content=self.content_to_xml("preconditions",testcase["preconditions"])+self.content
83self.content=self.content_to_xml("summary",testcase["summary"])+self.content
84self.content=self.content_to_xml("version",testcase["version"])+self.content
85self.content=self.content_to_xml("externalid",testcase["externalid"])+self.content
86self.content=self.content_to_xml("node_order",testcase["node_order"])+self.content
87self.content=self.content+self.content_to_xml("keywords",testcase["keywords"])
88self.content=self.content_to_xml("name",testcase["name"])+self.content
89self.content=self.content+"</testcase>"
90self.content_list.append(self.content)
91self.content=""
92self.content="".join(self.content_list)
93self.content='<testsuitename="'+self.testsuite+'">'+self.content+"</testsuite>"
94self.content='<?xmlversion="1.0"encoding="UTF-8"?>'+self.content
95self.write_to_file(ExcelFileName,SheetName)
96
97defwrite_to_file(self,ExcelFileName,SheetName):
98xmlFileName=ExcelFileName+'_'+SheetName+'.xml'
99cp=open(xmlFileName,"w")
100cp.write(self.content)
101cp.close()
102
103if__name__=="__main__":
104
105fileName=raw_input('enterexcelname:')
106sheetName=raw_input('entersheetname:')
107sheetList=sheetName.split("")
108forsheetNameinsheetList:
109test=operate(fileName,sheetName)
110test.xlsx_to_dic(sheetName)
111test.dic_to_xml(fileName,sheetName)
112print"Convertsuccess!"
113os.system('pause')
  二、转换方法:
  1、将要转换的测试用例文件放置在与py文件的文件夹中,测试用例样式见下图,

  将每个“测试集”放在一个Sheet中,每个Sheet的第二行为该“测试集”的名称,如下图,“运行环境测试”为该测试集的名称,
  Sheet的名称,建议与测试集的名称一致,如下图:

  双击"operate.py"文件,出现控制台窗口,输入excel文件名称,回车,输入要转换的sheet的名称,多个sheet之间以“空格”隔开,
  再回车,出现“Convertsuccess!”转换完成。

  转换前后的excel及xml文件:

  三、导入testlink:

  百度网盘:
  http://pan.baidu.com/s/1o8fOaPc

软件测试工具 | 联系我们 | 投诉建议 | 诚聘英才 | 申请使用列表 | 网站地图
沪ICP备07036474 2003-2017 版权所有 上海泽众软件科技有限公司 Shanghai ZeZhong Software Co.,Ltd