Custom Keyboard Shortcuts in Handsontable
We all love keyboard shortcuts as they boost our productivity and save lots of time. It’s easy to get used to working with them, but it’s painful when we cannot use them anymore. Handsontable, as a web-based spreadsheet, also allows you to use the most popular keyboard shortcuts. This provides users with an experience they’re already familiar with from industry behemoths such as Microsoft Excel and Open Office Calc.
In this article I will focus mainly on showing how Handsontable’s shortcuts can be extended and customized beyond what’s available in Excel and Calc.
Each of us use a different set of shortcuts, depending on what we do in a spreadsheet. For instance, if you do a comparison of products in your e-commerce website it might be handy to be able to automatically fill the adjacent cells with the names of product categories or labels. Accountants, on the other hand, more often use formulas in their spreadsheet, hence the extensive use of related keyboard shortcuts such as ‘show all formulas’ (Ctrl+~), or ‘show formula help’ (Shift+Fn+F1).
Convert text into a hyperlink
In this example I use custom shortcuts to turn text inside a cell into a hyperlink leading to Google search results. You can use either CTRL+M to affect one cell, or CTRL+Q to change all cells in a sheet into links.
To do this, I use the
keydown event listener. I’m listening to events fired when particular keys are pressed, and then call
setCellsMeta to use the right renderer. To change all cells at once I use the
updateSettings method for the entire spreadsheet instance.
getSelected method that we use to track which cells have been selected allows us to track the selection direction. To simplify the process, I am using the Math function to normalize the coordinates that are then used in the
Change the color of text
Using the same method as described in the previous example, I bound CTRL+M/Q to a function that allows me to change the color of the text inside a cell. I call the
render method to refresh the table each time the combination of keys is pressed.
Apply validation to a cell
In cases where you need to validate your data on demand (read more about validation rules), use the
validateCells method to achieve that. In this example, we check all the rendered cells against a pre-defined validation rule. When you hit CTRL+M you should see how all the non-numeric values turn red.
Add a comment with pre-defined text inside
Here I use the CTRL+M shortcut to call the
setCommentAtCell method, which adds a comment with pre-defined text inside (“Accepted”).