flask做Web的时候sqlserver查询数据使用openpyxl生成Excel并下载

 python生成excel的方式，大致有三种【本文只介绍第二种方式】：

1，使用xlwt模块，不过这个模块稍微老一些，最大只能写入六万多，考虑到总和情况，所以就不选择它。
      文档地址：https://xlwt.readthedocs.io/en/latest/

2，使用openpyxl，可以生成2010之后新的excel文件，比如xlsx格式
      文档地址：https://openpyxl.readthedocs.io/en/stable/

3，使用pandas，最强大的数据分析库，自带excel读取和生成模块
      文档地址：https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html

 

在网上查阅了一些资料，没有使用flask，openpyxl生成excel前端请求就下载的这种案例，就自己封装了一个，以下是我的代码

数据库连接使用的是pyodbc，数据库工具使用的sqlalchemy，数据库连接被单独提了出去，查询数据的时候只需要把数据库连接import过来就行。流程就是：数据库查询数据-》提前定义好列标题-》将excel存到内存-》将数据封装到Response模块返回。

可以直接把数据的连接，查询后的结果集直接替换了，放在你自己的flask程序里面就可以跑了，当然列数和数据库的字段数量一定要对应。根据这个可以随便改造，在django中或者在其他地方都可以使用。

# coding:utf-8
from . import api
from flask import jsonify, current_app, request
from application.utils.sql_conn import engine


@api.route("/origin_data/download", methods=["GET"])
def download():
    sql_code = """
        select  ppp.bh,ppp.xmname,ppp.comname,ppp.zy,DATEADD(SECOND,ppp.ctime + 8 * 3600,'1970-01-01 00:00:00')'ctime',ppp.gcsbh,ppp.ztime,ppp.state,
        DATEADD(SECOND,ppp.ptime + 8 * 3600,'1970-01-01 00:00:00')'ptime',DATEADD(SECOND,ppp.jtime + 8 * 3600,'1970-01-01 00:00:00')'jtime',DATEADD(SECOND,ppp.wctime + 8 * 3600,'1970-01-01 00:00:00')'wctime',ppp.beizhu,ppp.xsbh,DATEADD(SECOND,ppp.xgsj+ 8 * 3600,'1970-01-01 00:00:00')'xgsj',ppp.jishu,
        ppp.zd,ppp.zdyh,ppp.address,ppp.hangye,ppp.cusname,ppp.comtype,ppp.comaddress,ppp.contact,ppp.condetail,DATEADD(SECOND,ppp.cltime + 8 * 3600,'1970-01-01 00:00:00')'cltime',ppp.fromuser,ppp.jhddrq,ppp.yjfwts,
        ppp.yjfwts,ppp.jqcj,ppp.xtts,ppp.zsjll,ppp.zsjll,ppp.cpgm,ppp.xcgylj,ppp.xcwhglzk,ppp.qtxcryxm,ppp.qtzhiwu,ppp.qtdianhua,ppp.ifjihui,ppp.swjhxz,ppp.jhgkms,
        ppp.jppp,ppp.swjhts,ppp.swjhsjll,kghsbsl,ppp.yqtrsyrq,ppp.ghyx,ppp.qtsyjhms,ppp.xmlx,ppp.xmyxj,ppp.fwlb,ppp.qtxmcp,
        (select product from fa_product p1 where ppp.jishu=p1.id) 'product_产品类型', 
        jl.liyou 'jl_liyou',DATEADD(SECOND,jl.ctime + 8 * 3600,'1970-01-01 00:00:00')'ji_ctime',jl.gcsbh'jl_gcsbh',jl.xcddsj'jl_xcddsj',jl.sjddrq'jl_sjddrq',jl.ifwanbi,jl.gzsj
        ,tt.*
        from (select z.* from dbo.fa_zproject z inner join (select bh,max(id) id from dbo.fa_zproject group by bh) zz on z.id=zz.id) ppp 
        left join fa_zjilu jl on jl.xmbh=ppp.bh 
        left join (select zz.bh,wx.bu'wx_bu',wx.jishu'wx_jishu',wx.wenxun'wx_wenxun',wx.state'wx_state',wx.stanum'wx_stanum',DATEADD(SECOND,wx.ctime + 8 * 3600,'1970-01-01 00:00:00')'wx_ctime',wx.hy'wx_hy',wx.dq'wx_dq',
                wx.yxxj'wx_yxxj',wx.cusname'wx_cusname',wx.condetail'wx_condetail',wx.comname'wx_comname',wx.comtype'wx_contype',wx.hangye'wx_hangye',wx.address'wx_address',
                wx.zdyh'wx_zdyh',wx.cjr'wx_cjr',wx.from_'wx_from'
        from fa_zproject zz inner join (select * from fa_swenxun)wx on wx.cjr like'%'+zz.bh+'%' and zz.ifjihui=1 ) tt on tt.bh=ppp.bh 
        where jl.state!=9 and ppp.state!=8 and ppp.state!=7
         order by ppp.id desc
    """
    try:
        sql_connect = engine.connect() #获取数据库连接
    except Exception as e:
        return jsonify({"SQL_Connection was failed, please check connection again!!!"})
    try:
        data = sql_connect.execute(sql_code) # 查询数据库数据
    except Exception as e:
        current_app.logger.error("origin_data API fetch data was failed!-->%s" % e)
        sql_connect.close()
        return jsonify({"msg": "origin_data- failed"})
    excel_header_list=[
        "项目编号","项目名字","公司名称","项目摘要","创建时间","服务人员编号","最后处理时间","项目状态","转发时间","接收时间"
        , "完成时间","备注","销售编号","修改时间","产品线","是否终端","终端名称","终端地区","终端行业","联系人姓名","公司类型",
        "地区","联系方式类型","联系方式内容","处理时间戳","创建人","计划到达日期","预计服务天数","几期车间","系统套数","总设计流量","产品规模"
        , "现场工艺路径","现场维护管理状况","其他现场人员姓名","职务","电话","是否有商业机会","商务机会选择","机会工况描述","竞品品牌"
        , "商务机会套数","商务机会设计流量","可更换设备数量","预期投入使用时间","更换意向","其他商业机会描述","项目类型","项目优先级","服务类别"
        , "其余项目相关产品","产品类型","服务小结_理由","服务小结_创建时间","服务小结_服务工程师编号","服务小结_现场等待时间","服务小结_实际到达日期"
        , "服务小结_是否完毕","服务小结_工作时间","服务小结_编号","问询_部门","问询_产品线","问询_问询内容","问询_状态","问询_发起人"
        , "问询_创建时间","问询_终端行业","问询_地区","问询_有效小结","问询_客户名称","问询_联系方式内容","问询_公司名称","问询_公司类型"
        , "问询_行业","问询_终端地区","问询_终端用户","问询_创建人","问询_来源" ] # 定义excel列标题 
    excel_data=[ ]
    from openpyxl import Workbook
    from openpyxl.writer.excel import ExcelWriter
    from openpyxl.cell.cell import get_column_letter
    from io import BytesIO
    from flask import make_response
    wb=Workbook()
    ws=wb.worksheets[0]
    ws.title="wechat_originData"
    excel_data.append(excel_header_list)
    for i in data:
        excel_data.append(i)
    i=1
    for line in excel_data:
        for col in range(1,len(line)+1):
            colNum=get_column_letter(col)
            ws.cell(column=col,row=i).value=line[col-1]
        i+=1
    sio=BytesIO()
    wb.save(sio)
    sio.seek(0)
    response=make_response(sio.getvalue())
    response.headers['Content-type'] = 'application/vnd.ms-excel'  # 指定返回的类型
    response.headers['Transfer-Encoding'] = 'chunked'
    import datetime
    nowtime = datetime.datetime.now().strftime('%Y%m%d%H') # 获取当天当时日期
    response.headers['Content-Disposition'] = 'attachment;filename=OriginData'+nowtime+'.xls'  # 设定用户浏览器显示的保存文件名

    return response

请求之后效果如下图： 

 

参考文章：

https://blog.csdn.net/aaronthon/article/details/86577501
https://www.cnblogs.com/nixindecat/p/12157563.html
https://blog.csdn.net/qq_37049781/article/details/83009355
https://www.cnblogs.com/l471151015/articles/9789674.html
https://blog.csdn.net/qq_38689395/article/details/101421879
https://blog.csdn.net/qq_33042187/article/details/79042176