python 后端导出excel使用io流传给前端下载

1. 需要安装的第三方库

pip install pandas
pip install numpy
pip install openpyxl

2.从数据库读取数据写入Excel文件流

from django.views.decorators.csrf import csrf_exempt
from django.db import connection
import pandas as pd
import io

def _createxcel(numbers):
    excel_file = io.BytesIO()  # 保存文件流
    sql_str = """SELECT distinct bill_rpts.cnumber AS No,bill_rpts.socialcode AS 交款人统一社会信用代码,bill_rpts.payer AS 交款人, convert(VARCHAR(100),bill_rpts.ddate,23) AS 开票日期,
    bill_rpts_items.itemnumber AS 项目编码,bill_rpts_items.itemname AS 项目名称,bill_rpts_items.uunit AS 单位,bill_rpts_items.quantity AS 数量,bill_rpts_items.standard AS 标准,
    convert(DECIMAL(18,2),bill_rpts_items.isum) AS '金额(元)',bill_rpts_items.cnote AS 备注,CONVERT(DECIMAL(18,2),bill_rpts.isum) AS 金额合计,bill_rpts.cnote AS 其他信息,
    code_dep.titlename AS 收款单位,bill_rpts.reviewers AS 收款人,bill_rpts.payee AS 复核人
    FROM bill_rpts
    LEFT JOIN bill_rpts_items ON bill_rpts.cnumber=bill_rpts_items.cnumber 
    LEFT JOIN code_dep ON code_dep.ccode=bill_rpts.ccode_dep 
    WHERE bill_rpts.cnumber IN %s;
    """ % numbers
    df = pd.read_sql_query(sql_str, connection)
    print(df)
    # df.set_index(['No','交款人统一社会信用代码','交款人'])
    # 将 DataFrame 写入 Excel 文件流
    df.to_excel(excel_file, index=False, sheet_name='票据明细表', header=True)
    # 设置文件流指针到开始位置
    excel_file.seek(0)
    return excel_file

3.以io流的形式传给前端处理

@csrf_exempt
def GenerateFile(request):
    ret = {'code': 0, 'data': None, 'msg': None}
    try:
        req = request.body
        str_req = req.decode()
        dict_req = json.loads(str_req)
        numbers = dict_req['cnumbers']
        if numbers:
            excel_file = _createxcel(numbers)
            # print(excel_file.getvalue())
            response = HttpResponse(excel_file.getvalue())
            response["Content-Disposition"] = 'attachment; filename=票据明细.xlsx'
            response["Content-Type"] = 'application/x-xlsx'

            # df = pd.read_excel(io=BytesIO(excel_file.getvalue()))
            # df.fillna('',inplace=True)
            # col_list = df.columns
            # value = df.values
            # print(col_list)
            # print(value)
            return response
        else:
            ret['msg'] = '参数有误'
    except Exception as e:
        ret['msg'] = e
    return HttpResponse(json.dumps(ret))