Import xml to excel using Python

This article explains how to import xml into an excel table using Python Testlink. The method is very simple, for more information, see. When I recently used Testlink, I found that the imported use case is in xml format, and there is no proper tool to convert it to excel format. xml also contains too many display items in excel, some tools on the Internet are converted into csv format, and the results are not satisfactory.

Let's write one for yourself.

The required modules include xml. dom. minidom (python built-in) and xlwt.

Version used:

Python: 2.7.5

Xlwt: 1.0.0

I. Analyze the Testlink XML format first:

This is a typical testlink case structure with two levels of testusuit. We only need to take testsuite name, testcase name, preconditions, actions, expectedresults

2. The procedure is as follows:

# Coding: UTF-8 ''' Created on @ 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.doc umentElementrow = 1col = 1 borders = xlwt. borders () borders. left = 1borders. right = 1borders. top = 1borders. bottom = 1 style = xlwt. easyxf ('align: wrap on, vert center, horiz Center') # wrap, center horizontally, center vertically # Set the title format, Font font, bold, and background color: chrysanthemum # title of the test item = xlwt. easyxf (u'font: name, height 240, colour_index black, bold on, italic off; align: wrap on, vert center, horiz center; pattern: pattern solid, fore_colour light_orange; ') item = 'test item' Subitem = 'test 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 (, Result, title) # freeze the first row of booksheet. panes_frozen = Truebooksheet. horz_split_pos = 1 # level-1 directory for I in root. childNodes: testsuite = I. getAttribute ('name '). strip () # print testsuite ''' write test item ''' print "row is:", row booksheet. write (row, col, testsuite, style) # Level 2 Directory for dd in I. childNodes: print "% s" % dd. getAttribute ('name') testsuite2 = dd. getAttribute ('name') if not dd. getElementsByTagName ('testbase'): print "testcase is % s" % testsuite2 row = row + 1 booksheet. write (row, 2, testsuite2, style) # write test item row = row + 1 booksheet. write (row, 1, testsuite2, style) itemlist = dd. getElementsByTagName ('testbase') 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 "Test Procedure:", steplist [0]. firstChild. data. replace ("
"," ") Expectlist = subb. getElementsByTagName ('expectedresults') for each CT in expectlist: result = expect CT. childNodes [0]. nodeValue. replace ("
"," ") Booksheet. write (row, 5, result, style) row = row + 1 workbook.save('demo.xls ')

The effect of writing data to an excel file is as follows:

Let's look at an instance:

You need 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_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)