具有复杂数据结构的JSON响应

Question

我正在尝试从VBA迁移到Python。我正在尝试将结果传递到Excel

[
    {
        "id": 1993966466,
        "deficiencyArea": {
            "id": 270424374,
            "area": "18",
            "areaDescription": "Labour conditions",
            "subArea": "4",
            "subAreaDescription": "Health protection, medical care, social security",
            "ismRelated": False
        },
        "defectiveItem": {
            "id": 273058079,
            "code": "18417",
            "description": "Anchoring devices",
            "restricted": False
        },
        "defaultDescription": {
            "id": 742,
            "code": "1047",
            "description": "Not ready for use"
        },
        "groundForDetention": False,
        "recognizedOrganizationRelated": False,
        "accidentalDamage": False,
        "ismRelated": False
    },
    {
        "id": 1993966468,
        "deficiencyArea": {
            "id": 156,
            "area": "07",
            "areaDescription": "Fire safety",
            "subArea": "1",
            "subAreaDescription": None,
            "ismRelated": False
        },
        "defectiveItem": {
            "id": 1358,
            "code": "07105",
            "description": "Fire doors/openings in fire-resisting divisions",
            "restricted": False
        },
        "defaultDescription": {
            "id": 470,
            "code": "1011",
            "description": "Not as required"
        },
        "groundForDetention": False,
        "recognizedOrganizationRelated": False,
        "accidentalDamage": False,
        "ismRelated": False
    }
]

目标是将每个缺陷（每个缺陷一行）的以下键传递给Excel:“区域描述”、“子标题描述”、“描述”（此键位于“缺陷区域”内）、“受限”和“描述”（此键位于“defaultDescription”内）

我试过几件事，但都没试过。 有什么建议吗

Answer 1

一些代码作为开始。它创建一个新工作簿并将值写入行

import json
import openpyxl

data = [
    {
        "id": 1993966466,
        "deficiencyArea": {
            "id": 270424374,
            "area": "18",
            "areaDescription": "Labour conditions",
            "subArea": "4",
            "subAreaDescription": "Health protection, medical care, social security",
            "ismRelated": False
        },
        "defectiveItem": {
            "id": 273058079,
            "code": "18417",
            "description": "Anchoring devices",
            "restricted": False
        },
        "defaultDescription": {
            "id": 742,
            "code": "1047",
            "description": "Not ready for use"
        },
        "groundForDetention": False,
        "recognizedOrganizationRelated": False,
        "accidentalDamage": False,
        "ismRelated": False
    },
    {
        "id": 1993966468,
        "deficiencyArea": {
            "id": 156,
            "area": "07",
            "areaDescription": "Fire safety",
            "subArea": "1",
            "subAreaDescription": None,
            "ismRelated": False
        },
        "defectiveItem": {
            "id": 1358,
            "code": "07105",
            "description": "Fire doors/openings in fire-resisting divisions",
            "restricted": False
        },
        "defaultDescription": {
            "id": 470,
            "code": "1011",
            "description": "Not as required"
        },
        "groundForDetention": False,
        "recognizedOrganizationRelated": False,
        "accidentalDamage": False,
        "ismRelated": False
    }
]

# create workbook
wb = openpyxl.Workbook()
ws = wb.worksheets[0]

row1 = ["id","areaDescription","subAreaDescription","description","restricted","description"]
for i in range(1,7):
    ws.cell(1,i).value = row1[i-1]

i = 1
for rec in data:

    area = rec["deficiencyArea"]
    item = rec["defectiveItem"]    
    desc = rec["defaultDescription"]

    i += 1
    ws.cell(i,1).value = rec["id"]
    ws.cell(i,2).value = area["areaDescription"]
    ws.cell(i,3).value = area["subAreaDescription"]
    ws.cell(i,4).value = item["description"]
    ws.cell(i,5).value = item["restricted"]
    ws.cell(i,6).value = desc["description"]

filename = "result.xlsx"
wb.save(filename=filename)
print("{} created with {} rows".format(filename,i-1))