Testlink导出用例转化为excel用例

　　当前项目使用的是testlink平台管理测试用例，该平台用例导入导出文件均为xml格式

　　1.导入

　　平时做需求测试分析时，我总是习惯用xmind梳理测试点，而且现在敏捷测试下，很多公司都不局限于测试用例，都在使用xmind做测试点执行，个人感觉思路清晰，就是执行结果不好跟踪。

　　所以我xmind写的用例需要支持导入到testlink，网上找了下，直接使用前辈写的xmind2testlink脚本就好了

　　2.导出

　　交付的时候需要给客户excel格式的测试用例，这下就需要xml文件转为excel，网上找了下，没有好的直接可以用的资源，我照猫画虎写了一个，第一次用python写，可能写的比较挫，但是也完成了转换用例的需求。

　　3.使用方法

　　D:\work>python D:\work\xmlToexcel.py ceshiyongli.xml 

　　导出文件列是下面这个样子：

 

 　　大家可以根据需要修改脚本

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


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




def SetTextWithGt(str):
    strnew=str
    if (str.find(">") != -1):
        strnew = strnew.replace(">", ">")
    if (str.find("“") != -1):
        strnew = strnew.replace("“", "\"")
    if (str.find("”") != -1):
        strnew = strnew.replace("”", "\"")
    if (str.find("<p>") != -1):
        strnew = strnew.replace("<p>", "")
    if (str.find("</p>") != -1):
        strnew = strnew.replace("</p>", "")
    if (str.find("<br />") != -1):
        strnew = strnew.replace("<br />", "")
    return strnew
    pass
def combineCells(startRow,endRow,cloumStart,cloumEnd):
    for clo in range(cloumStart,cloumEnd):
        xls.mergeCells('sheet1', startRow, clo, endRow, clo)
    pass
def convertTestCase(caseinTestsuite,row_flag,rootName="",subName=""):
    for sub1TestCase in caseinTestsuite:
        row_flag = row_flag + 1
        title = SetTextWithGt(sub1TestCase.get("name"))
        externalid = sub1TestCase.find("externalid").text
        preconditions = ""
        if (sub1TestCase.find("summary").text != None):
            preconditions = SetTextWithGt(sub1TestCase.find("summary").text)
        if (sub1TestCase.find("preconditions").text != None):
            preconditions = preconditions+SetTextWithGt(sub1TestCase.find("preconditions").text)

        xls.setCell('Sheet1', row_flag, 1, rootName+'/'+subName)
        xls.setCell('Sheet1', row_flag, 2, externalid)
        xls.setCell('Sheet1', row_flag, 3, title)
        xls.setCell('Sheet1', row_flag, 4, preconditions)
        stepsNode = sub1TestCase.find("steps")
        num = 0
        if sub1TestCase.find("steps"):
            stepCount = len(stepsNode.findall("step"))
            for stepNode in stepsNode.findall("step"):
                stepExpt = ""
                stepText = ""
                if (stepNode.find('actions').text != None):
                    stepText = SetTextWithGt(stepNode.find('actions').text.strip())
                if (stepNode.find('expectedresults').text != None):
                    stepExpt = SetTextWithGt(stepNode.find('expectedresults').text.strip())
                xls.setCell('Sheet1', row_flag, 5, stepText)
                xls.setCell('Sheet1', row_flag, 6, stepExpt)
                if (num + 1 < stepCount):
                    num += 1
                    row_flag += 1

        combineCells(row_flag - num, row_flag, 1, 5)
    return row_flag
    pass
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"+str(int(time.time()))+".xlsx"
        inFile = tmpInFile + ".xml"
    else:
        outFile = tmpInFile[:-4] + "-tcexp"+str(int(time.time()))+".xlsx"
    print(f'outFile--{outFile}')
    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.set_col_width('Sheet1')

    tree = ElementTree.parse(inFile)
    root = tree.getroot()

    row_flag = 1
    # 一级目录
    if(len(root.findall("testsuite"))>0):
        sub1SuiteName = root.get("name")
        for sub1Testsuite in root.findall("testsuite"):
            sub1SuiteId = sub1Testsuite.get("id")
            sub2SuiteName = sub1Testsuite.get("name")
            row_flag = convertTestCase(sub1Testsuite.findall("testcase"), row_flag,sub1SuiteName,sub2SuiteName)
            print(f'{sub2SuiteName}---convert success')
            print(f'row_flag---{row_flag}')
    elif(len(root.findall("testcase"))>0):
        row_flag = convertTestCase(root.findall("testcase"),row_flag,root.get("name"),"")

    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)