如何用Python+XPath读取XML并向Excel对应行写入空值？

我需要用Python将XML数据输出为三列：

• 第1列：wnc90Value值
• 第2列：wnc90Docid值
• 第3列：wnc90Expheading值

要求每个//node[starts-with(local-name(), "level")]对应的三个值处于同一行，无wnc90Expheading时对应列填"N/A"。但目前缺失值都被放到了第3列的最后一行，而非对应行。

我尝试的代码 #

from lxml import etree
import glob
from openpyxl import Workbook
from pathlib import Path
import re

# Path to XML file
path="C:\\Users\\x\\x\\x\\x\\TestXML\\x.xml"

# Filename of XML file
fileName = Path(path).name

# Create a new workbook and rename the active worksheet
workbook = Workbook()
worksheet = workbook.active
worksheet.title = "WNC90"

# Save the workbook
workbook.save('Results - Detailed (' + fileName + ').xlsx')

# Write headers to worksheet
worksheet['A1'] = "metadata value"
worksheet['B1'] = "exp-level ID"
worksheet['C1'] = "exp-level heading"

# Initialize the row number to start writing data
row_num = 2

for file in glob.glob(path):
    
    parser = etree.XMLParser(load_dtd=True, resolve_entities=False)
    tree = etree.parse(file, parser=parser)
    root = tree.getroot()

    # Get the wnc90 values for the level elements   
    wnc90Value = root.xpath('.//*[starts-with(local-name(), "level")]/ext-metadata/ext-meta-object/ext-meta-attrs/ext-meta-attr[@value="ThreeMonthItem" or @value="FifteenMonthItem"]/@value')
    
    # Get the docID values for the level elements with wnc90 metadata
    wnc90Docid = root.xpath('.//*[starts-with(local-name(), "level")][ext-metadata/ext-meta-object/ext-meta-attrs/ext-meta-attr[@value="ThreeMonthItem" or @value="FifteenMonthItem"]]/@id')
    
    # Get the heading in the level elements with wnc90 metadata 
    wnc90Expheading = root.xpath('.//*[starts-with(local-name(), "level")][ext-metadata/ext-meta-object/ext-meta-attrs/ext-meta-attr[@value="armacThreeMonthItem" or @value="FifteenMonthItem"]]/heading[1]')
     
    # Write data to worksheet
    for i in range(len(wnc90Docid)):
        worksheet.cell(row=row_num, column=1, value=wnc90Value[i])
        worksheet.cell(row=row_num, column=2, value=wnc90Docid[i])
        
        if i < len(wnc90Expheading):
            worksheet.cell(row=row_num, column=3, value=wnc90Expheading[i].text)  
        else:
            worksheet.cell(row=row_num, column=3, value="N/A")                          
            
        row_num += 1

# Save the workbook
workbook.save('Results - Detailed (' + fileName + ').xlsx')

实际输出 #

data = {'metadata value': ['ThreeMonthItem', 'ThreeMonthItem', 'FifteenMonthItem'],
    'level ID': ['FB8404BF64C8C724862584BD0059BABD', 'A38592747306A36786257E290059CDF4', '51B86B7505A5F8BE862588AA006A6F53'],
    'level heading': ['Discussion', 'B.1-B.19 Appendix B', 'N/A']}

预期输出 #

data = {'metadata value': ['ThreeMonthItem', 'ThreeMonthItem', 'FifteenMonthItem'],
    'level ID': ['FB8404BF64C8C724862584BD0059BABD', 'A38592747306A36786257E290059CDF4', '51B86B7505A5F8BE862588AA006A6F53'],
    'level heading': ['Discussion', 'N/A', 'B.1-B.19 Appendix B']}

XML结构 #

<root>
    <level-1 id="FB8404BF64C8C724862584BD0059BABD">
        <heading>Discussion</heading>
        <ext-metadata>
            <ext-meta-object>
                <ext-meta-attrs>
                    <ext-meta-attr value="ThreeMonthItem"/>
                </ext-meta-attrs>
            </ext-meta-object>
        </ext-metadata>
    </level-1>
    <level-1 id="A38592747306A36786257E290059CDF4">
        <ext-metadata>
            <ext-meta-object>
                <ext-meta-attrs>
                    <ext-meta-attr value="ThreeMonthItem"/>
                </ext-meta-attrs>
            </ext-meta-object>
        </ext-metadata>
    </level-1>
    <level-2 id="51B86B7505A5F8BE862588AA006A6F53">
        <heading>B.1-B.19 Appendix B</heading>
        <ext-metadata>
            <ext-meta-object>
                <ext-meta-attrs>
                    <ext-meta-attr value="FifteenMonthItem"/>
                </ext-meta-attrs>
            </ext-meta-object>
        </ext-metadata>
    </level-2>
</root>

解决方案 #

问题根源是你分开提取三个独立列表，再通过索引匹配，但第二个level节点没有heading，导致wnc90Expheading列表只包含有heading的节点（第一个和第三个），索引错位。正确做法是先定位所有符合条件的level节点，再逐个提取每个节点的三个值，保证一一对应。

修改后的代码：

from lxml import etree
import glob
from openpyxl import Workbook
from pathlib import Path

# Path to XML file
path="C:\\Users\\x\\x\\x\\x\\TestXML\\x.xml"

# Filename of XML file
fileName = Path(path).name

# Create a new workbook and rename the active worksheet
workbook = Workbook()
worksheet = workbook.active
worksheet.title = "WNC90"

# Write headers to worksheet
worksheet['A1'] = "metadata value"
worksheet['B1'] = "exp-level ID"
worksheet['C1'] = "exp-level heading"

# Initialize the row number to start writing data
row_num = 2

for file in glob.glob(path):
    parser = etree.XMLParser(load_dtd=True, resolve_entities=False)
    tree = etree.parse(file, parser=parser)
    root = tree.getroot()

    # 先定位所有符合条件的level节点
    level_nodes = root.xpath('.//*[starts-with(local-name(), "level")][ext-metadata/ext-meta-object/ext-meta-attrs/ext-meta-attr[@value="ThreeMonthItem" or @value="FifteenMonthItem"]]')
    
    # 逐个处理每个节点
    for node in level_nodes:
        # 提取当前节点的metadata值
        wnc90_value = node.xpath('./ext-metadata/ext-meta-object/ext-meta-attrs/ext-meta-attr[@value="ThreeMonthItem" or @value="FifteenMonthItem"]/@value')[0]
        # 提取节点ID
        wnc90_docid = node.get('id')
        # 提取heading，不存在则设为N/A
        heading_elem = node.xpath('./heading[1]')
        wnc90_heading = heading_elem[0].text if heading_elem else "N/A"
        
        # 写入工作表
        worksheet.cell(row=row_num, column=1, value=wnc90_value)
        worksheet.cell(row=row_num, column=2, value=wnc90_docid)
        worksheet.cell(row=row_num, column=3, value=wnc90_heading)
        
        row_num += 1

# 保存工作簿
workbook.save(f'Results - Detailed ({fileName}).xlsx')

修改说明 #

1. 先定位节点再提取值：确保每个行的三个字段都来自同一个level节点，彻底避免索引错位问题
2. 修正XPath错误：原代码中wnc90Expheading的XPath多写了armac，导致匹配范围错误
3. 逐个处理节点：对每个节点单独判断是否存在heading，直接赋值"N/A"，保证对应行的正确性

内容的提问来源于stack exchange，提问作者user4504522