python3读取xml文件，写入excel表

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)

最后编辑于：2020.04.03 14:14:03