用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}")