flask 导出数据写入excel，实现文件报表导出

关于你们想要知道的flask导入数据写入Excel的方法来了，干活满满，欢迎来吸取！！！

@course_blu.route('/download')
def download():
    data = request.args
    search_date = data.get('searchDate') if data.get('searchDate') else ''
    teacher = data.get('teacher') if data.get('teacher') else ''
    cur_date = get_cur_date()
    tmp_date_m = '-'.join(cur_date.split('-')[:-1])

    lessons = Lesson.query.filter(
        Lesson.ClassStatus == '1',
        Lesson.classDay.like(f'{search_date if search_date else tmp_date_m}%'),
        Lesson.teacher.like(f'%{teacher if teacher else ""}%'),
    ).order_by(Lesson.classDay).all()
    data = [i.to_json() for i in lessons]
    # 创建IO对象
    output = BytesIO()
    # 写excel
    workbook = xlsxwriter.Workbook(output)  # 先创建一个book，直接写到io中

    sheet = workbook.add_worksheet('sheet1')
    fileds = ['类型', '课程名称', '方向', '期数', '班级',
              '上课日期', '课时数', '授课老师', '授课方式']

    # 写入数据到A1一列
    sheet.write_row('A1', fileds)

    # 遍历有多少行数据
    for i in range(len(data)):
        # 遍历有多少列数据
        for x in range(len(fileds)):
            key = [key for key in data[i].keys()]
            sheet.write(i + 1, x, data[i][key[x]])
            # current_app.logger.info('当前行：{}  当前列：{}  数据：{}'.format(str(i), str(x), data[i][key[x]]))
    workbook.close()  # 需要关闭
    output.seek(0)  # 找到流的起始位置
    resp = make_response(output.getvalue())
    filename = get_cur_datetime()
    basename = f'{filename}.xlsx'

    # 转码，支持中文名称
    resp.headers["Content-Disposition"] = f"attachment; filename={basename}"

    resp.headers['Content-Type'] = 'application/x-xlsx'
    return resp

Flask提供了send_file函数用来导出文件，以下用实例来说明

• 路由文件

# 直接调用该接口即可导出文件
@con.route("/exportExcel", methods=["GET", "POST"])
def export_excel_inter():
    """导出excel报表"""
    return export_excel()

• 实现

import time
from io import BytesIO
from flask import send_file
from openpyxl import Workbook
from loguru import logger


def export_excel():
    """excel 报表导出"""
    wb = Workbook()
    sheet = wb.create_sheet("报表")

    # excel数据处理并设置样式
    excel_data_deal(sheet)

    # 使用字节流存储
    output = BytesIO()

    # 保存文件
    wb.save(output)

    # 文件seek位置，从头(0)开始
    output.seek(0)
    filename = "%s.xls" % str(int(time.time()))

    # 打印文件大小
    logger.info("{} -> {} b".format(filename, len(output.getvalue())))

    # as_attachment：是否在headers中添加Content-Disposition
    # attachment_filename：下载时使用的文件名
    # conditional: 是否支持断点续传
    fv = send_file(output, as_attachment=True, attachment_filename=filename, conditional=True)
    fv.headers['Content-Disposition'] += "; filename*=utf-8''{}".format(filename)
    fv.headers["Cache-Control"] = "no_store"
    fv.headers["max-age"] = 1

    logger.info("导出报表---------%s" % filename)

    return fv

• 注意事项

'''这里创建文件使用openpyxl而不是xlwt，主要是xlwt支持上传的文件有大小限制，超过会报如下错误'''
File "/usr/local/lib/python3.8/site-packages/xlwt/UnicodeUtils.py", line 55, in upack2
    raise Exception('String longer than 32767 characters')
Exception: String longer than 32767 characters


'''Flask静态文件默认缓存时间为12小时，为了每次导出的文件不是之前缓存的文件，建议清除缓存，如下设置'''
from datetime import timedelta
app.config['SEND_FILE_MAX_AGE_DEFAULT'] = timedelta(seconds=1)  # 静态文件缓存为1s

# Flask默认配置源码
default_config = ImmutableDict(
        {
            "ENV": None,
            "DEBUG": None,
            "TESTING": False,
            "PROPAGATE_EXCEPTIONS": None,
            "PRESERVE_CONTEXT_ON_EXCEPTION": None,
            "SECRET_KEY": None,
            "PERMANENT_SESSION_LIFETIME": timedelta(days=31),
            "USE_X_SENDFILE": False,
            "SERVER_NAME": None,
            "APPLICATION_ROOT": "/",
            "SESSION_COOKIE_NAME": "session",
            "SESSION_COOKIE_DOMAIN": None,
            "SESSION_COOKIE_PATH": None,
            "SESSION_COOKIE_HTTPONLY": True,
            "SESSION_COOKIE_SECURE": False,
            "SESSION_COOKIE_SAMESITE": None,
            "SESSION_REFRESH_EACH_REQUEST": True,
            "MAX_CONTENT_LENGTH": None,
            "SEND_FILE_MAX_AGE_DEFAULT": timedelta(hours=12), # 静态文件默认缓存时间
            "TRAP_BAD_REQUEST_ERRORS": None,
            "TRAP_HTTP_EXCEPTIONS": False,
            "EXPLAIN_TEMPLATE_LOADING": False,
            "PREFERRED_URL_SCHEME": "http",
            "JSON_AS_ASCII": True,
            "JSON_SORT_KEYS": True,
            "JSONIFY_PRETTYPRINT_REGULAR": False,
            "JSONIFY_MIMETYPE": "application/json",
            "TEMPLATES_AUTO_RELOAD": None,
            "MAX_COOKIE_SIZE": 4093,
        }
)