excel转为xml导入testlink

发布时间：2019-09-19 08:02:03编辑：auto阅读（1580）

   testlink1.9x导入用例功能支持xml文件导入，所以如果测试用例能够按照一定格式写入excel再用脚本生成xml文件然后导入testlink的话，这样就可以避免在公司加班到很晚，完全可以晚上回去慢慢写excel。

   首先是一个操作excel的类，这个从网上找的源码，然后做了一些修改，其实这个功能里只用到了getCell这个函数，也就是读excel单元格中的内容。

#coding=utf-8
from xml.etree import ElementTree
from win32com.client import Dispatch
import win32com.client
import os
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

   主要的思路是先读excel里面的内容，然后存到一个特定的数据结构中，再从数据结构中取值生成xml文件。这里row_flag用来标记当前读到的行数。

class operate():
    def __init__(self):
        self.temp=easy_excel('test.xlsx')
        self.dic_testlink={}
        self.row_flag=2
        self.testsuite = self.temp.getCell('Sheet1',1,1)
        self.dic_testlink[self.testsuite]={"node_order":"13","details":"","testcase":[]}
        self.content = ""
        self.content_list = []
    def xlsx_to_dic(self):
        while True:
            #print 'loop1'
                #list_testcase = dic_testlink[testsuite].["testcase"]
            #1.用例名2.关键字3.摘要4.前提5.步骤6.预期结果
            testcase = {"name":"","node_order":"100","externalid":"","version":"1","summary":"","preconditions":"","execution_type":"1","importance":"3","steps":[],"keywords":"P1"}  
            testcase["name"] = self.temp.getCell('Sheet1',self.row_flag,1)
            testcase["summary"] = self.temp.getCell('Sheet1',self.row_flag,3)
            testcase["preconditions"] = self.temp.getCell('Sheet1',self.row_flag,4)          
            #print self.temp.getCell('Sheet1',self.row_flag,3)
            step_number=1
            testcase["keywords"] = self.temp.getCell('Sheet1',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('Sheet1',self.row_flag,5)
                step["expectedresults"] = self.temp.getCell('Sheet1',self.row_flag,6)
                testcase["steps"].append(step)
                step_number += 1
                self.row_flag += 1
                if self.temp.getCell('Sheet1',self.row_flag,1) is not None  or self.temp.getCell('Sheet1',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('Sheet1',self.row_flag,5) is None and self.temp.getCell('Sheet1',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="'+value+'"><notes><![CDATA[ 每个版本都会执行的用例 ]]></notes></keyword></keywords>'
        elif key == 'name':
            return '<testcase name="'+str(value)+'">'
        else:
            return '##########'
    def dic_to_xml(self):
        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()
    def write_to_file(self):
        cp = open("test.xml","w")
        cp.write(self.content)
        cp.close()
                                                                                                                                       
test=operate()
test.xlsx_to_dic()
test.dic_to_xml()

   excel的格式必须跟下图中的格式一样，否则会无法正确读取。



   这样导出的xml文件导入后的结果如下图。



   testlink中的用例结果如下图。



   如果需要编译好的文件，由于文件大于2M，请到我的下载空间下载。目标文件为test.xlsx，一定要严格按照格式，不该写数据的地方千万留空，生成的xml文件为test.xml。

关键字：