antdesign+egg 导出(1) egg生成Excel文件

创建组件 exprotExcel.vue

<template>
  <!-- 前端 创建a标签 get 请求 egg 生成Excel数据 -->
  <div>
    <a-button @click="exportExcel">
      <a-icon :type="downLoading == true ? 'loading' : 'download'" />导出
    </a-button>
  </div>
</template>

<script>
export default {
  data() {
    return {
      // Excel 开关
      downLoading: false,

      // 过滤条件，存放sql语句
      options: {
        filters: {}
      }
    };
  },
  methods: {
    exportExcel() {
      // 定时 状态
      if (this.downLoading == false) {
        this.downLoading = true;
        setTimeout(() => {
          this.downLoading = false;
        }, 3000);

        // this.options.filters:存放两个sql .likeMysql 模糊查询 .queryMysql 简版查询
        let tmp = this.options.filters;

        // sql 传递 % 符号有问题 字符串替换处理
        // console.log(tmp.likeMysql);
        // ( customerName  like '%张三%' or  code  like '%张三%' or  customerGrade  like '%张三%' or  creator  like '%张三%' or cast(creatTime as char) like '%NaN%' )
        if (tmp.likeMysql != undefined) {
          tmp.likeMysql = tmp.likeMysql.replace(/%/g, "sqlABC");
        }
        if (tmp.queryMysql != undefined) {
          tmp.queryMysql = tmp.queryMysql.replace(/%/g, "sqlABC");
        }

        //创建a标签 传递路由路径,实现点击下载
        const a = document.createElement("a");
        //JSON.stringify() 1. JSON 通常用于与服务端交换数据。2.在向服务器发送数据时一般是字符串 3.此方法将 JavaScript 对象转换为字符串
        a.href = "/api/guowei/customer/fs_customer/customerExcel?customerData=" +
          JSON.stringify(tmp);
        a.style.display = "none";
        document.body.appendChild(a);
        a.click();
        document.body.removeChild(a);
      }
    }
  }
};
</script>
<style lang="less" scoped>
</style>

后端代码:

// 1.配置router
router.get('/api/guowei/customer/fs_customer/customerExcel', controller.guowei.customerData.customerExcel);

// 2.对应controller层 customerData.js
// 安装插件:npm i xlsx --save
const Controller = require("egg").Controller;
const xlsx = require("xlsx");
class customerDataController extends Controller {
    async customerExcel() {
        const ctx = this.ctx;

        // 处理过滤条件
        let sqlWhere = "";
        if (ctx.query.customerData != "{}") {
            if (JSON.parse(ctx.query.customerData).likeMysql != undefined) {
                // ( customerName  like 'sqlABC张三sqlABC' or  code  like 'sqlABC张三sqlABC' or cast(creatTime as char) like 'sqlABCNaNsqlABC' )
                sqlWhere = JSON.parse(ctx.query.customerData).likeMysql.replace(/sqlABC/g, "%") + " and ";
            }
            if (JSON.parse(ctx.query.customerData).queryMysql != undefined) {
                sqlWhere = JSON.parse(ctx.query.customerData).queryMysql.replace(/sqlABC/g, "%") + " and ";
            }
            sqlWhere = sqlWhere.substring(0, sqlWhere.length - 4);
            sqlWhere = " where " + sqlWhere;
            // console.log(sqlWhere);
        }

        try {
            // sql 拼接 数据格式函数 当前数据支持60000条 
            // 保留两位小数,千位符号 FORMAT(price,2) as 价格 
            // 生成2020-05-06 14:12:56 date_format(creatTime,'%Y-%m-%d %H:%i:%S') as 填报时间
            let mysql =
                "select code as 客户编码, customerName as 客户简称,creatTime as 填报时间 from fs_customer " +
                sqlWhere +
                " order by creatTime desc limit 0,60000 ";

            // 向数据库查询sql语句  
            // [{code:'1',customerName:'2',creatTime:2020-04-30T05:24:07.000Z},{code:'1',customerName:'2',creatTime:2020-04-30T05:24:07.000Z}]
            let mysqlData = await this.app.mysql.query(mysql);


            // 生成workbook, 类型
            const workbook = xlsx.utils.book_new();
            let worksheet = xlsx.utils.json_to_sheet(mysqlData, {
                skipHeader: false
            });
            // 组装 sheet1插入工作表
            xlsx.utils.book_append_sheet(workbook, worksheet, "sheet");

            // 返回数据流
            // @ts-ignore
            this.ctx.set("Content-Type", "application/vnd.openxmlformats");
            // @ts-ignore  download:生成Excel名称
            this.ctx.set(
                "Content-Disposition",
                "attachment;filename*=UTF-8' '" +
                encodeURIComponent("客户信息") +
                ".xlsx"
            );
            // @ts-ignore

            //compression 压缩文件
            this.ctx.body = await xlsx.write(workbook, {
                bookType: "xlsx",
                compression: true,
                type: "buffer"
            });
        } catch (error) {
            console.log(error);
            console.log("导出数据过大,内存溢出");
        }


    }
}

module.exports = customerDataController;