45fan.com - 路饭网

搜索: 您的位置主页 > 网络频道 > 阅读资讯:怎么样在Python中实现将xml导入至excel?

怎么样在Python中实现将xml导入至excel?

2016-03-03 14:25:46 来源:www.45fan.com 【

怎么样在Python中实现将xml导入至excel?

最近在使用Testlink时,发现导入的用例是xml格式,且没有合适的工具转成excel格式,xml使用excel打开显示的东西也太多,网上也有相关工具转成csv格式的,结果也不合人意。

那求人不如尔己,自己写一个吧

需要用到的模块有:xml.dom.minidom(python自带)、xlwt

使用版本:

python:2.7.5

xlwt:1.0.0

一、先分析Testlink XML格式:

怎么样在Python中实现将xml导入至excel?

这是一个有两级testusuit的典型的testlink用例结构,我们只需要取testsuite name,testcase name,preconditions,actions,expectedresults

二、程序如下:

#coding:utf-8
'''
Created on 2015-8-20

@author: Administrator
'''
'''
'''
import xml.etree.cElementTree as ET
import xml.dom.minidom as xx
import os,xlwt,datetime

workbook=xlwt.Workbook(encoding="utf-8")
# 
booksheet=workbook.add_sheet(u'sheet_1')
booksheet.col(0).width= 5120
booksheet.col(1).width= 5120
booksheet.col(2).width= 5120
booksheet.col(3).width= 5120
booksheet.col(4).width= 5120
booksheet.col(5).width= 5120

dom=xx.parse(r'D:\\Python27\test.xml')
root = dom.documentElement
row=1
col=1

borders=xlwt.Borders()
borders.left=1
borders.right=1
borders.top=1
borders.bottom=1


style = 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=True
booksheet.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("<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 "测试步骤:",steplist[0].firstChild.data.replace("<br />"," ")
   expectlist=subb.getElementsByTagName('expectedresults')
   
   for expect in expectlist:
    result=expect.childNodes[0].nodeValue.replace("<br />","" )
    booksheet.write(row,5,result,style)

   
 row=row+1
    
workbook.save('demo.xls')

写入excel的效果如下:

怎么样在Python中实现将xml导入至excel?

我们再来看个实例:

需要下载一个module:xlwt,如下是source code

import xml.dom.minidom
import xlwt
import sys

col = 0
row = 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 = 0

if __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)


本文地址:http://www.45fan.com/a/question/41095.html
Tags: python 导入 XML
编辑:路饭网
关于我们 | 联系我们 | 友情链接 | 网站地图 | Sitemap | App | 返回顶部