In-cell data bars in jqGrid - possible or not?

Question

I generally don't like using Excel and Microsoft products in general, but Excel 2007/2010 has some very nice conditional formatting features which, sadly, I haven't seen in many other places so far. One of these which I use extensively in business reports is data bars. http://blogs.msdn.com/b/excel/archive/2009/08/07/data-bar-improvements-in-excel-2010.aspx

In my opinion, these data bars are extremely helpful in understanding the meaning of data beyond the numbers. While the difference between 200 and 2000 users is just a hard-to-grasp digit to the human eye, a bar which is 10 times longer is much more intuitive.

My question: Is there any way to include in-cell conditional data bars for every value of a column in jqGrid, mirroring the Excel functionality? This would be the only way I see to get rid of our Excel sheets and implement the reports in an online reporting system. Data bars are simply indispensable once you get used to them, and they are the only reason we still use Excel for reports.

If, as I assume, there is no built-in functionality like this in jqGrid, do you think it would be a lot of work to custom-build it? Do you have any ideas what the best way would be to approach this?

Answer 1

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.

Answer 2

I think it's possible, but with a little planning and a few assumptions.

Assumptions:

If you have a numeric column with a width of lets say 100px, then make a pre determined decision to have 10 possible data bar widths. (0, 10px, 20px, .... 100px). Each of these can be saved as images, You can have your nice gradient end bit too :)

Lets call them 0.png, 10.png, 20.png, .... 100.png

Now the approach would be something along these lines:

1. Let jQGrid do its thing, render the grid etc.
2. Fire some jQuery once its finished pick out the columns where you want the data bars
3. For each column
4. For each cell in above column
5. look at numeric value and scale it down/up by multiplying by a factor (it might need to be based on the largest value in the column) so you get a multiple of 10 between 0 and 100
6. Take this scaled value, lets say 20 and set 20.png as the background for this cell.
7. Rinse and repeat :)