excel导出 ejsexcel 好用

根据需要展示的字段准备模板，

image.png

ExcelHelpService.js

'use strict';

const Service = require('egg').Service;
const path = require('path');
const fs = require('fs');
const ejsExcel = require('ejsexcel');
class ExcelHelpService extends Service {
  async downloadProblemFile([{ data, fileName }]) {
    const ctx = this.ctx;
    const conf = {};
    try {
      const exlBuf = fs.readFileSync(path.join(__dirname, '../public/export.xlsx'));

      // 用数据源（对象）data渲染Excel模板
      const exlBuf2 = await ejsExcel.renderExcel(exlBuf, data);

      // fs.writeFileSync(path.join(__dirname, '../../download/' + fileName), exlBuf2); 可以在本地形成文件，方便调试验证
      console.log(fileName);
      // 可以直接返回二进制数据，由前端进行解析生成文件；
      return exlBuf2;
    } catch (e) {
      console.log(e);
    }

  }

}
module.exports = ExcelHelpService;

对于入参data，应为对象形式，对应模板文件data。
如 data = {list:[], detailInfo},则模板中可以
<%forRow list,i in data.list%><%=list.index%> 获取 data.list，并进行循环赋值
对象data.detailInfo 中的数据也可以以此获取
<%= data.detailInfo.name%>

image.png

引用abcservice.js

      const data = await ctx.service.excelHelp.downloadProblemFile([{ data: { list: rows, detailInfo }, fileName: 'problem.xlsx' }]);
      return { data, fileName: detail.name + '.xlsx' };

返回给前端 abccontroller.js

      const res = { data, fileName: detail.name + '.xlsx' };
      ctx.set('Content-Type', 'application/vnd.openxmlformats;charset=utf-8');
      ctx.set('Content-Disposition', 'attachment; filename=' + encodeURIComponent(res.fileName));
      ctx.body = res.data;

前端axios获取

import axios from 'axios';
import moment from 'moment';
export function exportFile ({url, param, filename, suffix = 'xls', headers, needQuery=false}) {
    if (!filename) {
        filename = moment().format('YYYYMMDDHHmmss');
    }
    let fullname = `${filename}.${suffix}`;
    return new Promise((resolve, reject) => {
        axios({
            method: 'POST',
            headers: {
                'Content-Type': 'application/json;charset=utf8',
                'Cache-Control': 'no-cache',
                ...headers
            },
            url: url,
            data: JSON.stringify({
                ...param
            }),
            responseType: 'blob'
        }).then(res => {
            if (res.headers && res.headers['content-type']) {
                let type = res.headers['content-type'];
                if (type.indexOf('application/json')>=0) {
                    throw res;
                }
            }
            if (res.headers && res.headers['content-disposition']) {
                console.log('headers content-disposition: ' + JSON.stringify(res.headers['content-disposition']) );
                let list = res.headers['content-disposition'].split('filename=');
                if (list.length>=2 && list[1] && list[1].length) {
                    let pre = list[1];
                    fullname = decodeURIComponent(pre) ;
                }
            }
            let reader = new FileReader();
            reader.readAsDataURL(res.data); // 转换为base64，可以直接放入a标签href
            reader.onload = function (e) {
                let a = document.createElement('a');
                a.download = fullname;
                a.href = window.URL.createObjectURL(res.data);
                a.click();
            };
            resolve(res);
        }).catch(error => {
                reject(error);
        });
    });
}

参考
https://blog.csdn.net/qq_41745216/article/details/101322411