使用Openpyxl实现DataFrame循环追加至Excel工作表的问题

我懂你现在的困扰——单独测循环里的追加代码能生成3行数据，但整合到实际业务里就掉链子了对吧？这种分批写入大数据到Excel的场景，最容易踩的坑就是文件句柄没管好、写入模式不对或者表头重复写入，我给你整理几个靠谱的解决方案，都是实战里验证过的：

这个方案最省心，直接用pandas自带的ExcelWriter追加模式，配合if_sheet_exists参数就能搞定，核心是第一次写入表头，之后循环只写数据：

import pandas as pd

# 第一步：先创建Excel文件并写入表头
# 替换成你实际的列名列表
column_names = ['用户ID', '订单金额', '下单时间']
# 用空DataFrame生成表头
empty_df = pd.DataFrame(columns=column_names)
empty_df.to_excel('大数据文件.xlsx', sheet_name='订单数据', index=False)

# 第二步：模拟循环迭代追加数据（替换成你的实际循环逻辑）
for iteration in range(5):
    # 生成当前批次的新数据（这里是模拟，你换成实际获取数据的代码）
    batch_data = pd.DataFrame({
        '用户ID': [f'user_{iteration+1}'],
        '订单金额': [100 + iteration*20],
        '下单时间': [f'2024-05-{10+iteration}']
    })
    
    # 打开现有Excel文件，追加数据
    with pd.ExcelWriter(
        '大数据文件.xlsx',
        engine='openpyxl',  # 必须用openpyxl引擎支持追加
        mode='a',  # 追加模式，不会覆盖整个文件
        if_sheet_exists='overlay'  # 工作表已存在时，直接在上面追加
    ) as writer:
        # 获取当前工作表的最后一行，从下一行开始写
        last_row = writer.sheets['订单数据'].max_row
        # 写入数据时关闭表头，避免重复写入
        batch_data.to_excel(
            writer,
            sheet_name='订单数据',
            index=False,
            header=False,
            startrow=last_row
        )

这个方案的关键注意点： #

• 必须指定engine='openpyxl'，因为默认的xlsxwriter不支持追加模式
• mode='a'是核心，不然每次都会覆盖整个文件
• header=False一定要加，不然循环里每次都会重复写表头，导致数据错位
• startrow用max_row获取最后一行，确保每次都追加到末尾

如果你的pandas版本比较老，没有if_sheet_exists参数，那就手动用openpyxl加载工作簿来管理：

import pandas as pd
from openpyxl import load_workbook

# 第一步：初始化文件和表头
column_names = ['用户ID', '订单金额', '下单时间']
pd.DataFrame(columns=column_names).to_excel('大数据文件.xlsx', sheet_name='订单数据', index=False)

# 第二步：循环追加数据
for iteration in range(5):
    batch_data = pd.DataFrame({
        '用户ID': [f'user_{iteration+1}'],
        '订单金额': [100 + iteration*20],
        '下单时间': [f'2024-05-{10+iteration}']
    })
    
    # 手动加载现有工作簿
    book = load_workbook('大数据文件.xlsx')
    writer = pd.ExcelWriter('大数据文件.xlsx', engine='openpyxl')
    # 把加载的工作簿绑定到writer上
    writer.book = book
    # 映射所有工作表到writer的sheets属性
    writer.sheets = {ws.title: ws for ws in book.worksheets}
    
    # 获取最后一行位置
    last_row = writer.sheets['订单数据'].max_row
    # 写入数据
    batch_data.to_excel(
        writer,
        sheet_name='订单数据',
        index=False,
        header=False,
        startrow=last_row
    )
    
    # 一定要关闭writer，不然文件会被占用
    writer.close()

1. 文件没正确关闭：实际循环中如果没使用with语句或者手动close()，文件句柄会被占用，后续写入失败
2. 表头重复写入：循环里忘了加header=False，导致每次都写表头，数据被覆盖或者错位
3. 写入模式错了：用了默认的mode='w'，每次循环都覆盖整个文件，最后只剩最后一批数据
4. 工作表名称不一致：循环里的工作表名和初始化时的拼写不一样，导致每次新建工作表，而不是追加到同一个表里
5. 文件被其他程序占用：Excel文件在本地客户端打开着，导致代码没有写入权限

• 循环里打印last_row的值，确认每次都是从正确的行开始写入
• 检查循环中是否每次都用了header=False
• 确保Excel文件没有在其他程序中打开
• 测试时把循环次数改成2-3次，看生成的Excel里数据是否正确追加

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