[双剑合璧] SpreadJ+GcExcel 应对大量公式计算场景的解决方案（下）

大家好，本期为大家带来该系列的最后一期（终于结束了）关于该方案的功能扩展以及性能测试，没有看过前面几期的小伙伴可以点击下方链接跳转观看。
SpreadJ+GcExcel 应对大量公式计算场景的解决方案（上）
SpreadJ+GcExcel 应对大量公式计算场景的解决方案（中）

那么，开始我们本期的内容，前两期中我们介绍了应对大量公式计算场景的解决方案，本次我们首先分析一下之前的解决方案还有什么能够改进的地方。
从之前的方案俩看，公式的计算发生在切换sheet的时候会从后端获取到对应的sheet然后再前端展示，这样做公式的计算会在切换sheet的时候自动更新。
原本SpreadJS公式自动计算的功能，现在为了向性能妥协，我们关闭了这个功能。所以这个时候当我们输入一个新的公式，或者去修改已有公式引用导致公式引用时，公式是不会进行计算或重算的。
对于这样的情况我们可以手动设置一个强制计算的按钮，点击该按钮，会强制在后端GcExcel中进行当前结果的公式计算，并将计算后的结果在返回给前端进行更新。效果类似于Excel的“开始计算功能”。

用户需要查看计算结果时可以手动点击该按钮，进行公式计算。这样可以避免前端无意义的公式计算，利用后端GcExcel的高性能进行公式计算，从而达到最合理的资源分配。
例如下面的代码：

1. document.getElementById("calc").onclick = function(){
   
2.                     var currentSheet = spread.getActiveSheet();
   
3.                     var spreadDirtyCells = {};
   
4.                     for(var i=0;i<spread.getSheetCount();i++){
   
5.                             var sheet = spread.getSheet(i);
   
6.                             var sheetName = sheet.name();
   
7.                             spreadDirtyCells[sheetName] = sheet.getDirtyCells();
   
8.                            
   
9.                     }
   
10.                     var calc = {
    
11.                                     currentSheet:currentSheet.name(),
    
12.                                 dirtyCells:spreadDirtyCells
    
13.                 }
    
14.                     
    
15.                     $.ajax({
    
16.                             url: "getCalcResult",
    
17.                             type:"POST",
    
18.                             data:JSON.stringify(calc),
    
19.                             contentType: 'application/json',
    
20.                             async:false,
    
21.                             success: function (data) {
    
22.                                     if (data != null) {
    
23.                             var json = ungzipString(data);
    
24.                             json = JSON.parse(json);
    
25.                             currentSheet.fromJSON(json);
    
26.                         }
    
27.                             }
    
28.                     });
    
29.                     
    
30.             }

复制代码

前端在点击计算后获取当前sheet中所有的dirtyCells并传给后端，后端拿到dirtyCells之后在GcExcel解析并以此填入

1. @RequestMapping(value = "/getCalcResult", method = RequestMethod.POST)
   
2.         @ResponseBody
   
3.         public String getCalcResult(@RequestBody Calc calc) {
   
4.                 IWorksheet currentSheet = workbook.getWorksheets().get(calc.getCurrentSheet());
   
5.                 Map<String,Object> map = calc.getDirtyCells();
   
6.                 for (Map.Entry<String, Object> entry : map.entrySet()) {
   
7.                         String key = entry.getKey();
   
8.                         IWorksheet dirtySheet = workbook.getWorksheets().get(key);
   
9.                         List<Map<String,Object>> sheetDirtys = (List<Map<String, Object>>) entry.getValue();
   
10.                         for(int i=0;i<sheetDirtys.size();i++) {
    
11.                                 Map<String,Object> dirtyCell = sheetDirtys.get(i);
    
12.                                 int row = (int) dirtyCell.get("row");
    
13.                                 int col = (int) dirtyCell.get("col");
    
14.                                 dirtySheet.getRange(row, col).setValue(dirtyCell.get("newValue"));
    
15.                         }
    
16.                 }
    
17.                 workbook.setEnableCalculation(true);
    
18.                 String result = null;
    
19.                 if(currentSheet!=null) {
    
20.                         result = currentSheet.toJson();
    
21.                         result = GZip.compress(result);        
    
22.                 }
    
23.                 return result;
    
24.         }

复制代码

之后设置workbook.setEnableCalculation(true);让整个workbook重新计算一遍以获取正确的结果，最后sheet.toJson()将结果返回给前端。

上述做法有多大的性能提升，相信小伙伴们目前都很好奇想知道，现在我们做一个测试：
我们选取了一个测试文件，该文件算是比较复杂的，其中有很多复杂的公式。(测试文件会在下方提供）
首先我们用SpreadJS直接导入该文件，测试一下花费的时间：我这边测试机器需要花费大约12S左右。

之后我们用前后端结合的方式来跑一下，花费不超过1S钟

当我们修改"INPUT"这个sheet中的填报内容，然后切换到"OUTPUT"sheet中查看结果
SpreadJS在每一步操作后都会有较为明显的卡顿，总共花费大约10S左右。

前后端结合每一步操作后不会存在卡顿，总共花费不到1S。


效果提升大约是单纯使用SpreadJS的10倍。

当然有得必有失，弊端是无法做到SpreadJS的公式那样进行实时计算，只能通过切换sheet或者手动点击计算按钮进行计算。总体来说对于存在大量公式的应用场景还是利大于弊的。

最后兑现之前所说的惊喜，那就是会在附件中附上本次方案的相关代码及测试文件。

该说不说，有点东西