Skip to content

Excel like grid


As of version 5.6 Guriddo jqGrid support Excel like navigation and editing. Actually this method is extended ariaGrid method

Purpose

The Purpose of this enhancement is to support to the extent possible Excel-style keyboard navigation.

When the Gurrido jqGrid control has the keyboard focus, the keyboard focus is always located in one of the grid's cells.
If the cell with focus is an editable cell, then typing any key will make the control in that cell editable, without changing the navigation mode (except for F2).

In general, the table is always in one of two navigation modes.

Mode Description
Table Navigation Left/Right/Down/Up Arrows move the focus to the next appropriate cell saving the contents in the previous cell.
In-Cell Navigation Left/Right/Down/Up Arrows move within the cell element (ex: input text control) i.e Edit Cell, leaving focus on the currently selected cell.

Single-Click w/Mouse on a Cell puts focus in that cell in "Table Navigation Mode".
Double-Click w/Mouse on a Cell puts focus in that cell in "In-Cell Navigation Mode".

If Cell Contents are Edited and another Cell is Clicked w/Mouse then save the contents of Previous cell and move to the clicked cell, navigation mode determined by whether it's a single-click or double-click.

When a cell has the focus, entering F2 puts the cell in "In-Cell Navigation Mode". Hitting F2 again puts the cell in "Table Navigation Mode". In other words, F2 toggles the navigation mode.

When the focus is in a cell with a text or textarea control, in "Table Navigation Mode", starting to type characters will replace the entire contents of the control.
When the focus is in a cell with a text or textarea control, in "In-Cell Navigation Mode", typing characters will insert at the current cursor location.

The tables below explain how to handle specific keystrokes in each of the two navigation modes.
Any keys not listed below should be handled as described above.
Note that except for the ESC key, when leaving a cell always save the currently selected value.

Table Navigation Mode

Key input type==text, textarea, select, radio
Left Arrow Move 1 cell left, hard stop in column 1
Right Arrow Move 1 cell right, hard stop in last column
Up Arrow Move 1 cell up, hard stop in first row
Down Arrow Move 1 cell down, hard stop in last row
Tab The Tab key should move right to the next cell, and stop at the last cell in the row.
Shift+Tab The Shift-Tab key should move left to the previous cell, and stop at the first cell in the row.
Home Move to first column of current row.
End Move to last column of current row.
ESC Restore content to what was in the cell when it received focus, stay in current cell.
Enter Move 1 row down, hard stop in last row.
F2 Switch to In-Cell Navigation Mode.

In-Cell Navigation Mode

Key input type==text, textarea, select, radio
Left Arrow Let the control handle it.
Right Arrow Let the control handle it.
Up Arrow Let the control handle it.
Down Arrow Let the control handle it.
Tab Switch to Table Navigation Mode. If in last column of last row, exit the Gurrido jQGrid control to the next control. Else if in last column of an earlier row, move to first cell of next row. Else move 1 cell right
Shift+Tab Switch to Table Navigation Mode. If in first column of first row, exit the Gurrido jQGrid control to the previous control. Else if in first column of a later row, move to last cell of previous row. Else move 1 cell left.
Home Let the control handle it.
End Let the control handle it.
ESC Restore content to what was in the cell when it received focus, stay in current cell. Switch to Table Navigation Mode.
Enter Switch to Table Navigation Mode. Move 1 row down, hard stop in last row.
F2 Switch to Table Navigation Mode.

Configuration

Basically to set up this mode it is needed to do 3 main steps.

  1. In colModel setup which fields are editable. See Editing options and setup
  2. Setup cell editing. See Cell editing
  3. Call the excelLikeGrid method. See example below.

The method is called like the other jqGrid methods after the grid is constructed and initialized. Suppose the id of the grid is jqGrid, Then

jQuery("#jqGrid").jqGrid({
    .....
}); // jqGrid init
...
jQuery("#jqGrid").jqGrid('excelLikeGrid', params); // excel method

where params is object parameter with the following Properties

params =  {
    beforeDeleteCell : null,
    customCellAction : null,
    customUndoFunction : null,
    specialChars : [
        '~', '!','@', '#', '$','%','^','&','*','(',')','_', '+','{','}', ':', '"', '|','<','>','?',',','.','/',';','\\','[',']'
    ],
    addonChars : false
}

beforeDeleteCell ( id , iRow, iCol, event) is event which is called when the user press Del or Backspace and before deletion of the word. If the event return true the deletion is performed otherwise no deletion is performed. ( not defined )
Parameters passed to this event are

  • id the id of the row
  • iRow the index of the row
  • iCol the index of the column in colModel
  • event - the event when the key is pressed.

customCellAction ( id , iRow, iCol, event) is event which is called every time the user press key different from the following: Up, Down, Enter, Left, Right, Home, End, PgUp, PgDown, Tab, Shif-Tab, F2, Del, Backspace. The default value is null ( not defined )
Parameters passed to this event are

  • id the id of the row
  • iRow the index of the row
  • iCol the index of the column in colModel
  • event - the event when the key is pressed.

customUndoFunction ( id , iRow, iCol, event) is event which is called every time the user press Ctrl-Z (undo). The default value is null ( not defined )
Parameters passed to this event are

  • id the id of the row
  • iRow the index of the row
  • iCol the index of the column in colModel
  • event - the event when the key is pressed.

specialChars - array which defines the special cars that are allowed when the used enter information is edit mode. By default the array contain the following chars:

Array('~', '!','@', '#', '$','%','^','&','*','(',')','_', '+','{','}', ':', '"', '|','<','>','?',',','.','/',';','\\','[',']')

addonChars expression. By default when the user enter data in the cell only Latin characters are allowed. To extend the character set use expression for this purpose. Default value is false. By example to allow Cyrillic character set do:

addonChars : /^[а-я]$/i

It is possible to call the method with setAriaGrid method like this:

jQuery("#jqGrid").jqGrid('setAriaGrid', {
    ...
    body : true,
    excel : true
}); // excel method

To reset the excelLike method use resetAriaGrid or resetAriaBody methods:

jQuery("#jqGrid").jqGrid('resetAriaGrid');
//or
jQuery("#jqGrid").jqGrid('resetAriaBody');

Example

The following example demostrates the use of the excelLikeGrid method:

HTML

...
<table id="jqGrid"></table>
<div id="jqGridPager"></div>
...

JavaScript

$("#jqGrid").jqGrid({
    url: 'data.json',
    datatype: "json",
    loadonce: true,
    colModel: [
        { label: 'Category Name', name: 'CategoryName', width: 75, editable: true },
        { label: 'Product Name', name: 'ProductName', width: 90, editable: true },
        { label: 'Country', name: 'Country', width: 100, editable: true },
        { label: 'Price', name: 'Price', width: 80, sorttype: 'integer', editable: true },
        { label: 'Quantity', name: 'Quantity', width: 80, sorttype: 'number', editable: true }                   
    ],
    width: 780,
    height: 200,
    rowNum: 150,
    cellEdit: true,
    cellsubmit: "clientArray",
    pager: "#jqGridPager"
});
jQuery("#jqGrid").jqGrid('excelLikeGrid', { addonChars : /^[а-я]$/i});