Getting the excel file after df.to_excel(...) with Panda

Question

I am using Pyrebase to upload my files to Firebase.

I have a DataFrame df and convert it to an Excel File as follows:

writer      = ExcelWriter('results.xlsx')
excelFile   = df.to_excel(writer,'Sheet1')

print(excelFile)

# Save to firebase
childRef        = "path/to/results.xlsx"

storage         = firebase.storage()
storage.child(childRef).put(excelFile)

However, this stores the Excel file as an Office Spreadsheet with zero bytes. If I run writer.save() then I do get the appropriate filetype (xlsx), but it is stored on my Server (which I want to avoid). How can I generate the right filetype as one would do with writer.save()?

Note: print(excelFile) returns None

Answer 1

It can be solved by using local memory:

# init writer
bio         = BytesIO()
writer      = pd.ExcelWriter(bio, engine='xlsxwriter')
filename    = "output.xlsx"

# sheets
dfValue.to_excel(writer, "sheetname")

# save the workbook
writer.save()
bio.seek(0)      

# get the excel file (answers my question)          
workbook    = bio.read()  
excelFile   = workbook

# save the excelfile to firebase
# see also issue: https://github.com/thisbejim/Pyrebase/issues/142
timestamp       = str(int(time.time()*1000));
childRef        = "/path/to/" + filename

storage         = firebase.storage()
storage.child(childRef).put(excelFile)
fileUrl         = storage.child(childRef).get_url(None)