如何使用Pandas将嵌套Python字典格式化写入Excel

示例嵌套字典 #

汽车数据 #

Car = [
    {
        "carbmodel": "Model A",
        "attributes": {
            "make": "Brand X",
            "year": 2023,
            "color": "Red",
            "engine": "V6",
            "transmission": "Automatic",
            "features": ["GPS", "Bluetooth", "Leather Seats"]
        }
    },
    {
        "carbmodel": "Model B",
        "attributes": {
            "make": "Brand Y",
            "year": 2022,
            "color": "Blue",
            "engine": "Inline-4",
            "transmission": "Manual",
            "features": ["Sunroof", "Backup Camera", "Alloy Wheels"]
        }
    },
    {
        "carbmodel": "Model C",
        "attributes": {
            "make": "Brand Z",
            "year": 2023,
            "color": "Black",
            "engine": "V8",
            "transmission": "Automatic",
            "features": ["Apple CarPlay", "Heated Seats", "Navigation"]
        }
    }
]

摩托车数据 #

bikes = [
    {
        "carbmodel": "Bike X",
        "attributes": {
            "make": "Brand P",
            "year": 2023,
            "color": "Green",
            "engine": "250cc",
            "transmission": "Manual",
            "features": ["ABS", "LED Lights", "Digital Display"]
        }
    },
    {
        "carbmodel": "Bike Y",
        "attributes": {
            "make": "Brand Q",
            "year": 2022,
            "color": "Orange",
            "engine": "200cc",
            "transmission": "Manual",
            "features": ["Disc Brakes", "Tubeless Tires", "Kick Start"]
        }
    },
    {
        "carbmodel": "Bike Z",
        "attributes": {
            "make": "Brand R",
            "year": 2023,
            "color": "Black",
            "engine": "300cc",
            "transmission": "Manual",
            "features": ["Fuel Injection", "Mono Suspension", "Dual Exhaust"]
        }
    }
]

需求说明 #

需要将上述嵌套字典通过Pandas DataFrame写入单个Excel工作表，实现指定的单元格合并格式（如顶部标题跨列合并、分类标题跨列合并等）。

现有测试代码及问题 #

用户尝试两种方式但均存在单元格合并问题：

方式一：直接用xlsxwriter写入嵌套字典 #

import xlsxwriter

# 创建Excel文件
workbook = xlsxwriter.Workbook('merged_cells_and_data.xlsx')
worksheet = workbook.add_worksheet()

# 合并单元格格式
cell_format = workbook.add_format({
    'bold': True,
    'align': 'center',
    'valign': 'vcenter',
    'bg_color': 'yellow'
})

worksheet.merge_range('A1:E1', 'Merged Cells Example', cell_format)

# 测试用嵌套字典
useful_info = {
    'personal_info': {
        'name': 'John Doe',
        'age': 30,
        'location': 'New York, USA'
    },
    'contact_info': {
        'email': 'johndoe@example.com',
        'phone': '123-456-7890'
    },
    'employment': {
        'company': 'ABC Corporation',
        'position': 'Software Engineer',
        'years_experience': 5
    },
    'skills': ['Python', 'JavaScript', 'SQL', 'Problem Solving'],
    'education': {
        'university': 'University of XYZ',
        'degree': 'Bachelor of Science in Computer Science',
        'graduation_year': 2018
    }
}

# 写入嵌套字典
row = 1
for key, value in useful_info.items():
    worksheet.write(row, 0, key)
    if isinstance(value, dict):
        for inner_key, inner_value in value.items():
            worksheet.write(row, 1, inner_key)
            worksheet.write(row, 2, inner_value)
            row += 1
    else:
        worksheet.write(row, 1, value)
    row += 1

workbook.close()
print("Excel文件已生成。")

方式二：Pandas DataFrame结合xlsxwriter #

# 多DataFrame合并单元格问题
import xlsxwriter
import pandas as pd

# 嵌套字典转DataFrame
df1 = pd.DataFrame(Car).T 
df2 = pd.DataFrame(bikes).T

print(df1.shape[0])
print(df1.shape[1])

def multiple_dfs(df_list, sheets, file_name, spaces): 
    writer = pd.ExcelWriter(file_name, engine='xlsxwriter') 
    # 重复创建workbook导致内容覆盖
    workbook = xlsxwriter.Workbook("merge1.xlsx") 
    worksheet = workbook.add_worksheet("Validation")

    # 设置列宽和行高
    worksheet.set_column("A:E", 12)
    worksheet.set_row(3, 30)
    worksheet.set_row(6, 30)
    worksheet.set_row(7, 30)
    
    # 合并格式
    merge_format = workbook.add_format({
        "bold": 1,
        "border": 1,
        "align":"center",
        "valign": "vcenter",
        "fg_color": "yellow"
    })

    worksheet.merge_range("A1:E1", "Merged Range", merge_format) 
    workbook.close()

    # 重新创建写入器，之前的合并设置丢失
    with pd.ExcelWriter(file_name) as writer:
        row = 3
        for dataframe in df_list:
            dataframe.to_excel(writer,sheet_name=sheets, startrow=row, startcol=0)
            row = row + len(dataframe.index) + spaces + 1

dfs = [df1, df2]
multiple_dfs(dfs, 'Validation', 'merge1.xlsx', 2)

核心问题：

• 方式一无法处理分类标题的跨行合并需求
• 方式二重复创建workbook对象，导致合并单元格设置被覆盖，且DataFrame写入位置与合并区域不匹配

解决方案 #

以下是整合Pandas与xlsxwriter，实现正确单元格合并的代码：

import pandas as pd
import xlsxwriter

# 扁平化嵌套字典，转为标准DataFrame格式
def process_nested_dict(data_list):
    rows = []
    for item in data_list:
        model = item["carbmodel"]
        attrs = item["attributes"]
        # 将features列表转为逗号分隔字符串
        attrs["features"] = ", ".join(attrs["features"])
        # 拼接模型名称与属性为一行数据
        row = {"车型": model}
        row.update(attrs)
        rows.append(row)
    return pd.DataFrame(rows)

# 处理汽车和摩托车数据
df_cars = process_nested_dict(Car)
df_bikes = process_nested_dict(bikes)

# 创建统一的Excel写入器
file_name = "车辆信息汇总.xlsx"
writer = pd.ExcelWriter(file_name, engine="xlsxwriter")
workbook = writer.book
worksheet = workbook.add_worksheet("所有车辆")

# 设置列宽
worksheet.set_column("A:G", 18)

# 定义格式
标题格式 = workbook.add_format({
    "bold": True,
    "align": "center",
    "valign": "vcenter",
    "bg_color": "#FFFF00",
    "border": 1
})
分类标题格式 = workbook.add_format({
    "bold": True,
    "align": "center",
    "valign": "vcenter",
    "bg_color": "#D9D9D9",
    "border": 1
})
单元格格式 = workbook.add_format({"border": 1})

# 写入顶部跨列标题
worksheet.merge_range("A1:G1", "车辆信息汇总", 标题格式)

# 写入汽车数据区域
worksheet.merge_range("A3:G3", "汽车数据", 分类标题格式)
df_cars.to_excel(writer, sheet_name="所有车辆", startrow=3, startcol=0, index=False)

# 计算摩托车数据起始行
汽车数据总行数 = len(df_cars) + 4  # 分类标题行1 + 表头行1 + 数据行n + 空行1
worksheet.merge_range(f"A{汽车数据总行数}:G{汽车数据总行数}", "摩托车数据", 分类标题格式)
df_bikes.to_excel(writer, sheet_name="所有车辆", startrow=汽车数据总行数, startcol=0, index=False)

# 给所有数据单元格添加边框
总数据行数 = 汽车数据总行数 + len(df_bikes) + 1
for row in range(3, 总数据行数):
    for col in range(7):
        worksheet.write(row, col, None, 单元格格式)

# 保存文件
writer.close()
print(f"Excel文件 {file_name} 已成功生成，包含正确的单元格合并格式。")

代码说明 #

1. 数据扁平化：将嵌套字典中的列表转为字符串，把模型名称与属性整合为单行，适配Pandas的表格结构
2. 统一写入器：仅创建一个pd.ExcelWriter对象，避免合并设置与数据内容冲突
3. 精准合并计算：根据DataFrame行数动态计算合并单元格范围，确保分类标题跨列对齐
4. 格式统一：定义多级格式，提升Excel文件的可读性

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