如何将嵌套Python字典导出为自定义排版的Excel工作簿

现有代码的核心问题 #

• 直接用pd.DataFrame(graph)生成的是交叉表结构：外层字典的键（Age、Sex等）会被设为列，内层键会被设为行，完全和需要的块式横向排版逻辑不匹配
• 原始字典里Income项的</>是网页转义格式，直接导出会在Excel里显示成</>的乱码，需要先还原成正常字符
• 没有实现“横向单排最多放4组键值对”的拼接规则，所有列会无限制堆在同一排

可落地的实现逻辑 #

1. 把每个外层大类别（Age、Sex等）拆成独立的2列小表格：第一列存该类别下的子项名称，第二列存对应数值，表头明确标注所属的大类别
2. 按每排最多4个块的规则拼接小表格，块和块之间预留1列空白做视觉分隔，避免内容挤在一起
3. 写入Excel时保留你之前设置的第4行（startrow=3）的顶部留白，同时自动适配每列宽度，避免内容被截断

完整可运行代码 #

import pandas as pd

# 原始数据，先还原HTML转义字符（原数据中为<、>的转义格式）
graph = {
    'Age': {'0-30':100, '31-50':106, '51-60':117, '60+':90},
    'Sex': {'Male':80, 'Female':125, 'Unknown':100},
    'Income': {'<25k':160, '25-75':110, '75-175':100, '>175':80}, # 替换原转义内容
    'Rent': {'1':160, '0':40},
    'Pets': {'Dog':120, 'Cat':110, 'Duck':80, 'Other':50}
}

# 单排最多排布的块数，可根据需要调整
MAX_BLOCK_PER_ROW = 4

# 1. 把每个大类别转成独立的2列小DataFrame
block_list = []
for cat_name, sub_items in graph.items():
    small_df = pd.DataFrame(
        sub_items.items(),
        columns=[f'{cat_name} 分类', f'{cat_name} 数值']
    )
    block_list.append(small_df)

# 2. 按规则拼接块
combined_rows = []
# 把块按最大单排数量分组
for i in range(0, len(block_list), MAX_BLOCK_PER_ROW):
    row_blocks = block_list[i:i+MAX_BLOCK_PER_ROW]
    # 对齐同排所有小表的最大行高度，不足的补空值
    max_row = max(b.shape[0] for b in row_blocks)
    row_concat = []
    for b in row_blocks:
        padded = b.reindex(range(max_row))
        row_concat.append(padded)
        # 块之间加空列做分隔
        row_concat.append(pd.DataFrame({'':['']*max_row}))
    # 横向拼接同排的所有块
    row_combined = pd.concat(row_concat, axis=1)
    combined_rows.append(row_combined)
    # 不同排的块组之间空1行做分隔
    combined_rows.append(pd.DataFrame(['']*row_combined.shape[1]).T)

# 合并所有块组为最终导出的表格
final_df = pd.concat(combined_rows, axis=0, ignore_index=True)

# 3. 写入Excel
with pd.ExcelWriter('index_vs_total.xlsx', engine='xlsxwriter') as writer:
    final_df.to_excel(writer, sheet_name='index_vs_total', startrow=3, index=False)
    # 自动适配列宽，避免内容截断
    workbook = writer.book
    worksheet = writer.sheets['index_vs_total']
    for idx, col in enumerate(final_df.columns):
        max_content_len = max(
            final_df[col].astype(str).map(len).max(),
            len(str(col))
        ) + 2
        worksheet.set_column(idx, idx, max_content_len)

导出效果匹配度 #

• 横向单排最多排布4个独立键值块，完全匹配你提出的排布数量要求
• 自动还原Income项的转义字符，不会出现异常的HTML标签乱码
• 块、块组之间保留适当空白分隔，排版和参考结构一致
• 保留顶部3行的空白区域，匹配你之前的导出设置习惯
• 自动适配列宽，不需要手动调整Excel列宽

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