Python implementation imports XML into Excel

When using Testlink recently, it was found that the imported use case is XML format, and there is no appropriate tool to go to Excel format, XML open display with Excel too much, the web also has related tools to CSV format, the results are unsatisfactory.

So ask yourself, write one yourself.

The modules that need to be used are: Xml.dom.minidom (Python comes with), XLWT

Use version:

python:2.7.5

xlwt:1.0.0

First, analyze the Testlink XML format:

This is a typical testlink use case structure with level two testusuit, we only need to take testsuite name,testcase name,preconditions,actions,expectedresults

Second, the procedure is as follows:

#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 ') #自动换行, horizontally centered, vertically centered # format the caption, Font square song, bold, background color: Chrysanthemum Yellow # The title of the test item title=xlwt.easyxf (U ' font:name imitation, height, colour_ Index black, bold on, italic off; Align:wrap on, vert centre, Horiz center;pattern:pattern Solid, fore_colour light_orange; ') item= ' Test item ' subitem= ' Test sub-item ' casetitle= ' test case title ' condition= ' preset condition ' actions= ' Operation Step ' result= ' expected 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=-level directory for I in  Root.childnodes:testsuite=i.getattribute (' name '). Strip () #print testsuite #print testsuite "' Write test item ' ' Print ' row is: ", Row Booksheet.write (row,col,testsuite,style) #二级目录-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 (r         Ow,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,te Stcase,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" test step: ", Steplist[0].firstchild.data.replac E ("
"," ") expectlist=subb.getelementsbytagname (' expectedresults ') for expect in Expectlist:result=exp Ect.childnodes[0].nodevalue.replace ("
"," ") Booksheet.write (Row,5,result,style) row=row+1 workbook.save (' Demo.xls ')

The effect of writing to Excel is as follows:

Let's look at an example:

To download a MODULE:XLWT, the following is the source code

Import xml.dom.minidomimport Xlwtimport syscol = 0row = 0 def handle_xml_report (Xml_report, Excel): Problems = Xml_rep Ort.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.firstchi Ld.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.A RGV) <= 1): Print ("Usage: Xml2xls src_file [Dst_file] ") exit (0) #the 1st argument is the XML report; The 2nd is XLS report if (len (sys.argv) = = 2): Xls_report = sys.argv[1][:-3] + ' XLS ' #if there was more than 2 argumen  TS, 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 ha Ndle_xml_report (xmldoc, WS) Wb.save (Xls_report)

