egg 生成Excel

项目中因为某些特定环境导致 不能前端去生成Excel只能通过后端接口去做 网上找了很大示例 总算是写出来了 趁着空闲来记录下

egg项目搭建我这里就不说了，再提一下，我这里使用的是ts版本的

我这边使用了某个npm插件
npm install exceljs
安装好后就可以用了 下面我来写下具体示例

公共方法

app/server/common.ts

import { Service } from 'egg';
const Excel = require('exceljs');

export default class Test extends Service {

  /**
   * 数据并生成excel
   * @param {Array} headers excel标题栏
   * @param {Array} param 数据参数
   * @param {string} name 文件名称
   */
  public async excelCommon(headers:any, data:any, name: string,) {
    let columns:any = [];// exceljs要求的columns
    let hjRow = {};// 合计行
    let titleRows = headers.length;// 标题栏行数

    // 处理表头
    for (let i = 0; i < titleRows; i++) {
        let row = headers[i];
        for (let j = 0, rlen = row.length; j < rlen; j++) {
            let col = row[j];
            let { f, t, w = 15 } = col;
            if (!f) continue;// 不存在f则跳过

            if (col.totalRow) hjRow[f] = true;
            if (col.totalRowText) hjRow[f] = col.totalRowText;
            col.style = { alignment: { vertical: 'middle', horizontal: 'center' } };
            col.header = t;
            col.key = f;
            col.width = w;
            columns.push(col);
        }
    }


    let workbook = new Excel.Workbook();
    let sheet = workbook.addWorksheet('My Sheet', { views: [{ xSplit: 1, ySplit: 1 }] });
    sheet.columns = columns;
    sheet.addRows(data);

    // 处理复杂表头
    if (titleRows > 1) {
        for (let i = 1; i < titleRows; i++)  sheet.spliceRows(1, 0, []);// 头部插入空行

        for (let i = 0; i < titleRows; i++) {
            let row = headers[i];
            for (let j = 0, rlen = row.length; j < rlen; j++) {
                let col = row[j];
                if (!col.m1) continue;

                sheet.getCell(col.m1).value = col.t;
                sheet.mergeCells(col.m1 + ":" + col.m2);
            }
        }
    }

    // 处理样式、日期、字典项
    sheet.eachRow(function (row, rowNumber) {
        // 设置行高
        row.height = 25;

        row.eachCell({ includeEmpty: true },  (cell)=> {
              // 设置边框 黑色 细实线
              const top:any = { style: 'thin', color: { argb: '000000' } };
              const left:any = { style: 'thin', color: { argb: '000000' } };
              const bottom:any = { style: 'thin', color: { argb: '000000' } };
              const right:any = { style: 'thin', color: { argb: '000000' } };
              cell.border = { top, left, bottom, right };

              // 设置标题部分为粗体
              if (rowNumber <= titleRows) { cell.font = { bold: true }; return; }
          });
      });


      this.ctx.set('Content-Type', 'application/vnd.openxmlformats');
      // 这个地方的空格不要更改
      this.ctx.set('Content-Disposition', "attachment;filename*=UTF-8' '" + encodeURIComponent(name) + '.xlsx');
      this.ctx.body = await workbook.xlsx.writeBuffer();
    }
}

控制器方法调用

app/controller/home.ts

import { Controller } from 'egg';


export default class HomeController extends Controller {
  public async index() {
      const { ctx } = this;
        let headers = [
          [ 
              { t: '下单时间', f: 'trade_time', totalRow: true },
              { t: '订单类型', f: 'order_type', totalRow: true },
              { t: '手机号码', f: 'phone_number', totalRow: true },
              { t: '扫描状态', f: 'scan_status', totalRow: true },
              { t: '交易状态', f: 'ctf_order_status', totalRow: true },
              { t: '订单份额(克)', f: 'trade_share', totalRow: true },
              { t: '当时账号总份额(克)', f: 'account_share', totalRow: true },
              { t: '订单号', f: 'order_no', totalRow: true },
          ]
        ];
        let data = [
            { trade_time: '2020-12-10', order_type:'线上', phone_number:'18374009921', scan_status:'1', ctf_order_status:'1', trade_share:'2', account_share:'2', order_no:'164656456546' },
            { trade_time: '2020-12-10', order_type:'线下', phone_number:'18374009921', scan_status:'1', ctf_order_status:'1', trade_share:'2', account_share:'2', order_no:'164656456546' }
        ] // 需要在这边自己适配数据，这边为空

    await ctx.service.common.excelCommon(headers, data, '订单信息');
  }  
}

路由

import { Application } from 'egg';

export default (app: Application) => {
  const { controller, router } = app;

  router.get('/Execl', controller.home.index);
};

这样就可以生成了 直接拿着接口去浏览器输出就好了

image.png

这里借鉴了这个大佬的 https://github.com/Wuwei9536/egg-exceljs

https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md#add-rows 中文