Documentation
Back to the Documentation

Spreadsheet filters

This section brings more details about the methods, events, and properties related to the spreadsheet filters. Jspreadsheet gives the developers a flexible way to interact programmatically with the filters and customize their behavior.

Documentation

Methods

Method Description
setFilter(number, array) Apply filters programatically.
setFilter(columnNumber: Number, values: String[])
getFilter(mixed) Currently filters applied to a column or to all columns.
getFilter(columnNumber: Number|null)
openFilter(number) Open the filter input.
openFilter(columnNumber: Number)
closeFilter() Close the filter input.
closeFilter()
resetFilters() Reset all filters.
resetFilters()
showFilter(number) Enable the filter icon for one or all columns.
showFilter(columnNumber: Number|null)
hideFilter(number) Disable the filter icon for one or all columns.
hideFilter(columnNumber: Number|null)
resetFilters(mixed) Reset the filters for one or all columns..
resetFilters(columnNumber: Number|null)


Related events

It is possible to complete overwrite the filters behavior using onbeforefilter. The event can be used to intercept andcancel or change the filter results.
Events Description
onbeforefilter Action to be executed before applying the filter. Return an array with the valid row numbers or return false to return all rows.
onbeforefilter(worksheet: Object, terms: Object, rowNumbers: Number[]). The terms would be an object that contains the terms used in each of the existing columns.
onfilter After the filter has been applied to the rows.
onfilter(worksheet: Object, terms: String[], rowNumbers: Number[])


Initial Settings

Property Description
filters: boolean Start the spreadsheet with the filters enabled. Default: false


Enable the filter for individual columns

The properties filter is available on the columns object when creating a new spreadsheet. The property filter: true will enabled the filter for the column specified.

jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: data,
        filters: false,
        columns: [
            { type: 'text', title: 'Car', filter: true },
            { type: 'text' },
         ]
     }]
});


Examples

Interacting with the filters programmatically

Enable the filters on the initialization and apply or reset filters programmatically.




Apply ['Honda'] programatically to the first worksheet, second column


Source code

<html>
<script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v4/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />

<div id="spreadsheet"></div>

<input type='button' value='Apply' class='jbutton dark' onclick="apply()">
<input type='button' value='Reset' class='jbutton dark' onclick="reset()">

<script>
var apply = function() {
    spreadsheet.worksheets[0].setFilter(1, ['Honda']);
}
var reset = function() {
    spreadsheet.worksheets[0].resetFilters();
}

var data = [
    ['Jazz', 'Honda', '2019-02-12', true, '2000,00', '=E1*0.1', '#777700'],
    ['Civic', 'Honda', '2018-07-11', false, '4000,01', '=E2*0.1', '#007777'],
    ['Civic', 'Honda', '2018-07-12', true, '3200,01', '=E3*0.1', '#117717'],
    ['Picanto', 'Kia', '2018-07-12', false, '4000,00', '=E4*0.1', '#ffb74d'],
    ['Optima', 'Kia', '2020-01-12', false, '3000,00', '=E5*0.1', '#4db6ac'],
];

var spreadsheet = jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: data,
        filters: true,
        columns: [
            {
                type:'text',
                title:'Car',
                width:120
            },
            {
                type: 'dropdown',
                title:'Make',
                width:180,
                source:[
                    "Alfa Romeo",
                    "Audi",
                    "Bmw",
                    "Chevrolet",
                    "Chrystler",
                    "Dodge",
                    "Ferrari",
                    "Fiat",
                    "Ford",
                    "Honda",
                    "Hyundai",
                    "Jaguar",
                    "Jeep",
                    "Kia",
                    "Mazda",
                    "Mercedez-Benz",
                    "Mitsubish",
                    "Nissan",
                    "Peugeot",
                    "Porsche",
                    "Subaru",
                    "Suzuki",
                    "Toyota",
                    "Volkswagen"
                  ]
            },
            {
                type: 'calendar',
                title:'Available',
                width:120,
                options:{ format:'DD/MM/YYYY' }
            },
            {
                type: 'checkbox',
                title:'Stock',
                width:80
            },
            {
                type: 'number',
                title:'Price',
                mask:'$ #.##,00',
                width:100,
                decimal:','
            },
            {
                type: 'text',
                width:110,
                title:'Commision',
                truncate: 3,
            },
            {
                title: 'Color',
                type: 'color',
                width:100,
                render:'square',
            },
         ]
     }]
});
</script>
</html>

Enable filters for individual columns

It is possible to enable the filters at the column level. It means you can use the filters for one specific column only.



Source code

<html>
<script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v4/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />

<div id="spreadsheet-2"></div>

<script>
jspreadsheet(document.getElementById('spreadsheet-2'), {
    worksheets: [{
        data: data2,
        filters: false,
        columns: [
            { type: 'text', filter: true, width: '300px' },
            { type: 'text' },
         ]
     }]
});
</script>
</html>

Customize the spreadsheet filter behavior

Custom filter can be achieved using the onbeforefilter event.


In this example, if Canada is present in the terms of of the first column filter, always display all rows.

Source code

<html>
<script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v4/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />

<div id="spreadsheet-3"></div>

<script>
var data3 = [
    ['United States', 'Wholemeal', 'Yes', '2019-02-12'],
    ['Canada', 'Breakfast Cereals', 'Yes', '2019-03-01'],
    ['Canada', 'Grains', 'No', '2018-11-10'],
    ['Brazil', 'Pasta', 'Yes', '2019-01-12'],
];

jspreadsheet(document.getElementById('spreadsheet-3'), {
    worksheets: [{
        data: data3,
        defaultColWidth: '140px',
        filters: true,
    }],
    onbeforefilter: function(worksheet, terms, results) {
        // Show all rows if Canada is one of the options
        if (terms[0] && terms[0].indexOf('Canada') >= 0) {
            return false;
        }
        return results;
    }
});
</script>
</html>