egg后台导出xlsx表格

安装模块

npm i zl-excel -S

引入

const ExcelMod = require("zl-excel")

使用

'use strict';
const ExcelMod = require("zl-excel")
const Controller = require('egg').Controller;

class ExcelController extends Controller {
  async excelInfo() {
    const { ctx,app}=this;
    // 框架信息
    const eggFrameInfo = { name: 'egg.js', filename: '测试表格.xlsx', other: ctx };
    // 表格数据信息
    const excelInfo = [
      {
        sheetName: '用户表', // 第一个sheet的名字
        columns: [
          { header: '姓名', key: 'name', width: 15 },
          { header: '性别', key: 'sex', width: 10 },
          { header: '年龄', key: 'age', width: 20, default: 0 },
          { header: '爱好', key: 'hobby', width: 15, default: 0 },
          { header: '成功率', key: 'sucPre', width: 15, default: 0, style: { numFmt: '0.00%' } },
        ],
        rows: [
          { name: '张三', sex: '男', age: 18, hobby: '小说、音乐', sucPre: '0.6789' },
          { name: '李四', sex: '女', age: 19, hobby: '小说、音乐、学习', sucPre: '0.8888' },
        ],
      },
      {
        sheetName: '书籍表', // 第二个sheet的名字
        columns: [
          { header: '书名', key: 'bookName', width: 20, default: 0 },
          { header: '出版社', key: 'publish', width: 15, default: 0 },
          { header: '内容摘要', key: 'content', width: 15, default: 0 },
        ],
        rows: [
          { bookName: '《经济学》', publish: '北大出版社', content: '适合经济学学入门的书籍' },
          { bookName: '《数学概览》', publish: '清华出版社', content: '适合现代数学入门的书籍' },
        ],
      },
    ];
    // 自动导出表格到前端
    // await ExcelMod.getWorkbook({
    //   excelData: excelInfo,
    //   frameInfo: eggFrameInfo,
    // });
    //------如果需要对表格进行复杂的操作，这里不传入框架信息即可，它会将表格返回到调用处,然后进行你想要的修改，最后在发送到前端 -----
	let name=`${Date.now()}${Number.parseInt( Math.random() * 1000)}巡检记录报表`
   // 自动导出表格到本地
   await ExcelMod.getWorkbook({
    excelPath: `./app/public/${name}.xlsx`,
    excelData: excelInfo,
  });   

   //将表格返回到调用处    
   const workbook = await ExcelMod.getWorkbook({
    excelData: excelInfo,
  });

  // 。。。对表格进行复杂的处理。。。

  // 设置表格信息请求头，然后进行导出到前端
  const fileName = encodeURIComponent('测试表格.xlsx');
  ctx.set('content-disposition', `attachment; filename* = UTF-8''${fileName}`);
  ctx.status = 200;
  await workbook.xlsx.write(ctx.res);
  ctx.res.end();

  }
}

module.exports = ExcelController;

路由

 router.get('/excelInfo', controller.excel.excelInfo);

前端下载

exportexclx(){
                this.axios.get(`/api/excelInfo`,{
                    token:true,
                    responseType:"blob"
                }).then(data=>{
                            let url = window.URL.createObjectURL(new Blob([ data ]))
                            console.log(data)
                            let link = document.createElement("a")
                            link.style.display = "none"
                            link.href = url
                            let filename = (new Date()).getTime() + ".xlsx"
                            link.setAttribute("download",filename)
                            document.body.appendChild(link)
                            link.click()
                            close()
                        }).finally(()=>{
                           
                        })
            },

多个表关联的时候处理一些数据

//导出巡检记录
  async exportsfried(){
    const { ctx, app } = this;
    const { Sequelize } = app;
    let Op = app.Sequelize.Op;
    // 参数验证
  ctx.validate({
    gtetime: { type: 'string', required: true, desc: '大于时间'},
    ltetime: { type: 'string', required: true,  desc: '小于时间' },
    factory:{ required: true, type: "string",  desc: "厂区"},
    propertynumber: { type: 'string', required:false , range: { max: 30 }, desc: '巡检类别'},
  })
  let gtetime=ctx.query.gtetime
  let ltetime=ctx.query.ltetime
  let factory=ctx.query.factory
  let propertynumber=ctx.query.propertynumber
  let sqlwhere={
    datatime:{
      [Op.lte]: new Date(ltetime), //小于等于
      [Op.gte]: new Date(gtetime) //大于等于
    },
    factory,propertynumber
}
if(!propertynumber) delete sqlwhere.propertynumber
  let res=await app.model.Friend.findAll({
    where:sqlwhere,
     //多对多 通过中间表  可以关联到tag表
     include: [{
      model: app.model.Tag,
      attributes: ['name','abnormal']
     }],
  })


// 表格数据信息
const excelInfo = [];
let columns=[
  { header: '厂区', key: 'factory', width: 15 },
  { header: '区域', key: 'specifications', width: 10 },
  { header: '栋别', key: 'machine', width: 20},
  { header: '巡检类别', key: 'propertynumber', width: 15},
  { header: '巡检时间', key: 'dianjiantime', width: 15},
  { header: '离线上传时间', key: 'created_time', width: 15},
  { header: '设备或区域编号', key: 'codenumber', width: 15},
  { header: '设备或区域名称', key: 'suppliernumber', width: 15},
  { header: '点检情况', key: 'abnormal', width: 15},
  { header: '巡检人工号', key: 'username', width: 15},
  { header: '巡检人姓名', key: 'nickname', width: 15},
  { header: '是否按时巡检', key: 'finishontime', width: 50},
  { header: '是否复检', key: 'abnormalchaoshi', width: 15},
  { header: '是否维护', key: 'maintain', width: 15},
  { header: '点检明细', key: 'tags', width: 80},
  
]
let rows=JSON.parse(JSON.stringify(res))
let BB=[]
if(rows.length>0){
  for(let i=0;i<rows.length;i++){
     let obj={}
     let a,b,c,d,f=''
     if(rows[i].abnormal=='0'){
         a='正常'
     }else{
         a='异常'
     }
     if(rows[i].finishontime=='0'){
          b='按时巡检'
      }else{
          b='超时巡检'
      }
    if(rows[i].abnormalchaoshi=='0'){
        c=''
    }else if(rows[i].abnormalchaoshi=='1'){
        c='复检'
    }else{
        c='超时复检'
    }

    if(rows[i].maintain=='0'){
      d=''
    }else if(rows[i].maintain=='1'){
        d='未维护'
    }else{
        d='已维护'
    }

    for(let j=0;j<rows[i].tags.length;j++){
      f +=`${j+1}.${rows[i].tags[j].name}  是否正常：${rows[i].tags[j].abnormal} \n`
   }

     obj.factory=rows[i].factory
     obj.specifications=rows[i].specifications
     obj.machine=rows[i].machine
     obj.propertynumber=rows[i].propertynumber
     obj.dianjiantime=rows[i].dianjiantime
     obj.created_time=rows[i].created_time
     obj.codenumber=rows[i].codenumber
     obj.suppliernumber=rows[i].suppliernumber
     obj.abnormal=a
     obj.username=rows[i].username
     obj.nickname=rows[i].nickname
     obj.finishontime=b
     obj.abnormalchaoshi=c
     obj.maintain=d
     obj.tags=f
     BB.push(obj)
  }
}


let obj={}
obj.sheetName= '工作记录表'
obj.columns=columns
obj.rows=BB
excelInfo.push(obj)


  //将表格返回到调用处    
  const workbook = await ExcelMod.getWorkbook({
    excelData: excelInfo,
  });
  

  // 设置表格信息请求头，然后进行导出到前端
  const fileName = encodeURIComponent('表格.xlsx');
  ctx.set('content-disposition', `attachment; filename* = UTF-8''${fileName}`);
  ctx.status = 200;
  await workbook.xlsx.write(ctx.res);
  ctx.res.end();
  }