testlink用例的导出到Excel

一直在网上寻找怎么把testlink的用例导出到Excel中，以及把Excel中已经写好的用例导入到Testlink中的方法。根据现网的经验，然后修改了一下。贴出来，以飨有这方面需求的测试同仁。

Testlink版本为1.9.16，导出的用例目录最大支持两层，

导出的Excel文件以xlsx结尾，必须有Sheet1这一页。

导出的格式应该是这个样子的

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

python文件 tcexport.py，testlink用例的导出

#coding=utf-8
from xml.etree import ElementTree
from win32com.client import Dispatch
import win32com.client
import sys,os

def ChangeReturnKeyInString(str):
    retStr = str.strip().replace('<p>','')
    retStr = retStr.replace('</p>','\n')
    return retStr

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.Add()
            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)
        return sht.Cell(row,col).Value
    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("E:H").ColumnWidth=30
 
if __name__ =='__main__':   
    if (len(sys.argv) == 1):
        print("Please specified a xml file")
        os.system("pause")
        sys.exit(0)
    else:
        tmpInFile = os.getcwd()+"\\"+sys.argv[1]
        inFile = tmpInFile
        if not tmpInFile.endswith(".xml"):
            outFile = tmpInFile + "-tcexp.xlsx"
            inFile  = tmpInFile + ".xml"
        else:
            outFile =  tmpInFile[:-4] +"-tcexp.xlsx"
            
        
    xls=easy_excel()
    xls.setCell('Sheet1',1,1,u"一级目录")
    xls.setCell('Sheet1',1,2,u"二级目录")
    xls.setCell('Sheet1',1,3,u"用例名称")
    xls.setCell('Sheet1',1,4,u"用例编号")
    xls.setCell('Sheet1',1,5,u"用例概要说明")
    xls.setCell('Sheet1',1,6,u"预置条件")
    xls.setCell('Sheet1',1,7,u"操作步骤")
    xls.setCell('Sheet1',1,8,u"预期结果")
    xls.set_col_width('Sheet1')
    
    tree=ElementTree.parse(inFile)
    root = tree.getroot()

    row_flag=1
    #一级目录
    for sub1Testsuite in root.findall("testsuite"):
        sub1SuiteId = sub1Testsuite.get("id")
        sub1SuiteName = sub1Testsuite.get("name")
        for sub1TestCase in sub1Testsuite.findall("testcase"):
            row_flag = row_flag + 1
            sub2SuiteName = ""
            title = sub1TestCase.get("name")
            externalid = sub1TestCase.find("externalid").text
            summary = ChangeReturnKeyInString(sub1TestCase.find("summary").text)
            preconditions = ChangeReturnKeyInString(sub1TestCase.find("preconditions").text)
            
            xls.setCell('Sheet1',row_flag,1,sub1SuiteName)
            xls.setCell('Sheet1',row_flag,2,sub2SuiteName)
            xls.setCell('Sheet1',row_flag,3,title)
            xls.setCell('Sheet1',row_flag,4,externalid)
            xls.setCell('Sheet1',row_flag,5,summary)
            xls.setCell('Sheet1',row_flag,6,preconditions)
            stepsNode=sub1TestCase.find("steps")
            actions = ""
            expectedresults = ""
            for stepNode in stepsNode.findall("step"):
                step_number = stepNode.find('step_number').text
                actions = actions+stepNode.find('actions').text.strip()
                expectedresults = expectedresults + stepNode.find('expectedresults').text.strip()
            actions = ChangeReturnKeyInString(actions)
            expectedresults = ChangeReturnKeyInString(expectedresults)       
            xls.setCell('Sheet1',row_flag,7,actions)
            xls.setCell('Sheet1',row_flag,8,expectedresults)        

        for sub2Testsuite in sub1Testsuite.findall("testsuite"):
            sub2SuiteId = sub2Testsuite.get("id")
            sub2SuiteName = sub2Testsuite.get("name")      
            for sub2TestCase in sub2Testsuite.findall("testcase"):
                row_flag = row_flag + 1
                title = sub2TestCase.get("name")
                externalid = sub2TestCase.find("externalid").text
                summary = ChangeReturnKeyInString(sub2TestCase.find("summary").text)
                preconditions = ChangeReturnKeyInString(sub2TestCase.find("preconditions").text)
                
                xls.setCell('Sheet1',row_flag,1,sub1SuiteName)
                xls.setCell('Sheet1',row_flag,2,sub2SuiteName)
                xls.setCell('Sheet1',row_flag,3,title)
                xls.setCell('Sheet1',row_flag,4,externalid)
                xls.setCell('Sheet1',row_flag,5,summary)  
                xls.setCell('Sheet1',row_flag,6,preconditions)    
                actions = ""
                expectedresults = ""            
                stepsNode=sub2TestCase.find("steps")
                for stepNode in stepsNode.findall("step"):
                    step_number = stepNode.find('step_number').text
                    actions =actions + stepNode.find('actions').text
                    expectedresults = expectedresults + stepNode.find('expectedresults').text
                actions = ChangeReturnKeyInString(actions)
                expectedresults = ChangeReturnKeyInString(expectedresults)             
                xls.setCell('Sheet1',row_flag,7,actions)
                xls.setCell('Sheet1',row_flag,8,expectedresults)
                
    for row in range(2,row_flag):
        for col in range(1,9):
            xls.setBorder('Sheet1',row,col)    
    xls.save(outFile)
    xls.close()
    
    print("finished.")
    sys.exit(0)

 

使用方法：在Excel用例文件所在的目录里，shift+鼠标右键，打开一个cmd窗口，输入如下命令：

假设py脚本在D盘

python D:\tcimport.py testproject-deep.xml