I can pass a StringIO object to pd.to_csv() just fine:
io = StringIO.StringIO()
pd.DataFrame().to_csv(io)
But when using the excel writer, I am having a lot more trouble.
io = StringIO.StringIO()
writer = pd.ExcelWriter(io)
pd.DataFrame().to_excel(writer,"sheet name")
writer.save()
Returns an
AttributeError: StringIO instance has no attribute 'rfind'
I'm trying to create an ExcelWriter
object without calling pd.ExcelWriter()
but am having some trouble. This is what I've tried so far:
from xlsxwriter.workbook import Workbook
writer = Workbook(io)
pd.DataFrame().to_excel(writer,"sheet name")
writer.save()
But now I am getting an AttributeError: 'Workbook' object has no attribute 'write_cells'
How can I save a pandas dataframe in excel format to a StringIO
object?
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.