SpreadJS already supports many of Excel's shortcut keys for quickly navigating through the spreadsheet data. However, there may be times you would like to customize your keyboard shortcut combination keys or implement a new Excel shortcut combination. Fortunately, SpreadJS makes this easy to do!
This blog will show you how to implement keyboard shortcut combinations of CTRL and an arrow key to allow the user to fast travel to the end of a data region of a worksheet.
To do this, you will first overwrite the default SpreadJS keyboard shortcut for CTRL and an arrow key as Null and then create custom commands to mimic Excel’s fast navigation. You can also refer to the SpreadJS’s Keyboard Navigation Shortcuts for the current key combinations.
Project Set-Up:
- We will first set up our project by including the required SpreadJS release files and css
- gc.spread.sheets.all
- gc.spread.sheets.charts
- gc.spread.excelio
- gc.spread.sheets.excel2016colorful
- FileSaver
These can be included in you project like so:
<!-- Set-Up 1.) Add Scripts and CSS: SpreadJS Script Files-->
<script
type="text/javascript"
src="SpreadJS\gc.spread.sheets.all.14.0.0.min.js"
></script>
<link
href="SpreadJS\gc.spread.sheets.excel2016colorful.14.0.0.css"
rel="stylesheet"
type="text/css"
/>
- Next, include a DOM element as the container
<!-- Set-Up 2.) Include a DOM element as the container -->
<div class="sample-container">
<div id="ss" class="spread-container"></div>
</div>
- Initialize the SpreadJS Component
window.onload = function () {
// Set-Up 3.) Initialize SpreadJS
var spread = new GC.Spread.Sheets.Workbook(
document.getElementById("ss"),
{ sheetCount: 2 }
);
}
Create Custom Command Functions:
We must write a new custom command that will be fired when hitting specified key combinations so SpreadJS will mimic Excel’s Fast Navigation.
Adjust Selection:
This function is used by the custom commands to adjust the selection of the SpreadJS cells:
// Adjust selection
function getNeedAdjustSelection(selections, rowIndex, colIndex) {
var sel = null;
for (var i = 0; i < selections.length; i++) {
if (selections[i].contains(rowIndex, colIndex)) {
sel = selections[i];
}
}
return sel;
}
Right:
// Custom select right
function customSelectRight(workbook, options) {
var sheet = workbook.getSheetFromName(options.sheetName);
var activeRowIndex = sheet.getActiveRowIndex();
var activeColIndex = sheet.getActiveColumnIndex();
var sheetColCount = sheet.getColumnCount();
var selNeedAdjust = getNeedAdjustSelection(
sheet.getSelections(),
activeRowIndex,
activeColIndex
);
var findNextNotNullColIndex = function (sheet, fixRow, offset, stop) {
while (offset < stop) {
if (sheet.getValue(fixRow, offset) !== null) {
break;
}
offset++;
}
return offset;
};
var rangeChangeSmall =
selNeedAdjust.col + selNeedAdjust.colCount - 1 === activeColIndex &&
selNeedAdjust.colCount > 1
? true
: false;
var stopSearchIndex = rangeChangeSmall ? activeColIndex : sheetColCount;
var startSearchIndex = rangeChangeSmall
? selNeedAdjust.col + 1
: selNeedAdjust.col + selNeedAdjust.colCount;
var findResult = findNextNotNullColIndex(
sheet,
activeRowIndex,
startSearchIndex,
stopSearchIndex
);
if (selNeedAdjust !== null && findResult <= sheetColCount) {
selNeedAdjust.colCount = rangeChangeSmall
? selNeedAdjust.colCount + selNeedAdjust.col - findResult
: findResult - selNeedAdjust.col + 1;
selNeedAdjust.col = rangeChangeSmall ? findResult : selNeedAdjust.col;
sheet.repaint();
}
}
Left:
// Custom select left
function customSelectLeft(workbook, options) {
var sheet = workbook.getSheetFromName(options.sheetName);
var activeRowIndex = sheet.getActiveRowIndex();
var activeColIndex = sheet.getActiveColumnIndex();
var selNeedAdjust = getNeedAdjustSelection(
sheet.getSelections(),
activeRowIndex,
activeColIndex
);
var findFirstNotNullColIndex = function (sheet, fixRow, offset, stop) {
while (offset > stop) {
if (sheet.getValue(fixRow, offset) !== null) {
break;
}
offset--;
}
return offset;
};
var rangeChangeSmall =
selNeedAdjust.col === activeColIndex && selNeedAdjust.colCount > 1
? true
: false;
var stopSearchIndex = rangeChangeSmall ? activeColIndex : 0;
var startSearchIndex = rangeChangeSmall
? selNeedAdjust.col + selNeedAdjust.colCount - 1 - 1
: selNeedAdjust.col - 1;
var findResult = findFirstNotNullColIndex(
sheet,
activeRowIndex,
startSearchIndex,
stopSearchIndex
);
if (selNeedAdjust !== null && findResult >= 0) {
selNeedAdjust.colCount = rangeChangeSmall
? findResult - selNeedAdjust.col + 1
: selNeedAdjust.col - findResult + selNeedAdjust.colCount;
selNeedAdjust.col = rangeChangeSmall ? selNeedAdjust.col : findResult;
sheet.repaint();
}
}
Up:
// Custom select up
function customSelectUp(workbook, options) {
var sheet = workbook.getSheetFromName(options.sheetName);
var activeRowIndex = sheet.getActiveRowIndex();
var activeColIndex = sheet.getActiveColumnIndex();
var selNeedAdjust = getNeedAdjustSelection(
sheet.getSelections(),
activeRowIndex,
activeColIndex
);
var findFirstNotNullRowIndex = function (sheet, fixCol, offset, stop) {
while (offset > stop) {
if (sheet.getValue(offset, fixCol) !== null) {
break;
}
offset--;
}
return offset;
};
var rangeChangeSmall =
selNeedAdjust.row === activeRowIndex && selNeedAdjust.rowCount > 1
? true
: false;
var stopSearchIndex = rangeChangeSmall ? activeRowIndex : 0;
var startSearchIndex = rangeChangeSmall
? selNeedAdjust.row + selNeedAdjust.rowCount - 1 - 1
: selNeedAdjust.row - 1;
var findResult = findFirstNotNullRowIndex(
sheet,
activeColIndex,
startSearchIndex,
stopSearchIndex
);
if (selNeedAdjust !== null && findResult >= 0) {
selNeedAdjust.rowCount = rangeChangeSmall
? findResult - selNeedAdjust.row + 1
: selNeedAdjust.row - findResult + selNeedAdjust.rowCount;
selNeedAdjust.row = rangeChangeSmall ? selNeedAdjust.row : findResult;
sheet.repaint();
}
}
Down:
// Custom select down
function customSelectDown(workbook, options) {
var sheet = workbook.getSheetFromName(options.sheetName);
var activeRowIndex = sheet.getActiveRowIndex();
var activeColIndex = sheet.getActiveColumnIndex();
var sheetRowCount = sheet.getRowCount();
var selNeedAdjust = getNeedAdjustSelection(
sheet.getSelections(),
activeRowIndex,
activeColIndex
);
var findNextNotNullRowIndex = function (sheet, fixCol, offset, stop) {
while (offset < stop) {
if (sheet.getValue(offset, fixCol) !== null) {
break;
}
offset++;
}
return offset;
};
var rangeChangeSmall =
selNeedAdjust.row + selNeedAdjust.rowCount - 1 === activeRowIndex &&
selNeedAdjust.rowCount > 1
? true
: false;
var stopSearchIndex = rangeChangeSmall ? activeRowIndex : sheetRowCount;
var startSearchIndex = rangeChangeSmall
? selNeedAdjust.row + 1
: selNeedAdjust.row + selNeedAdjust.rowCount;
var findResult = findNextNotNullRowIndex(
sheet,
activeColIndex,
startSearchIndex,
stopSearchIndex
);
if (selNeedAdjust !== null && findResult <= sheetRowCount) {
selNeedAdjust.rowCount = rangeChangeSmall
? selNeedAdjust.rowCount + selNeedAdjust.row - findResult
: findResult - selNeedAdjust.row + 1;
selNeedAdjust.row = rangeChangeSmall ? findResult : selNeedAdjust.row;
sheet.repaint();
}
}
Set SpreadJS’s Default Keyboard Shortcut to Null
Before we can apply these custom commands, we must set SpreadJS’s Keyboard Navigation Shortcuts to null.
Right
// default right
cm.setShortcutKey(
null,
GC.Spread.Commands.Key.right,
true,
false,
false,
false
);
Left
// default left
cm.setShortcutKey(
null,
GC.Spread.Commands.Key.left,
true,
false,
false,
false
);
Up
// default up
cm.setShortcutKey(
null,
GC.Spread.Commands.Key.up,
true,
false,
false,
false
);
Down
// default down
cm.setShortcutKey(
null,
GC.Spread.Commands.Key.down,
true,
false,
false,
false
);
Set the Custom Command Shortcut Keys
We will now set the keyboard shortcuts with new custom command name for each arrow and CTRL keyboard pressing combination. Below we broke down the custom commands for the keyboard combination of arrow keys: right, left, up, down.
Here is a link to SpreadJS’s documentation for more information on setting a shortcut key.
Right
// custom right
cm.setShortcutKey(
"ctrlRight",
GC.Spread.Commands.Key.right,
true,
false,
false,
false
);
Left
// custom left
cm.setShortcutKey(
"ctrlLeft",
GC.Spread.Commands.Key.left,
true,
false,
false,
false
);
Up
// custom up
cm.setShortcutKey(
"ctrlUp",
GC.Spread.Commands.Key.up,
true,
false,
false,
false
);
Down
// custom down
cm.setShortcutKey(
"ctrlDown",
GC.Spread.Commands.Key.down,
true,
false,
false,
false
);
Bind Custom Commands to Keyboard Shortcuts
Finally, we will bind our custom command functions to command name of the new keyboard shortcuts.
We will first need to get the command manager using the commandManager method:
// 5.) Bind custom commands to keyboard shortcuts
// 5.1) Get the command manager
var cm = spread.commandManager();
Then, we will register the new keyboard shortcut to the custom command functions using the register method:
// 5.) Bind custom commands to keyboard shortcuts
// 5.1) Get the command manager
var cm = spread.commandManager();
// 5.2) Register the keyboard shortcuts to the custom command functions using the register method
cm.register("ctrlLeft", customSelectLeft);
cm.register("ctrlRight", customSelectRight);
cm.register("ctrlDown", customSelectDown);
cm.register("ctrlUp", customSelectUp);
Call to Action:
Using the code shared here in this tutorial, the keyboard combination of CTRL + Arrow key will move to the end of the current data region in the row or column based on the Arrow key direction.
Users can use this logic to add other Excel Keyboard shortcuts that is not included by default.
You can learn more about SpreadJS and download a 30-day evaluation here.
Mackenzie Albitz