Excel用例导入到Testlink

一直在网上寻找怎么把Excel中已经写好的用例导入到Testlink中的方法。根据现网的经验，然后修改了一下。贴出来，以飨有这方面需求的测试同仁。
Excel文件以xlsx结尾，必须有Sheet1这一页。
Testlink版本为1.9.16，用例目录最大支持两层.
Excel用例的格式

一级目录	二级目录	用例名称	用例编号	用例概要说明	预置条件	操作步骤	预期结果
用例集1		测试用例1	1	测试用例1	前提1	步骤1 步骤2	期望1 期望2
用例集1	用例集1-1	测试用例2	2	测试用例2	前提1	步骤1 步骤2	期望1 期望2

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

 

转载于:https://www.cnblogs.com/huangchangqing/p/8447048.html