python解析XML文件并转存到excel「建议收藏」

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

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')