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