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

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

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

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

easy_excel.py:

 1 # coding=utf-8
 2 from xml.etree import ElementTree
 3 from win32com.client import Dispatch
 4 import win32com.client
 5 import os
 6 import sys
 7 reload(sys)
 8 sys.setdefaultencoding("utf-8")
 9 
10 class easy_excel:
11     def __init__(self, filename=None):
12         self.xlApp = win32com.client.Dispatch('Excel.Application')
13 
14         if filename:
15             self.filename = os.getcwd() + "\\" + filename
16             # self.xlApp.Visible=True
17             self.xlBook = self.xlApp.Workbooks.Open(self.filename)
18         else:
19             # self.xlApp.Visible=True
20             self.xlBook = self.xlApp.Workbooks.Add()
21             self.filename = ''
22 
23     def save(self, newfilename=None):
24         if newfilename:
25             self.filename = os.getcwd() + "\\" + newfilename
26             # if os.path.exists(self.filename):
27             # os.remove(self.filename)
28             self.xlBook.SaveAs(self.filename)
29         else:
30             self.xlBook.Save()
31 
32     def close(self):
33         self.xlBook.Close(SaveChanges=0)
34         self.xlApp.Quit()
35 
36     def getCell(self, sheet, row, col):
37         sht = self.xlBook.Worksheets(sheet)
38         return sht.Cells(row, col).Value
39 
40     def setCell(self, sheet, row, col, value):
41         sht = self.xlBook.Worksheets(sheet)
42         sht.Cells(row, col).Value = value
43         # 设置居中
44         sht.Cells(row, col).HorizontalAlignment = 3
45         sht.Rows(row).WrapText = True
46 
47     def mergeCells(self, sheet, row1, col1, row2, col2):
48         start_coloum = int(dic_config["start_coloum"])
49         # 如果这列不存在就不合并单元格
50         if col2 != start_coloum - 1:
51             sht = self.xlBook.Worksheets(sheet)
52             sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Merge()
53             # else:
54             # print 'Merge cells coloum %s failed!' %col2
55 
56     def setBorder(self, sheet, row, col):
57         sht = self.xlBook.Worksheets(sheet)
58         sht.Cells(row, col).Borders.LineStyle = 1
59 
60     def set_col_width(self, sheet, start, end, length):
61         start += 96
62         end += 96
63         msg = chr(start) + ":" + chr(end)
64         # print msg
65         sht = self.xlBook.Worksheets(sheet)
66         sht.Columns(msg.upper()).ColumnWidth = length

operate.py:

  1 # coding:utf-8
  2 import os
  3 import sys
  4 reload(sys)
  5 sys.setdefaultencoding("utf-8")
  6 
  7 from easy_excel import easy_excel
  8 class operate():
  9     def __init__(self, ExcelFileName, SheetName):
 10         self.excelFile = ExcelFileName + '.xls'
 11         self.excelSheet = SheetName
 12         self.temp = easy_excel(self.excelFile)
 13         self.dic_testlink = {}
 14         self.row_flag = 3
 15         self.testsuite = self.temp.getCell(self.excelSheet, 2, 1)
 16         self.dic_testlink[self.testsuite] = {"node_order": "13", "details": "", "testcase": []}
 17         self.content = ""
 18         self.content_list = []
 19 
 20     def xlsx_to_dic(self, SheetName):
 21         while True:
 22             # print 'loop1'
 23             # list_testcase = dic_testlink[testsuite].["testcase"]
 24 
 25             testcase = {"name": "", "node_order": "100", "externalid": "", "version": "1", "summary": "",
 26                         "preconditions": "", "execution_type": "1", "importance": "3", "steps": [], "keywords": "P1"}
 27             testcase["name"] = self.temp.getCell(self.excelSheet, self.row_flag, 1)
 28             testcase["summary"] = self.temp.getCell(self.excelSheet, self.row_flag, 3)
 29             testcase["preconditions"] = self.temp.getCell(self.excelSheet, self.row_flag, 4)
 30             execution_type = self.temp.getCell(self.excelSheet, self.row_flag, 7)
 31             if execution_type == "自动":
 32                 testcase["execution_type"] = 2
 33             # print self.temp.getCell('Sheet1',self.row_flag,3)
 34             step_number = 1
 35             testcase["keywords"] = self.temp.getCell(self.excelSheet, self.row_flag, 2)
 36             # print testcase["keywords"]
 37             while True:
 38                 # print 'loop2'
 39                 step = {"step_number": "", "actions": "", "expectedresults": "", "execution_type": ""}
 40                 step["step_number"] = step_number
 41                 step["actions"] = self.temp.getCell(self.excelSheet, self.row_flag, 5)
 42                 step["expectedresults"] = self.temp.getCell(self.excelSheet, self.row_flag, 6)
 43                 testcase["steps"].append(step)
 44                 step_number += 1
 45                 self.row_flag += 1
 46                 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:
 47                     break
 48             # print testcase
 49 
 50             self.dic_testlink[self.testsuite]["testcase"].append(testcase)
 51             # print self.row_flag
 52             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:
 53                 break
 54         self.temp.close()
 55         # print self.dic_testlink
 56 
 57     def content_to_xml(self, key, value=None):
 58         if key == 'step_number' or key == 'execution_type' or key == 'node_order' or key == 'externalid' or key == 'version' or key == 'importance':
 59             return "<" + str(key) + "><![CDATA[" + str(value) + "]]></" + str(key) + ">"
 60         elif key == 'actions' or key == 'expectedresults' or key == 'summary' or key == 'preconditions':
 61             return "<" + str(key) + "><![CDATA[<p> " + str(value) + "</p> ]]></" + str(key) + ">"
 62         elif key == 'keywords':
 63             return '<keywords><keyword name="' + str(value) + '"><notes><![CDATA[ aaaa ]]></notes></keyword></keywords>'
 64         elif key == 'name':
 65             return '<testcase name="' + str(value) + '">'
 66         else:
 67             return '##########'
 68 
 69     def dic_to_xml(self, ExcelFileName, SheetName):
 70         testcase_list = self.dic_testlink[self.testsuite]["testcase"]
 71         for testcase in testcase_list:
 72             for step in testcase["steps"]:
 73                 self.content += "<step>"
 74                 self.content += self.content_to_xml("step_number", step["step_number"])
 75                 self.content += self.content_to_xml("actions", step["actions"])
 76                 self.content += self.content_to_xml("expectedresults", step["expectedresults"])
 77                 self.content += self.content_to_xml("execution_type", step["execution_type"])
 78                 self.content += "</step>"
 79             self.content = "<steps>" + self.content + "</steps>"
 80             self.content = self.content_to_xml("importance", testcase["importance"]) + self.content
 81             self.content = self.content_to_xml("execution_type", testcase["execution_type"]) + self.content
 82             self.content = self.content_to_xml("preconditions", testcase["preconditions"]) + self.content
 83             self.content = self.content_to_xml("summary", testcase["summary"]) + self.content
 84             self.content = self.content_to_xml("version", testcase["version"]) + self.content
 85             self.content = self.content_to_xml("externalid", testcase["externalid"]) + self.content
 86             self.content = self.content_to_xml("node_order", testcase["node_order"]) + self.content
 87             self.content = self.content + self.content_to_xml("keywords", testcase["keywords"])
 88             self.content = self.content_to_xml("name", testcase["name"]) + self.content
 89             self.content = self.content + "</testcase>"
 90             self.content_list.append(self.content)
 91             self.content = ""
 92         self.content = "".join(self.content_list)
 93         self.content = '<testsuite name="' + self.testsuite + '">' + self.content + "</testsuite>"
 94         self.content = '<?xml version="1.0" encoding="UTF-8"?>' + self.content
 95         self.write_to_file(ExcelFileName, SheetName)
 96 
 97     def write_to_file(self, ExcelFileName, SheetName):
 98         xmlFileName = ExcelFileName + '_' + SheetName + '.xml'
 99         cp = open(xmlFileName, "w")
100         cp.write(self.content)
101         cp.close()
102 
103 if __name__ == "__main__":
104 
105     fileName = raw_input('enter excel name:')
106     sheetName = raw_input('enter sheet name:')
107     sheetList = sheetName.split(" ")
108     for sheetName in sheetList:
109         test = operate(fileName, sheetName)
110         test.xlsx_to_dic(sheetName)
111         test.dic_to_xml(fileName, sheetName)
112     print "Convert success!"
113     os.system('pause')