Get Dependents

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 getDependents method to get the dependent cellRange information object for an array of cells. In this example, click on cell C10 to see it's dependent cells.

Dependents are cells or ranges affected by the current cell's formula. Set formula =SUM(A1) in cell B1. Cell B1 is the dependent cell of cell A1. Use the getDependents method to get the dependent cellRange information object array of cell. As shown in the following code.
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("dependent"); sheet.setStyle(26,1,sheet.getStyle(10,0)); sheet.getCell(26, 13).text("precedent"); 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="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="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; }