Problems arise when a developer is tasked with taking a process and develop it for the web. The web paradigm does not lend itself to reproducing Excel very easily, sometimes not at all.
In my situation, Excel really wasn't a part of the business' original process, but they are most familiar with it. We came up with several ideas and concepts using standard grid view conventions. They were turned down because they were not "Excel". After several conversations to try to get at what exactly the business users did not like (which they could not immediately communicate), it finally came down to the fact that they wanted Excel in a web page.
Since we are using Microsoft Office SharePoint Services, it seemed clear that Excel Services would be the ideal solution. Not having used it before, I did some further investigation and determined that there was much to be desired.
I was forced to go back to the grid view. After more conversations it came to light what functionality we needed to satisfy the business users needs. For the amount of data and entry speed they were looking for, the standard way of navigating the controls was no acceptable. To increase the speed of navigating around the grid, I decided that we could implement arrow key navigation similar to Excel.
Goals & enhancements:
- Make the arrow key navigation work similar to Excel
- If a column is not editable, skip it and proceed to the next column with input
- If the caret is at the edge of the table, wrap around to the other side
- Make the solution generic so it can be applied to any grid
- Use javascript objects
- Use as little code as possible and optimize for the code to be Minified.
In the javascript include file:
var Grid = function(table) {
var obj = (typeof table == 'string') ? $("#"+ table)[0] : table;
this.length = obj.rows.length;
this.parent = obj.parentNode;
this.tbl = obj;
this.cellLength = (this.length > 0) ? this.row(1).cells.length : 0;
};
Grid.prototype = {
row: function(idx) {
return this.tbl.rows[idx];
},
cell: function(rowIdx, idx) {
return this.tbl.rows[rowIdx].cells[idx];
},
XYByEl: function(el) {
var td = ((el.tagName=="TD") ?
el :
(el.parentNode.tagName=="TD") ?
el.parentNode :
null);
return (td) ?
{r: td.parentNode.rowIndex, c: td.cellIndex} :
{r: 1, c: 0};
},
firstChild: function(rowIdx, idx) {
return this.firstInput(this.cell(rowIdx, idx));
},
firstInput: function(c) {
if (c!=null)
if (isIE && (c.children[0]!=null)&&( ( (c.children[0].tagName=="INPUT") && ( (c.children[0].type=="text")||(c.children[0].type=="checkbox") ) ) || (c.children[0].tagName=="SELECT"))) {
return c.children[0];
} else {
var cn = c.childNodes;
if (cn) {
for (var idx=0; idx < cn.length; idx++) {
if ( (cn[idx].nodeType == 1)&&( ( (cn[idx].tagName=="INPUT") && ( (cn[idx].type=="text")||(cn[idx].type=="checkbox") ) ) || (cn[idx].tagName=="SELECT")) ){//
cn[idx].setAttribute('autocomplete','off');
return cn[idx];
}
}
}
}
return null;
},
nextInput: function(r, index, adder) {
var fi = this.firstInput(r.cells[index]);
if (fi)
{return this.focusSelect(fi);}
else if (index+adder >= r.cells.length)
return this.nextInput(r, 0, adder);
else if (index+adder < 0)
return this.nextInput(r, this.cellLength-1, adder);
else
return this.nextInput(r, index+adder, adder);
},
focusSelect: function(ctl) {
if (ctl) {
ctl.focus();
if (ctl.type=="text"){ctl.select();}
}
return ctl;
},
cellUp: function(idx, cIdx) {//wrap to bottom when at top
idx = (idx-1 === 0) ? this.length-1 : idx - 1;
var newctl = this.firstChild(idx, cIdx);
return this.focusSelect(newctl);
},
cellDown: function(idx, cIdx) {
var newctl = null;
if(idx+1 == this.length) { //wrap to top when at bottom
newctl = this.firstChild(1, cIdx);
this.parent.scrollTop = 0;
} else {
newctl = this.firstChild(idx + 1, cIdx);
}
return this.focusSelect(newctl);
},
cellLeft: function(idx, cIdx, child) {
if(caretAtBegin(child)) {
var newctl = null;
if (prevKeyUp == 37) {
newctl = this.nextInput(this.row(idx),cIdx-1, -1);
if (!caretAtBegin(newctl)) prevKeyUp = -1;
return this.focusSelect(newctl);
} else {
prevKeyUp = 37;
}
}
},
cellRight: function(idx, cIdx, child) {
if(caretAtEnd(child)) {
if (prevKeyUp == 39) {
if(cIdx == this.cellLength-1)
this.parent.scrollLeft = 0;
newctl = this.nextInput(this.row(idx),cIdx+1, 1);
if (!caretAtEnd(newctl)) prevKeyUp = -1;
return this.focusSelect(newctl);
} else {
prevKeyUp = 39;
}
}
}
};
function getTarget(ev) {
var t = ev.target || ev.srcElement;
return (t && 1 == t.nodeType) ? t : null;
}
var prevKeyUp = -1;
function ArrowKeyNav(gridTable, e) {
if(!e) e=window.event;
var key = e.keyCode;
if (/^(?:3[7-9]|40)$/.test(key)) {
var table = new Grid(ScrollTable),
targ = getTarget(e),
newctl = null,
cellInfo = table.XYByEl(targ),
Index = cellInfo.r, childIndex = cellInfo.c;
if (cellInfo)// != null
switch(key) {
case 40: //down
newctl = table.cellDown(Index, childIndex);
break;
case 38: //up
newctl = table.cellUp(Index,childIndex);
break;
case 37: //left
newctl = table.cellLeft(Index, childIndex, targ);
break;
case 39: //right
newctl = table.cellRight(Index, childIndex, targ);
break;
default:
break;
}//end switch
return newctl;
}//if eventKey
}//end fcn
caretPos = function (control) {
var iCaretPos = 0;
if (control){//!=null
if (document.selection) { //IE Support
var oSel = document.selection.createRange();
oSel.moveStart('character', -1*(control.value.length));
iCaretPos = oSel.text.length;
} else if (control.selectionStart || control.selectionStart == '0')
//Firefox Support
iCaretPos = control.selectionStart;
}
return iCaretPos;
};
caretAtEnd = function(control) {
return ( (control) && ( (control.type=="checkbox")||(control.tagName=="SELECT") ) ) || (caretPos(control) >= control.value.length);
};
caretAtBegin = function(control) {
return ( (control) && ( (control.type=="checkbox")||(control.tagName=="SELECT") ) ) || (caretPos(control) == 0);
};
In the grid page, put a script block with the following in it: $(document).ready(function(){
$("table.NavWithArrows").keyup(function(e){
return ArrowKeyNav([table client id], e);
});
});
This and a couple other tweaks, satisfied the business' need. The class NavWithArrows would be attached to the grid view table. This code catches the on key up event when it bubbles up the DOM to the table element.As it turns out, there is a considerable amount of code to make this functionality happen. Hopefully this will save someone many hours of programming and debugging.
The applications of this go beyond a simple grid application. With a few tweaks, this solution can be applied to a genral input form or extended to allow more than one input control in grid cell(this version assumes there is only one control per cell). I'll leave it to others to extend this functionality.
