Excel转XML格式脚本

运行环境：

Python版本：Python2.7.15

第三方库：pywin32

Excel版本：Excel2016

1.安装Python2.7.15

1)下载Python安装包

进入Python官网：www.python.org，打开页面如下：

选择Downloads—>All releases—>Download Python 2.7.15，然后等待下载完成即可

2)安装Python

进入下载目录，双击安装包，一直点击next即可（如果不想修改安装路径）

安装完成后打开DOS窗口（Win+R，输入cmd按下Enter），输入命令行python2，验证python是否安装成功，如下图：

至此Python的安装已经完成

注：本人电脑安装了两个版本的python，所以用对应的命令需要加上版本号区分

2.安装pywin32库

pywin32安装方法：

安装方法：在命令行输入pip2 install pywin32

注：因为我的电脑安装了两个版本的Python，所以用pip2

3.完成转换

测试文件：easy_excel.py

# coding=utf-8
from xml.etree import ElementTree
from win32com.client import Dispatch
import win32com.client
import os
import sys
reload(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-8
import os
import sys
reload(sys)
sys.setdefaultencoding("utf-8")

from easy_excel import easy_excel
class 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')

test.xls

用例名称	执行方式	摘要	前置条件	操作步骤	预期结果
运行测试
路径测试	自动	测试摘要	前置条件	在有中文路径中打开程序	正常运行
				在有空格的路径中打开程序	正常运行
				在有中文和空格的路径中打开程序	正常运行
浏览器版本测试	手动	测试摘要	前置条件	在浏览器中打开	正常使用
				在谷歌浏览器打开	正常使用
				在火狐浏览器中打开	正常使用

操作方法：将上述文件放置同一个文件夹，然后打开Dos窗口，进入对应文件夹目录，输入python2 operate.py即可

例：我将文件放在E:\ExcelToXML目录下进行操作

​

生成的xml文件名称为：文件名+表名

然后将xml文件导入testlink即可

使用注意事项：

1)安装好pywin32可以测试一下是否可以打开Excel表，测试代码如下

import win32com.client

xlsApp = win32com.client.Dispatch('Excel.Application')

xlsApp.Workbooks.Open(r'F:/test.xls')

2)注意Excel版本要大于2007，在运行脚本之前将后缀名修改为.xls