pandas: write dataframe to excel file *object* (not file)?

Question

I have a dataframe that I want to convert to excel file, and return it using HTTP. Dataframe's to_excel method accepts either a path, or an ExcelWriter, which, in turn, refers to a path.

Is there any way to convert the dataframe to a file object, without writing it to disk?

Answer 1

This can be done using the BytesIO Object in the standard library:

import pandas
from io import BytesIO

# Create Random Data for example
cols = ["col1", "col2"]
df = pandas.DataFrame.from_records([{k: 0.0 for k in cols} for _ in range(25)])

# Create an in memory binary file object, and write the dataframe to it.
in_memory_fp = BytesIO()
df.to_excel(in_memory_fp)

# Write the file out to disk to demonstrate that it worked.
in_memory_fp.seek(0,0)
with open("my_file.xlsx", 'wb') as f:
    f.write(in_memory_fp.read())

In the above example, I wrote the object out to a file so you can verify that it works. If you want to just return the raw binary data in memory all you need is:

in_memory_fp.seek(0,0)
binary_xl = in_memory_fp.read()