python3+xlsxwriter+flask 导出excel

最新推荐文章于 2022-12-06 20:54:51 发布

屈强龙

最新推荐文章于 2022-12-06 20:54:51 发布

阅读量2.8k

点赞数

分类专栏： python

本文链接：https://blog.csdn.net/qq_36514588/article/details/84566611

版权

python 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

from app.db import Db
from flask import jsonify,make_response
import datetime,time
import json
import xlsxwriter
from io import BytesIO
import mimetypes
data = Db().query(sql) #数据
        output = BytesIO()
        # workfile = xlsxwriter.Workbook('test.xlsx')  # 创建Excel文件,保存
        workfile = xlsxwriter.Workbook(output, {'in_memory': True})  # 创建Excel文件,不保存,直接输出
        worksheet = workfile.add_worksheet('会员信息表')  # 创建工作表
        format1 = workfile.add_format({'font_size': '20', 'align': 'center', 'valign': 'vcenter', 'bold': True}) #设置样式,字体,水平,竖直居中,字体加粗
        format2 = workfile.add_format(
            {'font_size': '20', 'align': 'center', 'valign': 'vcenter', 'bold': True, 'font_color': '#217346'})
        format3 = workfile.add_format(
            {'font_size': '10', 'align': 'center', 'valign': 'vcenter', 'bold': True, 'bg_color': '#34A0FF',
             'font_color': 'white'})
        format4 = workfile.add_format(
            {'font_size': '10', 'align': 'center', 'valign': 'vcenter', 'bold': True,'font_color': '#217346'})
        worksheet.merge_range('A1:S1', '会员基本信息', format1)  #合并单元格 A1-S1
        worksheet.merge_range('T1:W1', '最近一周', format1)
        worksheet.merge_range('X1:AA1', '最近一月', format1)
        worksheet.merge_range('AB1:AE1', '最近三月', format1)
        worksheet.merge_range('AF1:AI1', '最近半年', format2)
        worksheet.merge_range('AJ1:AM1', '最近一年', format1)
        worksheet.set_column('AF:AI', None, format4)  #设置AF-AI列的样式

        title = [u'商益会员编号', u'性别', u'生日', u'注册时间', u'最新登录时间', u'用户等级', u'会员设备',
                 u'会员编号', u'公众号名称', u'手机归属省份', u'手机归属城市', u'手机归属区号', u'手机运营商',
                 u'首次消费时间', u'最近一次消费时间', u'消费总次数', u'消费总交', u'客单价', u'消费品类',
                 u'消费次数', u'沉睡会员', u'活着的会员', '超活跃会员',
                 u'消费次数', u'沉睡会员', u'活着的会员', '超活跃会员',
                 u'消费次数', u'沉睡会员', u'活着的会员', '超活跃会员',
                 u'消费次数', u'沉睡会员', u'活着的会员', '超活跃会员',
                 u'消费次数', u'沉睡会员', u'活着的会员', '超活跃会员'
                 ]

        worksheet.write_row('A2', title, format3)
        for i in range(len(data)):
            d=[
                data[i]['strBncCode'],data[i]['sex'],data[i]['birthday'],data[i]['reg_time'],data[i]['login_time'],
                data[i]['vip_level'],data[i]['agent'],data[i]['bu_code'],data[i]['bu_name'],data[i]['province'],
                data[i]['city'],data[i]['areaCode'],data[i]['phone_type'],data[i]['first_sale'],data[i]['last_sale'],
                data[i]['sale_num'],data[i]['sale_total'],data[i]['once_price'],data[i]['tag_name'],
                data[i]['one_week_num'],'','','',
                data[i]['one_month_num'],'','','',
                data[i]['three_months_num'],'','','',
                data[i]['half_year_num']'','','',
                data[i]['one_year_num'],'','','',
            ]
            worksheet.write_row('A'+str(i+3),d)
        workfile.close()
        # output.seek(0)
        # return send_file(output, mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        #                  as_attachment=True, attachment_filename='file.xlsx')
            #实现导出
        rv = make_response(output.getvalue())
        output.close()
        mime_type = mimetypes.guess_type('user_detail.xlsx')[0]
        rv.headers['Content-Type'] = mime_type
        rv.headers["Cache-Control"] = "no-cache"
        rv.headers['Content-Disposition'] = 'attachment; filename=user_detail.xlsx'
        return rv