Pandas能否导出带合并单元格的Excel？嵌套字典数据导出咨询

两种方案都能满足需求，优先推荐 Pandas + XlsxWriter 组合，兼顾数据处理效率与样式自定义能力；纯XlsxWriter适合需要高度定制化的场景，具体说明如下：

一、Pandas + XlsxWriter 方案（推荐） #

Pandas本身不直接支持合并单元格，但可以指定XlsxWriter作为导出引擎，结合其API实现合并逻辑——优势是用Pandas快速完成数据结构化，减少手动遍历的冗余代码。

步骤1：扁平化嵌套字典数据 #

先把原始嵌套字典转换成适合Pandas处理的扁平化结构：

import pandas as pd

# 原始数据
raw_data = [
    {"12345": {"cup": "123456789", "spoon": "234567891"}},
    {"23456": {"plate": "345678912"}}
]

# 扁平化处理
flattened = []
for item in raw_data:
    main_id = list(item.keys())[0]
    sub_items = item[main_id]
    for sub_key, sub_val in sub_items.items():
        flattened.append({
            "主ID": main_id,
            "物品": sub_key,
            "编码": sub_val
        })

df = pd.DataFrame(flattened)

步骤2：导出Excel并设置合并单元格 #

利用XlsxWriter的API实现合并和样式设置：

# 创建Excel写入对象
writer = pd.ExcelWriter("output.xlsx", engine="xlsxwriter")
df.to_excel(writer, sheet_name="Sheet1", index=False)

# 获取工作簿和工作表对象
workbook = writer.book
worksheet = writer.sheets["Sheet1"]

# 设置样式（可选）
header_format = workbook.add_format({
    "bold": True,
    "align": "center",
    "valign": "vcenter",
    "border": 1
})
merge_format = workbook.add_format({
    "align": "center",
    "valign": "vcenter",
    "border": 1
})

# 写入表头样式
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num, value, header_format)

# 计算并设置合并单元格
current_id = None
start_row = 1  # 跳过表头行
for row_num, row in df.iterrows():
    if row["主ID"] != current_id:
        if current_id is not None:
            # 合并上一个ID的单元格
            worksheet.merge_range(start_row, 0, row_num - 1, 0, current_id, merge_format)
        current_id = row["主ID"]
        start_row = row_num + 1  # DataFrame行号从0开始，Excel行号从1开始，表头占了第0行

# 处理最后一个ID的合并
worksheet.merge_range(start_row, 0, len(df), 0, current_id, merge_format)

# 调整列宽
worksheet.set_column(0, 2, 15)

# 保存文件
writer.close()

二、纯XlsxWriter方案 #

如果不需要Pandas的数据处理能力，也可以直接用XlsxWriter手动遍历数据实现——优势是完全自定义，但代码量更大：

import xlsxwriter

# 原始数据
raw_data = [
    {"12345": {"cup": "123456789", "spoon": "234567891"}},
    {"23456": {"plate": "345678912"}}
]

# 创建工作簿和工作表
workbook = xlsxwriter.Workbook("output.xlsx")
worksheet = workbook.add_worksheet()

# 设置样式
header_format = workbook.add_format({"bold": True, "align": "center", "border": 1})
merge_format = workbook.add_format({"align": "center", "border": 1})
cell_format = workbook.add_format({"border": 1})

# 写入表头
headers = ["主ID", "物品", "编码"]
for col, header in enumerate(headers):
    worksheet.write(0, col, header, header_format)

current_row = 1
for item in raw_data:
    main_id = list(item.keys())[0]
    sub_items = item[main_id]
    sub_count = len(sub_items)
    
    # 合并主ID单元格
    if sub_count > 1:
        worksheet.merge_range(current_row, 0, current_row + sub_count - 1, 0, main_id, merge_format)
    else:
        worksheet.write(current_row, 0, main_id, merge_format)
    
    # 写入物品和编码
    for sub_key, sub_val in sub_items.items():
        worksheet.write(current_row, 1, sub_key, cell_format)
        worksheet.write(current_row, 2, sub_val, cell_format)
        current_row += 1

# 调整列宽
worksheet.set_column(0, 2, 15)

workbook.close()

总结 #

• 若数据需要先做筛选、聚合等处理，Pandas + XlsxWriter 是最优选择，用Pandas处理数据，XlsxWriter负责样式，代码更简洁高效。
• 若仅需简单导出且追求极致自定义，纯XlsxWriter也能实现，但需要手动处理更多细节。

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