Write to StringIO object using Pandas Excelwriter?

Pandas expects a filename path to the ExcelWriter constructors although each of the writer engines support StringIO. Perhaps that should be raised as a bug/feature request in Pandas.

In the meantime here is a workaround example using the Pandas xlsxwriter engine:

import pandas as pd
import StringIO

io = StringIO.StringIO()

# Use a temp filename to keep pandas happy.
writer = pd.ExcelWriter('temp.xlsx', engine="xlsxwriter")

# Set the filename/file handle in the xlsxwriter.workbook object.
writer.book.filename = io

# Write the data frame to the StringIO object.
pd.DataFrame().to_excel(writer, sheet_name="Sheet1")
writer.save()
xlsx_data = io.getvalue()

Update: As of Pandas 0.17 it is now possible to do this more directly:

# Note, Python 2 example. For Python 3 use: output = io.BytesIO().
output = StringIO.StringIO()

# Use the StringIO object as the filehandle.
writer = pd.ExcelWriter(output, engine="xlsxwriter")

See also Saving the Dataframe output to a string in the XlsxWriter docs.