Python imports xml into excel and pythonxmlexcel

Source: Internet
Author: User
Tags testlink

Python imports xml into excel and pythonxmlexcel

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 ("<br/>", "") col = col + 1 booksheet. write (row, 3, preconditions, style) steplist = subb. getElementsByTagName ('actions') # print steplist for step in steplist: actions = step. firstChild. data. replace ("<br/>", "") col = col + 1 booksheet. write (row, 4, actions, style) # print "Test Procedure:", steplist [0]. firstChild. data. replace ("<br/>", "") expectlist = subb. getElementsByTagName ('expectedresults') for exact CT in expectlist: result = exact CT. childNodes [0]. nodeValue. replace ("<br/>", "") 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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.