It is an interesting feature of Excel about which you wrote in your question. I haven’t known about this before.
What you need is to implement a custom formater function. In general is is very easy. You should write a small function which display the cell contain based on the value (text over the color bar). Moreover you should define also Unformatting custom function which will be very easy in your case. The unformating function could be used during sorting and other jqGrid operation where one need get the value from the grid cell.
So your problem could be reduced to displaying the number over the color bar.
UPDATED: I though again and again about your question because I find that the usage of colors for formating of numbers could be really helpful. So I spend some time and created the corresponding code example which produce following results
and can be seen live here.
Small comments to the code. I had to create some CSS classes which produce gradient bar in any current browsers excepting Opera where the grid are seen as
The CSS classes are defined as following:
.cellDiv
{
left: 0px; top:5px; height:22px;
position:relative;padding:0;margin-right:-4px;border:0;
}
.cellTextRight
{
position:relative;
margin-right:4px;
text-align:right;
float:right;
}
.gradient1{
/* fallback (Opera) */
background: #008AEF;
/* Mozilla: https://developer.mozilla.org/en/CSS/-moz-linear-gradient */
background: -moz-linear-gradient(left, #008AEF, white);
/* Chrome, Safari: http://webkit.org/blog/175/introducing-css-gradients/ */
background: -webkit-gradient(linear, left top, right top, from(#008AEF), to(white));
/* MSIE http://msdn.microsoft.com/en-us/library/ms532997(VS.85).aspx */
filter: progid:DXImageTransform.Microsoft.Gradient(StartColorStr="#008AEF", EndColorStr="white", GradientType=1);
/*ie8*/
-ms-filter: "progid:DXImageTransform.Microsoft.Gradient(StartColorStr="#008AEF", EndColorStr="white", GradientType=1)";
position: absolute; left: -2px; top:-5px; right: 2px; height:22px; float:left;
}
.gradient2{
background: #63C384;
background: -moz-linear-gradient(left, #63C384 0%, white 100%);
background: -webkit-gradient(linear, left top, right top, from(#63C384), to(white));
filter: progid:DXImageTransform.Microsoft.Gradient(StartColorStr="#63C384", EndColorStr="white", GradientType=1);
-ms-filter: "progid:DXImageTransform.Microsoft.Gradient(StartColorStr="#63C384", EndColorStr="white", GradientType=1)";
position: absolute; left: -2px; top:-5px; right: 2px; height:22px; float:left;
}
and the jqGrid code inside of $(document).ready(function () {/*code*/});:
var grid = $("#list");
var gradientNumberFormat = function (cellvalue, gradientClass, minDataValue,
maxDataValue, minDisplayValue, maxDisplayValue) {
var dataAsNumber = parseFloat(cellvalue); /* parseInt(cellvalue, 10);*/
if (dataAsNumber > maxDataValue) {
dataAsNumber = maxDataValue;
}
if (dataAsNumber < minDataValue) {
dataAsNumber = minDataValue;
}
var prozentVal = minDisplayValue+(dataAsNumber-minDataValue)*(maxDisplayValue-
minDisplayValue)/(maxDataValue-minDataValue);
return '<div class="cellDiv"><div class="'+gradientClass+'" style="width:'+
prozentVal+'%;"></div><div class="cellTextRight">'+cellvalue +
'</div></div>';
};
var mydata = [
{ id: "1", invdate: "2007-10-01", name: "test", note: "note",
amount: "200.00", tax: "10.00", total: "210.00" },
{ id: "2", invdate: "2007-10-02", name: "test2", note: "note2",
amount: "300.00", tax: "20.00", total: "320.00" },
{ id: "3", invdate: "2007-09-01", name: "test3", note: "note3",
amount: "400.00", tax: "30.00", total: "430.00" },
{ id: "4", invdate: "2007-10-04", name: "test", note: "note",
amount: "200.00", tax: "10.00", total: "210.00" },
{ id: "5", invdate: "2007-10-05", name: "test2", note: "note2",
amount: "300.00", tax: "20.00", total: "320.00" },
{ id: "6", invdate: "2007-09-06", name: "test3", note: "note3",
amount: "400.00", tax: "30.00", total: "430.00" },
{ id: "7", invdate: "2007-10-04", name: "test", note: "note",
amount: "200.00", tax: "10.00", total: "210.00" },
{ id: "8", invdate: "2007-10-03", name: "test2", note: "note2",
amount: "300.00", tax: "20.00", total: "320.00" },
{ id: "9", invdate: "2007-09-01", name: "test3", note: "note3",
amount: "400.00", tax: "30.00", total: "430.00" },
{ id: "10", invdate: "2007-10-01", name: "test", note: "note",
amount: "200.00", tax: "10.00", total: "210.00" },
{ id: "11", invdate: "2007-10-02", name: "test2", note: "note2",
amount: "300.00", tax: "20.00", total: "320.00" },
{ id: "12", invdate: "2007-09-01", name: "test3", note: "note3",
amount: "400.00", tax: "30.00", total: "430.00" },
{ id: "13", invdate: "2007-10-04", name: "test", note: "note",
amount: "200.00", tax: "10.00", total: "210.00" },
{ id: "14", invdate: "2007-10-05", name: "test2", note: "note2",
amount: "300.00", tax: "20.00", total: "320.00" },
{ id: "15", invdate: "2007-09-06", name: "test3", note: "note3",
amount: "400.00", tax: "30.00", total: "430.00" },
{ id: "16", invdate: "2007-10-04", name: "test", note: "note",
amount: "200.00", tax: "10.00", total: "210.00" },
{ id: "17", invdate: "2007-10-03", name: "test2", note: "note2",
amount: "300.00", tax: "20.00", total: "320.00" },
{ id: "18", invdate: "2007-09-01", name: "test3", note: "note3",
amount: "400.00", tax: "30.00", total: "430.00" }
];
grid.jqGrid({
data: mydata,
datatype: "local",
colNames: ['Inv No', 'Date', 'Client', 'Amount', 'Tax', 'Total', 'Notes'],
colModel: [
{ name:'id', index:'id', key:true, width:70, align:"right", sorttype:"int",
formatter: function (cellvalue) {
// the number 1 will be mapped to no color bar
// the number 18 will be mapped to the color bar with 100% filled
return gradientNumberFormat(cellvalue, "gradient1", 1, 18, 0, 100);
}
},
{ name:'invdate', index:'invdate', width:90, sorttype:"date" },
{ name:'name', index:'name', width:100 },
{ name:'amount', index:'amount', width:80, align:"right", sorttype:"float",
formatter: function (cellvalue) {
// the number 200 will be mapped to the 10% filled color bar
// the number 400 will be mapped to the 90% filled color bar
return gradientNumberFormat(cellvalue,"gradient2",200,400,10,90);
}
},
{ name:'tax', index:'tax', width:80, align:"right", sorttype:"float" },
{ name:'total', index:'total', width:80, align:"right", sorttype:"float" },
{ name:'note', index:'note', width:150, sortable:false }
],
pager: '#pager',
rowNum: 10,
rowList: [5, 10, 20, 50],
sortname: 'id',
sortorder: 'desc',
viewrecords: true,
height: "100%",
caption: "Numbers with gradient color"
});
grid.jqGrid('navGrid', '#pager',
{ add:false, edit:false, del:false, search:false, refresh:true });
UPDATED: The actualized version of the demo is here.