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.

Parameters, calling conventions and related Methods

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,
    onKeyCheck : null
}

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.

onKeyCheck(id, iRow, iCol, event) - function to be called every time the key is pressed on the body cell. If the event return false nothing happen. Parameters passed to this function are:

• id - the row id of the focused cell
• iRow - the row index of the focused cell
• iCol - the column index of the focused cell
• event - the keydown event passed to this cell.

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});