python3读取xml文件，写入excel表
2020-04-03 本文已影响0人
demoxjl
body.xml
<Packet type="RESPONSE" version="1.0">
<Head>
<RequestType>C04</RequestType>
<RequestCode>1</RequestCode>
<ErrorCode>0000</ErrorCode>
<ErrorMessage>成功</ErrorMessage>
</Head>
<Body>
<VehicleInfo>
<!--车架号-->
<VIN>WDDDwe33455</VIN>
<LicensePlateNo>粤BC9098</LicensePlateNo>
<LicensePlateType>01</LicensePlateType>
<!--发动机号-->
<EngineNo>LB82061102</EngineNo>
<!--车辆类型-->
<PMVehicleType>03</PMVehicleType>
<!--性质代码-->
<PMUserNature>03</PMUserNature>
<!--校验有效期止-->
<IneffectualDate>20091222</IneffectualDate>
</VehicleInfo>
</Body>
</Packet>
readXmlToExcel.py
#!/usr/bin/python3
#create time: 2020-03-31
import xml.dom.minidom #导入处理xml文件的模块
import pandas as pd
import xlwt
try:
vin_list = []
LicensePlateNo_list = []
LicensePlateType_list = []
LicensePlateType_list = []
EngineNo_list = []
PMVehicleType_list = []
PMUserNature_list = []
IneffectualDate_list = []
#使用minidom解析器打卡xml文档
dom = xml.dom.minidom.parse("body.xml")
#得到文档元素对象
Packet = dom.documentElement #用于得到dom对象的文档元素，并把获得的对象给root
if Packet.hasAttribute("shelf"):
print('Root element: %s' % (Packet.getAttribute("type")))
#获得标签为Body的多组标签
child_tag = Packet.getElementsByTagName("Body")
first_child = child_tag[0]
#车架号
vin_value = first_child.getElementsByTagName('VIN')[0].childNodes[0].data #获得元素属性对应的值
print(vin_value)
vin_list.append(vin_value)
#号牌号码
LicensePlateNo = first_child.getElementsByTagName('LicensePlateNo')[0].firstChild.data
LicensePlateNo_list.append(LicensePlateNo)
#号牌种类代码
LicensePlateType = first_child.getElementsByTagName('LicensePlateType')[0].firstChild.data
LicensePlateType_list.append(LicensePlateType)
#发动机hao
EngineNo = first_child.getElementsByTagName('EngineNo')[0].firstChild.data
EngineNo_list.append(EngineNo)
#交管车辆类型
PMVehicleType = first_child.getElementsByTagName('PMVehicleType')[0].firstChild.data
PMVehicleType_list.append(PMVehicleType)
#车辆使用性质代码
PMUserNature = first_child.getElementsByTagName('PMUserNature')[0].firstChild.data
PMUserNature_list.append(PMVehicleType)
#校验有效日期
IneffectualDate = first_child.getElementsByTagName('IneffectualDate')[0].firstChild.data
IneffectualDate_list.append(IneffectualDate)
#将列表存储为字典
all_dict = {
'VIN':vin_list,
'LicensePlateNo':LicensePlateNo_list,
'LicensePlateType': LicensePlateType_list,
'EngineNo': EngineNo_list,
'PMVehicleType': PMVehicleType_list,
'PMUserNature': PMUserNature_list,
'IneffectualDate': IneffectualDate_list
}
df = pd.DataFrame(all_dict) #将字典转换为DataFrame
#将DataFrame数据写入excel表中
with pd.ExcelWriter('car_table.xls') as Writer:
df.to_excel(Writer,'Sheet1',index=False)
except xml.parsers.expat.ExpatError as e:
print(e)
