概述
本 Demo 展示了如何使用 SpreadJS 的公式追踪功能，可视化显示单元格之间的引用和从属关系。当用户选择单元格时，Demo 会自动识别该单元格的引用单元格（公式中使用的单元格）和从属单元格（引用该单元格的公式单元格），并以不同颜色的层级深度进行高亮显示。
实现思路
加载预设的表格数据（包含公式的数据模型）
创建状态栏，显示操作提示信息
绑定 SelectionChanging 事件，监听单元格选择变化
当选择改变时，清除上一次的高亮显示
使用 getDependents 方法递归构建从属单元格树结构
使用 getPrecedents 方法递归构建引用单元格树结构
根据层级深度为单元格设置不同背景色，实现可视化效果
代码解析
监听单元格选择变化
这段代码通过 SelectionChanging 事件监听单元格选择变化。每次选择新单元格时，先清除上一次的高亮显示，然后重新构建节点树并绘制新的高亮效果。使用 suspendPaint 和 resumePaint 优化性能。
递归构建从属单元格树
这段代码使用递归方式构建从属单元格的树结构。getDependents 方法返回当前单元格被哪些公式引用，然后对每个从属单元格继续递归查找，直到所有层级的从属关系都被识别。每个节点记录行、列、工作表和层级深度。
高亮显示单元格
这段代码根据层级深度为单元格设置不同颜色。从属单元格使用 dependentLevelColor 数组的颜色（绿色系），引用单元格使用 precedentLevelColor 数组的颜色（蓝色系）。clearFlag 参数用于清除高亮，恢复白色背景。
运行效果
打开 Demo 后，表格中显示预设的数据和公式
点击任意单元格，表格上方会显示颜色图例（从属单元格用绿色系，引用单元格用蓝色系）
选择包含公式的单元格时，自动高亮显示其引用单元格（用蓝色系表示）和从属单元格（用绿色系表示）
不同层级深度的单元格使用不同深浅的颜色区分
切换选择其他单元格时，之前的高亮会自动清除，新的依赖关系会实时显示
API 参考
getDependents 方法
row：单元格行索引
col：单元格列索引
返回值：ICellsInfo[] 对象数组，包含 row、col、rowCount、colCount、sheetName 属性
getPrecedents 方法
row：单元格行索引
col：单元格列索引
返回值：ICellsInfo[] 对象数组，包含 row、col、rowCount、colCount、sheetName 属性
SelectionChanging 事件
事件参数 info.newSelections：新选择的单元格范围数组
事件参数 info.oldSelections：旧选择的单元格范围数组
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
sheetCount: 1
});
initSpread(spread);
initStatusBar(spread);
buildNodeTreeAndPaint(spread);
};
var precedentLevelColor = ['#FFFFFF', '#19E093', '#09E8DB', '#12A0D1', '#096CE8', '#0926DE'];
var dependentLevelColor = ['#FFFFFF', '#ADDE0B', '#E8DD0C', '#D1AD00', '#E8A90C', '#E08804'];
function initStatusBar(spread){
var statusBarDOM = document.getElementById('statusBar');
var statusBar = new GC.Spread.Sheets.StatusBar.StatusBar(statusBarDOM);
statusBar.bind(spread);
}
function initSpread(spread) {
if (data.length > 0) {
spread.fromJSON(data[0]);
var sheet = spread.getActiveSheet();
dependentLevelColor.forEach(function (color, index) {
sheet.getCell(26, 7 - index, 3).backColor(color).text((index).toString()).font("bold 24px").hAlign(GC.Spread.Sheets.HorizontalAlign.center)
});
precedentLevelColor.forEach(function (color, index) {
sheet.getCell(26, 7 + index, 3).backColor(color).text((index).toString()).font("bold 24px").hAlign(GC.Spread.Sheets.HorizontalAlign.center)
})
sheet.getCell(26, 1).text("从属单元格");
sheet.setStyle(26,1,sheet.getStyle(10,0));
sheet.getCell(26, 13).text("引用单元格");
sheet.setStyle(26,13,sheet.getStyle(10,0));
sheet.getCell(26, 7).text('C');
}
}
function buildNodeTreeAndPaint(spread) {
var sheet = spread.getActiveSheet();
var oldDependentNodeTree, oldPrecedentNodeTree;
sheet.bind(GC.Spread.Sheets.Events.SelectionChanging, function (e, info) {
spread.suspendPaint();
if (oldDependentNodeTree || oldPrecedentNodeTree) {
if (oldDependentNodeTree.dependentChildNodes !== undefined || oldPrecedentNodeTree.precedentChildNodes !== undefined) {
paintDependentCells(oldDependentNodeTree, true);
paintprecedentCells(oldPrecedentNodeTree, true);
}
}
var newRow = info.newSelections[0].row;
var newCol = info.newSelections[0].col;
var dependentNodeTree = createDependentNodeTree(newRow, newCol, sheet);
oldDependentNodeTree = dependentNodeTree;
var precedentNodeTree = createPrecedentNodeTree(newRow, newCol, sheet);
oldPrecedentNodeTree = precedentNodeTree;
if (precedentNodeTree.precedentChildNodes !== undefined || dependentNodeTree.dependentChildNodes !== undefined) {
paintDependentCells(dependentNodeTree);
paintprecedentCells(precedentNodeTree);
}
spread.resumePaint();
})
}
function createDependentNodeTree(row, col, sheet, dependentLevel) {
if (dependentLevel === undefined) {
var dependentLevel = 0;
}
var node = {
row: row,
col: col,
sheet: sheet,
level: dependentLevel
};
var dependentChildNodes = addDependentChildNode(row, col, sheet, dependentLevel);
if (dependentChildNodes.length > 0) {
node.dependentChildNodes = dependentChildNodes;
}
return node;
}
function addDependentChildNode(row, col, sheet, dependentLevel) {
var childNodeArray = [];
var childNodes = sheet.getDependents(row, col);
if (childNodes.length >= 1) {
dependentLevel++;
childNodes.forEach(function (node) {
let _sheet = sheet.parent.getSheetFromName(node.sheetName);
childNodeArray.push(createDependentNodeTree(node.row, node.col, _sheet, dependentLevel))
})
}
return childNodeArray;
}
function createPrecedentNodeTree(row, col, sheet, precedentLevel) {
if (precedentLevel === undefined) {
var precedentLevel = 0;
}
var node = {
row: row,
col: col,
sheet: sheet,
level: precedentLevel
};
var precedentChildNodes = addPrecedentChildNode(row, col, sheet, precedentLevel);
if (precedentChildNodes.length > 0) {
node.precedentChildNodes = precedentChildNodes;
}
return node;
}
function addPrecedentChildNode(row, col, sheet, precedentLevel) {
var childNodeArray = [];
var childNodes = sheet.getPrecedents(row, col);
if (childNodes.length >= 1) {
precedentLevel++;
childNodes.forEach(function (node) {
var row = node.row, col = node.col, rowCount = node.rowCount,
colCount = node.colCount, _sheet = sheet.parent.getSheetFromName(node.sheetName);
if (rowCount > 1 || colCount > 1) {
for (var r = row; r < row + rowCount; r++) {
for (var c = col; c < col + colCount; c++) {
childNodeArray.push(createPrecedentNodeTree(r, c, _sheet, precedentLevel));
}
}
} else {
childNodeArray.push(createPrecedentNodeTree(row, col, _sheet, precedentLevel))
}
})
}
return childNodeArray;
}
function paintDependentCells(nodeTree, clearFlag) {
var currentRow = nodeTree.row, currentCol = nodeTree.col, currentSheet = nodeTree.sheet,
currentLevel = nodeTree.level;
var dependentChildNodes = nodeTree.dependentChildNodes;
currentSheet.getCell(currentRow, currentCol).backColor(clearFlag ? 'white' : dependentLevelColor[currentLevel]);
if (dependentChildNodes) {
dependentChildNodes.forEach(function (node) {
paintDependentCells(node, clearFlag)
});
}
}
function paintprecedentCells(nodeTree, clearFlag) {
var currentRow = nodeTree.row, currentCol = nodeTree.col, currentSheet = nodeTree.sheet,
currentLevel = nodeTree.level;
var precedentChildNodes = nodeTree.precedentChildNodes;
currentSheet.getCell(currentRow, currentCol).backColor(clearFlag ? 'white' : precedentLevelColor[currentLevel]);
if (precedentChildNodes) {
precedentChildNodes.forEach(function (node) {
paintprecedentCells(node, clearFlag);
});
}
}
<!DOCTYPE html>
<html lang="en" style="height: 100%;font-size: 14px;">
<head>
<meta charset="utf-8">
<meta name="spreadjs culture" content="zh-cn" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/dependent.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div id="statusBar"></div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.sample-spreadsheets{
height: calc(100% - 30px);
}
#statusBar {
width: 100%;
height: 30px;
}
