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

Alarmist

which version of Pandas are you using? – Sean

I'm using Pandas 0.17.1 and Python 3.5.1 – Alarmist

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/

Sean

This is the perfect solution, worked like a charm! Thanks! – Alarmist

I tried the same method and also able to get 'xlsx' file but not able to open it, File is corrupted after getting downloaded. – Circular

@Circular Did you try saving the Excel file first locally and see if it works? – Sean

@MaximilianPeters otherwise from send_file() it is still not working, any issue from send_file() ? – Circular

@Circular I tried the snippet and it is still working for me. Can you post your code and settings as new question? – Sean

You are right when I am trying code independently it is working fine, but on the other hand same code in my application is causing me issues, Which part of "settings" in my app I need to look for? – Circular

@Circular the comment section is not the right place to discuss it, but just ask a new question. Add information about your OS, Python version, etc. and a minimal reproducible example. – Sean

@MaximilianPeters it worked out, The problem was with my flask_configuration File, now I am using Flask only to send file using following method: application.config["USE_X_SENDFILE"] = True – Circular

Would this work for really large sized dataframes? For example 1,000,000 rows? – Italianize

If you are not hitting the row limit of Excel or your server runs out of memory, I don't see any reason why it shouldn't work. – Sean

Answer 2

you can use something similar to this:

from flask import Flask, send_file
import io

myio = io.BytesIO()

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

Landonlandor

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

Feltner