python - JSON 到 Excel 使用 Openpyxyl - 转换复杂/嵌套数组

Question

我是 JSON 和数组/列表的新手。我正在尝试将此 JSON 文件放入 Excel 文件中：

{
  "__collections__": {
    "users": {
      "a12jh2d53622146fj": {
        "age": "19",
        "location": "USA"
      },
      "agHSHnAp363mqo125": {
        "age": "4865",
        "location": "UK"
      }
    }
  }
}

这是我一直在处理的代码：

import json
from openpyxl import Workbook

if __name__ == '__main__':

    json_data = {}

    with open("Test.json") as json_file:
        json_data = json.load(json_file)

    wb = Workbook()
    ws = wb.active
    ws.title = "Test"

    ws.cell(1,1, "User ID")
    ws.cell(1,2, "Age")
    ws.cell(1,3, "Location")

    row = 1

    for userID in json_data.keys():
        row += 1
        ws.cell(row,1, userID)
        ws.cell(row,2, str(json_data[userID]["age"]))
        ws.cell(row,3, str(json_data[userID]["location"]))
        
    wb.save("Test.xlsx")

不幸的是，上面的代码只有在 JSON 文件是这样的情况下才有效：

{
  "a12jh2d53622146fj": {
    "age": "19",
    "location": "USA"
  },
  "agHSHnAp363mqo125": {
    "age": "4865",
    "location": "UK"
  }
}

这是输出的excel文件：

干杯!

标签： pythonarraysjsonexcelopenpyxl