Filter Rows

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 use the available options in the filter dialog.

filter1

The filter dialog helps you to filter by item, sort the list of items, hide filtered rows, as well as clear any applied filter condition on a column. The user can resize the dialog if filter items are too long or short to fit in the filter dialog.

You can create a filter in code with the rowFilter HideRowFilter class. The following code examples shows how to add a filter dialog in a worksheet and apply filter to a range.

// Configure Workbook and Worksheet
var spread = new GC.Spread.Sheets.Workbook("ss");
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(0, 0, 7, 1)));

// Filter column 1 by "North"
var rowFilter = spread.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);

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 space bar can be used to change the checked status.

Customize Filter Dialog

You can control the information shown in the filter dialog by using the filterDialogVisibleInfo method. This method helps to get or set the visible info for the row filter.

The following code sample shows how to hide "sort by value" and list filter areas in a filter dialog.

// Configure Workbook and Worksheet
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
var sheet = spread.getActiveSheet();

// Set a row filter
sheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(1, 1, 10, 3)));
            
// Hide filter options
var filter = sheet.rowFilter();
filter.filterDialogVisibleInfo({
    sortByValue: false,
    sortByColor: true,
    filterByColor: true,
    filterByValue: true,
    listFilterArea: false
})

Filter Dialog List Box

The filter dialog list box supports a hierarchical tree display. It displays the data type items which occupy the maximum amount of cells, whether they be numeric, text, or date type. If the filter data contains different kinds of value types, the filter items will follow a particular order: Date, Number, Text, Logical, and Blank, just like in Excel.

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.

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

The date tree display format is based on the first date type cell format. In case the format is not set by the user, the auto date format will apply.
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: MM, Day pattern: DD

The date type value filter is displayed according to the data set as detailed in the following scenarios:

Scenario	Example
If all the values of the filter range lie in different years, none of the levels are expanded.
If all the values of the filter range lie in the same year, then the year level is expanded.
If all the values of the filter range lie in the same year and same month, then the year level and month level are expanded.

Filter by Color

You can also apply color filters in the filter columns based on two modes:

Filter by Font Color - Shows the cells with selected font color.
Filter by Cell Background Color - Shows the cells with selected background color.

The color filter menu is enabled when the filtered range has more than one type of cell background colors or font colors.