您的位置:軟件測試 > 開源軟件測試 > 開源測試管理工具 > Testlink
Testlink用例轉(zhuǎn)換小工具(excel轉(zhuǎn)為xml,python版)
作者:網(wǎng)絡(luò)轉(zhuǎn)載 發(fā)布時間:[ 2017/2/24 15:02:30 ] 推薦標(biāo)簽:測試管理工具

 

前面文章記錄了testlink的安裝方法(CentOS7下安裝xampp和testlink),由于testlink僅支持xml格式的用例導(dǎo)入,研究了下excel轉(zhuǎn)xml的方法,從網(wǎng)上其他網(wǎng)友那里借用了部分代碼,自己又補充修改了下,供大家參考,使用的時候要在PC上安裝python2.7。
  所有文件在文章后面的百度網(wǎng)盤上。
  一、代碼(有兩個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#設(shè)置居中
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')
  二、轉(zhuǎn)換方法:
  1、將要轉(zhuǎn)換的測試用例文件放置在與py文件的文件夾中,測試用例樣式見下圖,

  將每個“測試集”放在一個Sheet中,每個Sheet的第二行為該“測試集”的名稱,如下圖,“運行環(huán)境測試”為該測試集的名稱,
  Sheet的名稱,建議與測試集的名稱一致,如下圖:

  雙擊"operate.py"文件,出現(xiàn)控制臺窗口,輸入excel文件名稱,回車,輸入要轉(zhuǎn)換的sheet的名稱,多個sheet之間以“空格”隔開,
  再回車,出現(xiàn)“Convertsuccess!”轉(zhuǎn)換完成。

  轉(zhuǎn)換前后的excel及xml文件:

  三、導(dǎo)入testlink:

  百度網(wǎng)盤:
  http://pan.baidu.com/s/1o8fOaPc

軟件測試工具 | 聯(lián)系我們 | 投訴建議 | 誠聘英才 | 申請使用列表 | 網(wǎng)站地圖
滬ICP備07036474 2003-2017 版權(quán)所有 上海澤眾軟件科技有限公司 Shanghai ZeZhong Software Co.,Ltd