基于预定义XML结构，将Excel每行生成独立XML文件的技术求助

我有一个预定义结构的XML模板，想要把Excel表格里的数据填充到指定元素（如<eadid>、<titleproper>等）中，并且为Excel的每一行数据生成一个独立的XML文件。但我写的Python代码生成的XML没有保留完整的预定义Schema结构，只输出了<ExportData>节点，和预期不符。Excel共有916行，相关文件、代码及输出示例如下：

预定义XML模板 #

<?xml version="1.0" encoding="UTF-8"?>
<ead xmlns="urn:isbn:1-931666-22-9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:isbn:1-931666-22-9 http://www.loc.gov/ead/ead.xsd">
<eadheader>
    <eadid></eadid>
    <filedesc>
    <titlestmt>
        <titleproper></titleproper>
    </titlestmt>
    </filedesc>
    <profiledesc>
        <langusage>
            <language langcode="eng" scriptcode="Latn"></language>
        </langusage>
    </profiledesc>
    </eadheader>
        <archdesc level="fonds">
            <did>
                <unitid></unitid>
                <langmaterial>
                    <language langcode="eng" scriptcode="Latn"></language>
                </langmaterial>
                <unittitle></unittitle>
                <unitdate normal=""></unitdate>
                <physdesc>
                    <extent>1 Cubic Feet</extent>
                </physdesc>
                <langmaterial>
                    <language langcode="eng" scriptcode="Latn">English</language>
                </langmaterial>
            </did>
            <dsc>
            </dsc>
        </archdesc>
</ead>

尝试的Python代码 #

import pandas as pd
pip install openpyxl
from lxml import etree as et
import xml.etree.ElementTree as Xet
tree = et.parse('test_resource.xml')
root = tree.getroot()
raw_data = pd.read_excel(r'/Users/smeyerkukan/Desktop/ArchivesSpace/Python Coding/aspace.xlsx')
tree = et.parse('test_resource.xml')
root = tree.getroot()
for row in raw_data.iterrows():
    root_tags = et.SubElement(root, 'ExportData')
    Column_heading_1 = et.SubElement(root_tags, 'titleproper')
    Column_heading_2 = et.SubElement(root_tags, 'unittitle')
    Column_heading_3 = et.SubElement(root_tags, 'eadid')
    Column_heading_4 = et.SubElement(root_tags, 'unitid')
    Column_heading_7 = et.SubElement(root_tags, 'unitdate')
    Column_heading_1.text = str(row[1]['<titleproper>'])
    Column_heading_2.text = str(row[1]['<unittitle>'])
    Column_heading_3.text = str(row[1]['<eadid>'])
    Column_heading_4.text = str(row[1]['<unitid>'])
    Column_heading_7.text = str(row[1]['<unitdate>'])
tree = et.ElementTree(root)
et.indent(tree, space="	", level=0)
tree.write('output.xml', encoding="utf-8")

期望输出 #

<?xml version="1.0" encoding="UTF-8"?>
<ead xmlns="urn:isbn:1-931666-22-9" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:isbn:1-931666-22-9 http://www.loc.gov/ead/ead.xsd">
<eadheader>
    <eadid>Church RG 095</eadid>
    <filedesc>
    <titlestmt>
        <titleproper>Salem Reformed Church (Philadelphia, PA)</titleproper>
    </titlestmt>
    </filedesc>
    <profiledesc>
        <langusage>
            <language langcode="eng" scriptcode="Latn"></language>
        </langusage>
    </profiledesc>
    </eadheader>
        <archdesc level="fonds">
            <did>
                <unitid>Church RG 095</unitid>
                <langmaterial>
                    <language langcode="eng" scriptcode="Latn"></language>
                </langmaterial>
                <unittitle>Salem Reformed Church (Philadelphia, PA)</unittitle>
                <unitdate normal="1811/2003"></unitdate>
                <physdesc>
                    <extent>1 Cubic Feet</extent>
                </physdesc>
                <langmaterial>
                    <language langcode="eng" scriptcode="Latn">English</language>
                </langmaterial>
            </did>
            <dsc>
            </dsc>
        </archdesc>
</ead>

实际输出 #

<ExportData>
    <titleproper>Salem Reformed Church (Philadelphia, PA)</titleproper>
    <unittitle>Salem Reformed Church (Philadelphia, PA)</unittitle>
    <eadid>Church RG 095</eadid>
    <unitid>Church RG 095</unitid>
    <unitdate>1811/2003</unitdate>
</ExportData>
<ExportData>
    <titleproper>First Zion Lehigh Union Church (Alburtis, Pa.)</titleproper>
    <unittitle>First Zion Lehigh Union Church (Alburtis, Pa.)</unittitle>
    <eadid>Church RG 001</eadid>
    <unitid>Church RG 001</unitid>
    <unitdate>1843/2019</unitdate>
</ExportData>
<ExportData>
    <titleproper>Christ Reformed Church (Allentown, PA)</titleproper>
    <unittitle>Christ Reformed Church (Allentown, PA)</unittitle>
    <eadid>Church RG 002</eadid>
    <unitid>Church RG 002</unitid>
    <unitdate>1876/1982</unitdate>
</ExportData>

Excel文件截图 #

解决方案 #

你的代码问题在于没有复用原XML模板的结构，反而直接在根节点下新增了ExportData元素。正确的做法是为每一行数据复制一份原XML模板，找到模板中对应的元素填充数据，最后保存为独立的XML文件。

修改后的代码如下：

import pandas as pd
from lxml import etree as et

# 读取Excel数据
raw_data = pd.read_excel(r'/Users/smeyerkukan/Desktop/ArchivesSpace/Python Coding/aspace.xlsx')
# 读取XML模板路径
template_path = 'test_resource.xml'

# 遍历每一行数据
for idx, row in raw_data.iterrows():
    # 每次循环重新加载模板，确保结构干净
    tree = et.parse(template_path)
    root = tree.getroot()
    
    # 填充eadid（定位到eadheader下的eadid元素）
    eadid_elem = root.find('.//eadid')
    if eadid_elem is not None:
        eadid_elem.text = str(row['<eadid>'])
    
    # 填充titleproper（定位到eadheader/filedesc/titlestmt下的titleproper元素）
    titleproper_elem = root.find('.//titleproper')
    if titleproper_elem is not None:
        titleproper_elem.text = str(row['<titleproper>'])
    
    # 填充unitid（定位到archdesc/did下的unitid元素）
    unitid_elem = root.find('.//unitid')
    if unitid_elem is not None:
        unitid_elem.text = str(row['<unitid>'])
    
    # 填充unittitle（定位到archdesc/did下的unittitle元素）
    unittitle_elem = root.find('.//unittitle')
    if unittitle_elem is not None:
        unittitle_elem.text = str(row['<unittitle>'])
    
    # 填充unitdate的normal属性
    unitdate_elem = root.find('.//unitdate')
    if unitdate_elem is not None:
        unitdate_value = str(row['<unitdate>'])
        unitdate_elem.set('normal', unitdate_value)
    
    # 生成独立文件名，按索引区分避免覆盖
    output_filename = f'output_{idx}.xml'
    # 格式化XML缩进
    et.indent(tree, space="\t", level=0)
    # 保存XML文件，保留声明头
    tree.write(output_filename, encoding="utf-8", xml_declaration=True)

代码说明 #

1. 循环加载模板：每处理一行数据就重新读取XML模板，避免之前的数据污染模板结构。
2. XPath定位元素：通过XPath精准找到模板中需要填充的元素，直接修改文本或属性。
3. 生成独立文件：为每一行数据生成单独的XML文件，文件名用索引区分，防止文件覆盖。
4. 保留XML声明：写入文件时添加xml_declaration=True，确保输出的XML包含版本和编码声明。

内容的提问来源于stack exchange，提问作者Scott Meyer-Kukan