python解析XML文件并转存到excel

python解析XML文件并转存到excel

转换前的xml文档信息如下：
处理后的效果如下：



python代码如下：

import xml.sax
from openpyxl import Workbook, load_workbook
import os

def write_to_excel(two_dimension_list):
    path = os.path.dirname(os.path.realpath(__file__))  # get the parent path of current file
    try:
        wb = load_workbook(path+"\\orderfile.xlsx") # load an existing workbook
        ws = wb.create_sheet()
    except:
        wb = Workbook() # create a new workbook
        ws = wb.create_sheet()
    for c in range(len(two_dimension_list)):
        for r in range(len(two_dimension_list[c])):
            ws.cell(r+1,c+1).value = two_dimension_list[c][r]
    wb.save(path+"\\orderfile.xlsx")

class OrderFileHandler(xml.sax.ContentHandler):
    def __init__(self):
        self.CurrentData=""
        self.dic_orderdata = {}
        self.dic_fileInfo = {}
        self.op_code = []
        self.list_optioncode = []
        self.list_orderdata = []
        self.list_fileInfo = []
        
    # 文档启动时调用
    def startDocument(self):
        print("XML file parse start!")
        
    # 遇到XML开始标签时调用，tag 是标签的名字，attributes 是标签的属性值字典
    def startElement(self,tag,attributes):
        self.CurrentData = tag
        if tag == "orderData":
            self.dic_orderdata['orderId'] = attributes.get('orderId')   # 用 get 方法，如果该键值对不存在会返回None
            self.dic_orderdata['longVIN'] = attributes.get('longVIN')
            self.dic_orderdata['shortVIN'] = attributes.get('shortVIN')
            self.dic_orderdata['dummy'] = attributes.get('dummy')   # 不存在于 xml 文件中
            self.dic_orderdata['softwareLevel'] = attributes.get('softwareLevel')
            self.list_orderdata.append(list(self.dic_orderdata.values()))
            print(self.dic_orderdata)
        elif tag == 'fileInfo':
            self.dic_fileInfo['date'] = attributes.get('date')
            self.dic_fileInfo['comment'] = attributes.get('comment')
            self.dic_fileInfo['author'] = attributes.get('author')
            self.dic_fileInfo['plantId'] = attributes.get('plantId')
            self.dic_fileInfo['firstCreationDate'] = attributes.get('firstCreationDate')
            self.dic_fileInfo['latestCreationDate'] = attributes.get('latestCreationDate')
            self.dic_fileInfo['vehicleState'] = attributes.get('vehicleState')
            self.list_fileInfo.append(list(self.dic_fileInfo.values()))
    
    # 元素结束调用
    def endElement(self, tag):
        if self.CurrentData == "optionCode":
            self.op_code.append(self.optionCode)
        self.CurrentData = ""
        
    # 读取标签之间的字符时调用
    def characters(self, content):
        if self.CurrentData == "optionCode":
            self.optionCode = content
            
    # 解析器到达文档结尾时调用         
    def endDocument(self):
        self.list_orderdata.insert(0,list(self.dic_orderdata.keys()))
        self.list_fileInfo.insert(0,list(self.dic_fileInfo.keys()))
        self.list_optioncode.insert(0,['optionCode'])
        self.list_optioncode.insert(1,self.op_code)
        print("file parse success!")


if (__name__ == "__main__"):
    # 创建一个 XMLReader
    parser = xml.sax.make_parser()
    # 关闭命名空间
    parser.setFeature(xml.sax.handler.feature_namespaces, 0)
    # 重写 ContextHandler
    Handler = OrderFileHandler()
    parser.setContentHandler(Handler)
    parser.parse("C:/Users/Administrator/Desktop/file/A0000000.xml")
    print(Handler.list_optioncode)
    write_to_excel(Handler.list_orderdata)
    write_to_excel(Handler.list_fileInfo)
    write_to_excel(Handler.list_optioncode)

如果xml文件较大，涉及到的属性比较多，人工敲代码也比较耗费时间。可以使用以下代码实现代码内容转换。

import os , sys , re

# 在代码文件相同目录下创建一个test.txt的文件，并将需要转换的xml片段粘贴到该文件中。并根据需要更改str_statement内容。
def generate_code():
    file = os.path.dirname(os.path.realpath(__file__))+"\\test.txt"
    with open(file,'a+') as f:
        f.seek(0,0) # 将指针放到文件其实位置
        line = str(f.readlines())
        key = re.findall(r'\s(\w*)=',line)
        print(key)
        for item in range(len(key)):
            attrs = key[item]
            str_statement = "self.dic_fileInfo['"+attrs+"'] = attributes.get('"+attrs+"')"+'\n'
            f.write(str_statement)
            
generate_code()

转换后的test.txt文件内容如下：

<fileInfo date="20170720065220" comment="RESERVED" author="system" plantId="gcdm" firstCreationDate="2017-07-20T06:52:20+08:00" latestCreationDate="2027-07-20T06:52:00+08:00" vehicleState="6300">

##上面是代码执行前加入的内容，下面是代码执行后追加的内容##

self.dic_fileInfo['date'] = attributes.get('date')
self.dic_fileInfo['comment'] = attributes.get('comment')
self.dic_fileInfo['author'] = attributes.get('author')
self.dic_fileInfo['plantId'] = attributes.get('plantId')
self.dic_fileInfo['firstCreationDate'] = attributes.get('firstCreationDate')
self.dic_fileInfo['latestCreationDate'] = attributes.get('latestCreationDate')
self.dic_fileInfo['vehicleState'] = attributes.get('vehicleState')