create excel file from DataFrame and allow download in flask, error: file format/extension not valid

Question

I've created a simple Flask app that turns a Pandas Dataframe into an excel file. The file gets downloaded when I go to the link, however, I'm just getting the following error once I try and open the file:

Excel cannot open the file 'df.xlsx' because the file format or file extension is not valid. Verify that the files has not been corrupted and that the file extension matches the format of the file.

Am I missing something here?

 @app.route('/download', methods=["GET"])
 def download_excel():
    df = pd.DataFrame({'Name': ['Tesla','Tesla','Toyota','Ford','Ford','Ford'],
                   'Type': ['Model X','Model Y','Corolla','Bronco','Fiesta','Mustang']})

    #create an output stream
    output = BytesIO()
    writer = pd.ExcelWriter(df, engine='xlsxwriter')
    df.to_excel(writer, sheet_name='Sheet1')

    #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="df.xlsx", as_attachment=True)

bas · Accepted Answer · 2021-07-29 11:08:58Z

2

The problem is that you're passing the dataframe to ExcelWriter's path parameter instead of the BytesIO object (output).

writer = pd.ExcelWriter(df, engine='xlsxwriter')

The ExcelWriter documentation says the following on the path parameter:

path: str or typing.BinaryIO Path to xls or xlsx or ods file.

So you could do something like this instead:

from flask import Flask, send_file
import pandas as pd
from io import BytesIO

app = Flask(__name__)


@app.route("/download", methods=["GET"])
def download_excel():
    df = pd.DataFrame(
        {
            "Name": ["Tesla", "Tesla", "Toyota", "Ford", "Ford", "Ford"],
            "Type": ["Model X", "Model Y", "Corolla", "Bronco", "Fiesta", "Mustang"],
        }
    )

    output = BytesIO()

    with pd.ExcelWriter(output, engine="xlsxwriter") as writer:
        df.to_excel(writer, sheet_name="Sheet1")

    output.seek(0)

    return send_file(output, attachment_filename="df.xlsx", as_attachment=True)

edited Jul 29, 2021 at 11:08

answered Jul 29, 2021 at 11:00

bas

15.2k7 gold badges28 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Qinjie Over a year ago

One possible reason for the error is because the writer object is not closed properly. Either close the writer explicitly or use context manager as shown in above code.

Collectives™ on Stack Overflow

create excel file from DataFrame and allow download in flask, error: file format/extension not valid

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related