如何将JSON**text**写入Excel文件？

Question

在Python中有没有方法可以将JSON文本写入Excel文件？在

通常我会将JSON响应加载到Pandas dataframe中，并将dataframe写入Excel：

import pandas
form pandas.io.json import json_normalize
import requests

def df_to_excel(df, filename):
    writer = pandas.ExcelWriter(filename)
    df.to_excel(writer, 'Sheet1')
    writer.save()

response = requests.get(url, params).json()
df = json_normalize(response)
df_to_excel(df, 'Response.xlsx')

因此，{cd4}要用

Answer 1

您可以先将json转换为字典，然后再转换为excel文件，如下所示Write dictionary values in an excel file：

import json
import xlsxwriter
import requests

response = requests.get(url, params)
d = json.loads(response.text)

workbook = xlsxwriter.Workbook('data.xlsx')
worksheet = workbook.add_worksheet()

row = 0
col = 0

for key in d.keys():
    row += 1
    worksheet.write(row, col, json.dumps(key))
    for item in d[key]:
        worksheet.write(row, col + 1, json.dumps(item))
        row += 1

workbook.close()

关键是使用json.dumps()将python格式转换回json格式。例如，json.dumps(None)返回'null'