使用Flask服务器和xlsxwriter进行Excel导出 [英] Excel export with Flask server and xlsxwriter

问题描述

因此，我过去一直在使用XLSXWriter导出一个excel文件，该文件包含一个填充有两个熊猫数据框的选项卡.过去，我只是将文件导出到用户计算机上的本地路径，但是我正在过渡到Web界面.

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.

我想要的输出是与下面的代码具有相同的excel文件，但在内存中创建并发送给用户，以供他/她通过Web界面下载.我已经看到了很多Django和StringIO，但是我正在寻找可以与Flask一起使用的东西，而我找不到真正可以使用的东西.

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.

有人知道这个问题吗?

提前谢谢！

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

推荐答案

以下代码段适用于带有Python 3.4 64位的Win10.

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

Pandas ExcelWriter写入BytesIO流，然后通过Flask和send_file发送回用户.

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)

参考文献:

• http://pandas.pydata.org/pandas-docs/stable/io.html
• http://flask.pocoo.org/snippets/32/

• http://pandas.pydata.org/pandas-docs/stable/io.html
• http://flask.pocoo.org/snippets/32/

这篇关于使用Flask服务器和xlsxwriter进行Excel导出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！