Python 奇淫技巧 — 利用pandas读取xml转换为excel

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

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

因为工作需要， 将xml中特定的节点值取出来， 然后统计到excel中。 于是乎试试写了一个python脚本， 加快工作效率。 而且今后还能复用。

以下为完整示例， 需要的朋友们可参考。

示例 XML

<?xml version="1.0" encoding="utf-8"?>
<breakfast_menu> 
<food> 
<name>Belgian Waffles</name>  
<price>$5.95</price>  
<description>Two of our famous Belgian Waffles with plenty of real maple syrup</description>  
<calories>650</calories> 
</food>  
<food> 
<name>Strawberry Belgian Waffles</name>  
<price>$7.95</price>  
<description>Light Belgian waffles covered with strawberries and whipped cream</description>  
<calories>900</calories> 
</food>  
<food> 
<name>Berry-Berry Belgian Waffles</name>  
<price>$8.95</price>  
<description>Light Belgian waffles covered with an assortment of fresh berries and whipped cream</description>  
<calories>900</calories> 
</food>  
<food> 
<name>French Toast</name>  
<price>$4.50</price>  
<description>Thick slices made from our homemade sourdough bread</description>  
<calories>600</calories> 
</food>  
<food> 
<name>Homestyle Breakfast</name>  
<price>$6.95</price>  
<description>Two eggs, bacon or sausage, toast, and our ever-popular hash browns</description>  
<calories>950</calories> 
</food> 
</breakfast_menu>

python 脚本

from lxml import etree
import pandas as pd
def read_data_from_xml(xml_path):
xml_content = ""
with open(xml_path,'rb') as f:
xml_content = f.read()
excel_data = [["食物", "价格", "卡路里", "描述"]]
xml_data = etree.XML(xml_content)
foods = xml_data.xpath("//food")
for food in foods:
excel_row_data = []
excel_row_data.extend(food.xpath("name/text()"))
excel_row_data.extend(food.xpath("price/text()"))
excel_row_data.extend(food.xpath("calories/text()"))
excel_row_data.extend(food.xpath("description/text()"))
excel_data.append(excel_row_data)
return excel_data
def to_csv(writer, excel_data, sheet_name):
data_df = pd.DataFrame(excel_data[1:])
data_df.columns = excel_data[0]
data_df.to_excel(writer,float_format='%.10f',index=False, sheet_name=sheet_name)
worksheet = writer.sheets[sheet_name]
// 设置列宽
cols = "%s:%s" % ('A', chr(ord('A') + len(data_df.columns) - 1))
worksheet.set_column(cols, 30)
// 读取xml
excel_data_ = read_data_from_xml("food.xml")
writer = pd.ExcelWriter('food.xlsx')
to_csv(writer, excel_data_, "food1")
writer.save()

最终效果