概述
本 Demo 展示了如何使用 getPrecedents 方法追踪公式的引用单元格,并通过可视化树形结构展示引用关系。当用户点击包含公式的单元格时,Demo 会在下方的工作表中绘制公式树,显示该单元格的所有引用单元格及其层级关系。
实现思路
初始化两个 Workbook 实例:一个用于显示源数据表格,另一个用于显示公式树
监听源表格的 SelectionChanging 事件,获取用户选中的单元格
调用 getPrecedents 方法获取选中单元格的引用单元格信息
递归构建引用关系树节点,包含单元格的值、位置和批注等描述信息
使用 Shape 形状和连接线可视化绘制引用关系树
代码解析
构建引用关系树节点
这段代码构建了引用关系树的节点结构。每个节点包含三个属性:value 存储单元格的值,position 使用 rangeToFormula 方法将单元格范围转换为公式字符串格式(例如 "Sheet1!A1"),description 存储单元格的批注文本。如果节点有引用单元格,还会添加 childNodes 属性。
获取引用单元格并递归构建树
这段代码使用 getPrecedents 方法获取引用单元格信息数组。每个引用单元格信息包含 row、col、rowCount、colCount 和 sheetName 属性。如果引用的是一个范围(rowCount 或 colCount 大于 1),则遍历范围内的所有单元格;如果是单个单元格,直接构建节点。通过递归调用 creatNodeTree,可以构建多层级引用关系树。
可视化绘制引用关系树
这段代码使用 Shape 形状绘制树形结构。每个节点用矩形表示,显示单元格的值、位置和描述信息。节点之间使用连接线形状 connectorShape 连接,形成可视化的引用关系树。startConnector 和 endConnector 方法分别设置连接线的起点和终点,index 参数指定连接点的位置(1 表示左侧,3 表示右侧)。
运行效果
在上方的工作表中点击包含公式的单元格(例如 D7 或 F7)
下方的工作表会显示公式树,展示该单元格的所有引用单元格
每个矩形节点显示单元格的值、位置和批注描述(如果有)
节点之间用连接线表示引用关系,从左到右展开树形结构
可以查看多层级的引用关系,了解复杂公式的计算来源
API 参考
getPrecedents 方法
获取指定单元格的前置引用单元格范围信息数组。
row: 单元格的行索引
col: 单元格的列索引
返回值: 返回 ICellsInfo[] 数组,每个元素包含:
row: 单元格范围的行索引
col: 单元格范围的列索引
rowCount: 单元格范围的行数
colCount: 单元格范围的列数
sheetName: 工作表名称
rangeToFormula 方法
将指定的单元格范围转换为公式字符串。
range: 要转换的单元格范围
baseRow: 可选,相对引用的基行索引
baseCol: 可选,相对引用的基列索引
rangeReferenceRelative: 可选,指定引用的相对性
useR1C1: 可选,是否使用 R1C1 样式
window.onload = function () {
initFunction();
}
function initFunction() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'));
var spreadForShow = new GC.Spread.Sheets.Workbook(document.getElementById('show'));
initShowSpread(spreadForShow);
buildNodeTreeAndPaint(spread, spreadForShow);
};
function initShowSpread(spreadForShow) {
var sheetForShow = spreadForShow.getActiveSheet();
spreadForShow.suspendPaint();
var spreadOptions = spreadForShow.options, sheetOptions = sheetForShow.options;
spreadOptions.allowContextMenu = false;
spreadOptions.scrollbarMaxAlign = true;
spreadOptions.tabStripVisible = false;
spreadOptions.allowUserResize = false;
spreadOptions.allowUserDragDrop = false;
spreadOptions.allowUserDragFill = false;
spreadOptions.allowUserZoom = false;
spreadOptions.grayAreaBackColor = '#ccddff';
sheetOptions.colHeaderVisible = false;
sheetOptions.rowHeaderVisible = false;
sheetOptions.selectionBackColor = "transparent";
sheetOptions.selectionBorderColor = "transparent";
sheetOptions.gridline = { showVerticalGridline: false, showHorizontalGridline: false };
sheetForShow.getCell(1, 0)
.foreColor("#71134E")
.text("公式树")
.font("bold italic 16pt Calibri")
.vAlign(GC.Spread.Sheets.VerticalAlign.center)
.textIndent(2);
sheetForShow.getRange(0, 0, 100, 100).backColor("#ccddff");
sheetOptions.isProtected = true;
spreadForShow.resumePaint();
}
function buildNodeTreeAndPaint(spread, spreadForShow) {
var sd = data;
if (sd.length > 0) {
spread.fromJSON(sd[0]);
var sheet = spread.getActiveSheet();
var sheetForShow = spreadForShow.getActiveSheet();
sheet.bind(GC.Spread.Sheets.Events.SelectionChanging, function (e, info) {
sheetForShow.shapes.clear();
var row = info.newSelections[0].row;
var col = info.newSelections[0].col;
var nodeTree = creatNodeTree(row, col, sheet);
paintDataTree(sheetForShow, nodeTree);
})
}
}
function creatNodeTree(row, col, sheet) {
var _comment = sheet.getCell(row, col).comment();
var node = {
value: sheet.getValue(row, col),
position: sheet.name() + '!' + GC.Spread.Sheets.CalcEngine.rangeToFormula(sheet.getRange(row, col, 1, 1)),
description: _comment && _comment.text(),
};
var childNodeArray = addChildNode(row, col, sheet);
if (childNodeArray.length > 0) {
node.childNodes = childNodeArray;
}
return node;
}
function addChildNode(row, col, sheet) {
var childNodeArray = [];
var childNodes = sheet.getPrecedents(row, col);
if (childNodes.length >= 1) {
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(creatNodeTree(r, c, _sheet));
}
}
} else {
childNodeArray.push(creatNodeTree(row, col, _sheet))
}
})
}
return childNodeArray;
}
function getRectShape(sheetForShow, name, x, y, width, height) {
var rectShape = sheetForShow.shapes.add(name, GC.Spread.Sheets.Shapes.AutoShapeType.rectangle, x, y, width, height);
var oldStyle = rectShape.style();
oldStyle.textEffect.color = "white";
oldStyle.fill.color = "#0065ff";
oldStyle.textEffect.font = "bold 15px Calibri";
oldStyle.textFrame.vAlign = GC.Spread.Sheets.VerticalAlign.top;
oldStyle.textFrame.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
oldStyle.line.beginArrowheadWidth = 2;
oldStyle.line.endArrowheadWidth = 2;
rectShape.style(oldStyle);
return rectShape;
}
function getConnectorShape(sheetForShow) {
var connectorShape = sheetForShow.shapes.addConnector('', GC.Spread.Sheets.Shapes.ConnectorType.elbow);
var LineStyle = connectorShape.style();
var line = LineStyle.line;
line.beginArrowheadWidth = GC.Spread.Sheets.Shapes.ArrowheadWidth.wide;
line.endArrowheadWidth = GC.Spread.Sheets.Shapes.ArrowheadWidth.wide;
line.color = "#FF6600";
connectorShape.style(LineStyle);
return connectorShape;
}
function paintDataTree(sheetForShow, nodeTree, index, childLength, fatherShape) {
var rectWidth = 260, rectHeight = 60;
var spacingWidth = 300;
var convertArray = [-0.75, 0.75, -2.25, 2.25, -2.25, 2.25, -4, 4, -5, 5];
var spacingHeightMapping = [145, 135, 125, 50, 50];
var name = Math.random().toString();
var rectShape;
if (fatherShape) {
var x = fatherShape.x(), y = fatherShape.y();
rectShape = getRectShape(sheetForShow, name, x + spacingWidth, y + convertArray[index] * spacingHeightMapping[childLength], rectWidth, rectHeight);
var connectorShape = getConnectorShape(sheetForShow);
connectorShape.startConnector({ name: fatherShape.name(), index: 3 });
connectorShape.endConnector({ name: rectShape.name(), index: 1 });
} else {
rectShape = getRectShape(sheetForShow, name, 200, 250, rectWidth, rectHeight);
}
var _description = 'Value: ' + nodeTree.value + '\nCell: ' + nodeTree.position + ((nodeTree.description !== null) ? ('\nDescription: ' + nodeTree.description) : '');
rectShape.text(_description);
var childNodes = nodeTree.childNodes;
if (childNodes) {
childNodes.forEach(function (node, index) {
if (node.description) {
paintDataTree(sheetForShow, node, index, childNodes.length, rectShape)
}
});
}
}
<!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="$DEMOROOT$/spread/source/data/precedent.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss"></div>
<div id="show"></div>
</div>
</body>
</html>
#ss {
width: 100%;
height: 60%;
border: 1px solid black;
}
#show {
width: 100%;
height: 40%;
border: 1px solid black;
}
.sample-tutorial {
height: 100%;
width: 100%;
overflow: hidden;
}
body{
height: 100%;
}
