Get Precedents

SpreadJS provides extensive support for formula auditing by providing users the ability to display relationships between formulas and cells. This can be done by tracing the precedent and dependent cells in the worksheet. The following sample uses the getPrecedents method to get the precedent cellRange information object for an array of cells. In this example, click on either cell D7 or F7 to see it's precedent cells in the Formula Tree below the SpreadJS instance.

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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/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%; }