Pandas 2.x下如何在内存中处理Excel模板生成邮件附件？

问题背景 #

之前用Pandas 1.5.3时，能通过BytesIO缓冲区加载Excel模板到ExcelWriter的workbook，全程在内存中处理后返回buffer作为邮件附件。升级到Pandas 2.x后，无法再直接赋值writer.book，目前只能通过shutil.copy把模板复制到本地临时文件再编辑，想知道有没有办法继续在内存中处理，以及对应的最佳实践。

旧代码（Pandas 1.5.3可用） #

def insert_template(dataframe) :
    path_template= f"templates\foo.xlsx"
    
    buffer = BytesIO()
    
    book = load_workbook(path_fichier_modele)
    
    with ExcelWriter(buffer, engine="openpyxl") as writer:
        writer.book = book
        sheet_name = book.sheetnames[0]
        book.close()
        
        dataframe.to_excel(writer, sheet_name=sheet_name, index=False)
        
        buffer.seek(0)
    return buffer.getbuffer()

当前临时文件方案（Pandas 2.x） #

def insert_template(dataframe) :
    path_template = f"templates\foo.xlsx"
    file_path = shutil.copy(path_template, '.\\')

    with ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="overlay") as writer:
        sheet_name = writer.book.sheetnames[0]

        dataframe.to_excel(writer, sheet_name=sheet_name, index=False)

    return file_path

内存中处理的解决方案 #

Pandas 2.x只是禁止了直接赋值writer.book，但依然可以通过以下方式在内存中完成模板加载和数据写入：

from io import BytesIO
from openpyxl import load_workbook
import pandas as pd

def insert_template(dataframe):
    path_template = "templates/foo.xlsx"
    
    # 把模板文件读取到内存缓冲区
    with open(path_template, "rb") as template_file:
        template_buffer = BytesIO(template_file.read())
    
    # 加载模板工作簿，获取目标工作表名称
    book = load_workbook(template_buffer)
    sheet_name = book.sheetnames[0]
    
    # 使用追加模式打开内存缓冲区，覆盖现有工作表内容
    with pd.ExcelWriter(
        template_buffer,
        engine="openpyxl",
        mode="a",
        if_sheet_exists="overlay"
    ) as writer:
        dataframe.to_excel(writer, sheet_name=sheet_name, index=False)
    
    # 重置缓冲区指针到起始位置，确保附件能正确读取
    template_buffer.seek(0)
    return template_buffer.getbuffer()

关键说明 #

• 核心思路是先将模板文件读入BytesIO内存缓冲区，再用mode="a"（追加模式）打开这个缓冲区，配合if_sheet_exists="overlay"实现数据覆盖写入现有工作表，全程无需磁盘文件。
• if_sheet_exists="overlay"会保留原工作表的格式（比如单元格样式、公式），只覆盖数据区域，完美适配模板场景。
• 操作完成后必须调用seek(0)将缓冲区指针重置到开头，否则后续读取附件时会得到空内容。

最佳实践 #

1. 优先内存处理：避免磁盘IO操作，提升处理速度，尤其是在批量生成邮件附件的场景下优势明显。
2. 模板格式保留：使用if_sheet_exists="overlay"而非replace，确保模板的样式、公式等不会被覆盖。
3. 缓冲区管理：操作完成后务必重置缓冲区指针，并且在使用完缓冲区后可以调用close()释放资源（如果是长期运行的服务）。
4. 大文件兼容：如果处理超大DataFrame或复杂模板，内存占用过高时，可以考虑使用tempfile模块创建临时文件，处理完成后自动删除，兼顾性能和内存占用。
5. 路径规范：使用正斜杠/作为路径分隔符，避免Windows下的转义字符问题。

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