Is there a way to copy a file to memory to edit it?

Question

I am sending pandas dataframes by e-mail as excel files.

Until now, I used pandas 1.5.3, which let me copy the workbook from a template excel file to my ExcelWriter's workbook. I would create a buffer, that I would use as my ExcelWriter's path parameter, so that I could insert the dataframe's data with the writer and then return what would be my e-mail attachment by returning buffer.getbuffer().

Here is a simplified sample of my old code (I have no idea whether it is good practice to use a BytesIO object for this kind of things) :

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()

Now that I use pandas 2.x, I can no longer assign to writer.book, which forces me to copy my template file first (following this answer), then edit this file and send it by e-mail.

Here is a simplified sample of my new code :

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

The Issue is that now that I have to use shutil.copy (which requires path-like or string objects as parameters), I have to create and delete a file in my directory, which bugs me a lot. Is there no way to work with a file in memory ? If so, what is the best practice ?

Answer 1

You can use a different shutil function, shutil.copyfileobj() to do this.

Example:

import shutil
from io import BytesIO


buf = BytesIO()
with open('test.txt', 'rb') as f_in:
    shutil.copyfileobj(f_in, buf)

After that, buf will contain the contents of the file test.txt.

You may also need buf.seek(0) to reset the position of the read/write pointer.