前面文章记录了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') |
二、转换方法：
将每个“测试集”放在一个Sheet中，每个Sheet的第二行为该“测试集”的名称，如下图，“运行环境测试”为该测试集的名称，
Sheet的名称，建议与测试集的名称一致，如下图：
双击"operate.py"文件，出现控制台窗口，输入excel文件名称，回车，输入要转换的sheet的名称，多个sheet之间以“空格”隔开，
再回车，出现“Convertsuccess！”转换完成。
转换前后的excel及xml文件：
三、导入testlink：
