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 pdimport StringIOio = 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.

Glancing at the pandas.io.excel source looks like it shouldn't be too much of a problem if you don't mind using xlwt as your writer. The other engines may not be all that difficult either but xlwt jumps out as easy since its save method takes a stream or a filepath.

You need to initially pass in a filename just to make pandas happy as it checks the filename extension against the engine to make sure it's a supported format. But in the case of the xlwt engine, it just stuffs the filename into the object's path attribute and then uses it in the save method. If you change the path attribute to your stream, it'll happily save to that stream when you call the save method.

Here's an example:

import pandas as pdimport StringIOimport base64df = pd.DataFrame.from_csv('http://moz.com/top500/domains/csv')xlwt_writer = pd.io.excel.get_writer('xlwt')my_writer = xlwt_writer('whatever.xls')  #make pandas happy xl_out = StringIO.StringIO()my_writer.path = xl_out  df.to_excel(my_writer)my_writer.save()print base64.b64encode(xl_out.getvalue())

That's the quick, easy and slightly dirty way to do it. BTW... a cleaner way to do it is to subclass ExcelWriter (or one of it's existing subclasses, e.g. _XlwtWriter) -- but honestly there's so little involved in updating the path attribute, I voted to show you the easy way rather than go the slightly longer route.

None of these were working from me. I had a view I wanted to return an excel workbook from in Django. I found my solution from the pandas documentation.

import iobio = io.BytesIO()writer = pd.ExcelWriter(bio, engine='xlsxwriter')df.to_excel(writer, sheet_name='Sheet1')writer.save()bio.seek(0)# BONUS CONTENT# .. because I wanted to return from an apiresponse = HttpResponse(bio, content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')response['Content-Disposition'] = 'attachment; filename=myfile.xlsx'return response # returned from a view here

Note, I used that value for content type because it was the mime type according to the mozzilla docs. From ".xlsx" in the following link. Replace as needed.https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/MIME_types/Common_types