Return excel file from pandas with flask

Question

In flask I would like to return an excel file as output.

This code works well for csv:

    out = StringIO()
    notification_df.to_csv(out)
    resp = make_response(out.getvalue())
    resp.headers["Content-Disposition"] = "attachment; filename=output.csv"
    resp.headers["Content-type"] = "text/csv"
    return resp

I tried to adapt it so that it can output an excel file with various tabs (that originate from a pandas dataframe) but that doesn't work:

output = StringIO()
writer = ExcelWriter(output)
trades_df.to_excel(writer, 'Tab1')
snapshots_df.to_excel(writer, 'Tab2')
# writer.close() # causes TypeError: string argument expected, got 'bytes'

resp = make_response(output.getvalue)
resp.headers['Content-Disposition'] = 'attachment; filename=output.xlsx'
resp.headers["Content-type"] = "text/csv"
return resp

The problem is that I get an error TypeError: string argument expected, got 'bytes' when I do writer.close(). But when I leave it I get: Exception: Exception caught in workbook destructor. Explicit close() may be required for workbook.

Any solution are appreviated.

Answer 1

For the ones who end up here, this should work:

from flask import Response
import io
buffer = io.BytesIO()

df = pd.read_excel('path_to_excel.xlsx', header=0)
df.to_excel(buffer)
headers = {
    'Content-Disposition': 'attachment; filename=output.xlsx',
    'Content-type': 'application/vnd.ms-excel'
}
return Response(buffer.getvalue(), mimetype='application/vnd.ms-excel', headers=headers)