Spread.Sheets Documentation
Filtering Rows
Spread.Sheets Documentation > Developer's Guide > Managing the User Interface > Using Row Filtering > Filtering Rows

Spread.Sheets allows users to apply conditional filters (Number filters, Text filters, Date filters, Color filters, Custom Auto) based on the data type of the cells. After the filter operation is performed in a worksheet, the rows that match the filter condition will be displayed and the other rows will be hidden.

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.

You can hide filtered rows. You can also clear the applied filter condition on a column. After you clear the filter, the filtered rows will be shown and the "clear filter" items will be disabled.

If the maximum amount of cells in the filter column are of numeric data type, the number filter will be displayed in the AutoFilterListBox menu. If the maximum amount of cells in the filter column are of text data type, the text filter will be displayed in the AutoFilterListBox menu.

An example of number filter is shown in the below image:

AutoFilterListBox menu for Number filter

AutoFilterListBox menu for Number filter

The FilterDialog ListBox supports hierarchial tree display. If the maximum amount of cells in the filter column are of date data type, the date filter will be displayed in the AutoFilterListBox menu and the data will be displayed as date tree display. If the filter data contains different kinds of the value type, the filter items will follow a particular order: Date, Number, Text, Logical and Blank, just like in Excel.

In the date tree view, the different hierarchy has a different format policy as explained below:

  1. The date tree display format is based on the first date type cell format. In case the format is not set by the user, an auto date format will apply.
  2. When the culture is EN and the date formatter string doesn't include the locale ID, the date tree view format will be displayed as : Year pattern: YYYY, Month pattern: MMMM, Day pattern: DD

An example of date filter with a three-level hierarchy (Year-->Month-->Day) is shown in the below image:

You can also apply color filters in the filter columns based on two modes : 1) Filter by Font Color 2) Filter by Cell Background Color. The color filter menu will be enabled when the filtered range has more than one type of cell background colors or font colors. 

The users 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.

If the filter range includes outline columns, the hierarchy will be the same in FilterDialog ListBox with outline column data and the filter items checkbox will support the tree status in the way: check, uncheck and indeterminate (the status of the children is not the same). For more information, please refer to Outline Columns.

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"/>

Using Code

The following code specifies how to apply conditional filters in a worksheet.

JavaScript
Copy Code
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
var sheet = spread.getActiveSheet();
sheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(1, 1, 10, 3)));
var filter = sheet.rowFilter();
filter.filterDialogVisibleInfo({
    sortByValue: false,
    sortByColor: true,
    filterByColor: true,
    filterByValue: true,
    listFilterArea: false
})     

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"/>
See Also
HideRowFilter class

 

 

Copyright © GrapeCity, Inc. All rights reserved.

Product Support Forum | Print this page