Filtering Rows

You can hide filtered rows. A filter button is displayed in the column header for a column that allows filtering. The user can click on the button and select the item they wish to filter by and they can sort the list of items.

The user can use the mouse or various keyboard keys to interact with the filter dialog. The Esc key can be used to cancel the dialog. The Tab key can be used to move to different sections and the up and down arrow keys can be used to move in the list of items. The Spacebar can be used to change the checked status.

You can create a filter in code with the rowFilter HideRowFilter class. You can hide the filter icon with the filterButtonVisible method.

Using Code

This example creates a filter.

JavaScript	Copy Code
var cellrange =new GC.Spread.Sheets.Range(0, 2, 5, 1); var hideRowFilter =new GC.Spread.Sheets.Filter.HideRowFilter(cellrange); activeSheet.rowFilter(hideRowFilter); //activeSheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(0,0,4,4)));

Using Code

This example filters rows using code.

JavaScript	Copy Code
$(function () { var workbook = new GC.Spread.Sheets.Workbook($("#ss")[0]); var activeSheet = workbook.getActiveSheet(); activeSheet.setRowCount(7); activeSheet.setValue(0, 0, "North"); activeSheet.setValue(1, 0, "South"); activeSheet.setValue(2, 0, "East"); activeSheet.setValue(3, 0, "South"); activeSheet.setValue(4, 0, "North"); activeSheet.setValue(5, 0, "North"); activeSheet.setValue(6, 0, "West"); activeSheet.setColumnWidth(0, 80); activeSheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(0, 0, 7, 1))); $("#button1").click(function(){ //Filter Column1 by "North". var rowFilter = $("#ss").data("workbook").getActiveSheet().rowFilter(); var condition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, { compareType: GC.Spread.Sheets.ConditionalFormatting.TextCompareType.equalsTo, expected: "North" }); rowFilter.addFilterItem(0, condition); rowFilter.filter(0); }); $("#button2").click(function(){ // Remove filtering for Column1 var rowFilter = $("#ss").data("workbook").getActiveSheet().rowFilter(); if(rowFilter){ rowFilter.removeFilterItems(0); rowFilter.filter(); } }); }); //Add button controls to page <input type="button" id="button1" value="button1"/> <input type="button" id="button2" value="button2"/>

JavaScript

Copy Code

$(function () {
            var workbook = new GC.Spread.Sheets.Workbook($("#ss")[0]);
            var activeSheet = workbook.getActiveSheet();
            activeSheet.setRowCount(7);
            activeSheet.setValue(0, 0, "North");
            activeSheet.setValue(1, 0, "South");
            activeSheet.setValue(2, 0, "East");
            activeSheet.setValue(3, 0, "South");
            activeSheet.setValue(4, 0, "North");
            activeSheet.setValue(5, 0, "North");
            activeSheet.setValue(6, 0, "West");
            activeSheet.setColumnWidth(0, 80);
            activeSheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(0, 0, 7, 1)));
            $("#button1").click(function(){
                //Filter Column1 by "North".
                var rowFilter = $("#ss").data("workbook").getActiveSheet().rowFilter();
                var condition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {
                    compareType: GC.Spread.Sheets.ConditionalFormatting.TextCompareType.equalsTo,
                    expected: "North"
                });
                rowFilter.addFilterItem(0, condition);
                rowFilter.filter(0);
            });
            $("#button2").click(function(){
                // Remove filtering for Column1
                var rowFilter = $("#ss").data("workbook").getActiveSheet().rowFilter();
                if(rowFilter){
                    rowFilter.removeFilterItems(0);
                    rowFilter.filter();
                }
            });

});
//Add button controls to page
<input type="button" id="button1" value="button1"/>
<input type="button" id="button2" value="button2"/>

Using Code

The following code creates a custom filter.

JavaScript	Copy Code
//Create a custom condition. function CustomFilter(){ GC.Spread.Sheets.ConditionalFormatting.Condition.apply(this, arguments); //this.conditionType("CustomFilter"); }; CustomFilter.prototype = new GC.Spread.Sheets.ConditionalFormatting.Condition(); CustomFilter.prototype.evaluate = function (evaluator, row, col) { var value = evaluator.getValue(row, col); if (value !== null && value >= 10 && value <= 50) { //Return True only when the following conditions are satisfied. // (1)Values are entered. // (2)Values are not lower than 10. // (3)Values are not greater than 50. return true; } else { return false; } }; $(function () { var workbook = new GC.Spread.Sheets.Workbook($("#ss")[0]); var activeSheet = workbook.getActiveSheet(); activeSheet.setValue(0, 0, 10); activeSheet.setValue(1, 0, 100); activeSheet.setValue(2, 0, 50); activeSheet.setValue(3, 0, 40); activeSheet.setValue(4, 0, 80); activeSheet.setValue(5, 0, 1); activeSheet.setValue(6, 0, 65); activeSheet.setValue(7, 0, 20); activeSheet.setValue(8, 0, 30); activeSheet.setValue(9, 0, 35); $("#button1").click(function(){ //Set a row Filter. var rowFilter = new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(0, 0, 7, 1)); activeSheet.rowFilter(rowFilter); rowFilter.addFilterItem(0, new CustomFilter()); rowFilter.filter(0); }); }); // Add a button at the bottom of the page <input id="button1" type="button" value="Button1"/>

JavaScript

Copy Code

//Create a custom condition.
        function CustomFilter(){
            GC.Spread.Sheets.ConditionalFormatting.Condition.apply(this, arguments);
            //this.conditionType("CustomFilter");
        };
        CustomFilter.prototype = new GC.Spread.Sheets.ConditionalFormatting.Condition();
        CustomFilter.prototype.evaluate = function (evaluator, row, col) {
            var value = evaluator.getValue(row, col);

            if (value !== null && value >= 10 && value <= 50) {
                //Return True only when the following conditions are satisfied.
                // (1)Values are entered.
                // (2)Values are not lower than 10.
                // (3)Values are not greater than 50.
                return true;
            } else {
                return false;
            }
        };

        $(function () {
            var workbook = new GC.Spread.Sheets.Workbook($("#ss")[0]);
            var activeSheet = workbook.getActiveSheet();
            activeSheet.setValue(0, 0, 10);
            activeSheet.setValue(1, 0, 100);
            activeSheet.setValue(2, 0, 50);
            activeSheet.setValue(3, 0, 40);
            activeSheet.setValue(4, 0, 80);
            activeSheet.setValue(5, 0, 1);
            activeSheet.setValue(6, 0, 65);
            activeSheet.setValue(7, 0, 20);
            activeSheet.setValue(8, 0, 30);
            activeSheet.setValue(9, 0, 35);

            $("#button1").click(function(){
                //Set a row Filter.
                var rowFilter = new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(0, 0, 7, 1));
                activeSheet.rowFilter(rowFilter);
                rowFilter.addFilterItem(0, new CustomFilter());
                rowFilter.filter(0);
            });
        });

// Add a button at the bottom of the page
<input id="button1" type="button" value="Button1"/>