python excel 转 xml
在网上百度到两种方法,先列出来
提醒,因为写入的xml需要标明encoding ,在刚开始找到方法一的时候用的是,
f = open('./SettingRC.xml','w')
f.write(doc.toprettyxml())
使用这种方式,生成的xml 是不含有encoding的,不能满足我的需要,然后再网络上继续百度,找到了方法二,
with open('./SettingRC.xml','w') as f:
f.write(dom.toprettyxml(indent='\t',encoding='utf-8'))
运行后提示,write() argument must be str, not byte
再然后,找到解决方法
with open('./SettingRC.xml','wb') as f:
把写的方式改成写入2进制,OK完美解决,反思一下,方法一也是可以的,
f = open('./SettingRC.xml','w') 改成
f = open('./SettingRC.xml','wb')
f.write(doc.toprettyxml(encoding='utf-8'))
完美解决
方法一,通过xml.dom.minidom 来解决
def excel_to_xml_byXmlDomMinidom(file, colnameindex = 0, by_index =0):
#读取excel中的内容的准备工作
data = open_excel(file)
table = data.sheets()[by_index]
nrows = table.nrows
ncols = table.ncols
#利用dom创建xml doc = xml.dom.minidom.Document()
xmain = doc.createElement('RCConfig')
doc.appendChild(xmain)
if ncols>1:
items = doc.createElement('ContactListEx')
for nrow in range(0,nrows):
if nrow == 0:
continue
for ncol in range(0,ncols):
item = doc.createElement('User')
# 在dom对象中塞入excel数据 item_name = doc.createElement('Name')
name = table.cell(nrow,0).value
name_ = doc.createTextNode(name)
item_name.appendChild(name_)
item_uri = doc.createElement('Uri')
uri = table.cell(nrow,1).value
uri_ = doc.createTextNode(uri)
item_uri.appendChild(uri_)
item.appendChild(item_name)
item.appendChild(item_uri)
items.appendChild(item)
xmain.appendChild(items)
else:
items = doc.createElement('ContactList')
for nrow in range(0,nrows):
if nrow == 0:
continue
item = doc.createElement('Uri')
uri = table.cell(nrow,0).value
uri_ = doc.createTextNode(uri)
item.appendChild(uri_)
items.appendChild(item)
xmain.appendChild(items)
# 把准备好的dom 写入xml
f = open('./SettingRC.xml','wb')
f.write(doc.toprettyxml(encoding='utf-8'))
f.close()
方法二,通过 from xml.dom.minidom import parseString 和lxml.etree import Element,SubElement, tostring 解决。
利用SubElement 生成 xml 节点
def export_to_xml_byLxmlEtree(file, colnameindex = 0, by_index =0):
try:
data = open_excel(file)
table = data.sheets()[by_index]
nrows = table.nrows
ncols = table.ncols
node_root = Element('RCConfig')
if ncols>1:
for nrow in range(0,nrows):
if nrow == 0:
continue
contactlistEx = SubElement(node_root,'ContactListEx')
user_node = SubElement(contactlistEx,'User')
item_name = SubElement(user_node,'Name')
item_uri = SubElement(user_node,'Uri')
name = table.cell(nrow,0).value
uri = table.cell(nrow,1).value
item_name.text = name
item_uri.text = uri
else:
contactlist = SubElement(node_root,'ContactList')
for nrow in range(0,nrows):
if nrow == 0:
continue
uri = SubElement(contactlist,'Uri')
uri.text = table.cell(nrow,0).value
xml = tostring(node_root,pretty_print=True)
dom = parseString(xml)
with open('./SettingRC.xml','wb') as f:
f.write(dom.toprettyxml(indent='\t',encoding='utf-8'))
except Exception as ex:
print('error info:{0}'.format(ex))