python 实现数据库数据导出excel的前端以及后台的实现

前端代码：html部分，一个隐藏的form表单

<form action="" @submit="formSubmitFn" id="export-from" style="visibility:hidden">
      <input type="submit" id="export-input">
</form>

js部分：form表单中input点击触发form的submit事件

exportData(){
    $("#export-input").click();
}

　　　　　　　　formSubmitFn(e){
                e.preventDefault();
                this.$axios({
                    method: 'post',
                    url: '/api/exportByItem',
                    data:{
                        itemArr: this.multipleSelection,
                        taskId: this.$route.params.task_id
                    },
                    responseType: 'blob'
                }).then(
                (res)=>{
                    var reader = new FileReader();
                    reader.readAsDataURL(res);

                    reader.onload = function (e) {
                        // 转换完成，创建一个a标签用于下载
                        var a = document.createElement('a');
                        var now = new Date();
                        var mon = (now.getMonth()+1) < 10 ? '0'+(now.getMonth()+1) : (now.getMonth()+1);
                        var date = now.getDate() < 10 ? '0'+now.getDate() : now.getDate();
                        var hour = now.getHours() < 10 ? '0'+now.getHours() : now.getHours();
                        var min = now.getMinutes() < 10 ? '0'+ now.getMinutes() : now.getMinutes();
                        var sec = now.getSeconds() < 10 ? '0'+ now.getSeconds() : now.getSeconds();
                        var resXlsName = now.getFullYear() + '-' + mon + '-' + date + ' '+hour+':'+min+':'+sec;
                        a.download = resXlsName+'.xls';
                        a.href = e.target.result;
                        // 修复firefox中无法触发click
                        $("body").append(a);
                        a.click();
                        $(a).remove();
                    }
                })
            },

python部分

from xlwt import *
import requests
import xlwt 

@app.route('/api/export/<task_id>', methods=['GET'])
def export_by_task_id(task_id):
    """
    根据task_id导出数据
    :return:
    """
    if not hasattr(g, 'db_connect'):
        g.db_connect = __connect_db()
    with g.db_connect.cursor() as cursor:
        sql = "SELECT * FROM `item` where `task_id` = %s"
        cursor.execute(sql, (task_id))
        result = cursor.fetchall()
        # 获取当前时间
    nowtime = datetime.datetime.now().strftime('%Y-%m-%d-%H-%M-%S')
    # 创建一个workbook,设置编码格式为utf8
    workbook = xlwt.Workbook(encoding='utf-8')
    style = xlwt.XFStyle()
    style.num_format_str = 'YYYY-MM-DD';
    # 创建一个 worksheet
    worksheet = workbook.add_sheet('Worksheet',cell_overwrite_ok=True)
    
    worksheet.write(0, 0, label = '编号') 
    worksheet.write(0, 1, label = '起点') 
    worksheet.write(0, 2, label = '终点') 
    worksheet.write(0, 3, label = '策略评价') 
    worksheet.write(0, 4, label = '标签分类') 
    worksheet.write(0, 5, label = '备注') 
    worksheet.write(0, 6, label = '操作时间') 
    
    #循环插入值
    for index,x in enumerate(result):
            worksheet.write(index+1, 0, label = x["item_id"])
            worksheet.write(index+1, 1, label = x["lbs_start_position"])
            worksheet.write(index+1, 2, label = x["lbs_end_position"])
            worksheet.write(index+1, 3, label = x["tag"])
            worksheet.write(index+1, 4, label = x["item_class"])
            worksheet.write(index+1, 5, label = x["remark"])
            worksheet.write(index+1, 6, x["update_time"], style)

    sio=BytesIO()
    workbook.save(sio) 
    # 设置文件读取的偏移量，0表示从头读起
    sio.seek(0)
    # response = make_response(send_file(sio.getvalue(),attachment_filename="export.xls"))
    # response.headers['Content-Type'] = 'application/vnd.ms-excel' #文件类型
    # response.headers['Content-Disposition'] = "attachment;filename=export.xls"
    return sio.getvalue()