service-dataimport/app/service/excelimport.js

'use strict';

const XLSX = require('xlsx');
const Service = require('egg').Service;

class ExcelimportService extends Service {

  constructor(ctx) {
    super(ctx);
    this.appid = '';
  }

  // 获取导入的XLSX文件中的数据
  async getImportXLSXData(result) {
    console.log(result);
    for (const elem of result) {
      const _id = elem._id;
      const filepath = this.ctx.app.config.fileDirImp + elem.content;
      if (elem.type === '0') {
        const workbook = XLSX.readFile(filepath);
        // 读取内容
        const sheetNames = workbook.SheetNames; // 获取所有sheet页
        const sheet = workbook.Sheets[sheetNames[0]]; // 通过取得当前sheet页
        // const theadRule = [ sheet.A1.v, sheet.B1.v, sheet.C1.v, sheet.D1.v, sheet.E1.v, sheet.F1.v, sheet.G1.v, sheet.H1.v, sheet.I1.v, sheet.J1.v, sheet.K1.v, sheet.L1.v, sheet.M1.v, sheet.N1.v, sheet.O1.v, sheet.P1.v ];
        const params = XLSX.utils.sheet_to_json(sheet); // 通过工具将表对象的数据读出来并转成json
        // const theadRule = [ '序号', '院系', '班级', '专业代码', '专业名称', '学号', '姓名', '身份证号', '性别', '学历代码', '学历名称', '毕业年份', '民族', '电话号码', '生源所在地', '政治面貌' ];
        console.log(params);
        if (!params) return [];
        const maps = {
          szyx: '院系',
          szbj: '班级',
          yxdm: '院校代码',
          yxmc: '院校名称',
          zydm: '专业代码',
          zymc: '专业名称',
          xh: '学号',
          xm: '姓名',
          sfzh: '身份证号',
          xb: '性别',
          xldm: '学历代码',
          xl: '学历名称',
          year: '毕业年份',
          mz: '民族',
          dhhm: '电话号码',
          syszddm: '生源所在地代码',
          syszd: '生源所在地',
          zzmm: '政治面貌',
        };
        const _datas = params.map(p => {
          const data = {};
          Object.keys(maps).forEach(k => { data[k] = p[maps[k]]; });
          // Object.entries(maps).forEach(a => { data[a[0]] = p[a[1]]; });
          return data;
        });
        // exceldata = [ ...exceldata, ..._datas ];
        // 将数据逐条导入学生库中
        const queryData = { year: elem.createtime, schid: elem.userid, schname: elem.name };
        let errCount = 0;
        const errmsgRes = [];
        for (const i in _datas) {
          try {
            const studRes = await this.ctx.service.axiox.student.create(queryData, _datas[i]);
            console.log(studRes);
          } catch (err) {
            console.log(err);
            errCount++;
            errmsgRes.push(_datas[i]);
          }
        }
        console.log(errCount);
        // 导入成功时更新状态
        if (errCount === 0) {
          const updatedata = { status: '1' };
          await this.ctx.service.dataimp.update({ id: _id }, updatedata);
        } else {
          console.log(errmsgRes);
          const updatedata = { status: '2', errmsg: errmsgRes };
          console.log(updatedata);
          await this.ctx.service.dataimp.update({ id: _id }, updatedata);
        }
      }
    }
  }

}

module.exports = ExcelimportService;