server-exam/app/service/excel.js

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

//
class ExcelService extends CrudService {
  constructor(ctx) {
    super(ctx, 'excel');
  }
  // eslint-disable-next-line jsdoc/require-param
  /**
   * 检查路径,创建路径;返回前端接收路径
   * @param {Array} filesPath 地址拆开 例如: ['www','dir','example']
   * @return {Object} path 短地址;rPath 文件真实路径
   */
  checkPath(filesPath = []) {
    const { root_path, project, export_dir } = this.ctx.app.config.filesConfig;
    const arr = [ root_path, project, export_dir, ...filesPath ];
    let rPath = '';
    for (const i of arr) {
      rPath = `${rPath}${i}${sep}`;
      if (!fs.existsSync(rPath)) {
        fs.mkdirSync(rPath);
      }
    }
    arr.shift();
    arr.unshift('files');
    const path = `/${arr.join('/')}`;
    return { path, rPath };
  }
  /**
   * 将短地址转换为真实地址
   * @param {String} path 短地址
   */
  toRealPath(path) {
    const r = path.includes('/files');
    if (!r) return path;
    const { root_path } = this.ctx.app.config.filesConfig;
    const rPath = _.replace(path, '/files', root_path);
    return rPath;
  }

  /**
   * 导入,读取excel文件
   * @param {String} path 导入的文件短地址
   * @return {Array[Array]} values 二维数组,外维:行;第二维:列
   */
  async importExcel(path) {
    const realPath = this.toRealPath(path);
    const workbook = new Excel.Workbook();
    await workbook.xlsx.readFile(realPath);
    const sheet = workbook.getWorksheet(1);
    const values = [];
    sheet.eachRow(row => {
      const data = row.values;
      // 第一列是空的,扔了
      data.shift();
      values.push(data);
    });
    return values;
  }
}

module.exports = ExcelService;