eta_front/src/views/dataEntry_manage/onlineExcelCopy.vue

<template>
  <div class="excel-data-container" v-loading="saveLoading" 
    :element-loading-text="$t('Edb.save_ing')" element-loading-spinner="el-icon-loading"
    element-loading-background="rgba(0, 0, 0, 0.8)">
    <div class="online-excel">
      <div id="luckysheet"></div>
    </div>
    <div class="data-options-box">
      <div class="data-options-buttons">
        <el-button type="primary" style="width: 80px;font-size: 16px;" @click="saveExcel"><!-- 保存 -->{{$t('Dialog.confirm_save_btn')}}</el-button>
        <el-button plain style="width: 80px;font-size: 16px;" @click="cancel"><!-- 取消 -->{{$t('Dialog.cancel_btn')}}</el-button>
      </div>
      <div class="data-options" >
        <el-form :model="dataForm" label-position="top" ref="dataOptions">
          <el-form-item :label="$t('ManualEntryPage.label_one_level_classify')" prop="firstClassify" :rules="[{required:true,message:'品种不能为空',trigger:'change'}]">
            <el-select v-model="dataForm.firstClassify" placeholder="请选择品种" style="width: 100%;" @change="changeFirstClassify">
              <el-option :label="item.ClassifyName" :value="item.ClassifyId" v-for="item in classifyArray" :key="item.ClassifyId"></el-option>
            </el-select>
          </el-form-item>
          <el-form-item :label="$t('ManualEntryPage.label_edb_classify')" prop="secondClassify" :rules="[{required:true,message:'子品种不能为空',trigger:'change'}]">
            <el-select v-model="dataForm.secondClassify" placeholder="请选择子品种" style="width: 100%;" @change="changeSecondClassify">
              <el-option :label="item.ClassifyName" :value="item.ClassifyId" v-for="item in secondClassifyArray" :key="item.ClassifyId"></el-option>
            </el-select>
          </el-form-item>
          <el-form-item :label="$t('Edb.Detail.e_fre')" prop="frequency" :rules="[{required:true,message:'频度不能为空',trigger:'change'}]">
            <el-select v-model="dataForm.frequency" placeholder="请选择频度" style="width: 100%;" @change="changeFrequency">
              <el-option :label="item.label" :value="item.value" v-for="item in frequencyList" :key="item.value"></el-option>
            </el-select>
          </el-form-item>
        </el-form>
        <el-tooltip :open-delay="400">
          <div slot="content" v-html="$t('ManualEntryPage.use_msg')">
            
          </div>
          <div class="instruction-hint" >
            <span><!-- 使用说明 -->{{$t('ManualEntryPage.use_intro')}}</span>
            <i class="el-icon-question" style="color: #999;"></i>
          </div>
        </el-tooltip>
      </div>
    </div>
  </div>
</template>
<script>

import { dataInterence } from "@/api/api.js";
import EventBus from '@/api/bus.js';
import {frequencySelectList} from '@/utils/defaultOptions';

export default {
  name:'onlineExcelCopy',
  data() {
    // 受保护的数据 维护一个二维数组 用于还原不能被修改或者以错误方式修改的数据
    this.protectedData=[]

    // 初始化数据
    this.options={
      container:'luckysheet',
      lang: this.$i18nt.locale,
      gridKey:'handmade-excel',
      showinfobar:false,
      row:300,
      column:60,
      data:[
        {
          "name":"test",
          "celldata":[
            {
              "r":0,
              "c":0,
              "v":{
                ct: {fa: "@", t: "s"},
                m:"一级分类",
                v:"一级分类",
                bg:'#b4a7d6'
              },
            },
            {
              "r":1,
              "c":0,
              "v":{
                ct: {fa: "@", t: "s"},
                m:"指标分类",
                v:"指标分类",
                bg:'#b4a7d6'
              },
            },
            {
              "r":2,
              "c":0,
              "v":{
                ct: {fa: "@", t: "s"},
                m:"频度",
                v:"频度",
                bg:'#b4a7d6'
              },
            },
            {
              "r":3,
              "c":0,
              "v":{
                ct: {fa: "@", t: "s"},
                m:"指标名称",
                v:"指标名称",
                bg:'#b4a7d6'
              },
            },
            {
              "r":4,
              "c":0,
              "v":{
                ct: {fa: "@", t: "s"},
                m:"单位",
                v:"单位",
                bg:'#b4a7d6'
              },
            },
          ],
          "config":{
            "borderInfo":[{
              "rangeType": "range",
              "borderType": "border-all",
              "style": "1",
              "color": "#fff",
              "range": [{
                  "row": [0,4],
                  "column": [0,0]
              }]
            }],
          },
          "frozen":{
            type: 'rangeBoth',
            range: {row_focus: 4, column_focus: 0}
          },
          "dataVerification":{}
        }
      ],
      hook:{},
      showsheetbar:false
    }

    return {
      dataForm:{
        firstClassify:'',
        secondClassify:'',
        frequency:'',
        tradeCode:''
      },
      copyDataForm:{},
      firstClassName:'',
      secondClassName:'',
      //frequencyList: ['日度','周度','旬度','月度','季度','半年度','年度'],
      classifyArray:[],
      unitList:[],
      secondClassifyArray:[],
      // 保存时候的加载框
      saveLoading:false,
      isEdit:"false",
      refreshed:false,
      haveSaveProtected:false,
      hintMsg:`
        1、新增指标：在预设好的指标列下输入指标名称、单位、数值并保存；</br>
        2、新增日期：在第一列选择日期或插入日期行(需复制已有日期格式)输入数值并保存；</br>
        3、清除指标：清空该指标下所有数据并保存，该操作不会删除指标，仅清除指标数据，请勿直接删除指标列；</br>
        4、删除日期：清空该日期对应所有数据并保存，请勿直接删除日期行；</br>
        5、指标名称编辑、单位编辑、频度编辑、指标删除等操作请在“手工指标列表”中完成；</br>
      `
    }
  },
  computed:{
    frequencyList(){
        return frequencySelectList()
    }
  },
created() {
    // this.dataForm.firstClassify = parseInt(this.$route.query.parentClassifyId) || ''
    // this.dataForm.secondClassify = parseInt(this.$route.query.ClassifyId) || ''
    // this.dataForm.frequency = this.$route.query.Frequency || ''
    // this.dataForm.tradeCode = this.$route.query.TradeCode || ''
    // this.dataForm.tradeName = this.$route.query.TradeName || ''
    // this.copyDataForm = JSON.parse(JSON.stringify(this.dataForm))
    // // 字符串
    // this.isEdit = this.$route.query.isEdit.toString()
    this.initExcelOptions()
    // 监听折叠导航栏事件
    EventBus.$off('collapseHandle')
    EventBus.$on('collapseHandle',(params)=>{
      luckysheet.destroy()
      luckysheet.create(this.options)
    })
  },
  beforeRouteLeave(to, from, next) {
    luckysheet.destroy()
    next();
  },
  // 监听浏览器返回事件
  mounted() {
    window.addEventListener('popstate',this.setCache)
  },
  unmounted() {
    window.removeEventListener('popstate',this.setCache)
  },
  methods: {
    async initExcelOptions(){
      // 获取单位接口
      await this.getTargetUnitList()
      // 获取分类
      await this.getClassify()
      let dataList = []
      // 编辑才请求数据
      if(this.dataForm.secondClassify && this.isEdit == "true"){
        let res = await dataInterence.getTargetList({
            Frequency: this.dataForm.frequency,
            ClassifyId: this.dataForm.secondClassify,
            TradeCode:this.dataForm.tradeCode,
            EdbShowType: this.dataForm.tradeCode?0:1,
        })
        if(res.Ret == 200){
          dataList = res.Data.List || []
        }
      }

      // hook设置
      // 处理复制粘贴造成的漏洞 - 记录数据
      this.options.hook.cellAllRenderBefore=(data,sheet)=>{
        if(this.haveSaveProtected) return 
        if(!this.protectedData.length>0){
          for (let i = 0; i < 5; i++) {
            if([0,1,2].includes(i)){
              for (let j = 0; j < 2; j++) {
                const element = data[i][j];
                if(!Array.isArray(this.protectedData[i])) this.protectedData[i]=[]
                this.protectedData[i][j] = element
              }
            }else{
              for (let k = 0; k <= dataList.length; k++) {
                const element = data[i][k];
                if(!Array.isArray(this.protectedData[i])) this.protectedData[i]=[]
                this.protectedData[i][k] = element
              }
            }
          }
        }
        this.haveSaveProtected=true
      }
      this.options.hook.cellUpdateBefore=(r,c)=>{
        let edbNameEle = luckysheet.getSheetData()[3][c]
        // 禁止更新的单元格 已经有的指标名称和单位不允许更新
        if(([0,1,2].includes(r) && c==0) 
          || ([3,4].includes(r) && edbNameEle && this.protectedData[3].some(data => data.v == edbNameEle.v))){
          return false
        }
      }

      this.options.hook.cellMousedownBefore=(e,position)=>{
        let edbNameEle = luckysheet.getSheetData()[3][position.c]
        // 禁止鼠标选中的单元格
        if([0,1,2].includes(position.r) 
          || ([3,4].includes(position.r) && edbNameEle && this.protectedData[3].some(data => data.v == edbNameEle.v))){
          return false
        }
      }

      // 处理复制粘贴造成的漏洞 - 还原数据 PS：这个钩子函数只有在单元格在可是区域内才会执行
      this.options.hook.cellRenderBefore=(cell,position,sheet,ctx)=>{
        // 清除两边空格
        if(cell && cell.m && typeof(cell.m)=='string'){
          cell.m = cell.m.trim()  
        }
        if(cell && cell.v && typeof(cell.v)=='string'){
          cell.v = cell.v.trim()  
        }
        /**
         * 从excel整个单元格复制，到这里进入编辑模式的粘贴，出来个cell.ct.s，不知道啥东西，官网也没写
         * 看似有内容，实则保存没内容，因为cell.m和cell.v 都是空。直接清掉了
         */
        if(cell && cell.ct && cell.ct.s){
          cell.ct.s='' 
        }
        
        // 是否更改了受保护的数据
        let haveUpdatedProtectedData=false
        if([3,4].includes(position.r)){
          if(!cell) return 
          let index = this.protectedData[3].findIndex((pd,ind) => pd && ind==position.c)
          if(index ==-1) return 
          Object.keys(this.protectedData[position.r][index]).forEach(key => {
            if(!_.isEqual(cell[key], this.protectedData[position.r][index][key])){
              haveUpdatedProtectedData=true
              cell[key] = this.protectedData[position.r][index][key]
            }
          }); 
        }else if([0,1,2].includes(position.r)){
          let cindex = position.c>1?1:position.c
          if(!cell){
            if(cindex<1){
              return 
            }
            sheet.data[position.r][position.c] = this.protectedData[position.r][1]
          }else{
            Object.keys(this.protectedData[position.r][cindex]).forEach(key => {
              if(!_.isEqual(cell[key], this.protectedData[position.r][cindex][key])){
                haveUpdatedProtectedData=true
                cell[key] = this.protectedData[position.r][cindex][key]
              }
            });
            if(position.c>0){
              if(position.r==0){
                if(cell.m !==this.firstClassName || cell.v !== this.firstClassName){
                  haveUpdatedProtectedData=true
                  cell.m = cell.v = this.firstClassName
                }
              }else if(position.r==1){
                if(cell.m !==this.secondClassName || cell.v !== this.secondClassName){
                  haveUpdatedProtectedData=true
                  cell.m = cell.v = this.secondClassName
                }
              }else{
                if(cell.m !==this.dataForm.frequency || cell.v !== this.dataForm.frequency){
                  haveUpdatedProtectedData=true
                  cell.m = cell.v = this.dataForm.frequency
                }
              }
            }
          }
        }
        if(haveUpdatedProtectedData){
          // 还原数据后的手动刷新
          this.refreshManual()
        }
      }

      this.options.hook.updated=(operation)=>{
        // 维护 protectedData数据
        if(operation.type == 'delRC'){
          // 删除列
          if(operation.ctrlValue.rc == 'c'){
            // console.log('删除列',`第${operation.ctrlValue.index+1}列开始`,`删除了${operation.ctrlValue.len}列`);
            if(!operation.data){
              // 没有data为撤销 恢复他
              for (let i = operation.ctrlValue.index; i < operation.ctrlValue.index+operation.ctrlValue.len; i++) {
                // 在celldata初始数据里面找，有找到说明不是新加的
                let isFind = luckysheet.getSheet().celldata.some(cd => {
                  return cd.r==3 && cd.c < operation.ctrlValue.index+operation.ctrlValue.len+1 &&
                         cd.v && cd.v.v == operation.curdata[3][i].v
                })
                this.protectedData[3].splice(i,0,isFind?operation.curdata[3][i]:false)
                this.protectedData[4].splice(i,0,isFind?operation.curdata[4][i]:false)
              }
            }else{
              for (let i = operation.ctrlValue.index; i < operation.ctrlValue.index+operation.ctrlValue.len; i++) {
                if(operation.ctrlValue.index > this.protectedData[3].length){
                  break;
                }
                this.protectedData[3].splice(operation.ctrlValue.index,1)
                this.protectedData[4].splice(operation.ctrlValue.index,1)
              }
            }
          }
        }else if(operation.type == 'addRC' && operation.ctrlValue.rc == 'c'){
          // 增加列
          let addIndex = operation.ctrlValue.index
          if(operation.ctrlValue.direction=='lefttop'){
            // 向左加
            addIndex--
          }
          // 比原本的protectedData数组长度大，就不会对protectedData造成形象 就return
          if(!(addIndex < this.protectedData[3].length)) return 
          // console.log('增加列',`第${addIndex+1}列开始向右`,`增加了${operation.ctrlValue.len}列，${operation.data?"正向操作":"撤销"}`);

          if(!operation.data){
            // 没有data为撤销 删除它
            for (let i = addIndex; i < addIndex+operation.ctrlValue.len; i++) {
              this.protectedData[3].splice(addIndex+1,1)
              this.protectedData[4].splice(addIndex+1,1)
            }
          }else{
            for (let i = addIndex; i < addIndex+operation.ctrlValue.len; i++) {
              // 新加的列，只占个位置，保持索引相对，不需要保护，置为false
              this.protectedData[3].splice(i+1,0,false)
              this.protectedData[4].splice(i+1,0,false)
            }
          }
        }
      }

      // 拿出所有数组
      let arr = dataList.map((item => item.DataList)).flat()
      // 拿出所有数组的日期并展平排序
      let dateArr = [...new Set(arr.map((item) => item.Dt))].sort().reverse()
      // 组合成需要的数据格式
      let excelData=dateArr.map(date =>{
        let objList = [date]
        dataList.forEach(element => {
          let obj = element.DataList.find((time) => time.Dt === date)
          objList.push(obj?obj.Close : '')
        });
        return objList
      })
      /**
       * 当数据的长度大于默认的300时，行数需要设置成数据的长度+15
       */
      this.options.row = dateArr.length>(this.options.row-15) ? (dateArr.length+15):this.options.row
      /**
       * 设置列数 
       * 当数据的长度大于默认的60时，列数需要设置成数据的长度+10
       */
      this.options.column = dataList.length>(this.options.column-10) ? (dataList.length+10):this.options.column
      let row = this.options.row
      let col = this.options.column
      // 初始化数据
      dataList.map((item,index) =>{
        this.options.data[0].celldata.push(
          {
            "r":3,
            "c":1+index,
            "v":{
              ct: {fa: "@", t: "s"},
              m:item.SecName,
              v:item.SecName,
              bg:'#cccccc'
            }
          },
          {
            "r":4,
            "c":1+index,
            "v":{
              ct: {fa: "@", t: "s"},
              m:item.Unit,
              v:item.Unit,
              bg:'#cccccc'
            }
        })
      })
      for (let i = 0; i < col; i++) {
        this.options.data[0].celldata.push(
        {
          "r":0,
          "c":1+i,
          "v":{
            ct: {fa: "@", t: "s"},
            m:this.firstClassName || '',
            v:this.firstClassName || '',
            bg:'#cccccc'
          }
        },
        {
          "r":1,
          "c":1+i,
          "v":{
            ct: {fa: "@", t: "s"},
            m:this.secondClassName || '',
            v:this.secondClassName || '',
            bg:'#cccccc'
          }
        },
        {
          "r":2,
          "c":1+i,
          "v":{
            ct: {fa: "@", t: "s"},
            m:this.dataForm.frequency || '',
            v:this.dataForm.frequency || '',
            bg:'#cccccc'
          }
        })
      }
      for (let i = 0; i < row-5; i++) {
        for (let j = 0; j < (col+1); j++) {
          this.options.data[0].celldata.push(
          {
            "r":5+i,
            "c":0+j,
            "v":{
              ct: j==0?{fa: "yyyy-MM-dd", t: "d"}:{fa: "0.0000", t: "n"},
              m:excelData[i]?excelData[i][j]:'',
              v:excelData[i]?excelData[i][j]:'',
            }
          })
        }
      }
      // // 设置日期单元格颜色
      this.options.data[0].celldata.forEach(element => {
        if(element.c==0 && (![0,1,2,3,4].includes(element.r))){
          element.v.bg = '#ff9900'
        }
      });
      // 设置边框颜色
      this.options.data[0].config.borderInfo.push({
        "rangeType": "range",
        "borderType": "border-all",
        "style": "1",
        "color": "#fff",
        "range": [{
            "row": [0,2],
            "column": [1,col]
        }]
      },{
        "rangeType": "range",
        "borderType": "border-all",
        "style": "1",
        "color": "#fff",
        "range": [{
            "row": [5,row],
            "column": [0,0]
        }]
      },{
        "rangeType": "range",
        "borderType": "border-all",
        "style": "1",
        "color": "#fff",
        "range": [{
            "row": [3,4],
            "column": [1,dataList.length]
        }]
      })
      // 设置数据验证
      for (let i = 0; i < col; i++) {
        this.options.data[0].dataVerification[`4_${1+i}`]={
          "type": "dropdown",
          "type2": null,
          "value1": this.unitList.join(','),
          "value2": "",
          "prohibitInput": false,
        }
      }
      this.$nextTick(()=>{
        luckysheet.create(this.options)
      })
    },
    refreshManual: _.debounce(function() {
      if(!this.refreshed) {
        luckysheet.refresh()
        this.refreshed=true
        setTimeout(()=>{
          this.refreshed=false
        },1500)
      }
		},1000),
    // 获取分类
    async getClassify() {
      let res=await dataInterence.getClassify()
      if(res.Ret===200){
        this.classifyArray = res.Data.List || []
        
        this.dataForm.firstClassify = this.classifyArray[0].ClassifyId;
        this.firstClassName = this.classifyArray[0].ClassifyName
        this.secondClassifyArray = this.classifyArray[0] ? this.classifyArray[0].Child : []
        if(this.secondClassifyArray.length>0){
          this.dataForm.secondClassify = this.secondClassifyArray[0].ClassifyId;
          this.secondClassName = this.secondClassifyArray[0].ClassifyName
        }
      }
    },
    // 获取指标单位
		async getTargetUnitList(){
			let res=await dataInterence.getTargetUnitList()
			if(res.Ret===200){
        this.unitList = res.Data || []
			}
		},
    // 切换选择一级分类
    changeFirstClassify(id){
      let col = this.options.column
      let item = this.classifyArray.find(item => item.ClassifyId == id)
      this.firstClassName = item.ClassifyName
      // 指标分类数组
      this.secondClassifyArray = item.Child || []
      if(this.secondClassifyArray.length >0){
        // 默认选择第一个指标分类
        this.dataForm.secondClassify = this.secondClassifyArray[0].ClassifyId
        this.secondClassName = this.secondClassifyArray[0].ClassifyName
      }
      luckysheet.refresh()
    },
    // 切换选择二级分类
    changeSecondClassify(id){
      let col = this.options.column
      let item = this.secondClassifyArray.find(item => item.ClassifyId == id)
      this.secondClassName=item.ClassifyName
      luckysheet.refresh()
    },
    // 切换选择频度
    changeFrequency(value){
      luckysheet.refresh()
    },
    saveExcel(){
      this.$refs.dataOptions.validate((valid)=>{
        if(valid){
          this.saveLoading=true
          let data = luckysheet.getAllSheets()
          for (let i = 0; i < data[0].data.length; i++) {
            // 清除日期列的两边的空格，防止保存时日期变成0000-00-00
            const element = data[0].data[i][0];
            if(element && element.v && typeof(element.v)=='string'){
              element.v = element.v.trim()  
            }
            if(element && element.m && typeof(element.m)=='string'){
              element.m = element.m.trim()  
            }
          }
          dataInterence.saveOnlineExcel({...data[0],ClassifyId:this.dataForm.secondClassify}).then(res=>{
          if(res.Ret == 200) {
            this.setCache('submit')
            this.$router.push({path:'/dataList'})
          }
          }).finally(()=>{
            this.saveLoading=false
          })
        }
      })
    },
    cancel(){
      this.setCache()
      this.$router.back()
    },
    // 设置sessionStorage缓存
    setCache(type = 'cancel'){
      // 提交
      let params={}
      if(type == 'submit'){
        params = this.dataForm
      }else{
        params = this.copyDataForm
      }
      let query={
        tradeCode:params.tradeCode,
        secondClassify:params.secondClassify,
        frequency:params.frequency,
        firstClassify:params.firstClassify,
        tradeName:params.tradeName
      }
      sessionStorage.setItem('onlineExcelBack',JSON.stringify(query))
    }
  },
}
</script>
<style lang="scss" scoped>
  .excel-data-container{
    display: flex;
    height: calc(100vh - 120px);
    .online-excel{
      flex-grow: 1;
      height: 100%;
      padding: 10px;
      border: 1px solid #ECECEC;
      box-sizing: border-box;
      #luckysheet{
        height: 100%;
        width: 100%;
        margin: 0;
        padding: 0;
      }
    }
    .data-options-box{
      min-width: 270px;
      height: 100%;
      margin-left: 20px;
      width: 270px;
      box-sizing: border-box;
      .data-options-buttons{
        border: 1px solid #ECECEC;
        box-sizing: border-box;
        padding: 20px 15px;
      }
      .data-options{
        padding: 25px 15px;
        box-sizing: border-box;
        .instruction-hint{
          display: inline-flex;
          align-items: center;
          cursor: pointer;
          span{
            font-size: 16px;
            margin-right: 4px;
          }
        }
      }
    }
  }
  .hint-message{
    font-size: 14px;
    line-height: 22px;
  }
</style>
<style lang="scss">
  // .online-excel{

  // }
  .data-options{
    .el-form--label-top .el-form-item__label{
      line-height: 16px;
      color: #333333;
      font-size: 16px;
    }
  } 
  .luckysheet .toolbar {
    background: none;
    margin: 0;
    padding: 0;
  }
</style>