python - 通过 Python 将 JSON 转换为 Excel

Question

我有一个需要转换为 Excel 的 JSON。我正在使用带有 xlsxwriter 库的 Python 3.8。下面是示例 JSON。

{
    "companyId": "123456",
    "companyName": "Test",
    "companyStatus": "ACTIVE",
    "document": {
        "employee": {
            "employeeId": "EM1567",
            "employeeLastName": "Test Last",
            "employeeFirstName": "Test Fist"
        },
        "expenseEntry": [
            {
            "allocation": [
                {
                "allocationId": "03B249B3598",
                "journal": [
                    {
                        "journalAccountCode": "888",
                        "journalPayee": "EMPL",
                        "journalPayer": "COMP",
                        "taxGuid": [
                            "51645A638114E"
                        ]
                    },
                    {
                        "journalAccountCode": "999",
                        "journalPayee": "EMPL",
                        "journalPayer": "EMPL",
                        "taxGuid": [
                            "8114E51645A63"
                        ]
                    },
                ],
                "tax": [
                    {
                        "taxCode": "TAX123",
                        "taxSource": "SYST"
                    },
                    {
                        "taxCode": "TAX456",
                        "taxSource": "SYST"
                    }
                ]
                }
            ],
            "approvedAmount": 200.0,
            "entryDate": "2020-12-10",
            "entryId": "ENTRY9988"
            }
        ],
        "report": {
            "currencyCode": "USD",
            "reportCreationDate": "2020-12-10",
            "reportId": "ACA849BBB",
            "reportName": "Test Report",
            "totalApprovedAmount": 200.0
        }
    },
    "id": "c71b7d756f549"
}

我当前的代码： https ://repl.it/@tonyiscoming/jsontoexcel

我试过熊猫

import pandas as pd

df = pd.json_normalize(data, max_level=5)
df.to_excel('test.xlsx', index=False)

并得到了结果

我试过 json_excel_converter

from json_excel_converter import Converter 
from json_excel_converter.xlsx import Writer

conv = Converter()
conv.convert(data, Writer(file='test.xlsx'))

并得到了结果

这是我的期望

在这种情况下有人可以帮助我吗？太感谢了。

标签： pythonjsonpython-3.xexcel

Answer 1

在 Excel 网格中拥有空单元格并不是真正的“正确”，这就是 json_excel_converter 行为如此的原因。所以，如果你想做到这一点，恐怕你必须自己开发它。