magazine-service/service-import-export/app/service/excel.js

'use strict';

const Service = require('egg').Service;
const ExcelJS = require('exceljs');
class ExcelService extends Service {
  // 创建
  async establish({ fileName, type, _this }) {
    const workbook = new ExcelJS.Workbook();
    const sheet = workbook.addWorksheet('Sheet');
    const exportFiled = _this.app.config.exportFiled[`export${type}`];
    sheet.columns = exportFiled.map(e => ({ header: e.title, key: e.name, width: 30 }));
    return await workbook.xlsx.writeFile(`${_this.app.config.exportsPath}/${fileName}.xlsx`).then(function() {
      return 'success';
    }, function(err) {
      return err;
    });
  }
  // 写入
  async insertFile({ type, data, fileName, _this }) {
    const filename = `${_this.app.config.exportsPath}/${fileName}.xlsx`;
    const workbook = new ExcelJS.Workbook();
    const exportFiled = _this.app.config.exportFiled[`export${type}`];
    // 读取文件
    await workbook.xlsx.readFile(filename)
      .then(async function() {
        // 获取sheet
        const worksheet = workbook.getWorksheet(1);
        // 遍历数据
        data.forEach(e => {
          // 获取左后可编辑行
          const lastRow = worksheet.lastRow;
          // 获取最后一行加一
          const getRowInsert = worksheet.getRow(lastRow.number + 1);
          // 遍历头文件 为制造单元格下表
          for (let i = 0; i < exportFiled.length; i++) {
            // 向最后一行加一的行 对应的单元格内添加数据
            getRowInsert.getCell(i + 1).value = e[exportFiled[i].name] || '';
          }
          getRowInsert.commit();
        });
        await workbook.xlsx.writeFile(filename);
      });
  }
}

module.exports = ExcelService;