需求

需求：

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

實現結果