EXCEL转换为XML如何用Python实现？

lsekfe

本文将结合作者项目实践，介绍如何将EXCEL文件通过python转换为XML，以方便导入到Testlink中（Testlink仅支持XML文件的导入），并且顺便介绍一下Python常见的错误及解决方案。希望对大家有所帮助。
声明：文中转换工具涉及的代码是借鉴了其他帖子上的一部代码，结合本文作者项目的实际情况，编写而成，原始出处已经不得而知，如有雷同，不是巧合。
1 输出EXCEL格式的测试用例文件
1.1 测试用例使用如下模板输出，保存为XXX.xlsx文件，例如命名case.xlsx

1.2 部分字段解释：
1.2.1 功能点
一般EXCEL文件的一个Sheet页代表一个模块，在一个模块内部分为多个功能点，通过一个或多个测试用例对每个功能点进行一一覆盖。功能点字段就是对模块进行结构化划分的。
1.2.2 测试方式
该字段用以标识测试方式，是手工测试还是自动化测试，方便日后识别哪些用例是自动化实现的用例
1.2.3 关键字
这个字段可以多种用途，例如用于识别产品形态，识别用例输出版本，或者用于识别质量属性，都是可以的。作者项目中，是用于识别质量属性的，例如功能测试、性能测试、易用性测试等等
1.2.4 重要性
填写P0/P1/P2，表示三个用例级别：高中低
1.2.5 设计人员
该字段是用来标识用例的作者的，用于方便可追溯性或用例维护。
2 安装Python 3
由于本文中的EXCEL转换为xml的工具是用python语言写的，所以，首先，需要安装python运行环境，本文中用的是3.6.2。
若已经安装了Python 3，则跳过此步骤。
2.1 下载安装python
Python3可以去网上免费下载安装，地址为：
https://www.python.org/ftp/python/3.6.2/python-3.6.2-amd64.exe
2.2 配置运行环境
安装完python后，配置环境变量
我的电脑-》属性-》高级系统设置-》高级-》环境变量，在用户变量部分，找到变量名为path的，点击编辑，新建，将Python安装目录及pip所在目录都添加到环境变量表中，此例中为:\Python3d6\Python36\和F:\Python3d6\Python36\Scripts\

具体的步骤在本文中就不赘述了，需要的可以自己去网上找。
2.3 调试python及pip
配置好环境变量后，就在CMD下，运行python和pip，安装py脚本中涉及的python库。
但是作者在调试过程中，曾经遇到了如下的几个问题，供大家参考：
2.3.1 错误一
现象：
Cmd命令行下，敲pip报错，敲python正常
具体信息如下：

C:\Users\Administrator>pip
Fatal error in launcher: Unable to create process using '"c:\python36\python3.exe""F:\Python3d6\Python36\Scripts\pip.exe" ': ???????????
C:\Users\Administrator>python3
Python 3.6.2 (v3.6.2:5fd33b5, Jul  8 2017, 04:57:36) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.

前置条件：
1）Python安装目录为F:\Python3d6\Python36\
2）已经将F:\Python3d6\Python36\和F:\Python3d6\Python36\Scripts\配置到环境变量中
排查思路：
1、第一步
经初步判断，感觉是路径有问题
随后，将安装路径改为F:\Python36\，即将“Python3d6”这层文件夹去掉，并重新配置环境变量
现象：Cmd命令行下，敲python正常，敲pip仍然后报错
报错如下：

1. C:\Users\Administrator>pip install pywin32
   
2. Fatal error in launcher: Unable to create process using '"c:\python36\python3.exe""F:\Python36\Scripts\pip.exe" install pywin32': ???????????
   

复制代码

2、第二步
排除环境变量配置问题：
cd到安装目录下，执行pip，仍然报错
具体报错信息如下：

1. F:\Python36\Scripts>pip install pywin32
   
2. Fatal error in launcher: Unable to create process using '"c:\python36\python3.exe""F:\Python36\Scripts\pip.exe" install pywin32': ???????????
   

复制代码

3、第三步
尝试把python整个文件夹从F盘移动到C盘，然后修改好环境变量
执行pip，没有报错，问题搞定
打印信息如下：

C:\Users\Administrator>pip
Usage:
  pip <command> [options]
Commands:
  install                     Install packages.
  download                    Download packages.
  uninstall                   Uninstall packages.
......

2.3.2 错误二
现象：
Python运行.py文件报错 ：NameError name 'reload' is not defined
相关代码：

import sys
reload(sys)
sys.setdefaultencoding("utf-8")

原因：
setdefaultencoding是python 2的sys库里边的函数，Python 3 的 sys 库里面已经没有 setdefaultencoding() 函数，并且Python 3 系统默认使用的就是utf-8编码
解决方案：
将sys.setdefaultencoding("utf-8")代码整行去掉即可
2.3.3 错误三
现象：调用print函数出错
具体报错信息如下：

1. C:\Users\Administrator>python3 F:\case\operate.py
   
2.   File "F:\case\operate.py", line 46
   
3.     print 'str=', str
   
4.                ^
   
5. SyntaxError: Missing parentheses in call to 'print'
   

复制代码

原因：

python2.X版本与python3.X版本输出方式不同造成的

解决方案：

Print函数后面加上括号（）

1.1.1 Python2代码与Python3代码不兼容

其实将python代码用python3执行，很多问题都是由于python2版本和python版本代码不兼容导致的。

下面以实际的例子，介绍一种python2代码转换成python3代码的方法：

假设：python 2.X到python 3.X转换文件2to3.py的路径为C:\Python36\Tools\scripts

待转换的文件operate.py的路径为D:\python2to3

CMD下执行如下命令，即可将该python2文件转换为python3文件

python3 C:\Python36\Tools\scripts\2to3.py -wD:\python2to3\operate.py

出现如下打印说明转换过程成功

1. C:\Users\Administrator>python3 C:\Python36\Tools\scripts\2to3.py -w D:\python2to3\operate.py
   
2. RefactoringTool: Skipping optional fixer: buffer
   
3. RefactoringTool: Skipping optional fixer: idioms
   
4. RefactoringTool: Skipping optional fixer: set_literal
   
5. RefactoringTool: Skipping optional fixer: ws_comma
   
6. RefactoringTool: Refactored D:\python2to3\operate.py
   
7. --- D:\python2to3\operate.py    (original)
   
8. +++ D:\python2to3\operate.py    (refactored)
   
9. [url=home.php?mod=space&uid=40658]@@[/url] -1,7 +1,8 @@
   
10. # coding:utf-8
    
11. import os,re,xlrd
    
12. import sys
    
13. -reload(sys)
    
14. +import imp
    
15. +imp.reload(sys)
    
16. sys.setdefaultencoding("utf-8")
    
17. 
    
18. from easy_excel import easy_excel
    
19. @@ -43,7 +44,7 @@
    
20.                  def actions_add_div(str):
    
21.                      if str:
    
22.                          new_action_str=""
    
23. -                        print 'str=', str
    
24. +                        print('str=', str)
    
25.                          for str_index in str.split('\n'):
    
26.                              newline=str_index + "" + "</div>" + "<div>" + "" + "</p>" + "<p>"
    
27.                              new_action_str+=newline
    
28. @@ -122,16 +123,16 @@
    
29. 
    
30. if __name__ == "__main__":
    
31. 
    
32. -    print os.path.abspath('.')
    
33. +    print(os.path.abspath('.'))
    
34.      excellist=[]
    
35.      #列出当前目录下的所有.xml文件
    
36.      for fullname in iterbrowse(os.path.abspath('.')):
    
37. -        print fullname
    
38. +        print(fullname)
    
39.          obj1=re.compile(r'([\W\w]*)(\.xlsx)
    
40. [/color][/align]
    
41. 
    
42. 
    
43. 
    
44. [/backcolor]
    
45. 
    
46. [backcolor=rgb(247, 247, 247)]
    
47. [/backcolor]
    
48. 
    
49. 
    
50. 
    
51. )
    
52.          for m in obj1.finditer(fullname):
    
53. -            print m.group()
    
54. +            print(m.group())
    
55.              excellist.append(m.group())
    
56. -    print excellist
    
57. +    print(excellist)
    
58.      for fileName in excellist:
    
59.          fileName=fileName.split('\\')[-1]
    
60.          file_data = xlrd.open_workbook(fileName)
    
61. @@ -139,12 +140,12 @@
    
62.          sheetList=[]
    
63.          for index in range(sheetnum):
    
64.              sheet_name=file_data.sheets()[index].name
    
65. -            print sheet_name
    
66. +            print(sheet_name)
    
67.              sheetList.append(sheet_name)
    
68. -        print sheetList
    
69. +        print(sheetList)
    
70.          for sheetName in sheetList:
    
71.              test = operate(fileName, sheetName)
    
72.              test.xlsx_to_dic(sheetName)
    
73.              test.dic_to_xml(fileName, sheetName)
    
74. -    print "Convert success!"
    
75. +    print("Convert success!")
    
76.      os.system('pause')
    
77. RefactoringTool: Files that were modified:
    
78. RefactoringTool: D:\python2to3\operate.py
    

复制代码

lsekfe

从日志中可以看出，转换过程中，对哪些行进行了修改一目了然
如果某python文件不需要转换，则会出现如下的打印：

1. C:\Users\Administrator>python3 C:\Python36\Tools\scripts\2to3.py -w D:\python2to3\operate.py
   
2. RefactoringTool: Skipping optional fixer: buffer
   
3. RefactoringTool: Skipping optional fixer: idioms
   
4. RefactoringTool: Skipping optional fixer: set_literal
   
5. RefactoringTool: Skipping optional fixer: ws_comma
   
6. RefactoringTool: No changes to D:\python2to3\operate.py
   
7. RefactoringTool: Files that need to be modified:
   
8. RefactoringTool: D:\python2to3\operate.py
   

复制代码

3 将EXCEL转换为xml
安装好python库后，将用例表格与脚本放入同目录（脚本代码在文末），如下：


注意：用例表格的名称需用英文，不要含有中文
cd到表格和脚本所在目录，然后运行 operate.py 文件，打印如下：

1. D:\python2to3>python3 operate.py
   
2. D:\python2to3
   
3. D:\python2to3\case.xlsx
   
4. D:\python2to3\case.xlsx
   
5. D:\python2to3\easy_excel.py
   
6. D:\python2to3\easy_excel.py.bak
   
7. D:\python2to3\operate.py
   
8. D:\python2to3\operate.py.bak
   
9. D:\python2to3\__pycache__\easy_excel.cpython-36.pyc
   
10. ['D:\\python2to3\\case.xlsx']
    
11. XX功能模块1
    
12. XX功能模块2
    
13. ['XX功能模块1', 'XX功能模块2']
    
14. ('str=', '1.步骤一\n2.步骤二\n3.步骤三')
    
15. ('str=', '1.步骤一\n2.步骤二\n3.步骤三')
    
16. Convert success!
    
17. 请按任意键继续. . .
    

复制代码

转换成功，按照EXCEL的sheet页输出两个XML文件：case.xlsx_XX功能模块1.xml和case.xlsx_XX功能模块2.xml，可以用来导入到Testlink用例管理系统中

4 代码
4.1 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. import imp
   
8. imp.reload(sys)
   
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
    

复制代码

4.2 operate.py

1. # coding:utf-8
   
2. import os,re,xlrd
   
3. import sys
   
4. import imp
   
5. imp.reload(sys)
   
6. 
   
7. from easy_excel import easy_excel
   
8. class operate():
   
9.     def __init__(self, ExcelFileName, SheetName):
   
10.         self.excelFile = ExcelFileName
    
11.         self.excelSheet = SheetName
    
12.         self.temp = easy_excel(self.excelFile)
    
13.         self.dic_testlink = {}
    
14.         self.row_flag = 2
    
15.         self.testsuite = self.temp.getCell(self.excelSheet, 2, 2)
    
16.         #print 'self.testsuite=',self.testsuite
    
17.         self.dic_testlink[self.testsuite] = {"node_order": "13", "details": "", "testcase": []}
    
18.         self.content = ""
    
19.         self.content_list = []
    
20. 
    
21.     def xlsx_to_dic(self, SheetName):
    
22.         while True:
    
23.             testcase = {"name": "", "node_order": "100", "externalid": "", "version": "1", "summary": "",
    
24. "preconditions": "", "execution_type": "1", "importance": "3", "steps": [], "keywords": "P1", "author":""}
    
25.             testcase["name"] = self.temp.getCell(self.excelSheet, self.row_flag, 5)
    
26.             testcase["summary"] = self.temp.getCell(self.excelSheet, self.row_flag, 3)
    
27.             testcase["preconditions"] = self.temp.getCell(self.excelSheet, self.row_flag, 6)
    
28.             execution_type = self.temp.getCell(self.excelSheet, self.row_flag, 9)
    
29.             testcase["keywords"] = self.temp.getCell(self.excelSheet, self.row_flag, 10)
    
30.             testcase["author"] = self.temp.getCell(self.excelSheet, self.row_flag, 11)
    
31.             if execution_type == "自动":
    
32.                 testcase["execution_type"] = 2
    
33.             step_number = 1
    
34.             importance = self.temp.getCell(self.excelSheet, self.row_flag, 4)
    
35.             if importance == "基本功能" or importance == "P1":
    
36.                 testcase["importance"]="2"
    
37.             elif importance == "拓展" or importance == "P2":
    
38.                 testcase["importance"] = "1"
    
39.             while True:
    
40.                 step = {"step_number": "", "actions": "", "expectedresults": "", "execution_type": ""}
    
41.                 step["step_number"] = step_number
    
42.                 step["actions"] = self.temp.getCell(self.excelSheet, self.row_flag, 7)
    
43.                 def actions_add_div(str):
    
44.                     if str:
    
45.                         new_action_str=""
    
46.                         print(('str=', str))
    
47.                         for str_index in str.split('\n'):
    
48.                             newline=str_index + "" + "</div>" + "<div>" + "" + "</p>" + "<p>"
    
49.                             new_action_str+=newline
    
50.                         return new_action_str[:-21]
    
51.                 step["actions"]=actions_add_div(step["actions"])
    
52.                 step["expectedresults"] = self.temp.getCell(self.excelSheet, self.row_flag, 8)
    
53.                 testcase["steps"].append(step)
    
54.                 step_number += 1
    
55.                 self.row_flag += 1
    
56.                 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:
    
57.                     break
    
58.             # print testcase
    
59. 
    
60.             self.dic_testlink[self.testsuite]["testcase"].append(testcase)
    
61.             # print self.row_flag
    
62.             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:
    
63.                 break
    
64.         self.temp.close()
    
65.         # print self.dic_testlink
    
66. 
    
67.     def content_to_xml(self, key, value=None):
    
68.         if key == 'step_number' or key == 'execution_type' or key == 'node_order' or key == 'externalid' or key == 'version' or key == 'importance':
    
69.             return "<" + str(key) + "><![CDATA[" + str(value) + "]]></" + str(key) + ">"
    
70.         elif key == 'actions' or key == 'expectedresults' or key == 'summary' or key == 'preconditions':
    
71.             return "<" + str(key) + "><![CDATA[<div>" + str(value) + "</div> ]]></" + str(key) + ">"
    
72.         elif key == 'keywords':
    
73.             return '<keywords><keyword name="' + str(value) + '"><notes><![CDATA[ aaaa ]]></notes></keyword></keywords>'
    
74.         elif key == 'name':
    
75.             return '<testcase name="' + str(value) + '">'
    
76.         elif key == 'author':
    
77.             return '<custom_fields><custom_field><name><![CDATA[设计人员]]></name><value><![CDATA[%s]]></value></custom_field></custom_fields>' % str(value)
    
78.         else:
    
79.             return '##########'
    
80. 
    
81.     def dic_to_xml(self, ExcelFileName, SheetName):
    
82.         testcase_list = self.dic_testlink[self.testsuite]["testcase"]
    
83.         for testcase in testcase_list:
    
84.             for step in testcase["steps"]:
    
85.                 self.content += "<step>"
    
86.                 self.content += self.content_to_xml("step_number", step["step_number"])
    
87.                 self.content += self.content_to_xml("actions", step["actions"])
    
88.                 self.content += self.content_to_xml("expectedresults", step["expectedresults"])
    
89.                 self.content += self.content_to_xml("execution_type", step["execution_type"])
    
90.                 self.content += "</step>"
    
91.             self.content = "<steps>" + self.content + "</steps>"
    
92.             self.content = self.content_to_xml("importance", testcase["importance"]) + self.content
    
93.             self.content = self.content_to_xml("execution_type", testcase["execution_type"]) + self.content
    
94.             self.content = self.content_to_xml("preconditions", testcase["preconditions"]) + self.content
    
95.             self.content = self.content_to_xml("summary", testcase["summary"]) + self.content
    
96.             self.content = self.content_to_xml("version", testcase["version"]) + self.content
    
97.             self.content = self.content_to_xml("externalid", testcase["externalid"]) + self.content
    
98.             self.content = self.content_to_xml("node_order", testcase["node_order"]) + self.content
    
99.             self.content = self.content + self.content_to_xml("keywords", testcase["keywords"])
    
100.             self.content = self.content_to_xml("name", testcase["name"]) + self.content
     
101.             self.content = self.content + self.content_to_xml("author", testcase["author"])
     
102.             self.content = self.content + "</testcase>"
     
103.             self.content_list.append(self.content)
     
104.             self.content = ""
     
105.         self.content = "".join(self.content_list)
     
106.         self.content = '<testsuite name="' + self.testsuite + '">' + self.content + "</testsuite>"
     
107.         self.content = '<?xml version="1.0" encoding="UTF-8"?>' + self.content
     
108.         self.write_to_file(ExcelFileName, SheetName)
     
109. 
     
110.     def write_to_file(self, ExcelFileName, SheetName):
     
111.         xmlFileName = ExcelFileName + '_' + SheetName + '.xml'
     
112.         cp = open(xmlFileName, "w")
     
113.         cp.write(self.content)
     
114.         cp.close()
     
115. 
     
116. #遍历某个文件目录下的所有文件名称
     
117. def iterbrowse(path):
     
118.     for home, dirs, files in os.walk(path):
     
119.         for filename in files:
     
120.             yield os.path.join(home, filename)
     
121. 
     
122. 
     
123. if __name__ == "__main__":
     
124. 
     
125.     print((os.path.abspath('.')))
     
126.     excellist=[]
     
127.     #列出当前目录下的所有.xml文件
     
128.     for fullname in iterbrowse(os.path.abspath('.')):
     
129.         print(fullname)
     
130.         obj1=re.compile(r'([\W\w]*)(\.xlsx)
     
131. )
     
132.         for m in obj1.finditer(fullname):
     
133.             print((m.group()))
     
134.             excellist.append(m.group())
     
135.     print(excellist)
     
136.     for fileName in excellist:
     
137.         fileName=fileName.split('\\')[-1]
     
138.         file_data = xlrd.open_workbook(fileName)
     
139.         sheetnum=len(file_data.sheets())
     
140.         sheetList=[]
     
141.         for index in range(sheetnum):
     
142.             sheet_name=file_data.sheets()[index].name
     
143.             print(sheet_name)
     
144.             sheetList.append(sheet_name)
     
145.         print(sheetList)
     
146.         for sheetName in sheetList:
     
147.             test = operate(fileName, sheetName)
     
148.             test.xlsx_to_dic(sheetName)
     
149.             test.dic_to_xml(fileName, sheetName)
     
150.     print("Convert success!")
     
151.     os.system('pause')
     

复制代码