SpreadJS结合GcExcel解决跨表公式#ref的问题

需求背景是这样的：
用户的Excel表格非常大，里面的公式非常多。并且存在着相互引用的关系。于是用户用GcExcel在服务端负责大文件Excel的完整加载。然后将每个sheet单独tojson序列化出来，返回给前端。前端用SpreadJS fromjson 单独加载这个sheet。每当用户切换sheet时，在重复上述操作。

看似这样的方案能够解决大文件加载的问题，但实际测试的时候却遇到了这么一件事情，用户的某个sheet中存在跨sheet的公式引用。当用户做上述操作打开这个sheet之后，发现所有的跨sheet引用都变成了#ref。

原因也很明显，就是因为用户是单个sheet加载，所以跨sheet引用相关的sheet没有加载进来。所以公式的计算结果变成了#ref。用户的业务逻辑中，跨sheet的公式不会做任何计算，但是要显示成之前计算的结果，并且双击之后还会有公式显示出来。其他公式也需要正常设置和计算。

针对此种情况，我们进行了分析，首先公式计算功能是不能被关闭的，如果公式计算功能关闭了就不符合双击之后还会有公式显示与其他公式也需要正常设置和计算这辆点需求了，但如果正常计算又会出现#ref。最终，想到的办法就是利用GcExcel在服务器端强大的处理功能，在Excel文件加载之后首先对该文件做一次预处理。
这里的预处理主要做以下几件事情：
1.按前端获取的sheet页对指定的sheet做预处理分析，对该sheet上所有的公式进行分类。找出其中跨sheet引用的相关公式。
2.将上面找到的公式的值与公式表达式存在该单元格所在的tag中。
3.在sheet级别的tag中存储存在跨sheet公式引用所有单元格的位置，这样方便在前端反序列化之后能够快速的定位这些sheet。减少前端循环遍历的操作。

1. JSONObject worksheetTag = new JSONObject();
   
2.                 JSONArray worksheetFomulaCells = new JSONArray();
   
3.                 //模板预处理
   
4.                 IRange usedRange = worksheet.getUsedRange(EnumSet.of(UsedRangeType.Data));
   
5.                 for(int i = usedRange.getRow(); i < usedRange.getLastRow() + 1; i++) {
   
6.     for(int j = usedRange.getColumn(); j < usedRange.getLastColumn() + 1; j++) {
   
7.         if(worksheet.getRange(i,j).getFormula() != null && !"".equals(worksheet.getRange(i,j).getFormula())) {
   
8.             var formula = worksheet.getRange(i,j).getFormula();
   
9.             //判断是否是跨sheet公式引用
   
10.             var formulaArr = formula.split("!");
    
11.             if(formulaArr.length > 1) {
    
12.                 JSONObject cellTag = new JSONObject();
    
13.                 cellTag.put("value", worksheet.getRange(i,j).getValue());
    
14.                 cellTag.put("formula", formula);
    
15.                 worksheet.getRange(i,j).setTag(cellTag);
    
16.                 JSONObject worksheetFomulaCell = new JSONObject();
    
17.                 worksheetFomulaCell.put("row", i);
    
18.                 worksheetFomulaCell.put("col", j);
    
19.                 worksheetFomulaCells.add(worksheetFomulaCell);
    
20.             }
    
21.         }
    
22.     }
    
23.                 }
    
24.                 worksheetTag.put("cells", worksheetFomulaCells);
    
25.                 worksheet.setTag(worksheetTag);

复制代码

上述代码通过worksheet.getUsedRange(EnumSet.of(UsedRangeType.Data));获取数据在sheet中所在的范围，然后遍历该范围，找出其中所有带公式的单元格，然后取出公式通过 "!" 来判断是否存在跨sheet的引用（这里的判断还不是特别的严谨，因为带有"!"的也可以是同一个sheet中的公式，只要前半部分跟当前sheet的名称相同就可以了。如果再严谨一些，还可以取出"!"前半部分后，拿到sheet名称跟当前sheet名称进行比较）。然后将公式和值都取出来，放到tag里保存。再将该单元格的row，col信息存在sheet的tag中。

后续到了前端sheet tojson之后，前端SpreadJS用fromjson 反序列化。后续就很简单了，首先先去sheet的tag。里面存储着所有跨sheet公式引用的信息。然后依次遍历将里面公式替换为空，value替换为每个单元格tag中存储的value。

1. var tag = JSON.parse(sheet.tag());
   
2. var cells = tag.cells;
   
3. if(cells != null && cells.length > 0){
   
4.     for(var i = 0; i < cells.length; i++){
   
5.         var tag = JSON.parse(sheet.getTag(cells[i].row,cells[i].col));
   
6.         sheet.setFormula(cells[i].row,cells[i].col,null);
   
7.         sheet.setValue(cells[i].row,cells[i].col,tag.value);
   
8.     }
   
9. }

复制代码

在这之后，所有的跨sheet引用公式将不再显示#ref，而是服务端正常计算后的结果。接下来我们需要做的就是完成双击之后还会有公式显示出来的效果。对应这样的需求，我们利用SpreadJS的事件功能，监听EditStarting与EditEnding事件来完成。

1. var cellTag;
   
2. sheet.bind(GC.Spread.Sheets.Events.EditStarting, function (sender, args) {
   
3.     cellTag = sheet.getTag(args.row,args.col)!=null?JSON.parse(sheet.getTag(args.row,args.col)):null;
   
4.     if(cellTag != null){
   
5.         sheet.setValue(args.row,args.col,cellTag.formula);
   
6.     }
   
7. });
   
8. sheet.bind(GC.Spread.Sheets.Events.EditEnding, function (sender, args) {
   
9.     if(cellTag != null){
   
10.         setTimeout(function(){
    
11.             sheet.setValue(args.row,args.col,cellTag.value);
    
12.         },0)
    
13.     }
    
14. });

复制代码

当双击进入编辑状态时，就会触发EditStarting事件，在事件中通过setValue对单元格重新赋值，将跨worksheet的公式当做value赋值到单元格中。同样当退出编辑状态时，会触发EditEnding事件，这个时候将原本计算的结果重新赋值回来。这里有一点要注意由于事件的时机不同，在EditEnding的事件中需要设置setTimeout来调整代码的执行顺序。
此处也可以进行扩展，如果想要实现真正的跨worksheet计算，也可以在此处发送请求，将结果传递到GcExcel，利用GcExcel的计算来实时获取最新的计算结果。