xml与Excel转换

使用Python将如下xml格式转换为Excel格式：

xml转为xls格式文件：

xml格式如下：

<?xml version="1.0" encoding="UTF-8"?>   
<photos>   
        <photo src="assets/images/001/3.jpg" desc="把兰花种进去！"/>      
        <photo src="assets/images/002/1.jpg" desc="每天都要锻炼身体哦！"/>   
        <photo src="assets/images/001/1.jpg" desc="我们在种兰花！"/>       
        <photo src="assets/images/001/2.jpg" desc="先把泥巴挖开！"/>   
</photos>  

通过使用lxml模块创建xml文件，使用xlwt模块创建Excel文件，转换方法如下

#coding:utf-8
#xml与Excel相互转换
from lxml import etree
import xlwt as xlwt

wb=xlwt.Workbook()
ws=wb.add_sheet("employee")

tree=etree.parse("employee2.xml")
root=tree.getroot()

row=0
col=0

for item in root:
    if len(item.attrib)==0:
        continue
        
    row=row+1
    col=0
    
    for attr in item.attrib:
        print attr
        ws.write(row,col,item.attrib[attr])
        col=col+1
        
wb.save("test.xls")

 最后结果如下：

 

 xls格式文件转换为xml:

 xls格式文件如下：

xls转换为xml:

#coding:utf-8
from lxml import etree
import xlrd,codecs

def getPathFileName(path):
    tmp=path.split('/')
    return tmp[len(tmp)-1].split('.')[0]
    

def xlsToxmlPath(path):
    return path.split('.')[0]+'.xml'
    
def export(path):
    data=xlrd.open_workbook(path,formatting_info=True)
    table=data.sheets()[0]
    
    f=open(xlsToxmlPath(path),'wb')
    f.write(u'<?xml version="1.0"  encoding="utf-8" ?>\n')
    f.write(u'<%s>\n'% getPathFileName(path))
    
    for i in range(1,table.nrows):
        s=u'<item';
        tmp = [u'   %s = "%s"' % (str(table.cell_value(0,j)), str(table.cell_value(i,j))) for j in range(table.ncols)];
        
        s+=u' '.join(map(str,tmp));
        s+=u'>\n';
        f.write(s);
        
    f.write(u'</%s>'%getPathFileName(path));
    
export('test.xls')

 结果为：

<?xml version="1.0"  encoding="utf-8" ?>
<test>
<item   src = "assets/images/001/3.jpg"    content = "aa">
<item   src = "assets/images/002/1.jpg"    content = "bb">
<item   src = "assets/images/001/1.jpg"    content = "cc">
<item   src = "assets/images/001/2.jpg"    content = "dd">
</test>