Pandas & OpenPyXL : Calling ExcelWriter.book.sheetnames when writing to an buffered file returns an empty list

Question

The way my script works is I copy a template file (containing nothing but formatting) into a buffer, using shutil.copyfileobj, then I insert a dataframe using pandas.DataFrame.to_excel into it, through a pandas.ExcelWriter (with engine='openpyxl').

This creates an Excel file in memory which will then be used as an attachment sent by e-mail.

In order to insert my dataframe, I have to pass sheet_name as a parameter to dataframe.to_excel(). Since I don't know in advance the name of the sheet, I first proceed to get it using writer.book.sheetnames.

If I call writer.book.sheetnames, I get an empty list ?

It seems the only way to get a list of the sheet names is to use openpyxl.load_workbook to load the workbook, extract the sheet names list, and then close the book before creating my ExcelWriter object.

Why can't I get a list of the sheet names directly from my book ? This would allow me not to import and use load_workbook, which I only use for this.

Also, I can confirm writing to the file in the buffer correctly uses the sheet even if I can't find its name since the output file actually has the correct formatting. So it's not that the file in the buffer has no sheets, it's just that I can't get their names.

Here is a code snippet which is not extracted from my script but re-written to better show the issue and simplify the code :

from pandas import DataFrame, ExcelWriter
from openpyxl import load_workbook
from io import BytesIO
from shutil import copyfileobj

path_template_file = r"templates\foo.xlsx"

buffer = BytesIO()
copyfileobj(open(path_template_file, 'rb'), buffer)
    
def Insert_Using_Only_Writer(dataframe):
        #This way we could get the sheet name using the same writer we use to insert
        #the dataframe into the excel file with 'to_excel'
        
    writer = ExcelWriter(buffer, engine="openpyxl")
    sheet_list = writer.book.sheetnames #<-- This returns an empty list '[]' ?
    sheet_name = sheet_list[0] #<-- This creates an IndexError
    
    dataframe.to_excel(writer, sheet_name=sheet_name)
    writer.close()
    return(sheet_name)
            
def Insert_Using_Writer_And_OpenPyXL(dataframe):
    book = load_workbook(buffer)
    sheet_list = book.sheetnames #<-- This returns ['Sheet1',]
    sheet_name = sheet_list[0] #<-- This returns 'Sheet1'
    book.close()
    
    writer = ExcelWriter(buffer, engine="openpyxl")
    dataframe.to_excel(writer, sheet_name=sheet_name)
    writer.close()
    return(sheet_name)


dataframe = DataFrame({'ID': [1, 2, 3, 4, 5, 6, 7],
                      'text_data': ['text1', 'text2', 'text3', 'text4', 'text5', 'text6', 'text7'],
                      'number_data': [11, 12, 13, 14, 15, 16, 17,]})

try:
    print(f"The name of the first sheet of the excel file is '{Insert_Using_Only_Writer(dataframe)}'")
except IndexError as e:
    print(f"Calling writer.book.sheetnames returned an empty list, causing an {type(e).__name__} : '{e}'.\n"\
           "We'll use openpyxl.load_workbook instead.\n"\
          f"The name of the first sheet of the excel file is '{Insert_Using_Writer_And_OpenPyXL(dataframe)}'")