Excel export with Flask server and xlsxwriter

The following snippet works on Win10 with Python 3.4 64bit.

The Pandas ExcelWriter writes to a BytesIO stream which is then sent back to the user via Flask and send_file.

import numpy as npimport pandas as pdfrom io import BytesIOfrom flask import Flask, send_fileapp = Flask(__name__)@app.route('/')def index():    #create a random Pandas dataframe    df_1 = pd.DataFrame(np.random.randint(0,10,size=(10, 4)), columns=list('ABCD'))    #create an output stream    output = BytesIO()    writer = pd.ExcelWriter(output, engine='xlsxwriter')    #taken from the original question    df_1.to_excel(writer, startrow = 0, merge_cells = False, sheet_name = "Sheet_1")    workbook = writer.book    worksheet = writer.sheets["Sheet_1"]    format = workbook.add_format()    format.set_bg_color('#eeeeee')    worksheet.set_column(0,9,28)    #the writer has done its job    writer.close()    #go back to the beginning of the stream    output.seek(0)    #finally return the file    return send_file(output, attachment_filename="testing.xlsx", as_attachment=True)app.run(debug=True)

References:

• http://pandas.pydata.org/pandas-docs/stable/io.html
• http://flask.pocoo.org/snippets/32/

you can use something similar to this:

from flask import Flask, send_fileimport iomyio = io.StringIO()with open(xlsx_path, 'rb') as f:    data = f.read()myio.write(data)myio.seek(0)app = Flask(__name__)@app.route('/')def index():    send_file(myio,              attachment_filename="test.xlsx",              as_attachment=True)app.run(debug=True)

you may also want to write your excel file using tempfile

If you want xlsx file in response without storing it at the server side. You can use the following code snippet.

from flask import Flaskapp = Flask(__name__)data = [[1, 2], [3, 4]]@app.route('/')def get_xslx_for_data():    try:        response = Response()        response.status_code = 200        output = StringIO.StringIO()        workbook = xlsxwriter.Workbook(output, {'in_memory': True})        worksheet = workbook.add_worksheet('hello')        for i, d in enumerate(data):            for j, res in enumerate(d):                worksheet.write(i, j, res)        workbook.close()        output.seek(0)        response.data = output.read()        file_name = 'my_file_{}.xlsx'.format(            datetime.now().strftime('%d/%m/%Y'))        mimetype_tuple = mimetypes.guess_type(file_name)        response_headers = Headers({            'Pragma': "public",  # required,            'Expires': '0',            'Cache-Control': 'must-revalidate, post-check=0, pre-check=0',            'Cache-Control': 'private',  # required for certain browsers,            'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',            'Content-Disposition': 'attachment; filename=\"%s\";' % file_name,            'Content-Transfer-Encoding': 'binary',            'Content-Length': len(response.data)        })        if not mimetype_tuple[1] is None:            response.update({                'Content-Encoding': mimetype_tuple[1]            })        response.headers = response_headers        response.set_cookie('fileDownload', 'true', path='/')        return response    except Exception as e:        print(e)if __name__ == '__main__':    app.run()