python - 从 Python 将分层 JSON 数据写入 Excel xls？

Question

我想将一些数据从 python 写入 xlsx。我目前将它存储为 JSON，但它从 Python 中输出什么并不重要。单篇文章的 JSON 格式如下:

{ 
   'Word Count': 50
   'Key Words': { 
                  ['Blah blah blah', 'Foo', ... ] }
   'Frequency': {
                  [9, 12, ... ] }
   'Proper Nouns': { 
                  ['UN', 'USA', ... ] }
   'Location': 'Mordor'
}

我查看了 XlsxWriter 模块，但无法弄清楚如何翻译不一定相同大小的分层数据(注意两个数据“对象”之间专有名词的数量)。

我希望数据看起来像什么:

有什么建议吗？

Answer 1

由于您的结构可以任意嵌套，我建议使用递归来实现这一点:

from collections import OrderedDict
import xlsxwriter
import json

def json_to_excel(ws, data, row=0, col=0):
    if isinstance(data, list):
        row -= 1
        for value in data:
            row = json_to_excel(ws, value, row+1, col)
    elif isinstance(data, dict):
        max_row = row
        start_row = row
        for key, value in data.iteritems():
            row = start_row
            ws.write(row, col, key)
            row = json_to_excel(ws, value, row+1, col)
            max_row = max(max_row, row)
            col += 1
        row = max_row
    else:
        ws.write(row, col, data)

    return row

text = """
[
    {
        "Source ID": 123,
        "WordCount": 50,
        "Key Words": ["Blah blah blah", "Foo"],
        "Frequency": [9, 12, 1, 2, 3],
        "Proper Nouns": ["UN", "USA"],
        "Location": "Mordor"
    },
    {
        "Source ID": 124,
        "WordCount": 50,
        "Key Words": ["Blah blah blah", "Foo"],
        "Frequency": [9, 12, 1, 2, 3],
        "Proper Nouns": ["UN", "USA"],
        "Location": "Mordor"
    }
]
"""

data = json.loads(text, object_pairs_hook=OrderedDict)
wb = xlsxwriter.Workbook("output.xlsx")
ws = wb.add_worksheet()
json_to_excel(ws, data)
wb.close()  

这将为您提供如下所示的输出文件: