spreadJS custom command control

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.

spread custom command

Project Set-Up:

  1. 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"
    />
  1. 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>
  1. 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.

// 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.

// 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.

spreadJS custom command control

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.