Saturday, 14 December 2013

Live search filter for jquery handsontable

Search:



I normally don't blog much about my coding skills which is the job that I do daily but since I like Jquery very much I might start doing it more often. Here I have shared a simple live search filter for Handsontable. Just ensure that you declare myData globally in your javascript.

Update:
Since many have asked as to how to keep the changes made to the grid persistent during searching and pagination operations I have added three plugin hooks to model the changes made to the grid to the original data source. You need to hook onto these three events of handsontable.
afterChange - to model the changes when the user searches for a row and edits a cell.
beforeRemoveRow - when the user removes a particular row.
afterCreateRow - when the user inserts a new row at a particular index.
With this your grid is ready and you can post the data to the server to save it in your database. You can do the validations at the client-side or at the server-side.
<style>
.pagination {
        padding: 2px 0;
        margin: 2px;
    }
.pagination a {
            border: 1px solid grey;    
            padding: 2px 5px;
        } 
</style>

<script type="text/javascript">



$(document).ready(function (){

//have a unique row index or a unique running number in your original data source for preserving persistent changes made to the grid. 

var myData = [["Maria Anders", "Sales Representative", "Alfreds Futterkiste", "Germany", "00-800-531-82220", "R||0"],
["Ana Trujillo", "Owner", "Ana Trujillo Emparedados y helados", "Mexico", "00-100-531-7676", "R||1"],
["Antonio Moreno", "Owner", "Antonio Moreno Taquería", "Mexico", "00-200-541-820", "R||2"],
["Thomas Hardy", "Sales Representative", "Around the Horn", "UK", "00-80-591-5620", "R||3"],
["Christina Berglund", "Order Administrator", "Berglunds snabbköp", "Sweden", "00-80-232-86320", "R||4"],
["Hanna Moos", "Sales Representative", "Blauer See Delikatessen", "Germany", "00-300-521-89890", "R||5"],
["Frédérique Citeaux", "Marketing Manager", "Blondel père et fils", "France", "00-200-531-82520", "R||6"],
["Martín Sommer", "Owner", "Bólido Comidas preparadas", "Spain", "00-809-431-82320", "R||7"],
["Laurence Lebihan", "Owner", "Bon app'", "France", "00-800-531-82220", "R||8"],
["Elizabeth Lincoln", "Accounting Manager", "Bottom-Dollar Markets", "Canada", "00-400-561-89220", "R||9"],
["Victoria Ashworth", "Sales Representative", "B's Beverages", "UK", "00-800-531-82220", "R||10"],
["Patricio Simpson", "Sales Agent", "Cactus Comidas para llevar", "Argentina", "00-800-531-82229", "R||11"],
["Francisco Chang", "Marketing Manager", "Centro comercial Moctezuma", "Mexico", "00-800-531-82645", "R||12"],
["Yang Wang", "Owner", "Chop-suey Chinese", "Switzerland", "00-100-521-82456", "R||13"],
["Pedro Afonso", "Sales Associate", "Comércio Mineiro", "Brazil", "00-700-431-85620", "R||14"],
["Elizabeth Brown", "Sales Representative", "Consolidated Holdings", "UK", "00-600-231-82900", "R||15"],
["Sven Ottlieb", "Order Administrator", "Drachenblut Delikatessen", "Germany", "00-200-561-89220", "R||16"],
["Janine Labrune", "Owner", "Du monde entier", "France", "00-300-431-88720", "R||17"],
["Ann Devon", "Sales Agent", "Eastern Connection", "UK", "00-201-531-82850", "R||18"],
["Roland Mendel", "Sales Manager", "Ernst Handel", "Austria", "00-802-541-82820", "R||19"]];


var noOfRowstoShow = 4; //set the maximum number of rows that should be displayed per page.

$("#exampleGrid").handsontable({
    startRows: 5,
    startCols: 5,
    rowHeaders: true,
    colHeaders: true,
    columnSorting: true,
    colHeaders: ["Employee Name", "Designation", "Company Name", "Country", "Contact No"],
    contextMenu: ["row_below", "remove_row", "undo", "redo", "sep1", "sep2", "sep3"],
    columns: [{ data: 0, type: 'text' }, { data: 1, type: 'text' }, { data: 2, type: 'text' }, { data: 3, type: 'text' }, { data: 4, type: 'text'}],
    afterChange: function (change, source) {
        if (source === 'edit') {
            var datarow = $("#exampleGrid").handsontable('getDataAtRow', change[0][0]);
            for (row = 0, r_len = myData.length; row < r_len; row++) {
                for (col = 0, c_len = myData[row].length; col < c_len; col++) {
                    if (myData[row][col] == datarow[5]) {
                        myData[row][change[0][1]] = change[0][3];
                    }
                }
            }
        }
    },
    afterCreateRow: function (index, amount) {
        var rowvalue = myData[myData.length - 1][5];
        var rowno = rowvalue.split("||");
        var newrow = ["", "", "", "", "", "R||" + (parseInt(rowno[1]) + 1)];

        myData.splice(index, 0, newrow);
        getgridData(myData, "1", noOfRowstoShow);
    },
    beforeRemoveRow: function (index, amount) {
        var removerow = $("#exampleGrid").handsontable('getDataAtRow', index);
        var flag = false;
        for (row = 0, r_len = myData.length; row < r_len; row++) {
            for (col = 0, c_len = myData[row].length; col < c_len; col++) {
                if (myData[row][col] == removerow[5]) {

                    myData.splice(row, 1);
                    flag = true;
                }
                if (flag == true) {
                    break;
                }
            }
            if (flag == true) {
                break;
            }
        }

    }
});

loadData();

function loadData() {
    getgridData(myData, "1", noOfRowstoShow);
}

$('#searchgrid').on('keyup', function (event) {
    var value = ('' + this.value).toLowerCase(), row, col, r_len, c_len, td;
    var example = $('#exampleGrid');
    var data = myData;
    var searcharray = [];
    if (value) {
        for (row = 0, r_len = data.length; row < r_len; row++) {
            for (col = 0, c_len = data[row].length; col < c_len; col++) {
                if (data[row][col] == null) {
                    continue;
                }
                if (('' + data[row][col]).toLowerCase().indexOf(value) > -1) {
                    searcharray.push(data[row]);
                    break;
                }
                else {
                }
            }
        }
        getgridData(searcharray, "1", noOfRowstoShow);
    }
    else {
        getgridData(myData, "1", noOfRowstoShow);
    }
});

function getgridData(res, hash, noOfRowstoShow) {

    var page = parseInt(hash.replace('#', ''), 10) || 1, limit = noOfRowstoShow, row = (page - 1) * limit, count = page * limit, part = [];

    for (; row < count; row++) {
        if (res[row] != null) {
            part.push(res[row]);
        }
    }

    var pages = Math.ceil(res.length / noOfRowstoShow);
    $('#gridpage').empty();
    for (var i = 1; i <= pages; i++) {
        var element = $("<a href='#" + i + "'>" + i + "</a>");
        element.bind('click', function (e) {
            var hash = e.currentTarget.attributes[0].nodeValue;
            $('#exampleGrid').handsontable('loadData', getgridData(res, hash, noOfRowstoShow));
        });
        $('#gridpage').append(element);
    }
    $('#exampleGrid').handsontable('loadData', part);
    return part;
}
});
</script>

Search: <input id="searchgrid" type="text" />

<div id="exampleGrid">
</div>
<div id="gridpage" class="pagination">
  </div>