使用Python将JSON转换为XLS文件

Question

我试图将JSON文件转换为XLS文件，但它返回一个名为：的错误

->name=记录['name']TypeError：字节索引必须是整数或切片，而不是str

我已经尝试过用JS做这件事，但我完全迷失了方向，我认为Python将更容易理解所有这些

import os
import json
import win32com.client as win32  # pip install pywin32
import requests
"""
Step 1.1 Read the JSON file
"""

json_data = requests.get("https://restcountries.com/v3.1/all")
print(json_data.content)
"""
Step 1.2 Examing the data and flatten the records into a 2D layout
"""
rows = []
for record in json_data:
name = record['name']

"""
Step 2. Inserting Records to an Excel Spreadsheet
"""
ExcelApp = win32.Dispatch('Excel.Application')
ExcelApp.Visible = True
wb = ExcelApp.Workbooks.Add()
ws = wb.Worksheets(1)
header_labels = ('name')
# insert header labels
for indx, val in enumerate(header_labels):
ws.Cells(1, indx + 1).Value = val
# insert Records
row_tracker = 2
column_size = len(header_labels)
for row in rows:
ws.Range(
ws.Cells(row_tracker, 1),
ws.Cells(row_tracker, column_size)
).value = row
row_tracker += 1
wb.SaveAs(os.path.join(os.getcwd(), 'Json output.xlsx'), 51)
wb.Close()
ExcelApp.Quit()
ExcelApp = None

Answer 1

.content给出的数据为bytes。您应该获得.json()，以便将其作为Python字典。

response = requests.get("https://restcountries.com/v3.1/all")
json_data = response.json()
#json_data = json.loads(response.content)

---

最小工作示例

我使用[:10]只显示前10个值，但您应该跳过[:10]

import requests
response = requests.get("https://restcountries.com/v3.1/all")
json_data = response.json()
#print(json_data)
rows = []
for record in json_data[:10]:
name = record['name']['official']
print(name)
rows.append(name)

结果：

Republic of Finland
Republic of Guatemala
Republic of Chile
Oriental Republic of Uruguay
Kyrgyz Republic
Republic of Zambia
Niue
Republic of Austria
Georgia
Republic of Trinidad and Tobago