service-zhwl/app/service/util/excel.js

'use strict';
const _ = require('lodash');
const { sep } = require('path');
const fs = require('fs');
const Excel = require('exceljs');
const moment = require('moment');
const { CrudService } = require('naf-framework-mongoose/lib/service');
const { BusinessError, ErrorCode } = require('naf-core').Error;


class ExcelService extends CrudService {
  constructor(ctx) {
    super(ctx);
    this.root_path = _.get(this.ctx.app.config.export, 'root_path');
    this.file_type = 'export';
    if (!fs.existsSync(`${this.root_path}${this.file_type}`)) {
      // 如果不存在文件夹,就创建
      fs.mkdirSync(`${this.root_path}${this.file_type}`);
    }
    this.excel_path = `${sep}excel${sep}`;
  }


  /**
   * 导出excel;
   * @param {Object} param  { data, meta, fn }
   * @property Object data 数据;有meta的形式就是正常数据直接用;没有meta情况,需要整理成行数据
   * @property meta 列设置; 若meta不存在,则逐行解析. meta存在,则直接自动解析
   * @property fn 文件名
   */
  async toExcel({ data = [], meta, fn = 'excel导出结果' } = {}) {
    const nowDate = new Date().getTime();
    const filename = `${fn}.xlsx`; // -${nowDate}
    const path = `${this.root_path}${this.file_type}${this.excel_path}`;
    if (!path) {
      throw new BusinessError(ErrorCode.BUSINESS, '服务端没有设置存储路径');
    }
    if (!fs.existsSync(path)) {
      // 如果不存在文件夹,就创建
      fs.mkdirSync(path);
    }
    const workbook = new Excel.Workbook();
    const sheet = workbook.addWorksheet('sheet');
    if (meta) {
      sheet.columns = meta;
      sheet.addRows(data);
    } else {
      // console.log(data);
      for (const row of data) {
        const { scell, ecell, content, alignment, font } = row;
        if (scell && ecell) {
          sheet.mergeCells(scell, ecell);
          sheet.getCell(ecell).value = content;
          if (alignment) sheet.getCell(ecell).alignment = alignment;
          if (font) sheet.getCell(ecell).font = font;
        } else {
          console.log('in function:');
          if (_.isObject(content)) {
            const keys = Object.keys(content);
            for (const cell of keys) {
              const value = content[cell];
              sheet.getCell(cell).value = value;
              if (alignment) sheet.getCell(cell).alignment = alignment;
              if (font) sheet.getCell(cell).font = font;
            }
          }
        }
      }
    }
    // 导出
    const filepath = `${path}${filename}`;
    if (data.length <= 0) return;
    await workbook.xlsx.writeFile(filepath);
  }

  numberToLetter(num) {
    const arr = [ 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'Y', 'Z' ];
    return arr[num];
  }
}
module.exports = ExcelService;