Excel export with Flask server and xlsxwriter

Question

So I've been using XLSXWriter in the past to export an excel file containing one tab filled with two pandas dataframes. In the past I've only been exporting the file to a local path on the user's computer but I'm doing the transition to a web interface.

My desired output is to have the same excel file as the code below, but created in memory and sent to the user for him/her to download through the web interface. I've been seeing a lot of Django and StringIO but I'm looking for something that could work with Flask and I could not find anything that actually worked.

Is anybody familiar with this problem?

Thanks in advance!

xlsx_path = "C:\test.xlsx"
writer = pd.ExcelWriter(xlsx_path, engine='xlsxwriter')

df_1.to_excel(writer,startrow = 0, merge_cells = False, sheet_name = "Sheet_1")
df_2.to_excel(writer,startrow = len(df_1) + 4, 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)

writer.close()

Answer 1

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/

Answer 2

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

Answer 3

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