BomWeb/src/pages/Detail/LuckySheet.js

import React from 'react';
import { Button, message } from 'antd';
import exportExcel, { getExcelBolob } from '@/utils/exportExcl';
import LuckyExcel from 'luckyexcel';
import { getToken, GetTokenFromUrl } from '@/utils/utils';
import GoalSeek from '@/utils/GoalSeek';

const hintText = '禁止编辑！请先点击编辑按钮。';
const DIFF_COLOR = '#ff0000';
const ADD_COLOR = '#00ff00';

class LuckySheet extends React.Component {
  constructor(props) {
    super(props);
    this.sheetRef = React.createRef();
    this.luckysheet = null;
    this.updateTimer = null;
    this.currentSheetIndex = null;
    this.updateCell = {
      add: [],
      diff: [],
    };
    this.renderTimer = null;
    this.chars = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'.split('');
  }
  componentWillUnmount() {
    this.luckysheet?.destroy();
  }
  componentDidUpdate(prveProps) {
    const prevVersion = prveProps.version || {};
    const curVersion = this.props.version || {};
    if (prevVersion?.id != curVersion?.id || prevVersion?.flow_id != curVersion.flow_id) {
      console.log(prevVersion, curVersion);
      this.renderSheet();
    }
  }

  getUUID(len = 8, radix = 16) {
    var chars = this.chars;
    var uuid = [],
      i;
    radix = radix || chars.length;

    if (len) {
      // Compact form
      for (i = 0; i < len; i++) uuid[i] = chars[0 | (Math.random() * radix)];
    } else {
      // rfc4122, version 4 form
      var r;

      // rfc4122 requires these characters
      uuid[8] = uuid[13] = uuid[18] = uuid[23] = '-';
      uuid[14] = '4';

      // Fill in random data.  At i==19 set the high bits of clock sequence as
      // per rfc4122, sec. 4.1.5
      for (i = 0; i < 36; i++) {
        if (!uuid[i]) {
          r = 0 | (Math.random() * 16);
          uuid[i] = chars[i == 19 ? (r & 0x3) | 0x8 : r];
        }
      }
    }

    return uuid.join('');
  }
  renderSheet(currentData) {
    const {
      onClickCell,
      version,
      getUser,
      onUpdate,
      templateId,
      onDelSheet,
      permissions,
      node,
    } = this.props;
    const data = currentData || this.props.data;
    const _this = this;
    if (!this.luckysheet) {
      clearTimeout(this.renderTimer);
      this.renderTimer = setTimeout(() => {
        this.renderSheet(currentData);
      }, 300);
      return;
    }
    let token = GetTokenFromUrl() || getToken();
    let option = {
      lang: 'zh',
      showinfobar: false,
      showstatisticBar: false,
      // forceCalculation: true,
      hook: {
        cellMousedown: (cell, position, sheet) => {
          console.log(cell, position, sheet);
          onClickCell && onClickCell(cell, position, sheet);
        },
        cellPasteBefore: cell => {
          console.log(cell);
          if (!cell) return;
          if (cell.cid) delete cell.cid;
          if (cell.bg == DIFF_COLOR || cell.bg == ADD_COLOR) {
            delete cell.bg;
          }
        },
        updated(operate) {
          if (operate.type == 'datachange') {
            if (_this.currentSheetIndex != operate.sheetIndex) {
              _this.currentSheetIndex = operate.sheetIndex;
              return;
            }
            // 延迟1秒
            clearTimeout(_this.updateTimer);
            _this.updateTimer = setTimeout(() => {
              onUpdate.bind(_this);
              onUpdate();
            }, 1000);
          }
        },
        // 修改批注后保存sheet
        commentUpdateAfter() {
          clearTimeout(_this.updateTimer);
          _this.updateTimer = setTimeout(() => {
            onUpdate.bind(_this);
            onUpdate(true);
          }, 1000);
        },
        sheetActivate: sheet => {
          console.log(sheet);
          setTimeout(() => {
            this.luckysheet.setCellFormat(0, 0, 'bg', '#fff');
          }, 100);
        },
        sheetDeleteAfter: sheet => {
          onDelSheet && onDelSheet(sheet?.sheet.id);
        },
        sheetActivate: sheet => {
          console.log(sheet);
          setTimeout(() => {
            this.luckysheet.setCellFormat(0, 0, 'bg', '#fff');
          }, 100);
        },
        workbookCreateAfter: options => {
          setTimeout(() => {
            this.luckysheet.refreshFormula();
          }, 800);
        },
      },
    };
    if (version) {
      const wsUrl =
        process.env.NODE_ENV == 'development'
          ? 'ws://47.96.12.136:8896/'
          : `ws://${location.host}/`;
      option = {
        ...option,
        allowUpdate: true,
        gridKey: version.id,
        templateId: templateId,
        // flowId: version.flow_id,
        loadUrl: `/api/v1/purchase/record/sheet?gridKey=${version.id}&JWT-TOKEN=${token}`,
        updateUrl: wsUrl + `api/v1/ws?id=${version.id}&sid=${templateId}&JWT-TOKEN=${token}`,
        // updateUrl: `ws://47.96.12.136:8896/api/v1/ws?id=${version.id}&sid=${templateId}&JWT-TOKEN=${token}`,
        // updateUrl: `ws://120.55.44.4:8896/api/v1/ws?id=${version.id}&sid=${templateId}&JWT-TOKEN=${token}`,
        authorityUrl: `/api/v1/purchase/bom/user/excel/col?depId=${localStorage.depId ||
          0}&JWT-TOKEN=${token}`,
        getUser,
        permissions,
        // workbookCreateBefore(luckysheet) {
        //   console.log('===============================', luckysheet);
        //   let oldConfig = JSON.parse(JSON.stringify(luckysheet.getConfig()));
        //   setTimeout(() => {
        //     luckysheet.setConfig({
        //       ...oldConfig,
        //       authority: { sheet: true, hintText },
        //     });
        //   }, 300);
        // },
      };
      console.log(version);

      const unableEdit = option => {
        option.showtoolbar = false;
        option.enableAddRow = false;
        option.sheetFormulaBar = false;
        option.enableAddBackTop = false;
        option.showsheetbarConfig = {
          add: false,
          menu: false,
          // sheet: false,
        };
        option.cellRightClickConfig = {
          copy: false, // 复制
          copyAs: false, // 复制为
          paste: false, // 粘贴
          insertRow: false, // 插入行
          insertColumn: false, // 插入列
          deleteRow: false, // 删除选中行
          deleteColumn: false, // 删除选中列
          deleteCell: false, // 删除单元格
          hideRow: false, // 隐藏选中行和显示选中行
          hideColumn: false, // 隐藏选中列和显示选中列
          rowHeight: false, // 行高
          columnWidth: false, // 列宽
          clear: false, // 清除内容
          matrix: false, // 矩阵操作选区
          sort: false, // 排序选区
          filter: false, // 筛选选区
          chart: false, // 图表生成
          image: false, // 插入图片
          link: false, // 插入链接
          data: false, // 数据验证
          cellFormat: false, // 设置单元格格式
        };
      };

      if (version.flow_id && node?.node_type_psr != 6) {
        option.authority = {
          sheet: true,
          hintText: '当前处于审批节点，禁止编辑！',
        };
        unableEdit(option);
      } else if (version.last_version) {
        option.authority = {
          sheet: true,
          hintText: '该清单已设置为最终版本，禁止编辑！',
        };
        unableEdit(option);
      } else if (
        node?.node_type_psr != 6 &&
        ((version.audit_status != 0 && version.audit_status != 5) || version.status == 1)
      ) {
        option.authority = {
          sheet: true,
          hintText: '当前清单不可编辑！',
        };
        unableEdit(option);
      } else if (node?.node_type_psr == 1 || node?.node_type_psr == 2 || node?.node_type_psr == 5) {
        option.authority = {
          sheet: true,
          hintText: '流程已完结，不可编辑！',
        };
        unableEdit(option);
      }
    } else if (data && data.length > 0) {
      option.data = JSON.parse(JSON.stringify(data));

      option.data.forEach(item => {
        if (item.celldata) {
          item.celldata.forEach(cell => {
            // 生成uuid
            if (!cell.v.cid) cell.v.cid = this.getUUID();
          });
        }
        // 默认禁止编辑
        // item.config.authority = { sheet: true, hintText };
      });
    } else {
      // 默认sheet页数据
      data.data = [
        {
          name: 'sheet1',
          // config: {
          //   authority: { sheet: true, hintText },
          // },
        },
      ];
    }

    this.luckysheet.destroy();
    this.luckysheet.create(option);
    // 比对模式会导致单元格出现[Object object]的情况  任意编辑后才会正常显示
    // 所以默认设置第一个单元格的背景色
    setTimeout(() => {
      this.luckysheet.setCellFormat(0, 0, 'bg', '#fff');
      this.luckysheet.refreshFormula();
    }, 500);
  }

  // componentDidUpdate(prevProps) {
  //   const { data } = this.props;
  //   if (prevProps.data != data) {
  //     this.renderSheet(data);
  //   }
  // }

  handleLoad() {
    const { version } = this.props;
    let contentWindow = this.sheetRef.current.contentWindow;
    this.luckysheet = contentWindow.luckysheet;
    // this.luckysheet = this.luckysheet;
    // version存在  则需调用render
    if (version) {
      this.renderSheet();
    }
    // onLoad && onLoad();
  }

  selectCell(row, col, order) {
    this.luckysheet.setRangeShow({ row: [row, row], column: [col, col] }, { order });
  }

  toggleSheet(order) {
    this.luckysheet.setSheetActive(order);
  }

  getSheetJson() {
    let data = JSON.parse(JSON.stringify(this.luckysheet.toJson()));
    data.data.forEach(sheet => {
      let allCell = {},
        unknowCid = [];
      // 将cell以cid为界分别存储
      (sheet.celldata || []).forEach(cell => {
        if (!cell.v.cid) {
          unknowCid.push(cell);
        } else if (!allCell[cell.v.cid]) {
          allCell[cell.v.cid] = cell;
        } else {
          // 当存在相同cid时
          // 做异常处理
          delete cell.v.cid;
          unknowCid.push(cell);
        }
        if (cell.v.tb) cell.v.tb = Number(cell.v.tb);
        // 清除比对样式
        if (cell.v.bg == DIFF_COLOR || cell.v.bg == ADD_COLOR) {
          delete cell.v.bg;
        }
      });

      unknowCid.forEach(cell => {
        // 根据坐标生成唯一key，重复则增加后缀直至不重复
        let key = `${cell.r}-${cell.c}`;
        while (allCell[key]) {
          key += '|c';
        }
        cell.v.cid = key;
        allCell[key] = cell;
      });
      sheet.celldata = Object.values(allCell);
    });

    return data;
  }

  // 切换编辑状态
  toggleEdit(edit) {
    let luckysheet = this.luckysheet;
    if (edit) {
      let config = luckysheet.getConfig();
      luckysheet.setConfig({
        ...config,
        authority: { sheet: !edit, hintText },
      });
    } else {
      luckysheet.exitEditMode();
    }
  }

  // 切换比对状态
  toggleCompare(isCompare, compareData, callback) {
    let luckysheet = this.luckysheet;
    let diff = [];
    let add = [];
    const { onCompareSuccess } = this.props;
    // 判断dom是否加载完成
    if (!luckysheet) {
      setTimeout(() => {
        this.toggleCompare(isCompare, compareData, callback);
      }, 300);
      return;
    }
    if (isCompare) {
      // let currentData = this.luckysheet.toJson();
      let currentData = JSON.parse(JSON.stringify(this.props.data));
      currentData.forEach((sheet, index) => {
        let celldata1 = sheet.celldata;
        let celldata2 = compareData[index]?.celldata || [];

        celldata1.forEach(item => {
          // 不判断空字符串
          if (this.isEmpty(item)) return;
          var d2Item = celldata2.find(item2 => item2.v.cid == item.v.cid);
          if (d2Item && !this.isEmpty(d2Item)) {
            // v.ct.s相同,不做处理
            if (item.v.ct?.s && JSON.stringify(item.v.ct?.s) == JSON.stringify(d2Item.v.ct?.s))
              return;
            // v.v相同,不做处理
            if (d2Item.v.v == item.v.v) return;
            // 内容不同,标记diff颜色
            diff.push({
              ...item,
              sheetOrder: index,
            });
            item.v.bg = DIFF_COLOR;
            // luckysheet.setCellFormat(item.r, item.c, 'bg', DIFF_COLOR);
          } else {
            // 找不到同cid的单元格,标记add颜色
            add.push({
              ...item,
              sheetOrder: index,
            });
            item.v.bg = ADD_COLOR;
            // luckysheet.setCellFormat(item.r, item.c, 'bg', ADD_COLOR);
          }
        });
      });
      console.log(currentData);
      this.renderSheet(currentData);
      // luckysheet.refresh()
    } else {
      this.renderSheet(this.props.data);
    }
    this.updateCell = {
      diff,
      add,
    };
    callback && callback(this.updateCell);
  }
  isEmpty(item) {
    return (item?.v?.v ?? '') === '' && !item?.v?.ct?.s;
  }
  mergeExcl(updateCell = {}) {
    const { diff = [], add = [] } = updateCell;
    let currentData = this.luckysheet.toJson().data;
    let luckysheet = this.luckysheet;
    console.log(updateCell);
    diff.forEach(item => {
      let sheet = currentData[item.sheetOrder];
      let d1Item = sheet.celldata.find(item2 => item2.v.cid == item.v.cid);
      // 将差异项覆盖至当前文档
      d1Item.v = {
        ...item.v,
        bg: undefined,
      };
    });
    add.forEach(item => {
      // 将新增项添加至当前文档
      let sheet = currentData[item.sheetOrder];
      let d1Item = sheet.celldata.find(item2 => item2.r == item.r && item2.c == item.c);
      if (d1Item) {
        d1Item.v = {
          ...item.v,
          bg: undefined,
        };
      } else {
        sheet.celldata.push({
          ...item,
          sheetOrder: undefined,
        });
      }
    });
    currentData.forEach(sheet => {
      delete sheet.data;
    });
    this.renderSheet(currentData);
    // currentData.data.forEach((sheet, index) => {
    //   if (!mergeData[index]) return;
    //   let celldata1 = sheet.celldata;
    //   let celldata2 = mergeData[index].celldata;
    //   celldata2.forEach(item => {
    //     let bg = item.v?.bg;
    //     let d1Item;
    //     if (bg == DIFF_COLOR) {
    //       delete item.v.bg;
    //       d1Item = celldata1.find(item2 => item2.v.cid == item.v.cid);
    //       // 将差异项覆盖至当前文档
    //       d1Item.v = item.v;
    //       // luckysheet.setCellValue(d1Item.r, d1Item.c, item.v);
    //     } else if (bg == ADD_COLOR) {
    //       delete item.v.bg;
    //       // 将新增项添加至当前文档
    //       // luckysheet.setCellValue(item.r, item.c, item.v);
    //       d1Item = celldata1.find(item2 => item2.r == item.r && item2.c == item.c);
    //       if (d1Item) {
    //         d1Item.v = item.v;
    //       } else {
    //         celldata1.push(item);
    //       }
    //     }
    //   });
    // });
    // this.renderSheet(currentData);
  }

  /**
   * 导入excl
   * @param {*} files input:file的evt.target.files
   * @returns
   */
  uploadExcel(files, callback) {
    if (files == null || files.length == 0) {
      return;
    }

    let name = files[0].name;
    let suffixArr = name.split('.'),
      suffix = suffixArr[suffixArr.length - 1];
    if (suffix != 'xlsx') {
      alert('Currently only supports the import of xlsx files');
      message.error('只支持xlsx格式的文件!');
      return;
    }
    LuckyExcel.transformExcelToLucky(files[0], (exportJson, luckysheetfile) => {
      if (exportJson.sheets == null || exportJson.sheets.length == 0) {
        message.error('读取xlsx文件失败!');
        return;
      }
      // this.luckysheet.destroy();
      // 同步当前文档内容
      let data = this.props.data;
      exportJson.sheets.forEach((sheet, index) => {
        if (!data || !data[index]) return;
        sheet.celldata.forEach(cell => {
          if (this.isEmpty(cell)) return;
          // return (item.v.v ?? '') === '' && !item.v.ct?.s;
          let dCell = (data[index].celldata || []).find(dCell => {
            return dCell.r == cell.r && dCell.c == cell.c;
          });
          if (this.isEmpty(dCell)) return;
          // 判断v.ct是否相同
          // if (cell?.v?.ct?.s && dCell.v.ct?.s && cell.v.ct?.s.join('') != dCell.v.ct?.s.join('')) return;
          if (cell?.v?.ct?.s && dCell.v.ct?.s) {
            if (cell.v.ct?.s.join('') != dCell.v.ct?.s.join('')) return;
            let cellS = cell.v.ct.s;
            let dCellS = dCell.v.ct.s;
            let isEqul = cellS.every((cur, idx) => {
              return JSON.stringify(cur) === JSON.stringify(dCellS[idx]);
            });
            if (!isEqul) return;
          }
          // 判断v.v是否相同
          if (cell?.v?.v && dCell.v.v != cell.v.v) return;
          // 内容相同则复制cid
          cell.cid = dCell.cid;
        });
      });
      this.renderSheet(exportJson.sheets);
      callback && callback();
    });
  }
  // 根据url导入excl
  // selectExcel(item) {
  //   const {value,name} = item

  //   if (value == '') {
  //     return;
  //   }
  //   LuckyExcel.transformExcelToLuckyByUrl(value, name, (exportJson, luckysheetfile) => {
  //     if (exportJson.sheets == null || exportJson.sheets.length == 0) {
  //       alert(
  //         'Failed to read the content of the excel file, currently does not support xls files!'
  //       );
  //       return;
  //     }

  //     this.luckysheet.destroy();

  //     this.luckysheet.create({
  //       container: 'luckysheet', //luckysheet is the container id
  //       showinfobar: false,
  //       data: exportJson.sheets,
  //       title: exportJson.info.name,
  //       userInfo: exportJson.info.name.creator,
  //     });
  //   });
  // }

  getExcelData(checkValue = null) {
    let resultList = [];
    console.log(this.luckysheet.getAllSheets());

    let currentData = this.luckysheet.getAllSheets();
    currentData.forEach(sheet => {
      let data = sheet.data;
      let celldata = sheet.celldata;
      let colList = [];
      data[0]?.forEach((rowOneItem, colIdx) => {
        if (rowOneItem) {
          if (!checkValue || checkValue.indexOf(rowOneItem.cid) !== -1) {
            colList.indexOf(colIdx) == -1 ? colList.push(colIdx) : true;
          }
        }
      });

      const newData = [];
      data.forEach(item => {
        if (item !== null) {
          let arr = item.filter((cur, idx) => {
            return item && colList.includes(idx);
          });
          newData.push(arr);
        }
      });
      sheet.data = newData;

      //消除空列后都列下标
      let newColIdxList = colList.map((cur, idx) => {
        return idx;
      });
      //处理celldata
      const newCellData = [];
      celldata.forEach(item => {
        let idx = colList.indexOf(item.c);
        if (idx !== -1) {
          item.c = newColIdxList[idx];
          newCellData.push(item);
        }
      });
      sheet.celldata = newCellData;
    });

    return currentData;
  }

  getExcelBolb() {
    let currentData = this.getExcelData();
    return getExcelBolob(currentData);
  }

  downloadExcel(checkValue) {
    let currentData = this.getExcelData(checkValue);
    exportExcel(currentData, '下载');
  }
  // 获取批注
  getComment() {
    let sheets = this.luckysheet.toJson().data;
    let comment = [];
    sheets.forEach(sheet => {
      sheet.celldata.forEach(cell => {
        // 判断是否含有批注
        if (cell?.v?.ps?.value) {
          comment.push({
            sheet: sheet.name,
            r: cell.r,
            c: cell.c,
            value: cell.v.ps.value || '',
          });
        }
      });
    });
    return comment;
  }

  async goalSeek(type, goal, setting) {
    const luckysheet = this.luckysheet;
    const sheet = this.luckysheet.getSheet({
      name: '毛利概算',
    });
    const order = sheet.order;

    try {
      let defaultValue = luckysheet.getCellValue(9, 2, {
        order,
      });
      const result = await GoalSeek({
        goal,
        fn: fn,
        fnParams: [defaultValue, sheet.data, type],
        maxIterations: 1000,
        independentVariableIdx: 0,
        ...setting,
      });
      luckysheet.setCellValue(9, 2, result, {
        order,
      });
      console.log(result);
      luckysheet.refreshFormula();
      message.success('计算成功');
    } catch (error) {
      message.error('计算失败');
      console.log(error);
    }
  }

  render() {
    return (
      <iframe
        onLoad={e => {
          this.handleLoad(e);
        }}
        ref={this.sheetRef}
        src="/luckysheet.html"
      ></iframe>
    );
  }
}
function fn(C10, data, type) {
  let C50 = data[49][2].v || 0;
  let C49 = data[48][2].v || 0;
  let C20 = data[19][2].v || 0;
  let C21 = data[20][2].v || 0;
  let C9 = data[8][2].v || 0;
  let C15 = data[14][2].v || 0;
  let C16 = data[15][2].v || 0;
  let C17 = data[16][2].v || 0;
  let C18 = data[17][2].v || 0;
  let C19 = data[18][2].v || 0;
  let C51 = data[50][2].v || 0;

  let G42 = data[41][6].v || 0;
  let G12 = data[11][6].v || 0;
  let G13 = data[12][6].v || 0;
  let G7 = data[6][6].v || 0;
  let G8 = data[7][6].v || 0;
  switch (type) {
    case 1:
      // 净利率
      return C3() / C59();
    case 2:
      // 贡献毛利率
      return C4() / C59();
    case 3:
      // 合同总价
      return C59();
  }

  function C59() {
    return C58() + C57() + C56() + C54();
  }

  function C4() {
    return C3() + C50 + C49 + C48() + G42 + C43();
  }
  function C48() {
    return C59() * G12;
  }
  function C58() {
    return (C56() + C57()) * 0.12;
  }
  function C57() {
    return (((C20 + C21) * C10) / (1 + G7)) * G7 - ((C20 + C21) / (1 + G8)) * G8;
  }
  function C56() {
    return (
      (((C15 + C16 + C17 + C18 + C19) * C10) / (1 + G7)) * G7 -
      ((C15 + C16 + C17 + C18 + C19) / (1 + G7)) * G7
    );
  }
  function C54() {
    return SUM(2, [14, 20]) * C10;
  }

  function C3() {
    return C55() - C53();
  }
  function C55() {
    return C59() / (1 + G7);
  }
  function C53() {
    return C52() - ((SUM(2, [14, 18]) / (1 + G7)) * G7 + (SUM(2, [19, 20]) / (1 + G8)) * G8);
  }
  function C52() {
    return SUM(2, [14, 50]);
  }
  function C43() {
    return C59() * G13;
  }
  function C44() {
    return C59() * C9;
  }

  function SUM(x, [y1, y2]) {
    let total = 0;
    for (let i = y1; i <= y2; i++) {
      const item = data[i][x];
      if (!isNaN(item.v)) {
        total += item.v;
      }
    }
    return total;
  }
}
export default LuckySheet;