Anyway to filter rows with Handsontable?

Question

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 1

I'm not a JS expert, but there is a basic example using http://jsfiddle.net/awyjnbj6/ . This is an answer to my question at Table filter not working with backspaces .

This includes:

Handsontable.Dom.addEvent(searchFiled, 'keyup', function (event) {
    //                    debugger
    hot.loadData(tData);

    var queryResult = hot.search.query(this.value);
    rows = getRowsFromObjects(queryResult);
    console.log('searchFiled',searchFiled.value);
    console.log('rows',rows);

    console.log('tData', tData);
    var filtered = tData.filter(function (d, ix) {
        return rows.indexOf(ix) >= 0;
    });
    console.log('filtered', filtered);

    hot.loadData(filtered);


 });

Answer 2

I have a requirement to do something similar. After some googling I found the following demo:

http://my-waking-dream.blogspot.co.uk/2013/12/live-search-filter-for-jquery.html

However I'd be interested to know if anyone has other approaches that play friendly with column sorting.

Answer 3

As far as I know, the search plugin will search all rows but only highlight them, and only the ones the get rendered. This means that because of lazy rendering (HOT only renders the visible window) if you tried to search with css the cells that are now blue, you'll have a bad time. Instead you can use the object it spits back which has all the matched rows.

From here out to hide the non-matched rows, it gets tough. What I did was write a simple function that physically changes the data array so that the matched rows go to the top, and then I hide the rest. This way the scrolling works fine.

Hope that works!

Answer 4

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.