用python代码将excel中的数据批量写入Json中的某个字段，生成新的Json文件

需求

需求：

1.将execl文件中的A列赋值给json中的TrackId，B列赋值给json中的OId
要求 execl的每一行，对应json中的每一个OId
json 如下：

{
    "List": [{
            "BatchNumber": "181-{{var}}",
            //  "BatchNumber": "181-17194575111",
            "FlightNumber": "10214",
            "AirlineCode": "CO",
            "FlightDepartureAirport": "ADA",
            "EstimatedExportTime":"2025-02-18 09:23:21",
            "EstimatedArriveTime":"2024-05-19 09:23:21",
            "FlightEntryAirport": "ADA",
            "FileType":"pdf",
			"AirWaybillFile": ""
            "BagDataList": [ {
                    "BagNo": "PKG{{var}}6",
                    "OrderDatas": [{
                            "OId": "BG-2401248300000001133",
                            "TrackId": "97222600001133"
                        },
                        {
                            "OId": "BG-2401248300000001133",
                            "TrackId": "97222600001133"
                        }
                    ]
                }
                    ]
                }
            ]
        }
    ]
}

2.execl 保存在 D:\PYTHON-学习\邮政接口压力测试脚本\pythonProject1\test_73553\IDS.xlsx 中
3.赋值完成后，输出新的json格式文件保存在项目JSON目录中

实现代码

# -*- coding: utf-8 -*-
import pandas as pd
import json
from pathlib import Path

# 读取Excel文件（注意路径中的中文）
excel_path = r"D:\PYTHON-学习\邮政接口压力测试脚本\pythonProject1\test_73553\IDS.xlsx"
df = pd.read_excel(excel_path)

# 构建JSON结构
template = {
    "List": [{
        "BatchNumber": "181-{{var}}",
        "FlightNumber": "10214",
        "AirlineCode": "CO",
        "FlightDepartureAirport": "ADA",
        "EstimatedExportTime": "2025-02-18 09:23:21",
        "EstimatedArriveTime": "2024-05-19 09:23:21",
        "FlightEntryAirport": "ADA",
        "FileType": "pdf",
        "AirWaybillFile": "",
        "BagDataList": [{
            "BagNo": "PKG{{var}}6",
            "OrderDatas": []
        }]
    }]
}

# 填充OrderDatas
for _, row in df.iterrows():
    order_data = {
        "OId": row["原平台ID"],
        "TrackId": row["跟踪号"]
    }
    template["List"][0]["BagDataList"][0]["OrderDatas"].append(order_data)

# 创建输出目录
output_dir = Path.cwd() / "JSON"
output_dir.mkdir(exist_ok=True)

# 保存JSON文件（显式指定utf-8编码）
output_path = output_dir / "output.json"
with open(output_path, "w", encoding="utf-8") as f:
    json.dump(template, f, indent=4, ensure_ascii=False)

print(f"JSON文件已生成至：{output_path}")

实现结果