JQGRID: any easy way to implement undo on excel like jqGrid implementation

Question

Edited on 5/14/12: i was finally able to jump out of my laziness and prepare this to share the actual EXCEL LIKE JQGRID implementation. The cell selection may work weird on jsfiddle on few browsers but it should work normal on your development box. Have fun!!!!

Edited on 9/13/11: This is my first use of JQGrid. I'm using ver 4.1.2. I spent few weeks to put together the excel like grid and the biggest challenge was to find the right information on how to use JQGrid. My current setup has lots of ajax updates and image galleries and use of jqgrid formatter but what I've put here is the main code to be able to use JQgrid with server side paging, excel like copy-paste and demonstration of few other features on jqgrid. This is just my way of giving back for all the help that I got from this community.

For people who just jumped to JQGrid, you will soon find out that there is some challenge to using Textarea in jqgrid. you can find some solutions here.

Original post:
Just to give little update before putting my question....

I've been able to come up with some additional features on the jqgrid that I'm using (after going through many forums) including: copy-paste back and forth from Excel to jqgrid, edit cell on keypress and dblclick, copy and paste multiple cells from one block to another on the same grid using mouse selection (from here Using Javascript to 'sum selected cells' in IE6)

Most of the copy paste features works on IE only as of now. I save all the changes together on "Save" button click so all the updates on the cells are on screen only until user hits the "Save" button.

Although, things are still in flux right now, I'd like to have the implementation design on paper now than later. I'm looking for an easy way to UNDO only the LAST change. I've been thinking of using jQuery's "data()" and "removeData()" methods to implement this but if there is anything already existing in jqgrid framework that would help, I would like to know. Any suggestions??

<style type="text/css">
    .sel {background-color: #96B9DC !important; }
    .altered {}
</style>
<script type="text/javascript">
    var enableOnSelectEvent = false; // handle text selection
</script>
<div style="width:100%; background-color:#FFF; border:1px solid #000;"><input id="btnsavechanges" value="Save Changes" style="width:120px;" class="formbutton ui-corner-all" type="button" onclick="getChanges(); return false;" /></div>
<table id="grd_asset" width="100%" onSelectStart="return enableOnSelectEvent;"></table>
<div id="pfrmac" style='width:100%;'></div>
<input type="hidden" id="hidSelected" value="" />

<!-- copy content from the grid cells -->
<input type="hidden" id="hidCopiedText" value="" />

<!-- Start and End of cell selection -->
<input type="hidden" id="hidStartCell" value="" />
<input type="hidden" id="hidEndCell" value="" />

<!-- Start and End of last modified cell(s) -->
<input type="hidden" id="hidModStartCell" value="" />
<input type="hidden" id="hidModEndCell" value="" />

<script type="text/javascript">
    /*************************************************/
    /**************** Grid Utilities  ****************/
    /*************************************************/
    FnGrid = function () {
        this.GridColumns = function () {
            return assetGrid.jqGrid('getGridParam', 'colModel');
        }
        this.GetSelCells = function () {
            return assetGrid.find("td.sel");
        }
        this.ClearSelection = function () {
            assetGrid.find("td").removeClass("sel");
        }
        this.ClearSavedHistory = function () {
            assetGrid.removeData();
        }
        this.ClearMarkedChanges = function () {
            assetGrid.find("tr").removeClass("altered");
        }
        this.GetRowCells = function (cell) {
            return cell.parent().children("td")
        }
        this.GetRowId = function (cell) {
            var row = cell.closest('tr.jqgrow');
            return row.attr('id');
        }
        this.GetRowIndex = function (cell) {
            var cellrow = cell.parent();
            return cellrow.parent().children("tr").index(cellrow);
        }
        this.GetColIndex = function (cell) {
            return cell.parent().children("td").index(cell);
        }
        this.IsInEditMode = function () {
            var savedRows = assetGrid.getGridParam('savedRow');
            return (savedRows && savedRows.length > 0);
        }
        this.PutCellInEdit = function (cell, irow, icol, edit) {
            assetGrid.editCell(irow, icol, edit);
            // transfer focus to the input
            var inp = $(cell).children("input")
            if (inp && inp.length > 0) {
                $(inp[0]).val('');
                $(inp[0]).focus();
            }
        }
        this.HandleEditMode = function (cell, e) {
            var ctrl = e.ctrlKey;
            var alt = e.altKey;

            var keyCode = (e.keyCode ? e.keyCode : e.which);
            if (keyCode) {
                if (keyCode >= 32 && keyCode <= 126 && !ctrl && !alt) {
                    // switch the cell to edit mode if not already
                    if (!($(cell).hasClass("edit-cell"))) {
                        this.PutCellInEdit(cell, this.GetRowIndex($(cell)), this.GetColIndex($(cell)), true);                        }
                }
            }
            return true;
        }
        this.HandleInputNavigation = function (ele, evt) {
            evt = window.event || evt;

            switch (evt.keyCode) {
                // down arrow                   
                case 40:
                    if (!$(ele).parent().hasClass("altered"))
                        $(ele).parent().addClass("altered");

                    irow = this.GetRowIndex($(ele).parent());
                    icol = this.GetColIndex($(ele).parent())
                    var prevcell = irow + "," + icol;
                    $("#hidModStartCell").val(prevcell);
                    $("#hidModEndCell").val(prevcell);

                    downele = $(ele).parent()
                            .parent()
                            .next()
                            .children("td")[this.GetColIndex($(ele).parent())];

                    this.ClearSelection();
                    assetGrid.editCell(this.GetRowIndex($(downele)), this.GetColIndex($(downele)), true);
                    break;

                // up arrow                   
                case 38:
                    if (!$(ele).parent().hasClass("altered"))
                        $(ele).parent().addClass("altered");

                    irow = this.GetRowIndex($(ele).parent());
                    icol = this.GetColIndex($(ele).parent())
                    var prevcell = irow + "," + icol;
                    $("#hidModStartCell").val(prevcell);
                    $("#hidModEndCell").val(prevcell);

                    topele = $(ele).parent()
                            .parent()
                            .prev()
                            .children("td")[this.GetColIndex($(ele).parent())];

                    if (this.GetRowIndex($(topele)) <= 0) break;

                    this.ClearSelection();
                    assetGrid.editCell(this.GetRowIndex($(topele)), this.GetColIndex($(topele)), true);
                    break;
            }
        }
    }

    var autocomp = new AutoCompleteRequest();
    var lastSel = "";
    var assetGrid = $('#grd_asset');
    var start = null;
    var fnassetgrid = new FnGrid();
    var lastSel = -1;

    function selectTo(cell) {
        if (start == null)
            return;
        fnassetgrid.ClearSelection();
        var stop = $(cell);
        var tbl = start.closest("table");
        var rs = tbl.children("tbody").children("tr");
        var r0 = rs.index(start.parent()), c0 = fnassetgrid.GetColIndex(start);
        var r1 = rs.index(stop.parent()), c1 = fnassetgrid.GetColIndex(stop);
        var concat = "";
        for (var i = r0; i <= r1; i++) {
            var cells = $(rs.get(i)).children("td");
            var rowid = 0;
            for (var j = c0; j <= c1; j++) {
                var cell = $(cells.get(j));
                if (rowid == 0) rowid = fnassetgrid.GetRowId(cell);
                if (cell.is(":hidden")) continue;
                cell.addClass("sel");
                concat += assetGrid.getCell(rowid, j) + "\t";
            }
            if (concat.lastIndexOf("\t") == concat.length - 1)
                concat = concat.substring(0, concat.lastIndexOf("\t"));

            concat += escape("\r\n");
        }
        $("#hidSelected").val(concat.trim());
    }


    $(document).ready(function () {
        /*************************************************/
        /******************* THE GRID  *******************/
        /*************************************************/
        assetGrid.jqGrid({
            ajaxGridOptions: { contentType: "application/json; charset=utf-8", type: "POST" },
            url: '../api/yourservices.asmx/GetData',
            datatype: 'json',
            serializeGridData: function (postData) {
                if (postData.searchField === undefined) postData.searchField = null;
                if (postData.searchString === undefined) postData.searchString = null;
                if (postData.searchOper === undefined) postData.searchOper = null;
                if (postData.filters === undefined) postData.filters = null;
                return JSON.stringify(postData);
            },
            colNames: [' ', 'AssetId', 'Item#', 'Make', 'Description'],
            colModel: [
                { name: 'ctrls', width: 80, fixed: true, sortable: false, resize: false, formatter: 'actions',
                    formatoptions: { keys: true }
                },
                { name: 'AssetID', label: 'AssetID', width: 65, key: true, hidden: true },
                { name: 'Sequence', label: 'Item#', width: 50, align: "right", sorttype: 'int', sortable: true, editoptions: { dataEvents: [{ type: 'keydown', fn: function (e) { fnassetgrid.HandleInputNavigation(this, e); } }]} },
                { name: 'Make', label: 'Make', width: 105, editable: true, edittype: 'text', editoptions: {
                    size: 18,
                    dataEvents: [{
                        type: 'focus',
                        fn: function (e) {
                            $(this).autocomplete({
                                source: autocomp.source,
                                delay: autocomp.delay,
                                minLength: autocomp.minLength
                            });

                            $(this).bind("autocompleteopen", autocomp.open);
                            $(this).bind("autocompleteclose", autocomp.close);
                        }
                    }]
                }
                },
                { name: 'Description', label: 'Description', fixed: false, editable: true, edittype: 'textarea', unformat: unfrmttextarea, editoptions: { rows: "10", cols: "40"} }
            ],
            rowNum: 10, /* no of recs in a grid */
            width: 1330,
            rowList: [10, 20, 30], /* array to construct a select box element in the pager */
            pager: '#pfrmac',
            sortname: 'AssetID', /* initial sorting column */
            viewrecords: true,  /* display the number of total records on the pager bar */
            pginput: true,
            sortorder: "desc",
            cellEdit: true,
            shrinkToFit: true,
            jsonReader: {
                root: function (obj) { return obj.d.SearchResultSet; },
                page: function (obj) { return obj.d.PageNum; }, // current page of the query
                total: function (obj) { return obj.d.TotalPages; }, // total pages for the query
                records: function (obj) { return obj.d.TotalNoOfSearchResultItems; },
                id: "AssetID",
                repeatitems: false,
                userdata: function (obj) {
                    extendUserSession();
                    return { "Error": obj.d.Error, "SearchResultSet": obj.d.SearchResultSet }
                }
            },
            loadonce: false,
            caption: "Asset list",
            height: '100%',
            cellsubmit: 'clientArray',
            beforeEditCell: function (rowid, cellname, value, iRow, iCol) {
                enableOnSelectEvent = true;
            },
            beforeSaveCell: function (rowid, cellname, value, iRow, iCol) {
                savedrow = assetGrid.getGridParam('savedRow');
                if (savedrow && savedrow.length > 0) {
                    if (savedrow[0].id == iRow && savedrow[0].ic == iCol && savedrow[0].v != value) {
                        tr = $('#' + rowid);
                        if (tr && !tr.hasClass("altered")) {
                            tr.addClass("altered");
                            there_are_unsaved_changes = 1;
                        }
                    }
                }
            },
            afterSaveCell: function (rowid, cellname, value, iRow, iCol) {
                enableOnSelectEvent = false;
            },
            afterRestoreCell: function (rowid, value, iRow, iCol) {
                enableOnSelectEvent = false;
            },
            loadComplete: function (data) {
                if (assetGrid.getGridParam('userData').Error && assetGrid.getGridParam('userData').Error != '')
                    alert("Error: " + assetGrid.getGridParam('userData').Error);
            },
            gridComplete: function () {
                rowindex = 1;
                rows = assetGrid.find("tr");

                if (rows && rows.length > 1) {
                    for (i = 1; i < rows.length; i++) {
                        $(rows[i]).find("td").each(function (evt) {
                            evt = window.event || evt;

                            start = $(this);
                            colindex = fnassetgrid.GetColIndex(start);
                            if (colindex > 0) {
                                $(this).click(function () {
                                    if (!($(this).hasClass("edit-cell")))
                                        return false;
                                }).dblclick(function () {
                                    if (!($(this).hasClass("edit-cell"))) {
                                        fnassetgrid.PutCellInEdit(this, fnassetgrid.GetRowIndex($(this)), fnassetgrid.GetColIndex($(this)), true);
                                        return;
                                    }
                                    else
                                        return true;
                                }).mousedown(function () {
                                    if (fnassetgrid.IsInEditMode())
                                        return true;
                                    start = $(this);
                                    selectTo(this);
                                    return false;
                                }).mouseover(function () {
                                    if (fnassetgrid.IsInEditMode()) return true;
                                    selectTo(this);
                                }).mouseup(function () {
                                    if (fnassetgrid.IsInEditMode()) return true;
                                    selectTo(this);
                                    $("#hidEndCell").val(fnassetgrid.GetColIndex($(this)));
                                    start = null;
                                }).keypress(function (e) {
                                    fnassetgrid.HandleEditMode(this, e);
                                });
                            }
                        });
                        rowindex++;
                    }
                }
            }
        });

        function unfrmttextarea(cellvalue, options, cellobject) {
            return cellvalue;
        }

        $("body").mouseup(function () {
            start = null;
        });


        /*************************************************/
        /*********** Global KEYUP integration  ***********/
        /*************************************************/
        $(assetGrid).keyup(function (e) {
            var ctrl = e.ctrlKey
            var key = e.charCode || e.keyCode || 0;

            if ((ctrl && key == 88) /* CUT */ || (ctrl && key == 67) /* COPY */ || (ctrl && key == 86) /* PASTE */ || (ctrl && key == 90) /* UNDO */) {

                if ((ctrl && key == 88) /* CUT */ || (ctrl && key == 67) /* COPY */) {
                    if (fnassetgrid.IsInEditMode()) return true;
                    CopyToClipboard("hidSelected");

                    var selectedCells = fnassetgrid.GetSelCells();

                    if (selectedCells && selectedCells.length > 0) {
                        $("#hidStartCell").val(fnassetgrid.GetRowIndex($(selectedCells[0])) + "," + fnassetgrid.GetColIndex($(selectedCells[0])));
                        $("#hidEndCell").val(fnassetgrid.GetRowIndex($(selectedCells[selectedCells.length - 1])) + "," + fnassetgrid.GetColIndex($(selectedCells[selectedCells.length - 1])));
                        $("#hidCopiedText").val($("#hidSelected").val());
                    }
                    else {
                        $("#hidStartCell").val('');
                        $("#hidEndCell").val('');
                    }

                    if (ctrl && key == 88) /* CUT */{
                        assetGrid.find("td.sel").each(function () {
                            row = $(this).closest('tr.jqgrow');
                            rowId = row.attr('id');
                            assetGrid.setCell(rowId, (fnassetgrid.GridColumns())[fnassetgrid.GetColIndex($(this))].name, '', '', '', true);
                        });
                        fnassetgrid.ClearSelection();
                    }
                }
                else if (ctrl && key == 86) /* PASTE */{
                    var clipboardata = getClipboardData();
                    if (get_objtype(clipboardata) != "[object String]") {
                        alert("The data you are pasting either is empty or incompatible");
                        return false;
                    }

                    pasteinfo(assetGrid, clipboardata);
                }
                else if ((ctrl && key == 90) /* UNDO */) {
                // TBD : No jqgrid features available to get the help 
                }
                return false; // prevent bubbling
            }
            else
                return true; // let it bubble
        });
    });


    /********************************************************************/
    /*********** Method to retrieve and submit altered asset information ***********/
    /********************************************************************/
    function getChanges() {
        var editedxml = "<?xml version='1.0' encoding='utf-8' ?\>\n";
        editedxml += "<ASSETS>\n";
        assetGrid.find("tr.altered").each(function () {
            editedxml += "<ASSET>\n";
            $(this).children("td").each(function () {
                colindex = fnassetgrid.GetColIndex($(this));                    
                if (colindex > 0) {
                    editedxml += "<" + (fnassetgrid.GridColumns())[colindex].name.toUpperCase() + ">" + $(this).text().trim() + "</" + (fnassetgrid.GridColumns())[colindex].name.toUpperCase() + ">\n";
                }
            })
            editedxml += "</ASSET>\n";
        })
        editedxml += "</ASSETS>";

        fnassetgrid.ClearMarkedChanges();

        //TBD: submit XML to an AJAX service
    }


    var _browserPasteData = null;
    function getClipboardData() {
        if (_browserPasteData) // Safari/Chrome logic
            return _browserPasteData;
        if (window.clipboardData) // IE logic
        {
            return window.clipboardData.getData("Text");
        }
        else if (typeof (netscape) != "undefined") // Firefox logic
        {
            netscape.security.PrivilegeManager.enablePrivilege('UniversalXPConnect');
            var clip = Components.classes["@mozilla.org/widget/clipboard;1"].createInstance(Components.interfaces.nsIClipboard);
            var trans = Components.classes["@mozilla.org/widget/transferable;1"].createInstance(Components.interfaces.nsITransferable);
            trans.addDataFlavor("text/unicode");
            clip.getData(trans, clip.kGlobalClipboard);
            var str = new Object();
            var len = new Object();
            trans.getTransferData("text/unicode", str, len);
            if (str)
                return str.value.QueryInterface(Components.interfaces.nsISupportsString).toString();
        }
        return null;
    }
    // In Safari/Chrome the clipboard data can only be accessed
    // from the onpaste event. In this sample the event is handled 
    // off the body element: <body onpaste="browserPaste(event)">
    function browserPaste(e) {
        _browserPasteData = e.clipboardData && e.clipboardData.getData ?
            e.clipboardData.getData('text/plain') : null;
    }

    function pasteinfo(objGrid, info) {
        selectedCells = fnassetgrid.GetSelCells();
        firstcell = $(selectedCells[0]);
        firstselectedcolindex = fnassetgrid.GetColIndex(firstcell);
        rowcellscount = fnassetgrid.GetRowCells(firstcell).length;

        if (firstselectedcolindex == 0) {
            alert("You cannot paste into an non-editable column");
            return false;
        }

        if (selectedCells && selectedCells.length > 0) {
            // if the clipboard info is from the asset grid
            if (info && info == $("#hidCopiedText").val()) {
                // get the index values of last copied source cell
                hidStartCell = -1;
                if ($("#hidStartCell").val() != '' && $("#hidStartCell").val().split(',').length > 1) {
                    hidStartCell = $("#hidStartCell").val().split(',')[1];
                }

                // if columns of source and dest do not match, throw warning
                if (firstselectedcolindex != hidStartCell) {
                    if (!confirm("The data you are pasting comes from a different set of \ncolumns than those that you are pasting into.\n\nAre you sure you want to paste into these columns?"))
                        return false;
                }
            }

            $("#hidModStartCell").val(fnassetgrid.GetRowIndex(firstcell) + "," + firstselectedcolindex);

            var prevcell = null;
            // remove the last "line break" and break clipboard info into lines
            datarows = unescape(info).replace(/\r\n$/, '').split("\r\n");
            if (datarows && datarows.length > 0) {
                currentrow = firstcell.parent();
                currentcell = firstcell;

                // if the source is a single cell, allow it to be pasted over multiple cells
                if (datarows.length == 1 && datarows[0].split("\t").length == 1) {
                    copydata = datarows[0].split("\t");

                    $.each(selectedCells, function (index, value) {
                        prevcell = $(value);
                        if (!prevcell.parent().hasClass("altered")) {
                            prevcell.parent().addClass("altered");
                            there_are_unsaved_changes = 1;
                        }
                        var rowId = prevcell.closest('tr.jqgrow').attr('id');
                        var icol = fnassetgrid.GetColIndex(prevcell);
                        assetGrid.setCell(rowId, (fnassetgrid.GridColumns())[icol].name, copydata[0], '', '', true);
                    });
                }
                else {
                    for (i = 0; i < datarows.length && currentrow.length > 0; ++i) {
                        if (datarows[i] == '') break;
                        // break each lines into columns
                        datarows[i] = datarows[i].split("\t");
                        var row = null;
                        var rowId = null;
                        var rowindex = null;
                        for (j = 0; j < datarows[i].length && currentcell.length > 0; ++j) {
                            // mark the row as altered
                            if (!currentcell.parent().hasClass("altered")) {
                                currentcell.parent().addClass("altered");
                                there_are_unsaved_changes = 1;
                            }
                            // for each outer iteration get the rowid
                            if (row == null) {
                                row = (currentcell).closest('tr.jqgrow');
                                rowId = row.attr('id');
                            }
                            var icol = fnassetgrid.GetColIndex(currentcell);
                            assetGrid.setCell(rowId, (fnassetgrid.GridColumns())[icol].name, datarows[i][j], '', '', true);
                            prevcell = currentcell;

                            // advance to the next visible cell -- only consider pasting into visible columns
                            do {
                                currentcell = currentcell.next();
                            }
                            while ((currentcell.length > 0) && currentcell.is(":hidden"))
                        }

                        currentrow = currentrow.next();
                        currentcell = $(currentrow.children("td")[firstselectedcolindex]);
                    }
                }
            }
        }

        if (prevcell.length > 0)
            $("#hidModEndCell").val(fnassetgrid.GetRowIndex(prevcell) + "," + fnassetgrid.GetColIndex(prevcell));
    }

</script>

Many Thanks in advance!

Answer 1

One possibility is to store the last value as an attribute of the cell which can be done using the following

$('#' + rowid + ' > td:eq(' + colIndex + ')').attr('lastval', valueToSave);

where rowid is the row you are working on and colIndex is the number of the column you wish to save the value in. This will create an attribute called lastval that can be used with your undo function. The drawback of this approach is that the entire grid will be updated on a refresh and you will lose the attributes stored within the grid.

Assuming that this is acceptable, then you can save the last value of every cell using

loadComplete: function() {
    $("#list").find("td").each(function(index, elem) {
        $(elem).attr('lastval', $(elem).html());
    });
},

where 'list' is the id of the jqGrid you initially created.

You can update the lastval as part of a beforeSubmit or other callback depending on how you want to maintain the lastval.

I'm sure there are more efficient techniques for doing the above, but with the values getting lost during refresh, I'm not sure this will really help with what you're trying to do. A better approach would be to store these attributes elsewhere in the DOM or back on the server. However, if I'm reading the above comments correctly, you want to keep the lastval in the grid itself.

Answer 2

Rather than a jqgrid-specific implementation, one way to approach this would be to have a secondary jqgrid instance which is not bound to any visible html element.

On any operational classed as a commit, the secondary instance is set as the current (unaltered) jqgrid, and the primary instance (i.e. the one actually displayed) is a duplicate of the secondary with the required change made.

Then all that would be required for an undo operation is to duplicate the secondary instance back into the primary displayed one, hence not needing any knowledge of the specific commit action.

Would also easily extend to multiple undo operations. Could be a resource hog though.