如何将JSON列表导出到Excel文件

Question

我有如下json列表，我想使用Python将其导出到excel文件中。请告知

代码：

dataInJSON = [{'ConfigRuleName': 'required-tags', 'ResourceType': 'AWS::CloudFormation::Stack', 'ResourceId': 'arn:aws:cloudformation:us-east-1:123456789:stack/security-headers/7304ffb0-8629-11ea-899e-0a5afd1032bb', 'OrderingTimestamp': '2020-10-26 09:20:07.860000-04:00'}, {'ConfigRuleName': 'required-tags', 'ResourceType': 'AWS::CloudFormation::Stack', 'ResourceId': 'arn:aws:cloudformation:us-east-1:123456789:stack/security-wells/f1924880-8311-11ea-9a26-0af77bd56d08', 'OrderingTimestamp': '2020-10-26 10:57:19.215000-04:00'}]
dumpJsonString = json.dumps(dataInJSON)
jd = json.loads(dumpJsonString)
df = pd.DataFrame.from_dict(jd, orient='index')
excelWriter = pd.ExcelWriter(r + outputExcelNameBase, engine='xlsxwriter')
df.to_excel(excelWriter, sheet_name='Sheet1')
excelWriter.save() 

数据：

[{'ConfigRuleName': 'required-tags', 'ResourceType': 'AWS::CloudFormation::Stack', 'ResourceId': 'arn:aws:cloudformation:us-east-1:123456789:stack/security-headers/7304ffb0-8629-11ea-899e-0a5afd1032bb', 'OrderingTimestamp': '2020-10-26 09:20:07.860000-04:00'}, {'ConfigRuleName': 'required-tags', 'ResourceType': 'AWS::CloudFormation::Stack', 'ResourceId': 'arn:aws:cloudformation:us-east-1:123456789:stack/security-wells/f1924880-8311-11ea-9a26-0af77bd56d08', 'OrderingTimestamp': '2020-10-26 10:57:19.215000-04:00'}]

我的期望：

错误：

Traceback (most recent call last):
  File "/Users/aniu/projects/aws/boto3/resourceManagement/getNonCompliantByConfigRule.py", line 175, in <module>
    df = pd.DataFrame.from_dict(jd, orient='index')
  File "/usr/local/lib/python3.9/site-packages/pandas/core/frame.py", line 1299, in from_dict
    if isinstance(list(data.values())[0], (Series, dict)):
AttributeError: 'list' object has no attribute 'values'

短暂性脑缺血发作

Answer 1

看起来你的代码很好用

只需pip安装xlwt

dataInJSON = [{'ConfigRuleName': 'required-tags', 'ResourceType': 'AWS::CloudFormation::Stack', 'ResourceId': 'arn:aws:cloudformation:us-east-1:123456789:stack/security-headers/7304ffb0-8629-11ea-899e-0a5afd1032bb', 'OrderingTimestamp': '2020-10-26 09:20:07.860000-04:00'}, {'ConfigRuleName': 'required-tags', 'ResourceType': 'AWS::CloudFormation::Stack', 'ResourceId': 'arn:aws:cloudformation:us-east-1:123456789:stack/security-wells/f1924880-8311-11ea-9a26-0af77bd56d08', 'OrderingTimestamp': '2020-10-26 10:57:19.215000-04:00'}]
dumpJsonString = json.dumps(dataInJSON)
jd = json.loads(dumpJsonString)
df = pd.DataFrame.from_dict(jd)
df.to_excel("./excel.xls", index=False)