ig.excel.WorksheetFilterSettings
Dependencies
-
applyAverageFilter
- .applyAverageFilter( relativeColumnIndex:number, type:ig.excel.AverageFilterType );
Applies an AverageFilter to the column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
- type
- Type:ig.excel.AverageFilterType
- The value indicating whether to filter in values below or above the average of the data range.
Exceptions
Exception Description ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. ig.excel.InvalidEnumArgumentException type is not defined in the AverageFilterType enumeration. -
applyCustomFilter
- .applyCustomFilter( relativeColumnIndex:number, condition:ig.excel.CustomFilterCondition );
Applies a CustomFilter to the column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
- condition
- Type:ig.excel.CustomFilterCondition
- The condition which must pass for the data to be filtered in.
Exceptions
Exception Description ig.ArgumentNullException condition is null. ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. Remarks
Note: If the filter condition value is longer than 255 characters in length and the workbook is saved in one of the 2003 formats, the correct rows will be hidden in the saved file, but the filter will be missing from the column.
-
applyCustomFilter
- .applyCustomFilter( relativeColumnIndex:number, condition1:ig.excel.CustomFilterCondition, condition2:ig.excel.CustomFilterCondition, conditionalOperator:ig.excel.ConditionalOperator );
Applies a CustomFilter to the column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
- condition1
- Type:ig.excel.CustomFilterCondition
- The first condition used to filter the data.
- condition2
- Type:ig.excel.CustomFilterCondition
- The second condition used to filter the data.
- conditionalOperator
- Type:ig.excel.ConditionalOperator
- The operator which defines how to logically combine condition1 and condition2.
Exceptions
Exception Description ig.ArgumentNullException condition1 is null. ig.excel.InvalidEnumArgumentException conditionalOperator is not defined in the ConditionalOperator enumeration. ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. Remarks
If condition2 is null, the conditionalOperator value is irrelevant.
Note: If one of the filter condition values is longer than 255 characters in length and the workbook is saved in one of the 2003 formats, the correct rows will be hidden in the saved file, but the filter will be missing from the column.
-
applyDatePeriodFilter
- .applyDatePeriodFilter( relativeColumnIndex:number, type:ig.excel.DatePeriodFilterType, value:number );
Applies an DatePeriodFilter to the column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
- type
- Type:ig.excel.DatePeriodFilterType
- The type of date period to filter in.
- value
- Type:number
- The 1-based value of the month or quarter to filter in.
Exceptions
Exception Description ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. ig.excel.InvalidEnumArgumentException type is not defined in the DatePeriodFilterType enumeration. ig.ArgumentException type is Quarter and value is less than 1 or greater than 4 or type is Month and value is less than 1 or greater than 12. Remarks
If the type is Month, a value of 1 indicates January, 2 indicates February, and so on. If type is Quarter, a value of 1 indicates Quarter 1, and so on.
-
applyFillFilter
- .applyFillFilter( relativeColumnIndex:number, fill:ig.excel.CellFill );
Applies a FillFilter to the column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
- fill
- Type:ig.excel.CellFill
- A CellFill by which the cells should be filtered.
Exceptions
Exception Description ig.ArgumentNullException fill is null. ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. -
applyFixedValuesFilter
- .applyFixedValuesFilter( relativeColumnIndex:number, includeBlanks:boolean, calendarType:ig.excel.CalendarType, dateGroups:ig.excel.FixedDateGroup[] );
Applies a FixedValuesFilter to the column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
- includeBlanks
- Type:boolean
- The value which indicates whether blank cells should be filtered in.
- calendarType
- Type:ig.excel.CalendarType
- The calendar type used to interpret values in the dateGroups collection.
- dateGroups
- Type:ig.excel.FixedDateGroup[]
- The collection of fixed date groups which should be filtered in.
Exceptions
Exception Description ig.ArgumentNullException dateGroups is null. ig.ArgumentNullException A FixedDateGroup in the dateGroups collection is null. ig.excel.InvalidEnumArgumentException calendarType is not defined in the CalendarType enumeration. ig.ArgumentException Multiple items in dateGroups are equal to each other. ig.InvalidOperationException includeBlanks is False and dateGroups has no items. At least one value must be allowed. ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. -
applyFontColorFilter
- .applyFontColorFilter( relativeColumnIndex:number, fontColorInfo:ig.excel.WorkbookColorInfo );
Applies a FontColorFilter to the column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
- fontColorInfo
- Type:ig.excel.WorkbookColorInfo
- A WorkbookColorInfo which describes the font color by which the cells should be filtered.
Exceptions
Exception Description ig.ArgumentNullException fontColorInfo is null. ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. -
applyFontColorFilter
- .applyFontColorFilter( relativeColumnIndex:number, fontColor:string );
Applies a FontColorFilter to the column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
- fontColor
- Type:string
- The font color by which the cells should be filtered.
Exceptions
Exception Description ig.ArgumentNullException fontColor is empty. ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. -
applyIconFilter
- .applyIconFilter( relativeColumnIndex:number, iconSet:ig.excel.FormatConditionIconSet, iconIndex:number );
Applies a IconFilter to the column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
- iconSet
- Type:ig.excel.FormatConditionIconSet
- The icon set containing the icon.
- iconIndex
- Type:number
- The zero-based index of the icon in the set or null for 'NoCellIcon'.
Exceptions
Exception Description ig.ArgumentException If the icon set is not valid. ig.IndexOutOfRangeException If the index is less than zero or greater than or equal to the number of icons in the icon set.. ig.InvalidOperationException The WorksheetTable.isFilterUIVisible value of the owning table is False. Filters cannot be applied when the header row or filter button is hidden. ig.InvalidOperationException If column was removed from the table. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. ig.excel.InvalidEnumArgumentException iconSet is not defined in the FormatConditionIconSet enumeration. -
applyRelativeDateRangeFilter
- .applyRelativeDateRangeFilter( relativeColumnIndex:number, offset:ig.excel.RelativeDateRangeOffset, duration:ig.excel.RelativeDateRangeDuration );
Applies a RelativeDateRangeFilter to the column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
- offset
- Type:ig.excel.RelativeDateRangeOffset
- The offset of relative filter. This combined with the duration determines the full range of accepted dates.
- duration
- Type:ig.excel.RelativeDateRangeDuration
- The duration of the full range of accepted dates.
Exceptions
Exception Description ig.excel.InvalidEnumArgumentException offset is not defined in the RelativeDateRangeOffset enumeration. ig.excel.InvalidEnumArgumentException duration is not defined in the RelativeDateRangeDuration enumeration. ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. Remarks
The RelativeDateRangeFilter allows you to filter in dates which are in the previous, current, or next time period relative to the date when the filter was applied. The time periods available are day, week, month, quarter, year. So when using the previous filter type with a day duration, a 'yesterday' filter is created. Or when using a current filter type with a year duration, a 'this year' filter is created. However, these filters compare the data against the date when the filter was created. So a 'this year' filter created in 1999 will filter in all cells containing dates in 1999, even if the workbook is opened in 2012.
-
applyTopOrBottomFilter
- .applyTopOrBottomFilter( relativeColumnIndex:number );
Applies a TopOrBottomFilter to the column which will filter in the top 10 values in the list of sorted values.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
Exceptions
Exception Description ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. -
applyTopOrBottomFilter
- .applyTopOrBottomFilter( relativeColumnIndex:number, type:ig.excel.TopOrBottomFilterType, value:number );
Applies a TopOrBottomFilter to the column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
- type
- Type:ig.excel.TopOrBottomFilterType
- The type of the filter.
- value
- Type:number
- The number or percentage of value of values which should be filtered in.
Exceptions
Exception Description ig.excel.InvalidEnumArgumentException type is not defined in the TopOrBottomFilterType enumeration. ig.ArgumentOutOfRangeException value is less than 1 or greater than 500. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. -
applyYearToDateFilter
- .applyYearToDateFilter( relativeColumnIndex:number );
Applies a YearToDateFilter to the column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
Exceptions
Exception Description ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. -
clearFilter
- .clearFilter( relativeColumnIndex:number );
Clears the filter that is applied to a specific column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
Exceptions
Exception Description ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. -
clearFilters
- .clearFilters( );
Clears all previously applied filters
-
clearRegion
- .clearRegion( );
Clears the region as well as any existing filters and sort criteria
-
getFilter
- .getFilter( relativeColumnIndex:number );
- Return Type:
- ig.excel.Filter
- Return Type Description:
- The apllied filter or null if no filter was applied to this column.
Gets the filter that is applied to a specific column.
- relativeColumnIndex
- Type:number
- A zero based column index relative to the WorksheetFilterSettings.region
Exceptions
Exception Description ig.InvalidOperationException If the WorksheetFilterSettings.region was not set. ig.ArgumentOutOfRangeException If the relativeColumnIndex is outside of the WorksheetFilterSettings.region. -
reapplyFilters
- .reapplyFilters( );
Re-filters all data cells in the filter region based on the applied filters.
Remarks
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.
Note: When the filters are reevaluated, the rows of any cells which don't meet the filter criteria of their column will be hidden. When a row is filtered out, the entire row is hidden from the worksheet, so any data outside the table but in the same row will also be hidden.
-
reapplySortConditions
- .reapplySortConditions( );
Re-sorts all data cells in the region based on the sort conditions.
-
region
- .region( );
Returns the total region including the filter headers (read-only).
-
setRegion
- .setRegion( address:string );
Sets the region where the filter settings should be applied
- address
- Type:string
- The address of the new region.
Exceptions
Exception Description ig.InvalidOperationException If the region intersects with a table of other reserved area in the worksheet. Remarks
The Workbook.cellReferenceMode of the workbook will be used to parse the region address.
Note: setting the region will clear any applied filters or sort criteria.
-
setRegion
- .setRegion( address:string, cellReferenceMode:ig.excel.CellReferenceMode );
Sets the region where the filter settings should be applied
- address
- Type:string
- The address of the new region.
- cellReferenceMode
- Type:ig.excel.CellReferenceMode
- The reference to use the parse the address.
Exceptions
Exception Description ig.InvalidOperationException If the region intersects with a table of other reserved area in the worksheet. Remarks
Note: setting the region will clear any applied filters or sort criteria.
-
sortAndFilterAreaRegion
- .sortAndFilterAreaRegion( );
Returns the region that the filters and sort settings are applied to (read-only).
-
sortSettings
- .sortSettings( );
Gets the settings which determine how the data within the worksheet's filer region should be sorted.
Remarks
Note: Sort conditions are not constantly evaluated as data within the region changes. Sort conditions are applied to the region only when they are are added or removed or when the WorksheetFilterSettings.reapplySortConditions method is called.