Python中是否有任何方法可以将JSONtext写入Excel文件?
通常,我会将JSON响应加载到Pandas数据帧中,并将数据帧写入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')
但这需要将JSON文本转换为Python对象,从而用'
替换"
,用False
替换false
,用None
替换null
等等,我不希望这样。
您可以先将json转换为dictionary,然后按照此处的建议转换为excel文件在excel文件中写入字典值:
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'