如何使用openpyxl将JSON文件数据写入Excel（xlsx）文件？

我有一个包含用户数据的JSON文件，想把它保存成xlsx格式。已经用openpyxl库开发，但卡在编写for循环——需要把每个用户数据依次存入Excel的单独行，对应单元格。

JSON文件内容 #

{
"entries": [
    {
        "attributes": {
            "cn": "Bruce Wayne",
            "displayName": "Batman",
            "distinguishedName": "CN=Bruce Wayne,OU=Users,OU=DC-COMICS,DC=universum,DC=local",
            "primaryGroupID": 513,
            "sAMAccountName": "batman",
            "sAMAccountType": 805306368,
            "userAccountControl": 514,
            "whenCreated": "2016-04-19 10:06:25+00:00"
        },
        "dn": "CN=Bruce Wayne,OU=Users,OU=DC-COMICS,DC=universum,DC=local"
    },
    {
        "attributes": {
            "cn": "Clark Kent",
            "displayName": "Superman",
            "distinguishedName": "CN=Clark Kent,OU=Users,OU=DC-COMICS,DC=universum,DC=local",
            "primaryGroupID": 513,
            "sAMAccountName": "superman",
            "sAMAccountType": 805306368,
            "userAccountControl": 514,
            "whenCreated": "2016-04-19 10:06:25+00:00"
        },
        "dn": "CN=Clark Kent,OU=Users,OU=DC-COMICS,DC=universum,DC=local"
    }
  ]
}

现有代码 #

import json
import configparser

### load main_config.ini file
main_config_file = './config/main_config.ini' # sciezka do pliku main_config.ini z ustawieniami
main_config = configparser.RawConfigParser()
main_config.read(main_config_file)

### VARIABLES
# from configs files
root_path = main_config['GLOBAL']['root_path']
json_dir = main_config['GLOBAL']['json_dir']
encoded_retrived_users_file = main_config['USERS']['encoded_users_file_name']

# load data from JSON file
encoded_retrived_users = './JSON/test.json'
#encoded_retrived_users = root_path + json_dir + encoded_retrived_users_file # sciezka do pliku encoded_users_from_LDAP.json
with open(encoded_retrived_users, 'r', encoding="UTF-8") as file:
    data = json.load(file)
    retrived_users = data['entries']

### CONSOLE VIEW
print("----------------------------------")

for user in retrived_users:
    no = str(i)
    attributes = user['attributes']
    cn = attributes['cn']
    sAMAccountName = attributes['sAMAccountName']
    i += i

    print("No:  " + no)
    print("cn:              " + cn)
    print("sAMAccountName:  " + sAMAccountName)
    print("----------------------------------")

### EXCEL
import openpyxl
from openpyxl import Workbook

wb = Workbook()

# Grab the active worksheet
ws_01 = wb.active

# Set the title of the worksheet
ws_01.title = "llogon>30d"

# Set first row
ws_01.cell(1, 1, "cn")
ws_01.cell(1, 2, "sAMA")

# Save it in an Excel file
wb.save("./test/test.xlsx")

期望效果 #

Excel表格第一行为完整属性表头：cn、displayName、distinguishedName、primaryGroupID、sAMAccountName、sAMAccountType、userAccountControl、whenCreated，后续每一行对应一个用户的各项属性数据。

解决方案 #

问题点修正 #

1. 原代码中计数器i未初始化，导致控制台打印报错
2. 缺少将所有用户属性逐行写入Excel的循环逻辑

修改后的完整代码 #

import json
import configparser
import openpyxl
from openpyxl import Workbook

### load main_config.ini file
main_config_file = './config/main_config.ini'
main_config = configparser.RawConfigParser()
main_config.read(main_config_file)

### VARIABLES
# from configs files
root_path = main_config['GLOBAL']['root_path']
json_dir = main_config['GLOBAL']['json_dir']
encoded_retrived_users_file = main_config['USERS']['encoded_users_file_name']

# load data from JSON file
encoded_retrived_users = './JSON/test.json'
#encoded_retrived_users = root_path + json_dir + encoded_retrived_users_file
with open(encoded_retrived_users, 'r', encoding="UTF-8") as file:
    data = json.load(file)
    retrived_users = data['entries']

### CONSOLE VIEW
print("----------------------------------")
i = 1
for user in retrived_users:
    no = str(i)
    attributes = user['attributes']
    cn = attributes['cn']
    sAMAccountName = attributes['sAMAccountName']
    
    print("No:  " + no)
    print("cn:              " + cn)
    print("sAMAccountName:  " + sAMAccountName)
    print("----------------------------------")
    i += 1

### EXCEL
wb = Workbook()
ws_01 = wb.active
ws_01.title = "llogon>30d"

# 设置表头（自动提取第一个用户的所有属性键）
if retrived_users:
    headers = list(retrived_users[0]['attributes'].keys())
    for col_idx, header in enumerate(headers, start=1):
        ws_01.cell(row=1, column=col_idx, value=header)
    
    # 逐行写入用户数据
    for row_idx, user in enumerate(retrived_users, start=2):
        attributes = user['attributes']
        for col_idx, key in enumerate(headers, start=1):
            ws_01.cell(row=row_idx, column=col_idx, value=attributes[key])

# 保存Excel文件
wb.save("./test/test.xlsx")

代码说明 #

• 初始化计数器i为1，修复控制台打印报错问题
• 自动提取第一个用户的属性键作为表头，确保表头与数据列完全对应
• 双重循环实现批量写入：外层循环遍历用户（从第2行开始写入数据），内层循环遍历每个属性，将对应值写入单元格
• 无需手动指定列位置，自动适配所有属性字段

内容的提问来源于stack exchange，提问作者Kubix