Monday, 9 January, 2017 UTC
Summary
In some cases, large spreadsheets have formulas with a lot of dependencies on other cells. Showing these dependencies can be useful. In Excel, there are keyboard shortcuts specifically for navigating to and selecting the dependent (and precedent) cells of the currently selected cell. These shortcuts are not currently in Spread.Sheets, but you can write code to add them. In this blog, you will learn how to add commands for shortcut keys to Spread.Sheets, as well as how to get dependent/precedent cells.
The download for this sample can be found here: Spread.Sheets V10 Dependencies
The finished page showing dependencies of a cell.
Create a simple HTML webpage and add references to the Spread.Sheets JS and CSS files:
<!DOCTYPE html>
<html>
<head>
<title>SpreadJS - Samples</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script src="http://code.jquery.com/jquery-2.1.3.min.js" type="text/javascript"></script>
<link type="text/css" href="http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2013white.10.0.0.css" rel="stylesheet" />
<script type="text/javascript" src="http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.10.0.0.min.js"></script>
</head>
<body>
</body>
</html>
Add a DOM element and some initialization code for the Spread.Sheets instance:
<script type="text/javascript">
// GC.Spread.Sheets.LicenseKey = "";
function init() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
var sheet = window.sheet = spread.getActiveSheet();
sheet.setValue(0, 0, 2);
sheet.setValue(0, 1, 3);
sheet.setFormula(0, 2, "sum(A1:a2)+B1");
sheet.setFormula(1, 2, "C1*2");
sheet.setActiveCell(0, 2);
}
</script>
<body onload="init()">
</body>
The Spread.Sheets V10 instance on an HTML page.
Now we have to write code to actually get the dependencies of a cell. This should return an array of those dependencies:
// Get the dependent cells of the cell at the specified row and column
function getDependencies(row, col) {
var dependencies = [];
var calcService = sheet.getCalcService();
var sourceModels = calcService.getAllSouceModels();
var sheetSourceModel = sourceModels[0];
var expression = sheetSourceModel.getExpression(row, col);
var node = sheetSourceModel.getNode(row, col);
if (node) {
let calcNode = node.calc;
if (calcNode) {
var cellListens = calcNode.cellListeners;
for (var i = 0; cellListens && i < cellListens.length; i++) {
var cellListen = cellListens[i];
dependencies.push({ row: cellListen.row, col: cellListen.column });
}
}
}
return dependencies;
}
In addition, we have to write code for getting the precedent cells as well:
function getPrecedents(formula, row, column) {
var expr = GC.Spread.Sheets.CalcEngine.formulaToExpression(sheet, formula, row, column);
var pres = [];
getPres(sheet, expr, pres, row, column);
return pres;
}
// Get the precedent cells of the cell at the specified row and column
function getPres(sheet, expr, pres, row, column) {
if (!expr) {
return;
}
var ExpressionType = GC.Spread.CalcEngine.ExpressionType;
while (expr.type === ExpressionType.parentheses) {
expr = expr.value;
}
if (expr.type === ExpressionType.reference) {
pres.push(expr.getRange(row, column));
} else if (expr.type === ExpressionType.operator) {
getPres(sheet, expr.value, pres, row, column);
getPres(sheet, expr.value2, pres, row, column);
} else if (expr.type === ExpressionType.function) {
for (var i = 0; i < expr.arguments.length; i++) {
getPres(sheet, expr.arguments[i], pres, row, column);
}
} else if (expr.type === ExpressionType.name) {
var nameInfo = sheet.getCustomName(expr.value);
if (nameInfo) {
nameInfo = sheet.parent && sheet.parent.getCustomName && sheet.parent.getCustomName(expr.value);
}
if (nameInfo) {
getPres(sheet, nameInfo.getExpression(), pres, row, column);
}
}
}
It might also be useful to display these dependencies in a text area on the page, so you can write additional functions to call the functions we just wrote:
function displayDependencies() {
var row = sheet.getActiveRowIndex();
var col = sheet.getActiveColumnIndex();
var deps = getDependencies(row, col);
var msg = "Dependencies:\r\n";
for (var i = 0; i < deps.length; i++) {
var range = deps[i];
msg += range.row + ", " + range.col;
}
$("#msg").val(msg);
}
function displayPrecedents() {
var pres = []
var row = sheet.getActiveRowIndex();
var column = sheet.getActiveColumnIndex();
var formula = sheet.getFormula(row, column);
if (!formula) {
return;
}
var pres = getPrecedents(formula, row, column);
var msg = "Precedents:\r\n";
for (var i = 0; i < pres.length; i++) {
var range = pres[i];
for (var row = 0; row < range.rowCount; row++) {
for (var col = 0; col < range.colCount; col++) {
msg += (range.row + row) + ", " + (range.col + col) + "\r\n";
}
}
}
$("#msg").val(msg);
}
In order to make those functions fire with specific key actions, we have to first register them as commands in the Command Manager:
// Add commands to Spread.Sheets for selecting the precedents/dependents of a cell
function registerCommands(spread) {
spread.commandManager().register('highlightPrecedents',
function PrecedentAction() {
var row = sheet.getActiveRowIndex();
var column = sheet.getActiveColumnIndex();
var formula = sheet.getFormula(row, column);
if (!formula) {
alert("No precedents!");
return;
}
var precedents = getPrecedents(formula, row, column);
spread.getActiveSheet().selectionPolicy(GC.Spread.Sheets.SelectionPolicy.multiRange);
for (var i = 0; i < precedents.length; i++) {
var range = precedents[i];
if (i == 0) {
spread.getActiveSheet().setActiveCell(range.row, range.col);
spread.getActiveSheet().setSelection(range.row, range.col, range.rowCount, range.colCount);
} else {
spread.getActiveSheet().addSelection(range.row, range.col, range.rowCount, range.colCount);
}
}
return;
}
);
spread.commandManager().register('highlightDependents',
function DependentAction() {
var row = sheet.getActiveRowIndex();
var column = sheet.getActiveColumnIndex();
var dependents = getDependencies(row, column);
spread.getActiveSheet().selectionPolicy(GC.Spread.Sheets.SelectionPolicy.multiRange);
for (var i = 0; i < dependents.length; i++) {
var range = dependents[i];
if (i == 0) {
spread.getActiveSheet().setActiveCell(range.row, range.col);
spread.getActiveSheet().setSelection(range.row, range.col, 1, 1);
} else {
spread.getActiveSheet().addSelection(range.row, range.col, 1, 1);
}
}
return;
}
);
}
After implementing the commands, we can connect those commands to the “Ctrl+[“ and “Ctrl+]” key shortcuts:
// Connect the commands for selecting depedent/precedent cells to the Ctrl+[ and Ctrl+] key combinations
function setShortcuts(spread) {
spread.commandManager().setShortcutKey('highlightDependents', 221, true, false, false, false);
spread.commandManager().setShortcutKey('highlightPrecedents', 219, true, false, false, false);
}
Lastly, add buttons and a text area on the page to correspond to the functions we added:
<input type="button" value="Get Precedents" onclick="displayPrecedents()"/>
<input type="button" value="Get Dependencies" onclick="displayDependencies()" />
<textarea id="msg" style="height:200px"></textarea>
The HTML page with the added textarea and buttons.
When running the page, the user will be able to select cells and use “Ctrl+[“ and “Ctrl+]” to navigate to and select dependent and precedent cells of the current selection. The added buttons on the page call the functions to display the dependencies in the text area on the page. This tutorial showed how to get the dependencies of cells as well as connect custom actions to keyboard shortcuts in Spread.Sheets.