Precedents are cells or ranges that affect the current cell's formula.
Set formula =SUM(A1) in cell B1.
Cell A1 is the precedent cell of cell B1.
Use the getPrecedents method to get the precedent cellRange information object array of cell. As shown in the following code.
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("white").text("Formula Tree")
.font("bold italic 24pt 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="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.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%;
}
