How to convert DataFrame to xlsx file without saving it?

Question

I am loading in an Excel file as a DataFrame, do some transformations, then I want to save the Excel file to some server (not locally).

Currently, I am able to do a workaround to achieve this:

import pandas as pd
import requests

df = pd.read_excel("file.xlsx")
df = do_some_transformation(df)

# Store DataFrame locally
df.to_excel("outputfile.xlsx")

# re-read locally stored file und upload it
with open("outputfile.xlsx", "rb") as fin:
        requests.put("url/outputfile.xlsx",
                     data=fin.read(),
                     auth=auth,
                     headers={'content-type': 'application/vnd.ms-excel'})

i.e. I am saving the transformed DataFrame locally, to then upload the local copy to the server. Is it possible to convert the df directly into an excel file, without having to store and re-loading it locally? How would I have to modify the requests.put statement?

with the hint of @Aryerez, I tried

df = pd.read_excel("file.xlsx")
df = do_some_transformation(df)

writer = pd.ExcelWriter("file.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')

requests.put("url/outputfile.xlsx",
                     data=writer,
                     auth=auth,
                     headers={'content-type': 'application/vnd.ms-excel'}),
                     

which results in a TypeError: '_XlsxWriter' object is not iterable.

How can I convert the pandas DataFrame to an Excel-File and pass this to request.put?

Answer 1

• you just need to return the bytes value not the writer itself.

• and you don't need the actual file to write to it you could just use io.BytesIO() instead of saving it to the disk.

    output = io.BytesIO()
  
    df = pd.read_excel("file.xlsx")
    df = do_some_transformation(df)
  
    writer = pd.ExcelWriter(output, engine='xlsxwriter')
    df.to_excel(writer, sheet_name='Sheet1')
  
    writer.save()
    xlsx_data = output.getvalue()
  
    requests.put("url/outputfile.xlsx",
             data=xlsx_data,
             auth=auth,
             headers={'content-type': 'application/vnd.ms-excel'}),
  

Answer 2

You can do:

writer = pd.ExcelWriter(fileName, engine='xlsxwriter')
df.to_excel(writer, sheetName)

Which will create an Excel object in writer with data from df. It won't save it until you do:

writer.save()