info_admin/app/util/excelUtils.js

'use strict';

const Excel = require('exceljs');
const utils = {
  // 定义格式: 工作表
  //  [ {sheetName:''}  , {}    ]
  // 获取excel
  getExcel(config) {
    if (!config) {
      return;
    }

    const workbook = new Excel.Workbook();

    config.forEach((con, index) => {
      const ws1 = workbook.addWorksheet(con.sheetName || 'sheet' + index, con.sheetOptions);

      con.sheetHeader.forEach(header => {
        const row = ws1.addRow([ header.headerName ]);
        Object.keys(header.headerConfig).forEach(key => {
          row[key] = header.headerConfig[key];
        });
      });
      ws1.mergeCells(con.sheetKey[0].letter + '1:'
        + con.sheetKey[con.sheetKey.length - 1].letter + '1');
      ws1.addRow(con.sheetKey.map(item => item.label));
      con.sheetData.forEach((data, index) => {
        const rowData = con.sheetKey.map((item, i) => {
          ws1.getColumn(i + 1).width = item.width;
          if (item.key == 'num') {
            return index + 1;
          }
          // console.log('====================', item)
          // if (item.key == 'birthday') {
          //     const myDate = new Date();
          //     const year = myDate.getFullYear();
          //     const month = (myDate.getMonth() + 1) > 9 ? (myDate.getMonth() + 1) : '0' + (myDate.getMonth() + 1);
          //     const day = myDate.getDate() > 9 ? (myDate.getDate()) : '0' + (myDate.getDate());
          //     return item.value = ;
          // }
          return this.getValue(data, item) || '';
        });
        ws1.addRow(rowData);
      });

      const tableKeyHeader = 1;
      const tableFirst = 1;
      this.rowCenter(ws1, tableFirst,
        con.sheetData.length + con.sheetHeader.length + tableKeyHeader);
    });
    return workbook;
  },

  rowCenter(arg_ws, arg_start, arg_end) {
    for (let i = arg_start; i <= arg_end; i++) {
      arg_ws.findRow(i).alignment = { vertical: 'middle', horizontal: 'center' };
      arg_ws.findRow(i).eachCell(function(cell, index) {
        if (i == 1) {
          cell.font = { bold: true };
        } else {
          cell.font = { bold: false };
        }
        cell.border = {
          top: { style: 'thin' },
          left: { style: 'thin' },
          bottom: { style: 'thin' },
          right: { style: 'thin' },
        };
      });
    }
  },

  getValue(data, config) {
    if (config.key.indexOf('.') != -1) { // 存在子属性
      const props = config.key.split('.');
      return props.reduce((first, second) => {
        if (first) {
          return first[second];
        }
        return null;

      }, data);

    } // 不存在
    return data[config.key];

  },
};

module.exports = utils;