Python實現將xml匯入至excel

最近在使用Testlink時，發現匯入的用例是xml格式，且沒有合適的工具轉成excel格式，xml使用excel開啟顯示的東西也太多，網上也有相關工具轉成csv格式的，結果也不合人意。

那求人不如爾己，自己寫一個吧

需要用到的模組有：xml.dom.minidom(python內建)、xlwt

使用版本：

python:2.7.5

xlwt:1.0.0

一、先分析Testlink XML格式：

這是一個有兩級testusuit的典型的testlink用例結構，我們只需要取testsuite name,testcase name，preconditions，actions，expectedresults

二、程式如下：

#coding:utf-8'''Created on 2015-8-20@author: Administrator'''''''''import xml.etree.cElementTree as ETimport xml.dom.minidom as xximport os,xlwt,datetimeworkbook=xlwt.Workbook(encoding="utf-8")# booksheet=workbook.add_sheet(u'sheet_1')booksheet.col(0).width= 5120booksheet.col(1).width= 5120booksheet.col(2).width= 5120booksheet.col(3).width= 5120booksheet.col(4).width= 5120booksheet.col(5).width= 5120dom=xx.parse(r'D:\\Python27\test.xml')root = dom.documentElementrow=1col=1borders=xlwt.Borders()borders.left=1borders.right=1borders.top=1borders.bottom=1style = xlwt.easyxf('align: wrap on,vert centre, horiz center') #自動換行、水平置中、垂直置中#設定標題的格式，字型方宋、加粗、背景色：菊黃#測試項的標題title=xlwt.easyxf(u'font:name 仿宋,height 240 ,colour_index black, bold on, italic off; align: wrap on, vert centre, horiz center;pattern: pattern solid, fore_colour light_orange;')item='測試項'Subitem='測試分項'CaseTitle='測試案例標題'Condition='預置條件'actions='操作步驟'Result='預期結果'booksheet.write(0,0,item,title)booksheet.write(0,1,Subitem,title)booksheet.write(0,2,CaseTitle,title)booksheet.write(0,3,Condition,title)booksheet.write(0,4,actions,title)booksheet.write(0,5,Result,title)#凍結首行booksheet.panes_frozen=Truebooksheet.horz_split_pos= 1#一級目錄for i in root.childNodes:  testsuite=i.getAttribute('name').strip()  #print testsuite  #print testsuite  '''  寫測試項  '''  print "row is :",row  booksheet.write(row,col,testsuite,style)    #二級目錄  for dd in i.childNodes:    print "    %s" % dd.getAttribute('name')    testsuite2=dd.getAttribute('name')    if not dd.getElementsByTagName('testcase'):      print "Testcase is %s" % testsuite2      row=row+1      booksheet.write(row,2,testsuite2,style)  #寫測試分項        row=row+1        booksheet.write(row,1,testsuite2,style)    itemlist=dd.getElementsByTagName('testcase')        for subb in itemlist:      #print "         %s" % subb.getAttribute('name')      testcase=subb.getAttribute('name')            row=row+1      booksheet.write(row,2,testcase,style)      ilist=subb.getElementsByTagName('preconditions')      for ii in ilist:        preconditions=ii.firstChild.data.replace("
"," ")        col=col+1        booksheet.write(row,3,preconditions,style)      steplist=subb.getElementsByTagName('actions')      #print steplist      for step in steplist:        actions=step.firstChild.data.replace("
"," ")        col=col+1        booksheet.write(row,4,actions,style)      #print "測試步驟：",steplist[0].firstChild.data.replace("
"," ")      expectlist=subb.getElementsByTagName('expectedresults')            for expect in expectlist:        result=expect.childNodes[0].nodeValue.replace("
","" )        booksheet.write(row,5,result,style)        row=row+1        workbook.save('demo.xls')

寫入excel的效果如下：

我們再來看個執行個體：

需要下載一個module：xlwt，如下是source code

import xml.dom.minidomimport xlwtimport syscol = 0row = 0  def handle_xml_report(xml_report, excel):    problems = xml_report.getElementsByTagName("problem")  handle_problems(problems, excel)  def handle_problems(problems, excel):  for problem in problems:    handle_problem(problem, excel)def handle_problem(problem, excel):  global row  global col  code = problem.getElementsByTagName("code")    file = problem.getElementsByTagName("file")    line = problem.getElementsByTagName("line")    message  = problem.getElementsByTagName("message")  for node in code:      excel.write(row, col, node.firstChild.data)    col = col + 1   for node in file:      excel.write(row, col, node.firstChild.data)     col = col + 1      for node in line:      excel.write(row, col, node.firstChild.data)         col = col + 1      for node in message:      excel.write(row, col, node.firstChild.data)         col = col + 1  row = row+1  col = 0if __name__ == '__main__':   if(len(sys.argv) <= 1):    print ("usage: xml2xls src_file [dst_file]")    exit(0)  #the 1st argument is XML report ; the 2nd is XLS report  if(len(sys.argv) == 2):    xls_report = sys.argv[1][:-3] + 'xls'  #if there are more than 2 arguments, only the 1st & 2nd make sense  else:    xls_report = sys.argv[2]  xmldoc = xml.dom.minidom.parse(sys.argv[1])   wb = xlwt.Workbook()  ws = wb.add_sheet('MOLint')  ws.write(row, col, 'Error Code')  col = col + 1  ws.write(row, col, 'file')  col = col + 1    ws.write(row, col, 'line')    col = col + 1    ws.write(row, col, 'Description')   row = row + 1  col = 0  handle_xml_report(xmldoc, ws)  wb.save(xls_report)

