YangHuOperation/web/maintain/price_info_summary/js/summarySheet.js

function setAlign(sheet, headers) {
  const fuc = () => {
    headers.forEach(({ hAlign, vAlign }, index) => {
      sheetCommonObj.setAreaAlign(sheet.getRange(-1, index, -1, 1), hAlign, vAlign)
    });
  };
  sheetCommonObj.renderSheetFunc(sheet, fuc);
}

function setFormatter(sheet, headers) {
  const fuc = () => {
    headers.forEach(({ formatter }, index) => {
      if (formatter) {
        sheet.setFormatter(-1, index, formatter);
      }
    });
  };
  sheetCommonObj.renderSheetFunc(sheet, fuc);
}

function initSheet(dom, setting) {
  const workBook = sheetCommonObj.buildSheet(dom, setting);
  const sheet = workBook.getSheet(0);
  setAlign(sheet, setting.header);
  setFormatter(sheet, setting.header);
  return workBook;
}

function showData(sheet, data, headers, emptyRows) {
  /* const style = new GC.Spread.Sheets.Style();
  style.wordWrap = true; */
  const fuc = () => {
    sheet.setRowCount(data.length);
    data.forEach((item, row) => {
      headers.forEach(({ dataCode }, col) => {
        //sheet.setStyle(row, col, style, GC.Spread.Sheets.SheetArea.viewport);
        sheet.setValue(row, col, item[dataCode] || '');
      });
      sheet.autoFitRow(row);
    });
    if (emptyRows) {
      sheet.addRows(data.length, emptyRows);
    }
    //sheet.autoFitRow(0);
  };
  sheetCommonObj.renderSheetFunc(sheet, fuc);
}

// 获取当前表中行数据
function getRowData(sheet, row, headers) {
  const item = {};
  headers.forEach(({ dataCode }, index) => {
    const value = sheet.getValue(row, index) || '';
    if (value) {
      item[dataCode] = value;
    }
  });
  return item;
}

// 获取表数据和缓存数据的不同数据
function getRowDiffData(curRowData, cacheRowData, headers) {
  let item = null;
  headers.forEach(({ dataCode }) => {
    const curValue = curRowData[dataCode];
    const cacheValue = cacheRowData[dataCode];
    if (!cacheValue && !curValue) {
      return;
    }
    if (cacheValue !== curValue) {
      if (!item) {
        item = {};
      }
      item[dataCode] = curValue || '';
    }
  });
  return item;
}

const UpdateType = {
  UPDATE: 'update',
  DELETE: 'delete',
  CREATE: 'create',
};

const TIME_OUT = 20000;

const SUMMARY_BOOK = (() => {
  const locked = lockUtil.getLocked();
  const setting = {
    header: [
      { headerName: '主从对应码', headerWidth: 200, dataCode: 'code', dataType: 'String', hAlign: 'left', vAlign: 'center', formatter: "@" },
      { headerName: '别名编码', headerWidth: 100, dataCode: 'classCode', dataType: 'String', hAlign: 'left', vAlign: 'center', formatter: "@" },
      { headerName: '计算式', headerWidth: 100, dataCode: 'expString', dataType: 'String', hAlign: 'left', vAlign: 'center' },
      { headerName: '材料名称', headerWidth: 350, dataCode: 'name', dataType: 'String', hAlign: 'left', vAlign: 'center' },
      { headerName: '规格型号', headerWidth: 200, dataCode: 'specs', dataType: 'String', hAlign: 'left', vAlign: 'center' },
      { headerName: '单位', headerWidth: 80, dataCode: 'unit', dataType: 'String', hAlign: 'center', vAlign: 'center' },
    ],
  };
  // 初始化表格
  const workBook = initSheet($('#summary-spread')[0], setting);
  workBook.options.allowUserDragDrop = true;
  workBook.options.allowUserDragFill = true;
  lockUtil.lockSpreads([workBook], locked);
  const sheet = workBook.getSheet(0);

  // 当前数据缓存
  const cache = [];
  // 清空
  function clear() {
    cache.length = 0;
    sheet.setRowCount(0);
  }

  let loading = false;
  // 当前页面数据总量
  let totalCount = 0;
  // 当前页数
  let curPage = 0;

  // 搜索内容
  let searchStr = '';

  // 加载分页数据
  const loadPageData = async (page) => {
    curPage = page;
    loading = true;
    const data = await ajaxPost('/priceInfoSummary/getPagingData', { page, searchStr, pageSize: 100 }, TIME_OUT);
    totalCount = data.totalCount;
    cache.push(...data.items);
    showData(sheet, cache, setting.header, 5);
    loading = false;
  }

  // 搜索
  const handleSearch = (val) => {
    if (val) {
      // 处理特殊字符，否则正则搜不到
      searchStr = val
        .replace(/\\/g, '\\\\')
        .replace(/\[/g, '\\[')
        .replace(/\]/g, '\\]')
        .replace(/\(/g, '\\(')
        .replace(/\)/g, '\\)')
        .replace(/\+/g, '\\+')
        .replace(/\?/g, '\\?')
        .replace(/\*/g, '\\*')
        .replace(/\$/g, '\\$')
        .replace(/\^/g, '\\^')
    } else {
      searchStr = '';
    }
    clear();
    loadPageData(0);
  }

  // 清除前后空格
  const handleTrim = async () => {
    try {
      $.bootstrapLoading.progressStart('清除前后空格', true);
      $("#progress_modal_body").text('正在清除前后空格，请稍后...');
      await ajaxPost('/priceInfoSummary/trim', {}, 1000 * 60 * 5);
      setTimeout(() => {
        $.bootstrapLoading.progressEnd();
        window.location.reload()
      }, 1000)
    } catch (error) {
      console.log(error);
      $.bootstrapLoading.progressEnd();
    }
  }


  // 无限滚动加载
  const onTopRowChanged = (sender, args) => {
    const bottomRow = args.sheet.getViewportBottomRow(1);
    console.log(cache.length, totalCount, loading, cache.length - 1, bottomRow)
    if (cache.length >= totalCount || loading) {
      return;
    }
    if (cache.length - 1 <= bottomRow) {
      loadPageData(curPage + 1, searchStr);
    }
  }
  sheet.bind(GC.Spread.Sheets.Events.TopRowChanged, _.debounce(onTopRowChanged, 100));

  // 编辑处理
  async function handleEdit(changedCells) {
    $.bootstrapLoading.start();
    const postData = []; // 请求用
    // 更新缓存用
    const updateData = [];
    const deleteData = [];
    const insertData = [];
    try {
      changedCells.forEach(({ row }) => {
        if (cache[row]) {
          const rowData = getRowData(sheet, row, setting.header);
          if (Object.keys(rowData).length) { // 还有数据，更新
            const diffData = getRowDiffData(rowData, cache[row], setting.header);
            if (diffData) {
              postData.push({ type: UpdateType.UPDATE, ID: cache[row].ID, data: diffData });
              updateData.push({ row, data: diffData });
            }
          } else { // 该行无数据了，删除
            postData.push({ type: UpdateType.DELETE, ID: cache[row].ID });
            deleteData.push(cache[row]);
          }
        } else { // 新增
          const rowData = getRowData(sheet, row, setting.header);
          if (Object.keys(rowData).length) {
            rowData.ID = uuid.v1();
            postData.push({ type: UpdateType.CREATE, data: rowData });
            insertData.push(rowData);
          }
        }
      });
      if (postData.length) {
        await ajaxPost('/priceInfoSummary/editSummaryData', { postData }, 1000 * 60 * 2);
        // 更新缓存，先更新然后删除，最后再新增，防止先新增后缓存数据的下标与更新、删除数据的下标对应不上
        updateData.forEach(item => {
          Object.assign(cache[item.row], item.data);
        });
        deleteData.forEach(item => {
          const index = cache.indexOf(item);
          if (index >= 0) {
            cache.splice(index, 1);
          }
        });
        insertData.forEach(item => cache.push(item));
        if (deleteData.length || insertData.length) {
          showData(sheet, cache, setting.header, 5);
        }
      }
    } catch (err) {
      // 恢复各单元格数据
      showData(sheet, cache, setting.header, 5);
    }
    $.bootstrapLoading.end();
  }
  sheet.bind(GC.Spread.Sheets.Events.ValueChanged, function (e, info) {
    const changedCells = [{ row: info.row }];
    handleEdit(changedCells);
  });
  sheet.bind(GC.Spread.Sheets.Events.RangeChanged, function (e, info) {
    const changedRows = [];
    let preRow;
    info.changedCells.forEach(({ row }) => {
      if (row !== preRow) {
        changedRows.push({ row });
      }
      preRow = row;
    });
    handleEdit(changedRows);
  });

  const initData = async () => {
    try {
      $.bootstrapLoading.start();
      await loadPageData(0);
    } catch (error) {
      console.log(error);
      alert(error.message);
    }
    $.bootstrapLoading.end();
  }

  initData();

  return {
    sheet,
    handleSearch,
    handleTrim,
  }
})()