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 a mouse or various keyboard keys to interact with the filter dialog. SpreadJS provides the following keyboard keys to interact with the filter dialog:

Key	Description
Esc	Cancels the dialog.
Tab	Moves to different sections.
Space Bar	Checks or unchecks the item in the list when in focus.
Enter	Applies the filter.
Left Arrow Key	Collapses all child items of the current item in the list when in focus.
Right Arrow Key	Expands all child items of the current item in the list when in focus.
Up Arrow Key	Switches the focus upwards in the filter dialog or moves the focus outside the list box when the first root node of the list box is in focus or switches the focus upwards in the list.
Down Arrow Key	Switches the focus downwards in the filter dialog or moves the focus outside the list box when the last visible node in the list box is in focus or switches the focus downwards in the list.

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.