Excel export with Flask server and xlsxwriter

you can use something similar to this:

from flask import Flask, send_file
import io

myio = 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 Flask

app = 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()

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 np
import pandas as pd
from io import BytesIO
from flask import Flask, send_file

app = 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/