Geek Answers Handbook

Excel export with Flask server and xlsxwriter

So, I used XLSXWriter in the past to export an excel file containing one tab filled with two pandas frames. Previously, I only exported the file to a local path on the user computer, but I am making the transition to the web interface.

My desired result should have the same excel file as the code below, but created in memory and sent to the user to download it via the web interface. I saw a lot of Django and StringIO, but I am looking for something that can work with Flask, and I could not find anything that really worked.

Is anyone 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()
+5
source share
2 answers

The following snippet runs on Win10 with Python 3.4 64bit.

Pandas ExcelWriter writes a BytesIO stream, which is then sent to the user through 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)

Literature:

+4
source

you can use something similar to this :

 from flask import Flask, send_file import io myio = io.StringIO() with open(xlsx_path, 'rb') as f: data = f.read() myio.write(data) myio.seek(0) app = Flask(__name__) @app.route('/') send_file(myio, attachment_filename="test.xlsx", as_attachment=True) app.run(debug=True)

you can also write your excel file with tempfile

+2
source

More articles:


All Articles