公式审核

SpreadJS通过允许用户显示公式与单元格之间的关系，为公式审核提供了广泛的支持。这可以通过跟踪表单中的先例单元格和从属单元格来完成。

公式审核的好处

在使用表单时，公式审核可帮助用户识别计算瓶颈，调试公式并验证结果的准确性。此外，此功能还可以帮助用户：

Maintain Regulatory Compliance -在处理财务，会计和其他复杂应用程序时，公式审核功能对于保持合规性很有用。
Check Errors - 公式审核可帮助用户识别错误的来源，否则这将是极其困难的任务，尤其是在表单中的公式使用先行或从属单元格的情况下。
Locate Circular References - 通过公式审核，用户可以通过跟踪表单中的先例和从属关系来快速找到所有循环引用，以节省时间。
Detect Inconsistency - 在大型表单中，用户可以推测列中公式中的任何不一致之处，而不必在验证数据时手动查看每个公式。

追踪前例和依赖

先例单元格是位于另一个单元格中的公式所引用的单元格。从属单元格是包含公式并引用其他单元格的单元格。

例如，如果单元格D10包含公式“ = B5”，则单元格B5将被视为单元格D10的先例，而单元格D10将被视为单元格B5的从属。

在审核表单中的公式时，可以使用 getPrecedents 方法跟踪先例，并使用 getDependents 方法跟踪从属关系以可视化并显示与所选公式单元格相关的单元格。

使用场景

例如-假设您正在开发一个金融或银行应用程序，其中使用表单中的公式分析每月交易（贷方 & 借方），并为每个客户生成每月报告。在这种情况下，单个评估错误会直接导致损失，因此，在与客户共享每月会计报表之前，审核公式成为前提。

下图说明了一个小型金融应用程序，其中使用公式为每笔交易计算余额，并且在单击最后一列中的任何单元格时，将突出显示先行单元格和从属单元格。

代码

下面的示例代码突出显示表单中的前例单元和从属单元。

<script type="text/javascript">
var oldPrecedentCells, oldDependentCells;
$(document).ready(function () {
// 初始化 Spread
var spread = new GC.Spread.Sheets.Workbook($("#sampleDiv")[0], { autoFitType: GC.Spread.Sheets.AutoFitType.cellWithHeader });
// 设置表单的数据源
var jsonArray = [
{
" DATE ": " 3-Oct-17 ",
" TRANSACTION DETAILS ": " INDO GIBL Indiaforensic STL28091 ",
" WITHDRAWAL AMOUNT ": 12106.00,
" DEPOSIT AMOUNT ": 1000000.00,
" BALANCE AMOUNT ": ""
},
{
" DATE ": " 3-Oct-17 ",
" TRANSACTION DETAILS ": " INDO GIBL Indiaforensic STL29091 ",
" WITHDRAWAL AMOUNT ": 12611.00,
" DEPOSIT AMOUNT ": 1000000.0,
" BALANCE AMOUNT ": ""
},
{
" DATE ": " 5-Oct-17 ",
" TRANSACTION DETAILS ": " INDO GIBL Indiaforensic STL04101 ",
" WITHDRAWAL AMOUNT ": 61400.00,
" DEPOSIT AMOUNT ": null,
" BALANCE AMOUNT ": ""
},
{
" DATE ": " 16-Oct-17 ",
" TRANSACTION DETAILS ": " FDRL/INTERNAL FUND TRANSFE ",
" WITHDRAWAL AMOUNT ": null,
" DEPOSIT AMOUNT ": 500000.00,
" BALANCE AMOUNT ": "",
},
{
" DATE ": " 17-Oct-17 ",
" TRANSACTION DETAILS ": " INDO GIBL Indiaforensic STL06101 ",
" WITHDRAWAL AMOUNT ": 6000.00,
" DEPOSIT AMOUNT ": null,
" BALANCE AMOUNT ": "",
},
{
" DATE ": " 17-Oct-17 ",
" TRANSACTION DETAILS ": " INDO GIBL Indiaforensic STL07101 ",
" WITHDRAWAL AMOUNT ": 97950.00,
" DEPOSIT AMOUNT ": null,
" BALANCE AMOUNT ": ""
},
{
" DATE ": " 18-Oct-17 ",
" TRANSACTION DETAILS ": " INDO GIBL Indiaforensic STL17101 ",
" WITHDRAWAL AMOUNT ": 206501.00,
" DEPOSIT AMOUNT ": null,
" BALANCE AMOUNT ": ""
},
{
" DATE ": " 21-Oct-17 ",
" TRANSACTION DETAILS ": " FDRL/INTERNAL FUND TRANSFE ",
" WITHDRAWAL AMOUNT ": null,
" DEPOSIT AMOUNT ": 500000.00,
" BALANCE AMOUNT ": ""
},
{
" DATE ": " 23-Oct-17 ",
" TRANSACTION DETAILS ": " INDO GIBL Indiaforensic STL21101 ",
" WITHDRAWAL AMOUNT ": 39000.00,
" DEPOSIT AMOUNT ": null,
" BALANCE AMOUNT ": ""
},
{
" DATE ": " 26-Oct-17 ",
" TRANSACTION DETAILS ": " INDO GIBL Indiaforensic STL25101 ",
" WITHDRAWAL AMOUNT ": 170125.00,
" DEPOSIT AMOUNT ": null,
" BALANCE AMOUNT ": ""
},
{
" DATE ": " 27-Oct-17 ",
" TRANSACTION DETAILS ": "INDO GIBL Indiaforensic STL26101",
" WITHDRAWAL AMOUNT ": 208000.00,
" DEPOSIT AMOUNT ": null,
" BALANCE AMOUNT ": ""
},
{
" DATE ": " 30-Oct-17 ",
" TRANSACTION DETAILS ": " FDRL/INTERNAL FUND TRANSFE ",
" WITHDRAWAL AMOUNT ": null,
" DEPOSIT AMOUNT ": 500000.00,
" BALANCE AMOUNT ": ""
}
];
var activeSheet = spread.getActiveSheet();
activeSheet.setDataSource(jsonArray);
// 配置表单设置
   activeSheet.getRange(0, 2, 12, 3).formatter('* #.00');
for (var c = 0; c < activeSheet.getColumnCount(); c++)
{
activeSheet.autoFitColumn(c);
}
// 设置单元格公式
   activeSheet.setFormula(0, 4, "D2");
for (var r = 1; r < activeSheet.getRowCount(); r++) {
activeSheet.setFormula(r, 4, "E" + r.toString() + "+ D" + (r + 1).toString() + "- C" + (r + 1).toString());
}
// 跟踪活动单元的前例和从属
   activeSheet.bind(GC.Spread.Sheets.Events.SelectionChanging, function (e, info)
{
spread.suspendPaint();
var newRow = info.newSelections[0].row;
var newCol = info.newSelections[0].col;
ColorPrecedents(activeSheet, newRow, newCol);
ColorDependents(activeSheet, newRow, newCol);
spread.resumePaint();
});
});
// 将先例单元格绘制黄色
  function ColorPrecedents(sheet, row, col)
{
if (oldPrecedentCells)
{
oldPrecedentCells.forEach(function (node)
{
sheet.getCell(node.row, node.col).backColor(undefined);
});
}
var precedentCells = sheet.getPrecedents(row, col);
if (precedentCells)
{
setTimeout(function ()
{
oldPrecedentCells = precedentCells;
precedentCells.forEach(function (node) {
sheet.getCell(node.row, node.col).backColor("yellow");});}, 10);
}
}
// 将依赖的单元格绘制绿色
  function ColorDependents(sheet, row, col)
{
if (oldDependentCells)
{
oldDependentCells.forEach(function (node) {
sheet.getCell(node.row, node.col).backColor(undefined);
});
}
var dependentCells = sheet.getDependents(row, col);
oldDependentCells = dependentCells;
if (dependentCells) {
dependentCells.forEach(function (node) {
sheet.getCell(node.row, node.col).backColor("green");
});
}
}
</script>