Flask将数据生成Excel文件下载

将list[dict]型数据,转换成excel,然后下载,用于一些报表数据下载等

import os
from flask import Flask, make_response, send_file
import xlwt

@app.route('/download', methods=['get','post'])
def hello_as():
    # 数据
    data = [{'a': 'aaaaa', 'id': 1}, {'a': 'b', 'id': 2}, {'a': 'c', 'id': 3}, {'a': 'e', 'id': 4}]
    # 文件名
    excel="test.xls"
    # execl sheet名
    sheet="test"
    return download_Excel(data,excel,sheet)


# 下载操作
def download_Excel(data=[],file="execl1.xls",sheet="sheet1"):
    # 生成要下载的临时文件
    listInDist_Excle(data, file, sheet)
    # 下载的文件内容
    response = make_response(send_file(file))
    # 下载的文件名字
    response.headers["Content-Disposition"] = "attachment; filename="+file+";"
    # 删除临时文件
    os.remove(file)
    return response


# 临时文件路径
temporary_file_PATH = ""

# 下载操作
def download_Excel(data=[],file="execl1.xls",sheet_name="sheet1"):
    # 生成要下载的临时文件
    listInDist_Excle(data, file, sheet_name)
    # 下载的文件内容
    response = make_response(send_file(temporary_file_PATH+file))
    # 下载的文件名字
    response.headers["Content-Disposition"] = "attachment; filename="+file+";"
    # 删除临时文件
    os.remove(temporary_file_PATH+file)
    return response

# 将list字典导出成excel
# data = [{'a': 'a', 'id': 1}, {'a': 'b', 'id': 2}, {'a': 'c', 'id': 3}, {'a': 'e', 'id': 4}]
def listInDist_Excle(data=[],excel_name="execl1.xls",sheet_name="sheet1"):
    # print(data)
    # 将查询结果写入到excel
    workbook = xlwt.Workbook()
    # 创建一个新的sheet
    sheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True)
    # 将表的字段名写入excel
    # 写入表头
    i = 0
    for key in data[0]:
        sheet.write(0, i, key)
        i += 1
    # 结果写入excle
    for row in range(len(data)):
        i = 0
        for key in data[row]:
            sheet.write(row + 1, i, data[row][key])
            i += 1
    # excel保存为文件
    workbook.save(temporary_file_PATH+excel_name)