Tuesday, 14 March, 2017 UTC
Summary
In Excel, the key combination of Ctrl and an arrow key allows the user to navigate to the end of a data region in a worksheet. While this functionality is not currently automatically provided in Spread.Sheets, it is very easy to add. In this tutorial you will learn how to overwrite default behavior for key combinations, as well as how to write custom commands.
The download for this sample can be found here: Excel Navigation
Set Up the Project
Create a new HTML page and add references to the Spread.Sheets script and css files:
<!DOCTYPE html>
<html>
<head>
<title>Spread.Sheets Excel Selection</title>
<link href="http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2013white.10.0.0.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.10.0.0.min.js"></script>
</head>
<body>
<div id="ss" style="height:600px ; width :100%; "></div>
</body>
</html>
Then add a script to the page that initializes the Spread.Sheets component as well as a div element to contain it:
<script type="text/javascript">
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
var sheet = spread.getActiveSheet();
};
</script>
</head>
<body>
<div id="ss" style="height:600px ; width :100%; "></div>
</body>
Before replacing key bindings, we first have to write the custom commands that will be fired when hitting those specific key combinations. For tutorial purposes, we will go over one specific action for when the user presses Ctrl + Right Arrow. With that action, the selection will navigate to the end of the data region that is currently selected. Essentially, the selection will move to the next cell that has data if the current cell is empty, and it will move to the last cell with data if the current cell has data in it.
For this command, we will write a function to contain the behavior:
function customSelectRight() {
var sheet = GC.Spread.Sheets.findControl(document.getElementById("ss")).getActiveSheet();
var activeRowIndex = sheet.getActiveRowIndex();
var activeColIndex = sheet.getActiveColumnIndex();
var sheetColCount = sheet.getColumnCount();
var findNextNotNullColIndex = function (sheet, fixRow, offset, stop) {
while (offset < stop) {
if (sheet.getValue(fixRow, offset) !== null) {
break;
}
offset++;
}
return offset;
}
var findNextNullColIndex = function (sheet, fixRow, offset, stop) {
while (offset < stop + 1) {
if (sheet.getValue(fixRow, offset) == null) {
offset--;
break;
}
offset++;
}
return offset;
}
var stopSearchIndex = sheetColCount;
var startSearchIndex = sheet.getActiveColumnIndex() + 1;
var findResult;
if (sheet.getValue(activeRowIndex, startSearchIndex) !== null) {
findResult = findNextNullColIndex(sheet, activeRowIndex, startSearchIndex, stopSearchIndex);
} else {
findResult = findNextNotNullColIndex(sheet, activeRowIndex, startSearchIndex, stopSearchIndex);
}
if (findResult That logic goes through and figures out which column is at the end of the current data region and set’s the active cell to the cell in that column in the same row. This is just an example of one direction, and custom functions will need to be written for each direction with similar logic.
Bind Commands
After the custom functions have been written, we will need to connect the functions to specific key combinations by registering the commands with the command manager:
spread.commandManager().register('customSelectLeft', customSelectLeft);
spread.commandManager().register('customSelectRight', customSelectRight);
spread.commandManager().register('customSelectDown', customSelectDown);
spread.commandManager().register('customSelectUp', customSelectUp);
Now that the commands are registered, we can set key combinations to fire those commands. However, we must also get rid of the default behavior of each key we are binding, so that it only does the command we specify:
spread.commandManager().setShortcutKey(undefined, GC.Spread.Commands.Key.left, true, false, false, false);
spread.commandManager().setShortcutKey('customSelectLeft', GC.Spread.Commands.Key.left, true, false, false, false);
spread.commandManager().setShortcutKey(undefined, GC.Spread.Commands.Key.right, true, false, false, false);
spread.commandManager().setShortcutKey('customSelectRight', GC.Spread.Commands.Key.right, true, false, false, false);
spread.commandManager().setShortcutKey(undefined, GC.Spread.Commands.Key.down, true, false, false, false);
spread.commandManager().setShortcutKey('customSelectDown', GC.Spread.Commands.Key.down, true, false, false, false);
spread.commandManager().setShortcutKey(undefined, GC.Spread.Commands.Key.up, true, false, false, false);
spread.commandManager().setShortcutKey('customSelectUp', GC.Spread.Commands.Key.up, true, false, false, false);
Once that code has been added, the Ctrl+Arrow key combination will move to the end of the current data region in the row or column, depending on the direction. This sort of logic can be applied to other commands that you might want to implement in Spread.Sheets.
You can learn more about Spread.Sheets and download your 30-day evaluation here.