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

收藏

本帖最后由 Clark.Pan 于 2021-11-8 16:23 编辑

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

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

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

document.getElementById("calc").onclick = function(){
var currentSheet = spread.getActiveSheet();
var spreadDirtyCells = {};
for(var i=0;i<spread.getSheetCount();i++){
var sheet = spread.getSheet(i);
var sheetName = sheet.name();
spreadDirtyCells[sheetName] = sheet.getDirtyCells();
}
var calc = {
currentSheet:currentSheet.name(),
dirtyCells:spreadDirtyCells
}
$.ajax({
url: "getCalcResult",
type:"POST",
data:JSON.stringify(calc),
contentType: 'application/json',
async:false,
success: function (data) {
if (data != null) {
var json = ungzipString(data);
json = JSON.parse(json);
currentSheet.fromJSON(json);
}
}
});
}

复制代码

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

@RequestMapping(value = "/getCalcResult", method = RequestMethod.POST)
@ResponseBody
public String getCalcResult(@RequestBody Calc calc) {
IWorksheet currentSheet = workbook.getWorksheets().get(calc.getCurrentSheet());
Map<String,Object> map = calc.getDirtyCells();
for (Map.Entry<String, Object> entry : map.entrySet()) {
String key = entry.getKey();
IWorksheet dirtySheet = workbook.getWorksheets().get(key);
List<Map<String,Object>> sheetDirtys = (List<Map<String, Object>>) entry.getValue();
for(int i=0;i<sheetDirtys.size();i++) {
Map<String,Object> dirtyCell = sheetDirtys.get(i);
int row = (int) dirtyCell.get("row");
int col = (int) dirtyCell.get("col");
dirtySheet.getRange(row, col).setValue(dirtyCell.get("newValue"));
}
}
workbook.setEnableCalculation(true);
String result = null;
if(currentSheet!=null) {
result = currentSheet.toJson();
result = GZip.compress(result);
}
return result;
}

复制代码

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

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