如何实现Excel每行数据批量生成对应独立的复杂XML文件

优先选用Python实现，性能远高于VBA，处理多层级XML结构逻辑清晰，5000条数据全程耗时不超过10秒，不会出现VBA卡顿、崩溃、中途中断的问题。

前置准备 #

• 安装Python环境，安装2个依赖库：执行命令pip install pandas openpyxl即可，XML生成用Python自带的标准库，无需额外安装组件。
• 整理Excel文件：将第一行设为列名，和XML字段做对应，比如订单号列命名为order_number、联系人姓名列命名为contact_name，方便后续字段映射。
• 在脚本同级目录新建名为xml_output的空文件夹，用于存放生成的所有XML文件。

实现代码 #

将以下代码保存为excel2xml.py，修改开头的配置项为你本地的实际文件路径，直接运行即可：

import pandas as pd
import xml.etree.ElementTree as ET
from xml.dom import minidom
import os

# 配置项 - 按实际情况修改
EXCEL_FILE_PATH = "你的业务数据.xlsx"
OUTPUT_FOLDER = "./xml_output"
XML_ENCODING = "UTF-8"

def build_single_xml(row):
    # 根节点
    root = ET.Element("comtec", attrib={"version": "2010"})
    transport_order = ET.SubElement(root, "transport_order")

    # 基础一级字段映射
    ET.SubElement(transport_order, "id").text = str(row["id"])
    ET.SubElement(transport_order, "order_number").text = str(row["order_number"])
    ET.SubElement(transport_order, "priority").text = str(row["priority"])
    ET.SubElement(transport_order, "order_date").text = str(row["order_date"])

    # order_status二级节点
    order_status = ET.SubElement(transport_order, "order_status")
    ET.SubElement(order_status, "code").text = str(row["order_status_code"])

    # contact二级节点
    contact = ET.SubElement(transport_order, "contact")
    ET.SubElement(contact, "id").text = str(row["contact_id"])
    ET.SubElement(contact, "code").text = str(row["contact_code"])
    ET.SubElement(contact, "name").text = str(row["contact_name"])
    ET.SubElement(transport_order, "contactId").text = str(row["contact_id"])
    ET.SubElement(transport_order, "productId").text = str(row["product_id"])

    # amounts二级节点，按模板固定5个单位循环生成
    amounts = ET.SubElement(transport_order, "amounts")
    unit_code_list = ["xy", "yz", "Total", "Kg", "zx"]
    for unit in unit_code_list:
        amount_node = ET.SubElement(amounts, "amount")
        ET.SubElement(amount_node, "unit_code").text = unit
        ET.SubElement(amount_node, "value").text = str(row[f"amount_{unit}"])

    # department二级节点
    department = ET.SubElement(transport_order, "department")
    ET.SubElement(department, "code").text = str(row["department_code"])
    ET.SubElement(department, "name").text = str(row["department_name"])
    ET.SubElement(transport_order, "department_code").text = str(row["department_code"])

    # pickup_task二级节点
    pickup_task = ET.SubElement(transport_order, "pickup_task")
    pickup_addr = ET.SubElement(pickup_task, "address")
    ET.SubElement(pickup_addr, "id").text = str(row["pickup_addr_id"])

    # delivery_task二级节点
    delivery_task = ET.SubElement(transport_order, "delivery_task")
    delivery_addr = ET.SubElement(delivery_task, "address")
    ET.SubElement(delivery_addr, "id").text = str(row["delivery_addr_id"])
    ET.SubElement(delivery_addr, "code").text = str(row["delivery_addr_code"])
    ET.SubElement(delivery_addr, "address_kind_code").text = str(row["address_kind_code"])
    ET.SubElement(delivery_addr, "street_name").text = str(row["street_name"])
    ET.SubElement(delivery_addr, "zipcode").text = str(row["zipcode"])
    ET.SubElement(delivery_addr, "city").text = str(row["city"])
    ET.SubElement(delivery_addr, "state_name")  # 空值自闭合节点
    ET.SubElement(delivery_addr, "country_code").text = str(row["country_code"])
    ET.SubElement(delivery_addr, "givenX")  # 空值自闭合节点
    ET.SubElement(delivery_addr, "givenY")  # 空值自闭合节点
    ET.SubElement(delivery_addr, "temporary").text = "true" if row["temporary"] else "false"
    ET.SubElement(delivery_addr, "plan_region_code").text = str(row["plan_region_code"])

    task_window = ET.SubElement(delivery_task, "task_window")
    ET.SubElement(task_window, "from_instant").text = str(row["window_from"])
    ET.SubElement(task_window, "till_instant").text = str(row["window_till"])
    ET.SubElement(delivery_task, "duration").text = str(row["duration"])

    # 格式化缩进，和模板排版一致
    rough_content = ET.tostring(root, encoding=XML_ENCODING)
    dom = minidom.parseString(rough_content)
    formatted_xml = dom.toprettyxml(indent="    ", encoding=XML_ENCODING)
    return formatted_xml

if __name__ == "__main__":
    # 批量读取Excel，默认读取第一个工作表
    df = pd.read_excel(EXCEL_FILE_PATH, dtype=str)
    os.makedirs(OUTPUT_FOLDER, exist_ok=True)
    # 逐行生成XML文件，文件名用订单号命名避免重名
    for _, row in df.iterrows():
        file_path = os.path.join(OUTPUT_FOLDER, f"{row['order_number']}.xml")
        xml_content = build_single_xml(row)
        with open(file_path, "wb") as f:
            f.write(xml_content)
    print(f"处理完成，共生成{len(df)}个XML文件，已保存至{OUTPUT_FOLDER}目录")

使用说明 #

• 代码中的字段名是和你提供的XML模板对应的示例，实际使用时把row["列名"]里的列名改成你自己Excel里的实际列名即可，比如Excel里订单号列叫“订单编号”，就把row["order_number"]改成row["订单编号"]。
• 模板里的空节点比如state_name/givenX/givenY代码已经做了适配，会自动生成标准自闭合标签，和你给的模板格式完全一致。
• 如果amount节点数量不固定，只需要修改unit_code_list的取值逻辑，从对应列动态读取即可，调整成本极低。
• 运行全程不需要打开Excel，内存占用稳定，不会出现VBA运行时假死、中途报错丢数据的问题。

不推荐继续优化VBA的原因：VBA是单线程解释执行，DOM操作、字符串拼接的内存开销极高，5000条复杂结构XML运行动辄十几分钟，且很容易因为内存溢出中断，Python方案的处理速度是VBA的几十倍，稳定性更高。

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