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

全栈程序员站长

发布于 2022-09-23 09:59:34

1.7K0

发布于 2022-09-23 09:59:34

文章被收录于专栏：全栈程序员必看

大家好，又见面了，我是你们的朋友全栈君。

因为工作需要，将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()

最终效果