Before you can take advantage of the filter settings in the JavaScript Excel Library, you will need to create a Workbook object. You can do this by either reading an existing Microsoft® Excel® file, as explained in the How Do I... topic: Read an Excel File into a Workbook or you can create a blank workbook. When you create a blank workbook, before writing it to a file, you must add at least one worksheet. After a worksheet is created you will be able to add filtering conditions and other related settings to the excel file, as explained by this topic.
Filtering is done by setting a filter condition on a worksheet's WorksheetFilterSettings. Filter conditions are only reapplied when they're are added, removed, modified, or when the reapplyFilters method is called on the sheet. Filters are not constantly evaluated as data within the region changes. Filters are applied to the region only when they are added or removed or when the ReapplyFilters method is called.
If no filters are applied this method will not do anything to the data.
The following table maps the desired Methods managed by the WorksheetFilterSettings.
| Method | Description |
|---|---|
| SetRegion | This is used to specify the region which will be filtered. |
| GetFilter | Gets the filter that is applied to a specific column. |
| Method | Description |
|---|---|
| ApplyAverageFilter | Represents a filter which can filter data based on whether the data is below or above the average of the entire data range. |
| ApplyDatePeriodFilter | Represents a filter which can filter dates in a Month, or quarter of any year. |
| ApplyFillFilter | Represents a filter which will filter cells based on their background fills. This filter specifies a single CellFill. Cells of with this fill will be visible in the data range. All other cells will be hidden. |
| ApplyFixedValuesFilter | Represents a filter which can filter cells based on specific, fixed values, which are allowed to display. |
| ApplyFontColorFilter | Represents a filter which will filter cells based on their font colors. This filter specifies a single color. Cells with this color font will be visible in the data range. All other cells will be hidden. |
| ApplyIconFilter | Represents a filter which can filter cells based on their conditional formatting icon. |
| ApplyRelativeDateRangeFilter | Represents a filter which can filter date cells based on dates relative to the when the filter was applied. |
| ApplyTopOrBottomFilter | Represents a filter which can filter in cells in the upper or lower portion of the sorted values. |
| ApplyYearToDateFilter | Represents a filter which can filter in date cells if the dates occur between the start of the current year and the time when the filter is evaluated. |
| ApplyCustomFilter | Represents a filter which can filter data based on one or two custom conditions. These two filter conditions can be combined with a logical "and" or a logical "or" operation. |
This code shows how to filter cells above the average value of all cells in the column. Filtering below average values is done in a similar manner.
The code in this example creates a workbook, and worksheet to modify the specified region through the WorksheetFilterSettings. After that, a filter is applied on a column of the region. In the end, the workbook is saved so the filtered region can be seen.
Following is the code that implements this example.
In JavaScript:
// Create a new workbook
var workbook = new $.ig.excel.Workbook($.ig.excel.WorkbookFormat.excel2007);
var sheet = workbook.worksheets().add('Sheet1');
// Filter the worksheet object
sheet.filterSettings().setRegion("C1:C15");
sheet.filterSettings().applyCustomFilter(0, new $.ig.excel.CustomFilterCondition($.ig.excel.ExcelComparisonOperator.equals, "Dairy Products"));
View on GitHub