pandas 使用 Flask 服务器和 xlsxwriter 导出 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37426879/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel export with Flask server and xlsxwriter
提问by John_Mtl
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.
所以我过去一直使用 XLSXWriter 来导出一个 excel 文件，其中包含一个充满两个 Pandas 数据框的选项卡。过去，我只是将文件导出到用户计算机上的本地路径，但我正在过渡到 Web 界面。
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.
我想要的输出是与下面的代码具有相同的 excel 文件，但在内存中创建并发送给用户，供他/她通过 Web 界面下载。我已经看到了很多 Django 和 StringIO，但我正在寻找可以与 Flask 一起使用的东西，但我找不到任何真正有效的东西。
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()
回答by Maximilian Peters
The following snippet works on Win10 with Python 3.4 64bit.
以下代码段适用于使用 Python 3.4 64 位的 Win10。
The Pandas ExcelWriter writes to a BytesIOstream which is then sent back to the user via Flaskand send_file.
Pandas ExcelWriter 写入一个BytesIO流，然后通过Flask和发送回用户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:
参考：
回答by MaxU
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('/')
def index():
send_file(myio,
attachment_filename="test.xlsx",
as_attachment=True)
app.run(debug=True)
you may also want to write your excel file using tempfile
您可能还想使用tempfile编写您的 excel 文件
回答by Vitthal Sarode
If you want xlsx file in response without storing it at the server side. You can use the following code snippet.
如果您希望 xlsx 文件作为响应而不将其存储在服务器端。您可以使用以下代码片段。
from flask import Flask
app = Flask(__name__)
data = [[1, 2], [3, 4]]
@app.route('/')
def get_xslx_for_data():
try:
response = Response()
response.status_code = 200
output = StringIO.StringIO()
workbook = xlsxwriter.Workbook(output, {'in_memory': True})
worksheet = workbook.add_worksheet('hello')
for i, d in enumerate(data):
for j, res in enumerate(d):
worksheet.write(i, j, res)
workbook.close()
output.seek(0)
response.data = output.read()
file_name = 'my_file_{}.xlsx'.format(
datetime.now().strftime('%d/%m/%Y'))
mimetype_tuple = mimetypes.guess_type(file_name)
response_headers = Headers({
'Pragma': "public", # required,
'Expires': '0',
'Cache-Control': 'must-revalidate, post-check=0, pre-check=0',
'Cache-Control': 'private', # required for certain browsers,
'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
'Content-Disposition': 'attachment; filename=\"%s\";' % file_name,
'Content-Transfer-Encoding': 'binary',
'Content-Length': len(response.data)
})
if not mimetype_tuple[1] is None:
response.update({
'Content-Encoding': mimetype_tuple[1]
})
response.headers = response_headers
response.set_cookie('fileDownload', 'true', path='/')
return response
except Exception as e:
print(e)
if __name__ == '__main__':
app.run()
