train-center/src/views/new-plan/arrange/school-arrange/excel-view.vue

<template>
  <div id="excel-view">
    <el-row>
      <el-col :span="4">
        <el-button size="mini" @click="getExcelData">获取excel数据</el-button>
      </el-col>
      <el-col :span="4">
        <el-button @click="toAddCol" size="mini" type="primary">新增日期安排</el-button>
      </el-col>
    </el-row>
    <div class="sheetContainerbox" ref="sheetContainer" id="x-spreadsheet-demo"></div>

    <el-dialog :visible.sync="dialog" title="计划变更" @close="toClose" :destroy-on-close="true">
      <term-add :key="new Date().getTime()" :data="form" :classTypeList="classTypeList" :placeList="placeList" v-bind="$attrs" v-on="$listeners"></term-add>
    </el-dialog>
    <el-dialog :visible.sync="dialog2" title="培训安排" @close="toClose" :destroy-on-close="true">
      <arrange-edit :key="new Date().getTime()" :data="form" :planData="planData" :schoolData="schoolData"></arrange-edit>
    </el-dialog>
  </div>
</template>

<script>
const _ = require('lodash');
const moment = require('moment');
import termAdd from './term-add.vue';
import arrangeEdit from './arrange-edit.vue';
import XLSX from 'xlsx';
//引入依赖包
import zhCN from 'x-data-spreadsheet/src/locale/zh-cn';
import Spreadsheet from 'x-data-spreadsheet';
//设置中文
Spreadsheet.locale('zh-cn', zhCN);
import { mapState, createNamespacedHelpers } from 'vuex';
export default {
  name: 'excel-view',
  props: {
    year: { type: String }, //当前计划年份
    plan: { type: Object }, //计划
    classTypeList: { type: Array }, // 班级类型列表
    schStuList: { type: Array }, // 学校上传学生的结果 [{schid:学校编码, sum:1}]
    schoolList: { type: Array }, //需要设置的学校列表
    placeList: { type: Array }, //场地列表
    schPlan: { type: Array }, // 培训计划安排
  },
  components: { termAdd, arrangeEdit },
  data: function() {
    return {
      dialog: false, // 计划dialog
      dialog2: false, // 培训计划dialog
      form: {},
      planData: {}, // 为修改培训计划整理的学校数据
      schoolData: {}, // 为修改培训计划整理的期批数据
      /**
       * merge:合并单元格,实际上是 [sri,sci,eri,eci]
       * sri:起始行数,在rows中的合并,已经确定了 起始行这个参数
       * sci:起始列数,在rows中的合并,r.cells.${n} n即为起始列数,也可以确定
       * eri:结束行数,需要自己确定
       * eci:结束列数,需要自己确定
       * 由此可知: 在rows中合并单元格, 我们只需要填写 eri和eci这两个参数.
       * 即由当前单元格出发,计算要合并几行几列,[行,列]
       */
      sheetDefaultConfig: {
        // name: '普通班', // 表单名(sheet1)
        // 样式
        styles: {
          valign: 'middle',
          align: 'center',
        },
        //行数据
        rows: {
          1: {
            cells: {
              0: {
                text: '序号',
                merge: [3, 0],
              },
              1: {
                text: '学校名称',
                merge: [3, 0],
              },
              2: {
                text: '计划数',
                merge: [3, 0],
              },
              3: {
                text: '地区',
                merge: [3, 0],
              },
              4: {
                text: '期数',
              },
            },
          },
          2: {
            cells: {
              4: {
                text: '场地',
              },
            },
          },
          3: {
            cells: {
              4: {
                text: '班级数',
              },
            },
          },
          4: {
            cells: {
              4: {
                text: '时间',
              },
            },
          },
          5: {
            cells: {
              2: {
                text: '批次人数',
                merge: [0, 2],
              },
            },
          },
          6: {
            cells: {
              2: { text: '督导', merge: [1, 1] },
              4: { text: '天数' },
            },
          },
          7: {
            cells: {
              4: { text: '人数' },
            },
          },
          8: {
            cells: {
              2: {
                text: '长春高校用车数',
                merge: [0, 2],
              },
            },
          },
        },
      },
      // 表格事件设置 如果在 x,y其中一个在无操作中, 则本次事件不操作
      /**无操作行: 点击事件,行在这里面,则直接当无事发生 */
      ignoreRows: [0],
      /**无操作列: 点击事件,列在这里面,则直接当无视发生 */
      /**计划行: 点击事件,行在这里面,且 列不在无操作列中, 则进入确定具体修改那期,并进入修改*/
      ignoreCols: [0, 1, 2, 3, 4],
      planRows: [1, 2, 3, 4],
      /**学校计划安排起始行 */
      arrangeStartRow: 8,
    };
  },
  computed: {
    ...mapState(['user']),
  },
  mounted() {
    this.init();
  },
  methods: {
    init() {
      this.xs = new Spreadsheet('#x-spreadsheet-demo', {
        mode: 'read',
        view: {
          height: () => this.$refs.sheetContainer?.offsetHeight,
          width: () => this.$refs.sheetContainer?.offsetWidth,
        },
        col: { len: 45 },
        style: { align: 'center' },
        showToolbar: false,
        showGrid: true,
        showContextmenu: false,
      }).loadData([]);
      this.xs
        .on('cell-selected', (cell, ri, ci) => {
          console.log('cell:', cell, ', ri:', ri, ', ci:', ci);
          this.cellClick(ri, ci, cell);
        })
        .on('cell-edited', (text, ri, ci) => {
          console.log('text:', text, ', ri: ', ri, ', ci:', ci);
        });
      // 整理表头数据
      const sheetDatas = this.organizeLine3456();
      // 检查场地,督导的合并,计算督导的人数
      this.organizeLine378(sheetDatas);
      // 输出学校列表
      this.organizeLineFrom9(sheetDatas);
      this.xs.loadData(sheetDatas);
      // 学校安排人数设置
      this.setSchPlan();
      // TODO:计算高校用车数
    },

    // #region 单元格事件
    /**
     * 单元格点击事件
     * @param {Number} ri 点击的行位置
     * @param {Number} ci 点击的列位置
     * @param {Object} cell 单元格
     */
    cellClick(ri, ci, cell) {
      const inIngnoreRow = this.ignoreRows.includes(ri);
      // 在不操作行中,直接返回
      if (inIngnoreRow) return;
      const inIngnoreCol = this.ignoreCols.includes(ci);
      // 在不操作列中,直接返回
      if (inIngnoreCol) return;
      // 查看是否在计划行中
      const inPlanRows = this.planRows.includes(ri);
      if (inPlanRows) {
        // 确定点击位置是计划的哪个位置,然后进行计划修改
        console.log('in plan');
        // 使用列,确定是哪一批次即可
        const batch = this.toConfirmPlan(ri, ci);
        if (batch) {
          // 没有延迟会又选择好几个
          _.delay(() => {
            this.form = batch;
            this.dialog = true;
          }, 100);
        }
      } else if (ri > this.arrangeStartRow) {
        // 学校的安排,打开学校安排界面
        console.log('in arrange');
        // 确定是哪个学校,哪个批次
        let number = _.get(cell, 'text', 0);
        if (number) number = parseInt(number);
        const res = this.toConfirmArrange(ri, ci);
        if (!res) {
          this.$message.error('培训计划安排整理数据发生错误');
          return;
        }
        const { schoolData, planData } = res;
        console.log(planData)
        _.delay(() => {
          this.form = { number };
          this.schoolData = schoolData;
          this.planData = planData;
          this.dialog2 = true;
        }, 100);
      } else {
        console.log('无操作');
      }
    },
    /**
     * 根据坐标确定培训安排取出数据
     * 收集学校信息:学校名(name);层次(level);需不需要派车(hascar);总名额;剩余名额
     * 期批信息;期数;批次;班级类型;开始时间;结束时间;批次总人数;批次剩余名额
     * 然后提供给修改组件取设置数值
     * @param {Number} ri 行位置
     * @param {Number} ci 列位置
     */
    toConfirmArrange(ri, ci) {
      // ri确定学校, ci确定期-批次
      const sheetName = this.getSheetName();
      const sheetDatas = this.xs.getData();
      const sheetData = sheetDatas.find(f => f.name === sheetName);
      if (!sheetName) return;
      const ct = this.classTypeList.find(f => f.name === sheetName);
      // 班级类型
      const classType = ct.name;
      // 先找行: 学校名 => 学校, 获取分配限制
      const schoolName = _.get(sheetData, `rows.${ri}.cells.1.text`);
      const school = this.schoolList.find(f => f.name === schoolName);
      if (!school) return;
      /**
       * 丰富:
       * 学校总名额: 根据当前班级类型,取出分配的总量即可
       * 剩余名额: 计算当前行所有安排的名额作为减数
       */
      const classnum = _.get(school, 'classnum', []);
      const thisTypeClass = classnum.find(f => f.code === ct.code);
      if (!thisTypeClass) return;
      // 总名额
      school.total = _.get(thisTypeClass, 'number');
      // 剩余名额
      const thisRow = _.get(sheetData, `rows.${ri}.cells`);
      if (!thisRow) return;
      const arrColsDataObject = _.omit(thisRow, this.ignoreCols);
      const arrColsDatas = [];
      for (const key in arrColsDataObject) {
        const e = arrColsDataObject[key];
        let number = _.get(e, 'text', 0);
        if (number) number = parseInt(number);
        arrColsDatas.push(number);
      }
      const et = arrColsDatas.reduce((p, n) => p + n, 0);
      school.elseNumber = school.total - et;
      /**
       * 确定批次,因为之后修改了向3,4,5行都注入了 term和batch,方便快速定位期批.
       * 所以固定取时间行(因为时间行完全不涉及合并问题,就是跟着批次走)
       */
      const row5SameColCell = this.xs.cell(4, ci, 0);
      const { term, batch } = row5SameColCell;
      const termnum = this.plan.termnum.find(f => f.term === term);
      if (!termnum) return;
      const batchnum = _.get(termnum, 'batchnum');
      if (!batchnum) return;
      const batchData = batchnum.find(f => f.batch === batch);
      const planData = {
        term,
        termid: _.get(termnum, '_id'),
        batch,
        batchid: _.get(batchData, '_id'),
        startdate: _.get(batchData, 'startdate'),
        enddate: _.get(batchData, 'enddate'),
        type: classType,
      };
      /**
       * 丰富:
       * 总名额: 通过行数据可以直接获取
       * 剩余名额: 需要算列总和
       */
      /**总名额 */
      const total = _.get(sheetData, `rows.5.cells.${ci}.text`);
      planData.total = total;
      /**计算列和 */
      let keys = Object.keys(sheetData.rows).map(i => parseInt(i));
      keys = keys.filter(f => f > this.arrangeStartRow);
      const schoolRowObject = _.pick(sheetData.rows, keys);
      const arr = [];
      for (const key in schoolRowObject) {
        const e = schoolRowObject[key];
        const obj = _.get(e, 'cells', {});
        if (!obj || Object.keys(obj).length < 0) continue;
        const tc = _.get(obj, `${ci}.text`, 0);
        arr.push(parseInt(tc));
      }
      const inNumber = arr.reduce((p, n) => p + n, 0);
      const elseNumber = total - inNumber;
      planData.elseNumber = elseNumber;
      return { schoolData: school, planData };
    },

    /**
     * 根据坐标确定计划期并取出数据
     * @param {Number} ri 行位置
     * @param {Number} ci 列位置
     */
    toConfirmPlan(ri, ci) {
      const excelDatas = this.xs.getData();
      const sheetName = this.getSheetName();
      const allData = excelDatas.find(f => f.name === sheetName);
      if (!allData) return;
      /**返回的结果变量 */
      let result;
      let batchIndex = 0;
      let term;
      // 不需要看行,只看列在哪,因为已经冻结了所有表格,无法修改.固定走第二行拿到期数,然后去计划中换数据进行修改
      const rowDataObj = allData.rows[1].cells;
      const keys = Object.keys(rowDataObj).map(i => parseInt(i));
      /**定位列分几种情况:
       * 1.该列没有合并单元格,那就能在key中直接找到
       * 2.该列有合并单元格,那就不一定能在里面直接找到:只有合并的起始单元格才能被直接找到,需要查看 点击的单元格 是否在 被合并的单元格中.
       */
      const hasKey = keys.includes(ci);
      if (hasKey) {
        // 直接找到的一定都是第一个单元格
        const cellData = rowDataObj[ci];
        term = _.get(cellData, 'text');
      } else {
        // 将所有列展开,检查是否在被合并的单元格中
        for (const key in rowDataObj) {
          // 无操作列不要
          if (this.ignoreCols.includes(parseInt(key))) continue;
          const cols = [parseInt(key)];
          const { merge = [], text } = rowDataObj[key];
          // 没有两行直接返回
          if (merge.length === 2) {
            const last = _.last(merge);
            if (last) {
              for (let i = 1; i <= last; i++) {
                cols.push(parseInt(i) + parseInt(key));
              }
            }
          }
          // 看下点击的列是否在这里面
          const res = cols.find(f => f === ci);
          if (res) {
            batchIndex = cols.findIndex(f => f === ci);
            term = text;
            break;
          }
        }
      }
      if (term && batchIndex >= 0) {
        const termnum = this.plan.termnum.find(f => f.term === term);
        if (termnum) {
          const batch = _.get(termnum, `batchnum.${batchIndex}`, {});
          result = { ...batch, term: _.get(termnum, 'term') };
        }
      }
      return result;
    },
    /**获取当前浏览的表格名 */
    getSheetName() {
      const sheetName = _.get(this.xs, 'sheet.data.name');
      return sheetName;
    },
    // #endregion

    // #region excel初始化数据整理
    /**
     * 设置excel中学校安排的数据
     * 根据arrange的type:班级类型,确定是普通班还是特殊班
     * 然后计算出该数据所在的横纵坐标,使用 this.xs.cellText(ri,ci,text,sheetIndex)
     */
    setSchPlan() {
      const list = this.schPlan.filter(f => f.arrange.length > 0);
      const datas = this.xs.getData();
      for (let si = 0; si < datas.length; si++) {
        const sheetData = datas[si];
        const name = _.get(sheetData, 'name');
        const ct = this.classTypeList.find(f => f.name === name);
        // 这里不能找到不到,如果找不到,那就中断别找了
        if (!ct) continue;
        const termRowObject = sheetData.rows[1].cells;
        const arrangeCols = _.omit(termRowObject, this.ignoreCols);
        const termRowKeys = Object.keys(arrangeCols);
        const termRowValues = Object.values(arrangeCols);
        for (const i of list) {
          const { arrange, schid } = i;
          const schIndex = this.schoolList.findIndex(f => f.code === schid);
          // 小于零就是没有该学校,那就不要处理
          if (schIndex < 0) continue;
          // 查看有没有当前要处理的班级类型的安排
          const thisTypeClassArrange = arrange.filter(f => f.type === ct.code);
          if (thisTypeClassArrange.length <= 0) continue;
          // 计算行位置
          const ri = this.arrangeStartRow + schIndex + 1;
          // 计算列位置: 先确定是普通班还是特殊班,然后
          for (const a of arrange) {
            const { term, batchid, number } = a;
            let ci;
            // term 确定列范围, batchid确定索引, 两个值之和就是列的位置
            const valIndex = termRowValues.findIndex(f => f.text === term);
            if (valIndex < 0) continue;
            const termStartPos = termRowKeys[valIndex];
            // 再确定批次索引
            const termnum = this.plan.termnum.find(f => f.term === term);
            if (!termnum) continue;
            const batchnum = _.get(termnum, 'batchnum');
            if (!batchnum) continue;
            const batchIndex = batchnum.findIndex(f => f._id === batchid);
            if (batchIndex >= 0) ci = batchIndex + parseInt(termStartPos);
            if (ci) {
              this.xs.cellText(ri, ci, number, si);
            }
          }
        }
      }
      this.xs.reRender();
    },

    /**
     * 整理学校数据
     * 从3456或378后开始都行,反正不影响计划
     * @param {Object} sheetData organizeLine3456返回的数据
     */
    organizeLineFrom9(sheetDatas) {
      for (const sheetData of sheetDatas) {
        const name = _.get(sheetData, 'name');
        const ct = this.classTypeList.find(f => f.name === name);
        // 这里不能找到不到,如果找不到,那就中断别找了
        if (!ct) continue;
        let rowKey = this.arrangeStartRow + 1;
        let dataIndex = 0;
        for (let i = 0; i < this.schoolList.length; i++) {
          const sch = this.schoolList[i];
          const clas = _.get(sch, 'classnum', []);
          const typeCla = clas.find(f => f.code === ct.code);
          if (!typeCla) continue;
          const num = _.get(typeCla, 'number', 0);
          if (num <= 0) continue;
          dataIndex = dataIndex + 1;
          const obj = {
            0: { text: dataIndex },
            1: { text: _.get(sch, 'name', '') },
            2: { text: num },
            3: { text: _.get(sch, 'address', '') },
          };
          sheetData.rows[rowKey] = { cells: obj };
          rowKey = rowKey + 1;
        }
      }
    },
    /**
     * 处理场地的合并及督导天数,人数行合并及内容
     * 场地合并: 相邻且相同场地合并;
     * 督导天数合并: 与场地合并一致;
     * 督导天数内容: 多个批次的 最晚结束时间 - 最早开始时间;
     * 督导人数合并: 和期数合并一致;
     * 督导人数内容: 几个场地几个人;
     * @param {Array<Object>} sheetDatas organizeLine3456返回的数据
     */
    organizeLine378(sheetDatas) {
      // 场地合并
      // 取出期数行
      for (const sheetData of sheetDatas) {
        const name = _.get(sheetData, 'name');
        const ct = this.classTypeList.find(f => f.name === name);
        // 这里不能找到不到,如果找不到,那就中断别找了
        if (!ct) continue;
        const termRowObject = sheetData.rows[1].cells;
        const termColsMappings = [];
        // 整理出期范围
        for (const key in termRowObject) {
          // 无操作列直接跳过
          if (this.ignoreCols.includes(parseInt(key))) continue;
          const termObject = termRowObject[key];
          const cols = [parseInt(key)];
          const { merge = [], text } = termObject;
          // 没有两行直接返回
          if (merge.length === 2) {
            const last = _.last(merge);
            if (last) {
              for (let i = 1; i <= last; i++) {
                cols.push(parseInt(i) + parseInt(key));
              }
            }
          }
          termColsMappings.push({ term: text, cols, merge, col: parseInt(key) });
        }

        // 先把督导人数行处理了
        const ddpersonNumRowObject = {};
        for (const i of termColsMappings) {
          const { term, merge = [], col } = i;
          const obj = { text: 0, merge };
          const r = this.plan.termnum.find(f => f.term === term);
          if (r) {
            const bm = _.get(r, 'batchnum', []);
            let batchnum = this.batchFilterByClassType(bm, ct.code);
            if (batchnum.length <= 0) continue;
            const placeList = _.uniq(_.compact(batchnum.map(i => _.get(i, 'place')).filter(f => f !== '')));
            obj.text = placeList.length;
          }
          ddpersonNumRowObject[col] = obj;
        }
        const old7Cells = _.get(sheetData, `rows.7.cells`, {});
        sheetData.rows[7] = { cells: { ...old7Cells, ...ddpersonNumRowObject } };
        const placeRowObject = sheetData.rows[2].cells;
        // 处理督导天数,再查相邻场地是否相同
        for (const tcm of termColsMappings) {
          const { cols } = tcm;
          const inSameTermDatas = _.pick(placeRowObject, cols);
          const keys = Object.keys(inSameTermDatas);
          for (const key in inSameTermDatas) {
            const cell = inSameTermDatas[key];
            const isMerged = _.get(cell, 'isMerged', false);
            if (isMerged) continue;
            const text = _.get(cell, 'text');
            const keyIndex = keys.findIndex(f => f === key);
            let nextKeyIndex = keyIndex + 1;
            //  需要确定当前场地和下一个场地是否一致.所以需要确定当前列是否是最后一列,如果是最后一列,就不需要处理了,因为没有后面.
            while (nextKeyIndex < keys.length) {
              const nextKey = keys[nextKeyIndex];
              const nextCell = inSameTermDatas[nextKey];
              const nextCellText = _.get(nextCell, 'text');
              if (text === nextCellText) {
                // 一致,主单元格写入合并参数
                const merge = _.get(inSameTermDatas[key], 'merge', [0, 0]);
                const newLast = _.last(merge) + 1;
                merge[merge.length - 1] = newLast;
                inSameTermDatas[key].merge = merge;
                // 当前单元格写入isMerged:true
                inSameTermDatas[nextKey].isMerged = true;
                nextKeyIndex = nextKeyIndex + 1;
                continue;
              }
              // 下一个单元格的场地与当前单元格场地不一致,不处理,直接跳过
              break;
            }
          }
        }
        // 删掉被合并的单元格
        for (const key in placeRowObject) {
          const value = placeRowObject[key];
          if (_.get(value, 'isMerged', false)) delete placeRowObject[key];
        }
        // 督导天数的合并同 场地行一致,内容由 时间行计算而来
        // 时间行数据
        const timeRowObject = sheetData.rows[4].cells;
        // 督导天数行
        let ddDaysRowObject = sheetData.rows[6].cells;
        const timeColsMappings = [];
        // 根据场地范围,再计算天数,整理出督导天数行
        for (const key in placeRowObject) {
          // 无操作列直接跳过
          if (this.ignoreCols.includes(parseInt(key))) continue;
          const object = placeRowObject[key];
          const cols = [parseInt(key)];
          const { merge = [] } = object;
          // 没有两行直接返回
          if (merge.length === 2) {
            const last = _.last(merge);
            if (last) {
              for (let i = 1; i <= last; i++) {
                cols.push(parseInt(i) + parseInt(key));
              }
            }
          }
          timeColsMappings.push({ cols, merge, col: parseInt(key) });
          const timeDataInPlaceRange = _.pick(timeRowObject, cols);
          let days = [];
          for (const key in timeDataInPlaceRange) {
            const cell = timeDataInPlaceRange[key];
            const text = _.get(cell, 'text');
            if (text === '') continue;
            let arr = text.split('-');
            if (arr.length <= 0) continue;
            arr = arr.map(i => this.excelDateToStringDate(i));
            days.push(...arr);
          }
          // 升序排序
          days = days.sort((a, b) => new Date(a).getTime() - new Date(b).getTime());
          const start = _.head(days);
          const last = _.last(days);
          const diff = moment(last).diff(start, 'd');
          const m = _.get(object, 'merge');
          const cell = { text: diff };
          if (m) cell.merge = m;
          ddDaysRowObject[key] = cell;
        }
      }
    },
    /**组织表头,3&4&5&6行的数据:场地,班级,时间,批次人数
     */
    organizeLine3456() {
      /**
       * 这几行主要都是由批次信息得来的
       * 场地(3),班级数(4),时间(5),批次人数(6)都是可以在批次中获取的.
       * 期数可以直接获取,但是合并单元格的长度是根据批次来的
       */
      const sheetDataTemplate = _.cloneDeep(this.sheetDefaultConfig);
      const result = [];
      // 根据班级类型开始生成对应excel的数据
      for (const ct of this.classTypeList) {
        const sheetData = _.cloneDeep(sheetDataTemplate);
        // 先给名字
        sheetData.name = ct.name;
        // 获取本excel的预设数据,之后的操作都在这上面搞
        let rows = sheetData.rows;
        // 对期进行循环
        const termnum = _.get(this.plan, 'termnum', []);
        for (const t of termnum) {
          const { batchnum: bm = [], term } = t;
          /**
           * 因为根据班级来分sheet,所以从最开始就把班级都分开
           * 根据batchnum下的classnum来决定,如果有1个班级符合当前的班级类型.
           * 那就留下这个班,这个批次,这个期
           * 如果1个符合条件的班级都没有,那就直接爆了这期
           */
          let batchnum = this.batchFilterByClassType(bm, ct.code);
          if (batchnum.length <= 0) continue;
          const { pos, merge } = this.termLineDeal(rows, term, batchnum);
          for (const b of batchnum) {
            const { place, class: cla = [], startdate, enddate } = b;
            // 场地行处理
            const p = this.placeList.find(f => f._id === place);
            let pstr = place;
            if (p) pstr = _.get(p, 'name');
            const params = { term, batch: b.batch };
            this.batchLineDeal(rows, 2, pstr, params);
            // 班级数处理
            const clanum = cla.length;
            this.batchLineDeal(rows, 3, clanum, params);
            // 时间处理
            const sStr = moment(startdate).format('M.D');
            const eStr = moment(enddate).format('M.D');
            const timeStr = `${sStr}-${eStr}`;
            this.batchLineDeal(rows, 4, timeStr, params);
            // 批次人数
            const batchPersonNumber = cla.reduce((p, n) => parseInt(p) + parseInt(_.get(n, 'number', 0)), 0);
            this.batchLineDeal(rows, 5, batchPersonNumber, params);
          }
        }
        result.unshift(sheetData);
      }

      return result;
    },
    /**
     * 针对批次的统一处理方式
     * @param {Object} rows excel的所有行数据
     * @param {String} linePos 行位置
     * @param {String} text 显示内容
     * @param {Object} params 额外参数{term,batch}
     */
    batchLineDeal(rows, linePos, text, params = {}) {
      // 获取行数据
      const row = _.get(rows, `${linePos}.cells`, {});
      // 计算单元格的起始位置
      const pos = this.computedPosition(row);
      const cell = { text, ...params };
      // 不需要计算合并
      row[pos] = cell;
    },
    /**
     * 设置期数行
     * @param {Object} rows excel的所有行数据
     * @param {String} term 期数
     * @param {Array} batchnum 批次
     */
    termLineDeal(rows, term, batchnum) {
      // 获取行数据
      const row = _.get(rows, `1.cells`, {});
      // 计算单元格的起始位置
      const pos = this.computedPosition(row);
      // 设置单元格的具体内容(显示内容和单元格合并的设置)
      const cell = { text: term };
      const blen = batchnum.length;
      if (blen > 0) cell.merge = [0, blen - 1];
      row[pos] = cell;
      return { pos, merge: cell.merge };
    },
    /**
     * 计算该单元格所在列的位置.
     * @param {Object} row 当前行
     */
    computedPosition(row) {
      // 获取最后单元格的位置
      const keys = Object.keys(row);
      const lastKey = _.last(keys);
      const last = row[lastKey];
      // 设置单元格的位置
      let pos = parseInt(lastKey) + 1;
      if (!_.isObject(last)) {
        console.error(`${pos}_${lastKey}解析错误-单元格设置不是object类型`);
        return;
      }
      const lastCellMerge = _.get(last, 'merge', []);
      if (lastCellMerge.length > 0) {
        // 说明有合并操作,需要将被合并的单元格留下来后,再继续添加
        // [r,c]: r是行数合并; c:列合并主要看c,需要往后多窜c个位置, pos + c
        const ec = _.last(lastCellMerge);
        pos = parseInt(pos) + parseInt(ec);
      }
      return pos;
    },
    /**
     * 根据班级类型过滤批次
     * @param {Array} batch 批次
     * @param {String} code 班级类型编码
     */
    batchFilterByClassType(batch, code) {
      let batchnum = [];
      for (const bnum of batch) {
        const { class: clas = [], ...others } = bnum;
        const r = clas.find(f => f.type === code);
        if (!r) continue;
        const thisTypeClass = clas.filter(f => f.type === code);
        const obj = { ...others, class: thisTypeClass };
        batchnum.push(obj);
      }
      return batchnum;
    },
    // #endregion

    /**关闭对话框 */
    toClose() {
      this.form = {};
      this.planData = {};
      this.schoolData = {};
    },
    /**打开新增日期安排的对话框 */
    toAddCol() {
      this.dialog = true;
    },
    /**
     * 拼接成正常时间: YYYY-MM-DD
     * @param {String} pointDate 带点的缩短时间 7.1
     */
    excelDateToStringDate(pointDate) {
      return moment(`${this.year}.${pointDate}`).format('YYYY-MM-DD');
    },

    /**获取excel数据 */
    getExcelData() {
      const data = this.xs.getData();
      console.log(data);
      const copy = this.xs.copy();
      console.log(copy);
      /**
       * 由期行 的位置与合并数量,可以确定一期有几批次,批次的场地,班级,
       * 批次还原的原则: 场地,时间; 如果场地和时间都一致还存在2条以上的数据,那就说明分配有问题.在一个场地一个时间只能有个1个批次
       * 所以根据场地和时间判断数据是否新添加的(其实判断是不是新数据并没什么用,要做的是把数据整理出来)
       * 根据时间和场地,与plan中的数据对比:存在-修改数据;不存在:创建数据
       */
    },
    /** 导出excel */
    exportExcel() {
      var new_wb = this.xtos(this.xs.getData());
      /* generate download */
      XLSX.writeFile(new_wb, `培训计划${new Date().getTime()}.xlsx`);
    },
    xtos(sdata) {
      console.log(sdata);
      var out = XLSX.utils.book_new();
      sdata.forEach(function(xws) {
        var aoa = [[]];
        var rowobj = xws.rows;
        for (var ri = 0; ri < rowobj.len; ++ri) {
          var row = rowobj[ri];
          if (!row) continue;
          aoa[ri] = [];
          Object.keys(row.cells).forEach(function(k) {
            var idx = +k;
            if (isNaN(idx)) return;
            aoa[ri][idx] = row.cells[k].text;
          });
        }
        var ws = XLSX.utils.aoa_to_sheet(aoa);

        /** 读取在线中的合并单元格，并写入导出的数据中
               * merges: Array(19)
                  0: "A16:P16"
                  1: "A17:P17"
                  2: "O2:P2"
                  3: "F2:G2"
               */
        ws['!merges'] = [];
        xws.merges.forEach(merge => {
          ws['!merges'].push(XLSX.utils.decode_range(merge));
        });

        XLSX.utils.book_append_sheet(out, ws, xws.name);
      });
      return out;
    },
  },
  metaInfo() {
    return { title: this.$route.meta.title };
  },
};
</script>

<style lang="less" scoped>
.sheetContainerbox {
  width: 100%;
  height: 80vh;
}
</style>