Anyway to filter rows with Handsontable?

Michael B picture Michael B · Feb 3, 2015 · Viewed 11.5k times · Source

I'm currently trying to add a search filter on a column in handsontable. I can use the callback of the search plugin to hide the rows with css but that breaks scrolling. The search plugin also seems to only look at the first 100 or so of a table. Is there any plugin that exists that adds row filtering to handsontable?

Answer

Fab picture Fab · Jun 6, 2016

For me, their is two cases for a live filtering with Handsontable. A columns filters, and/or search filter.

1. Individual Column Filter

One filed per column allowing to apply multiple filter at the same time :

function filter() {
    var row, r_len, col, c_len;
    var data = myData; // Keeping the integrity of the original data
    var array = [];
    var match = true;
    for (row = 0, r_len = data.length; row < r_len; row++) {
        for(col = 0, c_len = searchFields.length; col < c_len; col++) {
            if(('' + data[row][col]).toLowerCase().indexOf(searchFields[col]) > -1);
            else match=false;
        }
        if(match) array.push(data[row]);
        match = true;
    }
    hot.loadData(array);
}

Find the working example in this JS Fiddle

2. Search Filter

A field allowing to search any value anywhere in the table :

function filter(search) {
    var 
    row, r_len,
    data = myData, // Keeping the integretity of the original data
    array = [];
    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]).toLowerCase().indexOf(search) > -1) {
                array.push(data[row]);
                break;
            }
        }
    }
    hot.loadData(array);
}

Find the working example in this JS Fiddle


In both case, if the data are destined to be modified, the integrity of the original data must be kept every time a filter is applied. You can refer to the two first links for more details of the two cases.

Note that both function can be merged and be used at the same time.