return a created excel file with flask

Question

I am creating a excel file using openpyxl which I would like to return as a file download (so not saving locally).

I can create the excel file fine and save it to disk. However, I cannot get this file to download.

Attempt 1:

import flask_excel as excel

...

create_excel_sheet(data) # internally save the sheet with name sheet.xlsx

output = excel.make_response()
output.headers["Content-Disposition"] = "attachment; filename=" + \
                                        'sheet.xlsx'
output.headers["Content-type"] = "application/vnd.openxmlformats-\
officedocument.spreadsheetml.sheet"

return output

This returns an empty text file with name sheet.xlsx

Attempt 2: wb = create_excel_sheet(data) # return openpyxl workbook

output = excel.make_response(wb)
output.headers["Content-Disposition"] = "attachment; filename=" + \
                                        'sheet.xlsx'
output.headers["Content-type"] = "application/vnd.openxmlformats-\
officedocument.spreadsheetml.sheet"

return output

I don't want to use pyexcel for the data as I need openpyxl to create a fancy excel sheet. Obviously if pyexcel and openpyxl communicated that would be fine.

any thoughts?

Cheers, Mike

openpyxl does allow you to save to a file like object with `save_virtual_workbook()` — Charlie Clark, Mar 23 '17 at 16:50

ghchoi · Answer 1 · 2021-01-23T11:24:10.937

Since I struggled with ambiguity to reassemble fragmented and a little bit old-fashioned code snippets, I want to leave another answer here. This is technically the same but a quite complete code snippet which is a little bit more up to date.

from flask import Response
from openpyxl import Workbook
from openpyxl.writer.excel import save_virtual_workbook

...

@app.route("/download")
def download():
    wb = Workbook()
    
    ...

    return Response(
        save_virtual_workbook(wb),
        headers={
            'Content-Disposition': 'attachment; filename=sheet.xlsx',
            'Content-type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        }
    )

score 4 · Accepted Answer · edited Jun 10 '22 at 11:09

Based on Charlie Clark's hint, I finally settled on the following solution.

output = make_response(create_sheet(data))
output.headers["Content-Disposition"] = "attachment; filename=" + \
                                        "sheet.xlsx"
output.headers["Content-type"] = \
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

where

def create_sheet(data):

returns

return save_virtual_workbook(wb)

score 1 · Answer 3 · answered Mar 22 '17 at 17:47

1

What I did when I faced the same problem is that I wrote a temporary file on the server, I made my create_excel_sheet(data) function to return file name and then send the file back with the flask send_file() function:

send_file( create_excel_sheet_and_return_filename(data) )

You can use python modules that create temporary files which are deleted when the process exists and use authorization if security is an issue.

answered Mar 22 '17 at 17:47

grepe

1,897
2
14
24

Thanks! This seems to work, but now my tempfiles are adding up in my /tmp/dir . I used the approach in: http://stackoverflow.com/questions/31391344/using-tempfile-to-create-pdf-xls-documents-in-flask – Mike Mar 22 '17 at 20:41
did you create the named temporary file with delete=True? https://docs.python.org/2/library/tempfile.html#tempfile.NamedTemporaryFile – grepe Mar 23 '17 at 10:14

score 1 · Answer 4 · answered Mar 22 '17 at 21:15

1

That all looks correct. Are you actually returning the response from your view? That wasn't clear in the question, and would explain the problem, I think.

For example:

@app.route('/download_sheet')
def download():
    create_excel_sheet(data)
    output = excel.make_response()
    output.headers["Content-Disposition"] = "attachment; filename=sheet.xlsx"
    output.headers["Content-type"] = "application/vnd.openxmlformats-\
officedocument.spreadsheetml.sheet"
    return output  # Send the response to the user

...

<a href="{{ url_for('app.download') }}">Click here for Sheet</a>

answered Mar 22 '17 at 21:15

Christopher Alexander Campbell

567
3
11

1

I do return output (edited above). My problem (now) is to return the file without filling my memory with temporaries – Mike Mar 22 '17 at 22:41
Hm -- It seems like the temporary file solution is working. So, how about you just have the app search for and remove temporary files at regular intervals? – Christopher Alexander Campbell Mar 23 '17 at 18:03

return a created excel file with flask

4 Answers4