egg导出excel功能

例子：

使用egg+vue3 处理账单列表的数据导出功能。
废话不多说，先看结果

结果

此处放置前端浏览器下载的结果。

egg-后端

插件 exceljs

yarn add exceljs

封装导出excel的服务

service/exportExcel.js

const Service = require('egg').Service;
const Excel = require('exceljs');
 
class exportExcel extends Service {
  /**
   * 数据并生成excel
   * @param {Array} headers excel标题栏
   * @param {Array} param 数据参数
   * @param {string} name 文件名称
   */
   async excelCommon(headers, data, name) {
      let columns = [];// exceljs要求的columns
      let hjRow = {};// 合计行
      let titleRows = headers.length;// 标题栏行数
 
      // 处理表头
      headers.forEach(row=>{
        row.forEach(col=>{
          let { f, t ,w} = col;
          if (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 ? w : 15;//宽度
            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, []);
 
        headers.forEach(row=>{
          row.forEach(col=>{
            if (col.m1){
              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 = { style: 'thin', color: { argb: '000000' } };
          const left = { style: 'thin', color: { argb: '000000' } };
          const bottom = { style: 'thin', color: { argb: '000000' } };
          const right = { 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');
      return await workbook.xlsx.writeBuffer()
    }
}
module.exports = exportExcel;

controller

controller/bill.js

'use strict';
const Controller = require('egg').Controller;

class BillController extends Controller {
  async list() {
    const { ctx } = this;
    const params = ctx.query;
    ctx.body = await ctx.service.bill.list(params);
  }


  async downExcel() {
    console.log('downExcel111111111111111---');
    const { ctx } = this;
    const params = ctx.query;
    //两层数组，一层是行，一层是列，允许多行
    let headers = [
      [
        { t: 'id', f: 'id', totalRow: true },
        { t: '流水号', f: 'water_num', totalRow: true },
        { t: '时间', f: 'create_time', totalRow: true },
      ]
    ];
    // 注：f的字段名需要跟数据库的字段一致
    let data = await this.app.model.Bill.findAll({
      where: {},
      raw: true
    });
    let res = await ctx.service.exportExcel.excelCommon(headers, data, '订单信息')
    console.log('res--------: ', res);
    // ctx.body = ctx.helper.resSuc(res); // 包一层有问题
    ctx.body = res;
  }
}
module.exports = BillController;

router

  router.get('/bill/downExcel', controller.bill.downExcel);

待优化

数据不能包一层，例如{code, data, msg}，否则会出错

前端-vue3

有两种方案下载

1. a标签直接下载：弊端 1只能用get 2jwt鉴权问题(token问题)
2. 正常的axios下载

axios封装

重点用downFile， 注意config配置： responseType: ‘blob’
路径：service/request.ts

import axios from 'axios'
import type { AxiosInstance } from 'axios'
import cache from '@/utils/cache'
import { ElMessage } from 'element-plus'
import router from '@/router'
const BASE_URL = process.env.VUE_APP_BASE_URL || ''

class ZGLRequest {
  instance: AxiosInstance
  constructor(baseURL: string, timeout = 10000) {
    this.instance = axios.create({
      baseURL,
      timeout
    })
    this.instance.interceptors.request.use((config: any) => {
      const token = cache.getCache('token')
      if (token) {
        config.headers['token'] = token
      }
      return config
    })
    this.instance.interceptors.response.use((response: any) => {
      const res = response.data
      if (res.code !== 10000) {
        ElMessage.error(res.msg)
        if (res.code === 10001) {
          // token失效就跳到登录页
          router.push('/login')
        }
      }
      return response
    })
  }
  request(config: any) {
    return new Promise((resolve, reject) => {
      this.instance
        .request(config)
        .then((res) => {
          console.log('request-then-res-------: ', res);
          resolve(res.data)
        })
        .catch((err) => {
          console.log('request-err: ', err);
          reject(err)
        })
    })
  }
  post(config: any) {
    return this.request({ ...config, method: 'post' })
  }
  get(config: any) {
    return this.request({ ...config, method: 'get' })
  }
  patch(config: any) {
    return this.request({ ...config, method: 'patch' })
  }
  delete(config: any) {
    return this.request({ ...config, method: 'delete' })
  }
  // 注意responseType: 'blob'
  getfile(config: any) {
    return this.request({ ...config, method: 'get', responseType: 'blob'})
  }
}

export default new ZGLRequest(BASE_URL)

封装exportExcel.ts

service/exportExcel.ts

import zglRequest from './request'

export default {
  // 方案1
  getUrl: (url: any) => {
    let host = process.env.VUE_APP_BASE_URL == '/api' ? 'http://192.168.0.67:7003' : process.env.VUE_APP_BASE_URL;
    console.log('host: ', host);
    return host + url;
  },
  // 方案2
  exportfn: () => zglRequest.downFile({ url: '/bill/downExcel', }),
}

页面使用

// 方案1 - 类似a标签
<el-button type="primary" @click="exportfn">导出-简单版</el-button>
// 方案2 - axios
<el-button type="primary" @click="exportfnjinjie">导出-进阶版</el-button>


// js------------------
// 方案1 - 类似a标签
const exportfn = () => {
  let url = exportExcel.getUrl('/bill/downExcel')
  window.location.href = url
}

// 方案2 - axios
const exportfnjinjie = () => {
  exportExcel.exportfn().then((res: any) => {
    downFile(res, 'file.xlsx')
  })

  // 可以将此方法封装
  function downFile(content: any, filename: any) {
    // 创建隐藏的可下载链接
    var eleLink = document.createElement('a')
    eleLink.download = filename
    eleLink.style.display = 'none'
    // 字符内容转变成blob地址
    var blob = new Blob([content])
    eleLink.href = URL.createObjectURL(blob)
    document.body.appendChild(eleLink)
    // 触发点击
    eleLink.click()
    // 然后移除
    document.body.removeChild(eleLink)
  }
}