Testlink用例转换工具（excel转为xml，python版）

前面文章记录了testlink的安装方法（CentOS 7下安装xampp和testlink），由于testlink仅支持xml格式的用例导入，研究了下excel转xml的方法，从网上其他网友那里借用了部分代码，自己又补充修改了下，供大家参考，使用的时候要在PC上安装python 2.7。

所有文件在文章最后面的百度网盘上。

一、代码（有两个py文件）：

easy_excel.py:

# coding=utf-8from xml.etree import ElementTreefrom win32com.client import Dispatchimport win32com.clientimport osimport sysreload(sys)sys.setdefaultencoding("utf-8")class easy_excel:    def __init__(self, filename=None):        self.xlApp = win32com.client.Dispatch('Excel.Application')        if filename:            self.filename = os.getcwd() + "\\" + filename            # self.xlApp.Visible=True            self.xlBook = self.xlApp.Workbooks.Open(self.filename)        else:            # self.xlApp.Visible=True            self.xlBook = self.xlApp.Workbooks.Add()            self.filename = ''    def save(self, newfilename=None):        if newfilename:            self.filename = os.getcwd() + "\\" + newfilename            # if os.path.exists(self.filename):            # os.remove(self.filename)            self.xlBook.SaveAs(self.filename)        else:            self.xlBook.Save()    def close(self):        self.xlBook.Close(SaveChanges=0)        self.xlApp.Quit()    def getCell(self, sheet, row, col):        sht = self.xlBook.Worksheets(sheet)        return sht.Cells(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):        start_coloum = int(dic_config["start_coloum"])        # 如果这列不存在就不合并单元格        if col2 != start_coloum - 1:            sht = self.xlBook.Worksheets(sheet)            sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Merge()            # else:            # print 'Merge cells coloum %s failed!' %col2    def setBorder(self, sheet, row, col):        sht = self.xlBook.Worksheets(sheet)        sht.Cells(row, col).Borders.LineStyle = 1    def set_col_width(self, sheet, start, end, length):        start += 96        end += 96        msg = chr(start) + ":" + chr(end)        # print msg        sht = self.xlBook.Worksheets(sheet)        sht.Columns(msg.upper()).ColumnWidth = length

operate.py:

# coding:utf-8import osimport sysreload(sys)sys.setdefaultencoding("utf-8")from easy_excel import easy_excelclass operate():    def __init__(self, ExcelFileName, SheetName):        self.excelFile = ExcelFileName + '.xls'        self.excelSheet = SheetName        self.temp = easy_excel(self.excelFile)        self.dic_testlink = {}        self.row_flag = 3        self.testsuite = self.temp.getCell(self.excelSheet, 2, 1)        self.dic_testlink[self.testsuite] = {"node_order": "13", "details": "", "testcase": []}        self.content = ""        self.content_list = []    def xlsx_to_dic(self, SheetName):        while True:            # print 'loop1'            # list_testcase = dic_testlink[testsuite].["testcase"]            testcase = {"name": "", "node_order": "100", "externalid": "", "version": "1", "summary": "",                        "preconditions": "", "execution_type": "1", "importance": "3", "steps": [], "keywords": "P1"}            testcase["name"] = self.temp.getCell(self.excelSheet, self.row_flag, 1)            testcase["summary"] = self.temp.getCell(self.excelSheet, self.row_flag, 3)            testcase["preconditions"] = self.temp.getCell(self.excelSheet, self.row_flag, 4)            execution_type = self.temp.getCell(self.excelSheet, self.row_flag, 7)            if execution_type == "自动":                testcase["execution_type"] = 2            # print self.temp.getCell('Sheet1',self.row_flag,3)            step_number = 1            testcase["keywords"] = self.temp.getCell(self.excelSheet, self.row_flag, 2)            # print testcase["keywords"]            while True:                # print 'loop2'                step = {"step_number": "", "actions": "", "expectedresults": "", "execution_type": ""}                step["step_number"] = step_number                step["actions"] = self.temp.getCell(self.excelSheet, self.row_flag, 5)                step["expectedresults"] = self.temp.getCell(self.excelSheet, self.row_flag, 6)                testcase["steps"].append(step)                step_number += 1                self.row_flag += 1                if self.temp.getCell(self.excelSheet, self.row_flag, 1) is not None or self.temp.getCell(self.excelSheet, self.row_flag, 5) is None:                    break            # print testcase            self.dic_testlink[self.testsuite]["testcase"].append(testcase)            # print self.row_flag            if self.temp.getCell(self.excelSheet, self.row_flag, 5) is None and self.temp.getCell(self.excelSheet, self.row_flag + 1, 5) is None:                break        self.temp.close()        # print self.dic_testlink    def content_to_xml(self, key, value=None):        if key == 'step_number' or key == 'execution_type' or key == 'node_order' or key == 'externalid' or key == 'version' or key == 'importance':            return "<" + str(key) + "><![CDATA[" + str(value) + "]]></" + str(key) + ">"        elif key == 'actions' or key == 'expectedresults' or key == 'summary' or key == 'preconditions':            return "<" + str(key) + "><![CDATA[<p> " + str(value) + "</p> ]]></" + str(key) + ">"        elif key == 'keywords':            return '<keywords><keyword name="' + str(value) + '"><notes><![CDATA[ aaaa ]]></notes></keyword></keywords>'        elif key == 'name':            return '<testcase name="' + str(value) + '">'        else:            return '##########'    def dic_to_xml(self, ExcelFileName, SheetName):        testcase_list = self.dic_testlink[self.testsuite]["testcase"]        for testcase in testcase_list:            for step in testcase["steps"]:                self.content += "<step>"                self.content += self.content_to_xml("step_number", step["step_number"])                self.content += self.content_to_xml("actions", step["actions"])                self.content += self.content_to_xml("expectedresults", step["expectedresults"])                self.content += self.content_to_xml("execution_type", step["execution_type"])                self.content += "</step>"            self.content = "<steps>" + self.content + "</steps>"            self.content = self.content_to_xml("importance", testcase["importance"]) + self.content            self.content = self.content_to_xml("execution_type", testcase["execution_type"]) + self.content            self.content = self.content_to_xml("preconditions", testcase["preconditions"]) + self.content            self.content = self.content_to_xml("summary", testcase["summary"]) + self.content            self.content = self.content_to_xml("version", testcase["version"]) + self.content            self.content = self.content_to_xml("externalid", testcase["externalid"]) + self.content            self.content = self.content_to_xml("node_order", testcase["node_order"]) + self.content            self.content = self.content + self.content_to_xml("keywords", testcase["keywords"])            self.content = self.content_to_xml("name", testcase["name"]) + self.content            self.content = self.content + "</testcase>"            self.content_list.append(self.content)            self.content = ""        self.content = "".join(self.content_list)        self.content = '<testsuite name="' + self.testsuite + '">' + self.content + "</testsuite>"        self.content = '<?xml version="1.0" encoding="UTF-8"?>' + self.content        self.write_to_file(ExcelFileName, SheetName)    def write_to_file(self, ExcelFileName, SheetName):        xmlFileName = ExcelFileName + '_' + SheetName + '.xml'        cp = open(xmlFileName, "w")        cp.write(self.content)        cp.close()if __name__ == "__main__":    fileName = raw_input('enter excel name:')    sheetName = raw_input('enter sheet name:')    sheetList = sheetName.split(" ")    for sheetName in sheetList:        test = operate(fileName, sheetName)        test.xlsx_to_dic(sheetName)        test.dic_to_xml(fileName, sheetName)    print "Convert success!"    os.system('pause')

二、转换方法：

1、将要转换的测试用例文件放置在与py文件的文件夹中，测试用例样式见下图，

将每个“测试集”放在一个Sheet中，每个Sheet的第二行为该“测试集”的名称，如下图，“运行环境测试”为该测试集的名称，

Sheet的名称，建议与测试集的名称一致，如下图：

 双击"operate.py"文件，出现控制台窗口，输入excel文件名称，回车，输入要转换的sheet的名称，多个sheet之间以“空格”隔开，

再回车，出现“Convert success！”转换完成。

转换前后的excel及xml文件：

三、导入testlink：

 百度网盘：

http://pan.baidu.com/s/1o8fOaPc