一直在网上寻找怎么把Excel中已经写好的用例导入到Testlink中的方法。根据现网的经验,然后修改了一下。贴出来,以飨有这方面需求的测试同仁。
Excel文件以xlsx结尾,必须有Sheet1这一页。
Testlink版本为1.9.16,用例目录最大支持两层.
Excel用例的格式
一级目录 | 二级目录 | 用例名称 | 用例编号 | 用例概要说明 | 预置条件 | 操作步骤 | 预期结果 |
用例集1 | 测试用例1 | 1 | 测试用例1 | 前提1 | 步骤1 | 期望1 | |
用例集1 | 用例集1-1 | 测试用例2 | 2 | 测试用例2 | 前提1 | 步骤1 | 期望1 |
python脚本内容如下 tcimport.py
#-*- coding: UTF-8 -*- from xml.dom import minidom from win32com.client import Dispatch import win32com.client import os,sys def formatLines(strLines): if strLines.startswith("<p>"): return strLines strRes = "" lines = strLines.strip().split("\n") for line in lines: strRes = strRes + "<p>"+line+"</p>" return strRes 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.Open(filename) 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) cellvalue = sht.Cells(row,col).Value if cellvalue is None: return "" else: return cellvalue 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("D:F").ColumnWidth=30 class easy_minidom: def __init__(self): self.dom = minidom.Document() self.rootNode =self.dom.createElement("testsuite") self.dom.appendChild(self.rootNode) self.rootNode =self.dom.childNodes[0] #print(self.dom.toxml()) def getSub1Testsuite(self,sub1SuiteName): locateSub1Node = None for oneSub1Node in self.rootNode.childNodes: tmpSub1Name = oneSub1Node.getAttribute("name") if tmpSub1Name.strip() == sub1SuiteName.strip(): return oneSub1Node return locateSub1Node def getSub2Testsuite(self,sub1SuiteNode,sub2SuiteName): locateSub2Node = None if sub1SuiteNode is not None: for oneSub2Node in sub1SuiteNode.getElementsByTagName("testsuite"): tmpSub2Name = oneSub2Node.getAttribute("name") if tmpSub2Name.strip() == sub2SuiteName.strip(): return oneSub2Node return locateSub2Node def appendSub1Testsuite(self,sub1SuiteName): if sub1SuiteName.strip()=="": return locateSub1Node = self.getSub1Testsuite(sub1SuiteName) if locateSub1Node is None: suiteElement = self.dom.createElement("testsuite") suiteElement.setAttribute("name", sub1SuiteName) self.rootNode.appendChild(suiteElement) node_orderElement = self.dom.createElement("node_order") child = self.dom.createCDATASection("1234567") node_orderElement.appendChild(child) return suiteElement def appendSub2Testsuite(self,sub1SuiteNode,sub2SuiteName): if sub2SuiteName.strip()=="": return locateSub2Node = None if sub1SuiteNode is not None: locateSub2Node = self.getSub2Testsuite(sub1SuiteNode,sub2SuiteName) if locateSub2Node is None: suiteElement = self.dom.createElement("testsuite") suiteElement.setAttribute("name", sub2SuiteName) sub1SuiteNode.appendChild(suiteElement) return suiteElement def appendTestCase(self,suiteNode,title,summary,preconditions,actions,expectedresults): if suiteNode is not None: testcaseNode = self.dom.createElement("testcase") testcaseNode.setAttribute("name", title) summaryElement = self.dom.createElement("summary") summaryElement.appendChild(self.dom.createCDATASection(summary)) preconditionsElement = self.dom.createElement("preconditions") preconditionsElement.appendChild(self.dom.createCDATASection(preconditions)) stepsElement = self.dom.createElement("steps") stepElement = self.dom.createElement("step") step_numberElement= self.dom.createElement("step_number") step_numberElement.appendChild(self.dom.createCDATASection("1")) actionsElement = self.dom.createElement("actions") actionsElement.appendChild(self.dom.createCDATASection(actions)) expectedresultsElement = self.dom.createElement("expectedresults") expectedresultsElement.appendChild(self.dom.createCDATASection(expectedresults)) testcaseNode.appendChild(summaryElement) testcaseNode.appendChild(preconditionsElement) testcaseNode.appendChild(stepsElement) stepsElement.appendChild(stepElement) stepElement.appendChild(step_numberElement) stepElement.appendChild(actionsElement) stepElement.appendChild(expectedresultsElement) suiteNode.appendChild(testcaseNode) def addOneTestCase(self,sub1SuiteName,sub2SuiteName,title,summary,preconditions,actions,expectedresults): if sub1SuiteName.strip()=="": return sub1node = self.getSub1Testsuite(sub1SuiteName) if sub1node is None: sub1node = self.appendSub1Testsuite(sub1SuiteName) if sub2SuiteName.strip()=="": self.appendTestCase(sub1node,title,summary,preconditions,actions,expectedresults) else: sub2node=self.getSub2Testsuite(sub1node, sub2SuiteName) if sub2node is None: sub2node = self.appendSub2Testsuite(sub1node,sub2SuiteName) self.appendTestCase(sub2node,title,summary,preconditions,actions,expectedresults) def dump(self): print(self.dom.toxml()) def write_xml(self,newfilename=None): f = open(newfilename,"w",encoding='utf-8') f.write(self.dom.toprettyxml()) f.close() if __name__ =='__main__': if (len(sys.argv) == 1): print("Please specified a xlsx file") os.system("pause") sys.exit(0) else: tmpInFile = os.getcwd()+"\\"+sys.argv[1] inFile = tmpInFile if not tmpInFile.endswith(".xlsx"): outFile = tmpInFile + "-tcimp.xml" inFile = tmpInFile + ".xlsx" else: outFile = tmpInFile[:-5] + "-tcimp.xml" xmltree = easy_minidom() xls=easy_excel(inFile) row_flag=1 while True: row_flag = row_flag + 1 sub1SuiteName = xls.getCell('Sheet1',row_flag,1) sub2SuiteName = xls.getCell('Sheet1',row_flag,2) title = xls.getCell('Sheet1',row_flag,3) summary = formatLines(xls.getCell('Sheet1',row_flag,5)) preconditions = formatLines(xls.getCell('Sheet1',row_flag,6)) actions = formatLines(xls.getCell('Sheet1',row_flag,7)) expectedresults = formatLines(xls.getCell('Sheet1',row_flag,8)) xmltree.addOneTestCase(sub1SuiteName,sub2SuiteName,title,summary,preconditions,actions,expectedresults) if sub1SuiteName=="": break if row_flag >999: break xmltree.write_xml(outFile) xls.close() print("\n finished") sys.exit(0)
使用方法:在Excel用例文件所在的目录里,shift+鼠标右键,打开一个cmd窗口,输入如下命令:
假设py脚本在D盘
python D:/tcimport.py Excel用例文件.xlsx