全栈表格技术轻松应对复杂公式计算场景（二）：代码篇

代码篇

葡萄城类Excel全栈解决方案可以帮助开发者应对复杂公式的计算场景，该方案以纯前端表格控件SpreadJS为视图层，用于在浏览器中直接编辑表格数据；以服务端表格组件GcExcel为后端逻辑层，用于在Java、.NET等平台下批量处理 Excel 文档，二者结合可使Web应用程序在前后端负载均衡的系统结构下，满足在线文档数据同步、在线填报、批量导出与打印，以及类 Excel 报表模板设计等场景开发需求。

用全栈表格技术实现复杂公式计算场景的代码逻辑：

1、利用 GcExcel在后端读取xlsx文件，并借助Excel自动重算的特性，可以在第一次读取文件后关闭GcExcel的计算引擎以节省一次重计算的时间。注意：在借助GcExcel读取文件的时候尽量用流进行读取，因为流的效率要比字符串高效的多。

workbook.open(SpreadController.class.getClassLoader().getResourceAsStream("Excel/Wicked.xlsx"),options);
workbook.setEnableCalculation(false);

2、在读取文件的同时，查询并获取所有Sheet工作表的个数、名称，以及工作表显示/隐藏的状态。

List<Sheet> sheetNameList = new ArrayList<Sheet>();
for(int i=0;i<workbook.getWorksheets().getCount();i++) {
  Sheet sheet = new Sheet();
  IWorksheet worksheet = workbook.getWorksheets().get(i);
  sheet.setName(worksheet.getName());
  if(Visibility.Hidden.equals(worksheet.getVisible())) {
    sheet.setVisiable(false);
  }else {
    sheet.setVisiable(true);
  }
  sheetNameList.add(sheet);
}

3、获取activeSheet的名称和ssjson后，将其合并输出到结果中返回，为了节省网络请求的时间，可以将返回结果进行压缩（此处会用到GZip通用压缩工具）。

IWorksheet activeSheet = workbook.getActiveSheet();
String activeSheetName = activeSheet.getName();
returnMap.put("activeSheetName", activeSheetName);

String result = activeSheet.toJson();
result = GZip.compress(result);
returnMap.put("sheetJSON", result);

4、等前端SpreadJS接收到结果后，GcExcel会根据第二步工作表的名称及个数新建等量的工作表，并修改名称和状态。执行完上述步骤后，便可将前端计算引擎挂起（本方案无需前端计算，但需要有前端的公式显示作为提示），然后获取activeSheet并反序列化第三步生成的ssjson。

spread.setSheetCount(length);
for(var i=0;i<length;i++){
  spread.getSheet(i).name(data.sheetNames[i].name);
  if(data.sheetNames[i].visiable == false){
    spread.getSheet(i).visible(false);
  }
  if(data.sheetNames[i].name == data.activeSheetName){
    spread.setActiveSheetIndex(i);
  }
}
spread.suspendCalcService(false);
var activeSheet = spread.getActiveSheet();
var json = data.sheetJSON;
json = ungzipString(json);
json = JSON.parse(json);
activeSheet.fromJSON(json);

5、截至目前，前端SpreadJS中只储存了activeSheet的值和状态，其余都为空sheet。为了在后端获取新的activeSheet的ssjson，我们需要对activeSheet的状态进行监听（这里监听了ActiveSheetChanging事件），并借助事件驱动来进行缓式加载。

注意：此处为了防止用户在sheet上直接修改从而产生脏数据，需要同时获取脏数据并一同传给后端，最终将后端的返回结果在前端反序列化。

spread.bind(GC.Spread.Sheets.Events.ActiveSheetChanging, function (sender, args) {
                var oldSheet = args.oldSheet;
                var dirtyCells = oldSheet.getDirtyCells();
                var newSheet = args.newSheet;
                var sheetChange = {
  oldSheetName:oldSheet.name(),
  newSheetName:newSheet.name(),
  dirtyCells:dirtyCells        
                }
                $.ajax({
  url: "getSheet",
  type:"POST",
  data:JSON.stringify(sheetChange),
  contentType: 'application/json',
  async:false,
  success: function (data) {
    if (data != null) {
      var json = ungzipString(data);
      json = JSON.parse(json);
      newSheet.fromJSON(json);
    }
  }
                    });
            });

6、GcExcel在后端拿到上述信息并将其同步到workbook中，然后重新计算得到计算后的结果。最后，再将新的activeSheet序列化成ssjson返回。

IWorksheet oldSheet = workbook.getWorksheets().get(sheetChange.getOldSheetName());
                if(sheetChange.getDirtyCells().size()>0) {
  for(int i=0;i<sheetChange.getDirtyCells().size();i++) {
    Map<String,Object> dirtyCell = sheetChange.getDirtyCells().get(i);
    int row = (int) dirtyCell.get("row");
    int col = (int) dirtyCell.get("col");
    oldSheet.getRange(row, col).setValue(dirtyCell.get("newValue"));
  }
  workbook.setEnableCalculation(true);
                }

                IWorksheet newSheet = workbook.getWorksheets().get(sheetChange.getNewSheetName());
                String result = null;
                if(newSheet!=null) {
  result = newSheet.toJson();
  result = GZip.compress(result);        
                }
                return result;

以上便是葡萄城全栈表格技术（SpreadJS + GcExcel）应对复杂公式计算场景的代码逻辑，除了 SpreadJS 和 GcExcel，这里还用到了缓式加载、序列化和反序列化等开发技巧。在下一篇文章中，我们将就该方案的功能进行进一步扩展，并测试其性能能否满足企业项目需要。

如果您希望进一步了解类 Excel 全栈解决方案的应用场景和案例，请点击此处了解更多。