如何使用openpyxl在XLSX中创建多行单元格？

我有如下两个JSON文件：

test.json #

{
    "entries": [
        {
            "attributes": {
                "sAMAccountName": "wonder-woman",
                "memberOf": [
                    "woman",
                    "dc-comics",
                    "justice-league",
                    "human"
                ]
            },
            "dn": "CN=Diana Troy,OU=Users,OU=DC-COMICS,DC=universum,DC=local"
        }
    ]
}

excl.json #

{
    "sAMAccountName": [
        "ironman"
    ],
    "dnsHostName": [
        "dell"
    ]
}

我尝试用以下Python代码将数据导出到Excel，但目前memberOf的列表元素会被循环覆盖，最终单元格只显示最后一个元素。我希望单元格内的每个列表元素之间插入换行符，实现类似Excel中ALT+ENTER的换行效果：

import json
from datetime import datetime
from openpyxl import Workbook

encoded_retrieved_users = './test.json'
accounts_excluded = './excl.json'

with open(accounts_excluded, 'r', encoding="UTF-8") as file:
    excluded_accounts = json.load(file)
    excluded_users = excluded_accounts['sAMAccountName']

with open(encoded_retrieved_users, 'r', encoding="UTF-8") as file:
    data = json.load(file)
    retrieved_users = data['entries']
retrieved_users.sort(key=lambda d: d["attributes"]["sAMAccountName"])

def user_validation(suspect):
    account = True
    for account_checked in excluded_users:
        if (account_checked == suspect):
            account = False
    return account

A = 'sAMAccountName'
B = 'memberOf'

# Set worksheet
wb = Workbook() # create excel worksheet
ws_01 = wb.active # Grab the active worksheet
ws_01.title = "all inf" # Set the title of the worksheet
# Set first row
row =1
ws_01.cell(row, 1, A) # cell(row, col, value)
ws_01.cell(row, 2, B)
# Set rest rows
row = 2
for user in retrieved_users:
    attributes = user['attributes']
    sAMAccountName = attributes['sAMAccountName']
    if(user_validation(sAMAccountName) == True):
        A = str(sAMAccountName)
        ws_01.cell(row, 1, A)
        for group_name in attributes['memberOf']:
            B = group_name 
            ws_01.cell(row, 2, B)

# Save it in an Excel file
wb.save('./excel.xlsx')

要实现单元格内换行，需要做两个关键修改：

1. 将memberOf列表的所有元素用换行符\n拼接成一个字符串，一次性写入单元格，避免循环覆盖。
2. 开启单元格的自动换行属性wrap_text=True，让Excel识别换行符并显示换行效果。

修改后的代码如下：

import json
from openpyxl import Workbook

encoded_retrieved_users = './test.json'
accounts_excluded = './excl.json'

with open(accounts_excluded, 'r', encoding="UTF-8") as file:
    excluded_accounts = json.load(file)
    excluded_users = excluded_accounts['sAMAccountName']

with open(encoded_retrieved_users, 'r', encoding="UTF-8") as file:
    data = json.load(file)
    retrieved_users = data['entries']
retrieved_users.sort(key=lambda d: d["attributes"]["sAMAccountName"])

def user_validation(suspect):
    return suspect not in excluded_users  # 简化验证逻辑，效果一致

# Set worksheet
wb = Workbook()
ws_01 = wb.active
ws_01.title = "all inf"
# Set first row
row = 1
ws_01.cell(row, 1, 'sAMAccountName')
ws_01.cell(row, 2, 'memberOf')
# Set rest rows
row = 2
for user in retrieved_users:
    attributes = user['attributes']
    sAMAccountName = attributes['sAMAccountName']
    if user_validation(sAMAccountName):
        # 写入用户名
        ws_01.cell(row, 1, sAMAccountName)
        # 拼接memberOf列表为带换行的字符串
        joined_groups = '\n'.join(attributes['memberOf'])
        # 获取单元格对象，设置值和自动换行
        group_cell = ws_01.cell(row, 2, joined_groups)
        group_cell.alignment = group_cell.alignment.copy(wrap_text=True)
        row += 1  # 处理完一个用户后，行号加1

# Save it in an Excel file
wb.save('./excel.xlsx')

关键修改说明 #

• 简化了user_validation函数，直接用suspect not in excluded_users判断，逻辑更简洁。
• 用'\n'.join(attributes['memberOf'])将所有组名拼接成带换行符的字符串，一次性写入单元格，避免循环覆盖。
• 通过group_cell.alignment.copy(wrap_text=True)开启单元格自动换行，这样Excel会解析\n为单元格内的换行（等同于ALT+ENTER效果）。
• 添加了row += 1，确保每个用户的数据写入单独的行（原代码没有行号递增，多个用户会覆盖同一行）。

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