SpreadJS 14
Features / Rows and Columns / Filter Rows
In This Topic
    Filter Rows
    In This Topic

    SpreadJS 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:

    Date filter with a three-level hierarchy

    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.

    Row filtering

    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.

    Create Row Filter

    This example creates a row filter using code.

    JavaScript
    Copy Code 
    $(document).ready(function ()
{
    var spread =
    new GC.Spread.Sheets.Workbook(document.getElementById("ss"),
    {sheetCount:3});
    var activeSheet = spread.getActiveSheet();
    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);
   
    // Set a row Filter
    activeSheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter
    (new GC.Spread.Sheets.Range(-1, 0, -1, 1)));
});

    Filter Rows

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

    Apply Conditional Filter

    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
})

    Get Row Filter Status

    This example gets the status of row filter using code.

    JavaScript
    Copy Code 
    $(document).ready(function ()
{
    var spread =
    new GC.Spread.Sheets.Workbook(document.getElementById("ss"),
    {sheetCount:3});
    var activeSheet = spread.getActiveSheet();
    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);
   
    // Set a rowFilter.
    activeSheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter
    (new GC.Spread.Sheets.Range(0, 0, 7, 1)));
   
    $("#button1").click(function()
     {
          var rowFilter = spread.getActiveSheet().rowFilter();
          if(rowFilter.isFiltered(0))
        {
            console.log("Row-filtering executed for Column1");
        }
       else
        {
            console.log("Row-filtering not executed for Column1");
        }
    });
});

    Get Row Filter Results

    This example gets row filter results using code.

    JavaScript
    Copy Code 
    $(function ()
{
   var spread = new GC.Spread.Sheets.Workbook($("#ss")[0]);
   var activeSheet = spread.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);
   // Set a rowFilter.
   activeSheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter
   (new GC.Spread.Sheets.Range(0, 0, 7, 1)));
   $("#button1").click(function ()
   {
     var rowFilter = spread.getActiveSheet().rowFilter();
     //*********************************************
     
     // Exit if Column1 has not been filtered.
     //*********************************************
     if (!rowFilter.isFiltered(0))
       {
         return;
       }
     //*********************************************
     // Filtered strings
     //*********************************************
     var filterItems = rowFilter.getFilterItems(0);
     var str = "Filtered strings:";
     filterItems.forEach(function (item)
     {
       str += " " + item.expected();
     })
     console.log(str);
     console.log("");
     //*********************************************
     // Number of Filtered-In (displayed) rows
     //*********************************************
     var range = rowFilter.range;
     var filteredInRows = [], filteredOutRows = [];
     for (var i = range.row, last = range.row + range.rowCount; i < last; i++)
      {
        if (rowFilter.isRowFilteredOut(i))
         {
           filteredOutRows.push(i);
         }
           else
         {
           filteredInRows.push(i);
         }
       }
      
      console.log("Number of Filtered-In (displayed) rows: " + filteredInRows.length);
      console.log("");
      // *********************************************
      // Number of Filtered-Out (hidden) rows
      // *********************************************
      console.log("Number of Filtered-Out (hidden) rows: " + filteredOutRows.length);
      console.log("");
      filteredOutRows.forEach(function(item){
      console.log("Filtered-Out (hidden) row index: " + item);
   });
   
     console.log("");
     //*********************************************
    //Filtered-In (displayed)/Filtered-Out (hidden) rows
    //*********************************************
    filteredOutRows.forEach(function(item){
    console.log("Data of Filtered-Out (hidden) row: " + activeSheet.getValue(item, 0));
   });
  
    console.log("");
    filteredInRows.forEach(function(item)
     {
        console.log("Data of Filtered-In (displayed) row: " + activeSheet.getValue(item, 0));
     });
});        
})

    Create Custom Filter

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

    Hide Filter Indicator

    This example hides the filter indicator using code.

    JavaScript
    Copy Code 
     $(document).ready(function ()
 {
    var spread =
    new GC.Spread.Sheets.Workbook(document.getElementById("ss"),
    {sheetCount:3});
    var activeSheet = spread.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);
   
    // Set a row Filter.
    activeSheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter
    (new GC.Spread.Sheets.Range(0, 0, 7, 1)));
    $("#button1").click(function()
    {
        // Hide the filter indicator.                        
        var rowFilter = spread.getActiveSheet().rowFilter();
        rowFilter.filterButtonVisible(0, false);
        activeSheet.repaint();
    });
   
    $("#button2").click(function()
    {
        // Display the filter indicator again.                        
        var rowFilter = spread.getActiveSheet().rowFilter();
        rowFilter.filterButtonVisible(0, true);
        activeSheet.repaint();
    });

    Also, refer Customize Row Filter Actions

    See Also
    HideRowFilter class

     

     

    Copyright © 2021 GrapeCity, Inc. All rights reserved.

    Product Support Forum | Print this page