在 SpreadJS 中复刻 Excel 模拟运算功能

收藏

背景：SpreadJS暂不支持Microsoft Excel中的模拟运算功能，如何在SpreadJS中自定义支持对应功能？
问题原帖：https://gcdn.grapecity.com.cn/forum.php?mod=viewthread&tid=235726&pid=890271

Microsoft Excel的模拟分析功能可以利用一个既有的公式，批量替换其中的一个或两个参数。如图1所示，B3单元格公式为SUM（B1,B2)，对单元格B1和B2求和；B6单元格复用B3单元格公式执行模拟分析，通过模拟分析功能，将公式中的B1引用逐次替换为单元格A7:A10，同时保持B2引用不变，从而快速得到一组新计算结果。

以上便是肉眼观察到的模拟运算功能提供的交互方式和计算结果，从此可窥得模拟运算三要素，分别为计算公式、新引用数据和待替换参数。虽然SpreadJS没有集成这一功能特性，但已知如上述的该功能实现原理，可以利用SpreadJS的计算引擎提供的功能自定义实现。将原始计算公式、新引用单元格、待替换参数等均看作一个处理流程中不可缺少的三种参数，把它们封装在自定义公式内部参与运算逻辑。

那么，如何定义自定义公式内部的计算逻辑呢？其中最关键的部分是替换计算公式中的某位参数，公式以字符串形式显示在单元格中，作为参数传递至自定义公式中也是一个字符串，如果考虑解析字符串，那么需要根据每个可能使用到的字符串设计通用正则表达式，从中匹配出参数并替换后得到新公式字符串。然而，解析字符串需要的正则表达式更复杂，不太能兼容所有公式。幸运的是，SpreadJS计算引擎中引入了公式表达树的概念，通过CalcEngine:formulaToExpression()，能够准确无误地得到公式字符串对应的公式表达树。如图2所示，公式“SUM(A1,A2)”对应的表达树中包含了公式主体（function属性值）、参数（arguments属性值）、表达式类型（type属性值）等基本信息。

图2. 公式表达树

公式表达树中保存的参数arguments属性值是数组，其中的元素对应为参与公式计算的各项参数，在此为各单元格引用，包括单元格所在行列的索引。至此，用以实现模拟运算功能的自定义公式计算逻辑便清晰了。将其命名为CUSTOMSIMULATION，参数列表依次为计算公式、新引用单元格、待替换参数索引，核心计算逻辑如下步骤所述：

1. 解析新引用单元格范围：从参数新引用单元格中解析行列范围，用以后期遍历替换计算公式中的待替换参数执行计算，如下代码所示。

// 保存从参数中解析所得的新引用单元格范围
var newRefs;
// 新引用单元格对应参数索引为1
var cellRange = args[1];
if (typeof(cellRange) === 'object') {
// 新引用为对象，其中包含了引用范围起始行列索引和跨越行列数，以此修改公式中被修改参数
newRefs = {
row: cellRange.getRow(),
col: cellRange.getColumn(),
rowCount: cellRange.getRowCount(),
colCount: cellRange.getColumnCount()
};
console.log('newRefs: ', newRefs);
}

复制代码

2. 替换参数并执行计算：解析计算公式为公式表达树，遍历新引用单元格。在每次循环中，根据待替换参数索引，精准替换公式表达树中的计算参数。完成参数替换后，将修改后的表达树转换为公式字符串执行计算，最后保存计算结果，如下代码所示。

// args[0]为基础计算公式，将其转换为表达树，用以更新计算参数
var formulaExpression = GC.Spread.Sheets.CalcEngine.formulaToExpression(sheet, args[0]);
console.log('expression: ', formulaExpression);
var results = [];
for (var i = newRefs.row; i < newRefs.row + newRefs.rowCount; i++) {
for (var j = newRefs.col; j < newRefs.col + newRefs.colCount; j++) {
var tempExpression = formulaExpression;
// args[2]是基础计算公式中待替换参数的索引
var targetArg = tempExpression.arguments[args[2]];
targetArg.row = i;
targetArg.column = j;
// 替换参数后将表达树转换为公式，用以计算引擎执行计算
var newFormula = GC.Spread.Sheets.CalcEngine.expressionToFormula(sheet, tempExpression);
var tempRes = GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, newFormula);
results.push(tempRes);
}
}

复制代码

通过上述方案，成功在SpreadJS中复刻了Microsoft Excel模拟运算分析功能中单变量计算。附上demo，以供参考，运行效果如图3所示。

图3. 自定义模拟运算功能

电子表格除了保存数据外，通过其具备的计算能力处理数据也是常用操作。在公式计算方面，SpreadJS已同步Microsoft Excel的计算公式，并且额外支持了部分计算公式，当前共支持500多种计算公式。面对如Microsoft Excel的模拟运算分析相关功能等SpreadJS暂未直接支持的功能，我们完全可以尝试通过交互过程了解Microsoft Excel中该功能的执行流程，以此分析所需要素，然后结合SpreadJS的计算引擎，实现个性化功能开发，满足多样化的数据处理需求。

		自动登录	找回密码
密码			立即注册

在 SpreadJS 中复刻 Excel 模拟运算功能

0 个回复