python解析Testlink导出的xml并写入excel
#coding=utf-8from xml.etree import ElementTree
from win32com.client import Dispatch
import win32com.client
import os
class easy_excel:
def __init__(self,filename=None):
self.xlApp=win32com.client.Dispatch('Excel.Application')
if filename:
self.filename=os.getcwd()+"\\"+filename
print self.filename
self.xlBook=self.xlApp.Workbooks.Open(self.filename)
else:
self.xlBook=self.xlApp.Workbooks.Open(filename)
self.filename=''
def save(self,newfilename=None):
if newfilename:
self.filename=newfilename
self.xlBook.SaveAs(newfilename)
else:
self.xlBook.Save()
def close(self):
self.xlBook.Close(SaveChanges=0)
def getCell(self,sheet,row,col):
sht=self.xlBook.Worksheets(sheet)
return sht.Cell(row,col).Value
def setCell(self,sheet,row,col,value):
sht=self.xlBook.Worksheets(sheet)
sht.Cells(row,col).Value=value
sht.Cells(row,col).HorizontalAlignment=3
sht.Rows(row).WrapText=True
def mergeCells(self,sheet,row1,col1,row2,col2):
sht=self.xlBook.Worksheets(sheet)
sht.Range(sht.Cells(row1,col1),sht.Cells(row2,col2)).Merge()
def setBorder(self,sheet,row,col):
sht=self.xlBook.Worksheets(sheet)
sht.Cells(row,col).Borders.LineStyle=1
def set_col_width(self,sheet):
sht=self.xlBook.Worksheets(sheet)
sht.Columns("D:F").ColumnWidth=30
xls=easy_excel('test.xlsx')
xls.setCell('Sheet1',2,2,u"编号")
xls.setCell('Sheet1',2,3,u"优先级")
xls.setCell('Sheet1',2,4,u"操作步骤")
xls.setCell('Sheet1',2,5,u"预期结果")
xls.setCell('Sheet1',2,6,u"实际结果")
xls.set_col_width('Sheet1')
dic_temp={}
root=ElementTree.parse("./test.xml")
lst_node=root.getiterator("testcase")
num=len(lst_node)
row_flag=3
step_num=0
for case in range(0,num):
steps=lst_node.find("steps")
step_num=len(steps)
keywords=lst_node.find("keywords")
if keywords is not None:
dic_temp['keyword']=keywords.attrib["name"]
else:
dic_temp['keyword']=0
for step in steps:
list_temp=[]
list_temp.append(step.text)
list_temp.append(step.text)
dic_temp.text]=list_temp
row_start=row_flag
xls.setCell('Sheet1',row_flag,2,case+1)
xls.setCell('Sheet1',row_flag,3,dic_temp['keyword'])
for j in range(1,step_num+1):
xls.setCell('Sheet1',row_flag,4,dic_temp)
xls.setCell('Sheet1',row_flag,5,dic_temp)
row_flag+=1
xls.mergeCells('Sheet1',row_start,3,row_flag-1,3)
xls.mergeCells('Sheet1',row_start,2,row_flag-1,2)
for row in range(2,row_flag):
for col in range(2,7):
xls.setBorder('Sheet1',row,col)
xls.save()
xls.close()
print 'success!'
raw_input("Press any key to continue...")
页:
[1]