Pandas to openpyxl Workbook to download file in Flask

Question

The goal is save multiple dataframes in Excel sheet (each dataframe as a sheet) and download the file when the user hit the specified URL.

This is the code.

@app.server.route("/file/excel")
def download_excel():

    wb = Workbook()

    df1 = pd.DataFrame(...)
    sheet1 = wb.active
    sheet1.title = "Sheet1"
    for r in dataframe_to_rows(df1, index=False, header=True):
        sheet1.append(r)

    df2 = pd.DataFrame(...)
    sheet2 = wb.active
    sheet2.title = "Sheet1"
    for r in dataframe_to_rows(df2, index=False, header=True):
        sheet2.append(r)


    excel_stream = io.BytesIO()
    wb.save(excel_stream)
    excel_stream.seek(0)  # go to the beginning of the stream
    #
    return send_file(
        excel_stream,
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        attachment_filename="File.xlsx",
        as_attachment=True,
        cache_timeout=0
    )

I am getting the following error.

AttributeError: 'DatetimeArray' object has no attribute 'tolist'

df1 has a column with datatime data type. I did some search and found out that iterating through dataframe is not advised and that is causing this error.

The alternative is to use df.to_excel(), but I don't know how to make it work with BytesIO as I need to stream the data to be downloaded.

Question: how can I save the data to the excel sheet and get the error. I have to use send_file() for flask to download the file on the client.

Answer 1

Converting the datatime dtype to string before appending to the excel sheet resolved the issue. There might be a better solution, but this solved my issue