ig.excel.WorksheetTableColumn
Remarks
Each column contains various settings for controlling the contents, formatting, sorting, and filtering within it.
Dependencies
-
applyAverageFilter
- .applyAverageFilter( type:ig.excel.AverageFilterType );
Applies an AverageFilter to the column.
- 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.excel.InvalidEnumArgumentException type is not defined in the AverageFilterType enumeration. 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. -
applyCustomFilter
- .applyCustomFilter( condition:ig.excel.CustomFilterCondition );
Applies a CustomFilter to the column.
- 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 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. 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( condition1:ig.excel.CustomFilterCondition, condition2:ig.excel.CustomFilterCondition, conditionalOperator:ig.excel.ConditionalOperator );
Applies a CustomFilter to the column.
- 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 The WorksheetTable.isFilterUIVisible value of the owning table is False. Filters cannot be applied when the header row or filter button is hidden. 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( type:ig.excel.DatePeriodFilterType, value:number );
Applies an DatePeriodFilter to the column.
- 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.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. 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. 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( fill:ig.excel.CellFill );
Applies a FillFilter to the column.
- fill
- Type:ig.excel.CellFill
- A CellFill by which the cells should be filtered.
Exceptions
Exception Description ig.ArgumentNullException fill is null. 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. -
applyFixedValuesFilter
- .applyFixedValuesFilter( includeBlanks:boolean, calendarType:ig.excel.CalendarType, dateGroups:ig.excel.FixedDateGroup[] );
Applies a FixedValuesFilter to the column.
- 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 The WorksheetTable.isFilterUIVisible value of the owning table is False. Filters cannot be applied when the header row or filter button is hidden. -
applyFontColorFilter
- .applyFontColorFilter( fontColorInfo:ig.excel.WorkbookColorInfo );
Applies a FontColorFilter to the column.
- 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 The WorksheetTable.isFilterUIVisible value of the owning table is False. Filters cannot be applied when the header row or filter button is hidden. -
applyFontColorFilter
- .applyFontColorFilter( fontColor:string );
Applies a FontColorFilter to the column.
- fontColor
- Type:string
- The font color by which the cells should be filtered.
Exceptions
Exception Description ig.ArgumentNullException fontColor is empty. 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. -
applyIconFilter
- .applyIconFilter( iconSet:ig.excel.FormatConditionIconSet, iconIndex:number );
Applies a IconFilter to the column.
- 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.ArgumentOutOfRangeException 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.excel.InvalidEnumArgumentException iconSet is not defined in the FormatConditionIconSet enumeration. -
applyRelativeDateRangeFilter
- .applyRelativeDateRangeFilter( offset:ig.excel.RelativeDateRangeOffset, duration:ig.excel.RelativeDateRangeDuration );
Applies a RelativeDateRangeFilter to the column.
- 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 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. 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( );
Applies a TopOrBottomFilter to the column which will filter in the top 10 values in the list of sorted values.
Exceptions
Exception Description 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. -
applyTopOrBottomFilter
- .applyTopOrBottomFilter( type:ig.excel.TopOrBottomFilterType, value:number );
Applies a TopOrBottomFilter to the column.
- 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.InvalidOperationException The WorksheetTable.isFilterUIVisible value of the owning table is False. Filters cannot be applied when the header row or filter button is hidden. -
applyYearToDateFilter
- .applyYearToDateFilter( );
Applies a YearToDateFilter to the column.
Exceptions
Exception Description 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. -
areaFormats
- .areaFormats( );
Gets the collection of formats used for each area of the column.
Remarks
The available areas of the column which can have a format set are the header, data, and totals areas.
Applying a format to an area will apply the format to all cells in that area.
If any area formats on the columns are set when the table is resized to give it more rows, the new cells in the column will get the new format applied.
-
areaFormats
- .areaFormats( area:ig.excel.WorksheetTableColumnArea );
- Return Type:
- ig.excel.IWorksheetCellFormat
- Return Type Description:
- An IWorksheetCellFormat instance describing the appearance of the specified area.
Gets the format for the specified area.
- area
- Type:ig.excel.WorksheetTableColumnArea
- The area for which to get the format.
Exceptions
Exception Description ig.excel.InvalidEnumArgumentException area is not defined. -
clearFilter
- .clearFilter( );
Removes the filter from the column if one is applied.
Exceptions
Exception Description ig.InvalidOperationException If column was removed from the table. See Also
- ig.excel.WorksheetTableColumn.applyAverageFilter
- ig.excel.WorksheetTableColumn.applyCustomFilter
- ig.excel.WorksheetTableColumn.applyCustomFilter
- ig.excel.WorksheetTableColumn.applyDatePeriodFilter
- ig.excel.WorksheetTableColumn.applyFillFilter
- ig.excel.WorksheetTableColumn.applyFixedValuesFilter
- ig.excel.WorksheetTableColumn.applyFixedValuesFilter
- ig.excel.WorksheetTableColumn.applyFixedValuesFilter
- ig.excel.WorksheetTableColumn.applyFixedValuesFilter
- ig.excel.WorksheetTableColumn.applyFontColorFilter
- ig.excel.WorksheetTableColumn.applyFontColorFilter
- ig.excel.WorksheetTableColumn.applyRelativeDateRangeFilter
- ig.excel.WorksheetTableColumn.applyTopOrBottomFilter
- ig.excel.WorksheetTableColumn.applyTopOrBottomFilter
- ig.excel.WorksheetTableColumn.applyYearToDateFilter
- ig.excel.WorksheetTableColumn.filter
-
columnFormula
- .columnFormula( );
- Return Type:
- ig.excel.Formula
- Return Type Description:
- A Formula instance representing the formula for the data area of the column or null if no formula is applied.
Gets the formula associated with the data area of the column.
Remarks
When the column formula is set and the table is resized to give it more rows, the new cells in the column will have the column formula applied to them.
-
dataAreaRegion
- .dataAreaRegion( );
Gets the WorksheetRegion which represents the region of cells in the data area of the column.
-
filter
- .filter( );
- Return Type:
- ig.excel.Filter
- Return Type Description:
- A WorksheetTableColumn.filter-derived instance if a filter is applied or null if the column is not filtered.
Gets the filter applied to the column.
Remarks
Filters are not constantly evaluated as data within the table changes. Filters are applied to the table only when they are added or removed on a column in the table or when the WorksheetTable.reapplyFilters method is called.
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.
See Also
- ig.excel.WorksheetTableColumn.applyAverageFilter
- ig.excel.WorksheetTableColumn.applyCustomFilter
- ig.excel.WorksheetTableColumn.applyCustomFilter
- ig.excel.WorksheetTableColumn.applyDatePeriodFilter
- ig.excel.WorksheetTableColumn.applyFontColorFilter
- ig.excel.WorksheetTableColumn.applyFontColorFilter
- ig.excel.WorksheetTableColumn.applyFixedValuesFilter
- ig.excel.WorksheetTableColumn.applyFixedValuesFilter
- ig.excel.WorksheetTableColumn.applyFixedValuesFilter
- ig.excel.WorksheetTableColumn.applyFixedValuesFilter
- ig.excel.WorksheetTableColumn.applyRelativeDateRangeFilter
- ig.excel.WorksheetTableColumn.applyTopOrBottomFilter
- ig.excel.WorksheetTableColumn.applyTopOrBottomFilter
- ig.excel.WorksheetTableColumn.applyYearToDateFilter
- ig.excel.WorksheetTableColumn.clearFilter
- ig.excel.WorksheetTable.clearFilters
- ig.excel.WorksheetTable.reapplyFilters
-
headerCell
- .headerCell( );
- Return Type:
- ig.excel.WorksheetCell
- Return Type Description:
- A WorksheetCell which represents the header cell for the column or null if the header row is not visible in the table.
Gets the WorksheetCell which represents the header cell for the column.
-
index
- .index( );
- Return Type:
- number
- Return Type Description:
- The 0-based index of the column in its collection or -1 if the column has been removed from the table.
Gets the 0-based index of the column in the owning WorksheetTable.columns collection.
See Also
-
name
- .name( );
- Return Type:
- string
- Return Type Description:
- The unique name of the column within the owning WorksheetTable.
Gets the name of the column.
Exceptions
Exception Description ig.InvalidOperationException From the setter if column was removed from the table. Remarks
If the header row is visible in the WorksheetTable, the name of the column will be displayed in the cell of the column in the header row.
When the WorksheetTable is created, the column names will be taken from the cells in the header row. If the table does not contain a header row, the column names will be generated.
The column names are unique within the owning WorksheetTable. If, when the table is created, there are two or more columns with the same name, the second and subsequent duplicate column names will have a number appended to make them unique. If any cells in the header row have a non-string value, their value will be changed to a string (the current display text of the cell). If any cells in the header row have no value, they will be given a generated column name.
If the Name property is set to a null or empty string, a column name will be generated. If the value is set to a column name which already exists in the table, the column with the higher index will have a number appended to its name so all column names can stay unique.
-
name
- .name( value:string );
- Return Type:
- string
- Return Type Description:
- The unique name of the column within the owning WorksheetTable.
Sets the name of the column.
- value
- Type:string
Exceptions
Exception Description ig.InvalidOperationException From the setter if column was removed from the table. Remarks
If the header row is visible in the WorksheetTable, the name of the column will be displayed in the cell of the column in the header row.
When the WorksheetTable is created, the column names will be taken from the cells in the header row. If the table does not contain a header row, the column names will be generated.
The column names are unique within the owning WorksheetTable. If, when the table is created, there are two or more columns with the same name, the second and subsequent duplicate column names will have a number appended to make them unique. If any cells in the header row have a non-string value, their value will be changed to a string (the current display text of the cell). If any cells in the header row have no value, they will be given a generated column name.
If the Name property is set to a null or empty string, a column name will be generated. If the value is set to a column name which already exists in the table, the column with the higher index will have a number appended to its name so all column names can stay unique.
-
setColumnFormula
- .setColumnFormula( formula:ig.excel.Formula, overwriteExistingValues:boolean );
Sets the formula to use in the data cells in the column.
- formula
- Type:ig.excel.Formula
- The formula for the data cells of the column or null to remove the current column formula.
- overwriteExistingValues
- Type:boolean
- True to overwrite the existing cells values and apply the formula to all data cells in the column. False to only apply the formula to the cells with no value set.
Exceptions
Exception Description ig.ArgumentException If column was removed from the table. formula is already applied to something else, such as a cell or table column.Remarks
If any relative cell or region references are in the specified formula, it will be assumed that the actual formula is being applied to the first data cell in the column. When the formula is applied to other cells in the column, the relative references will be offset by the appropriate amount.
When the column formula is set and the table is resized to give it more rows, the new cells in the column will have the column formula applied to them.
If there was a different column formula applied previously and it was applied to any of the cells in the column, setting it to a different formula will overwrite the formulas on those cells, regardless of the value of overwriteExistingValues.
-
sortCondition
- .sortCondition( );
- Return Type:
- ig.excel.SortCondition
- Return Type Description:
- The WorksheetTableColumn.sortCondition-derived instance used to sort the column or null of the column is not sorted.
Gets the sort condition used to sort the column in the table.
Exceptions
Exception Description ig.InvalidOperationException From the setter if column was removed from the table. Remarks
When a sort condition is set on the column, the SortConditions collection on the WorksheetTable.sortSettings will be cleared and the new sort condition will be added. To sort by multiple columns, the sort conditions must be added to the SortConditions collection instead of set on the column. However, if a sort condition is cleared with this property, just the sort condition for the column will be removed from the SortConditions collection. All other SortConditions will remain in the collection.
Note: Sort conditions are not constantly evaluated as data within the table changes. Sort conditions are applied to the table only when they are are added or removed on a column in the table or when the WorksheetTable.reapplySortConditions method is called.
-
sortCondition
- .sortCondition( value:ig.excel.SortCondition );
- Return Type:
- ig.excel.SortCondition
- Return Type Description:
- The WorksheetTableColumn.sortCondition-derived instance used to sort the column or null of the column is not sorted.
Sets the sort condition used to sort the column in the table.
- value
- Type:ig.excel.SortCondition
Exceptions
Exception Description ig.InvalidOperationException From the setter if column was removed from the table. Remarks
When a sort condition is set on the column, the SortConditions collection on the WorksheetTable.sortSettings will be cleared and the new sort condition will be added. To sort by multiple columns, the sort conditions must be added to the SortConditions collection instead of set on the column. However, if a sort condition is cleared with this property, just the sort condition for the column will be removed from the SortConditions collection. All other SortConditions will remain in the collection.
Note: Sort conditions are not constantly evaluated as data within the table changes. Sort conditions are applied to the table only when they are are added or removed on a column in the table or when the WorksheetTable.reapplySortConditions method is called.
-
table
- .table( );
- Return Type:
- ig.excel.WorksheetTable
- Return Type Description:
- The WorksheetTable to which the column belongs or null if the column has been removed from the table.
Gets the WorksheetTable to which the column belongs.
-
totalCell
- .totalCell( );
- Return Type:
- ig.excel.WorksheetCell
- Return Type Description:
- A WorksheetCell which represents the total cell for the column or null if the totals row is not visible in the table.
Gets the WorksheetCell which represents the total cell for the column.
-
totalFormula
- .totalFormula( );
Gets the formula to use in the total cell of the column.
Exceptions
Exception Description ig.ArgumentException The value is already applied to something else, such as a cell or table column. ig.InvalidOperationException From the setter if column was removed from the table. Remarks
The total formula can be set regardless of whether or not the totals row is visible. If the totals row is hidden, the formula will not be applied anywhere. When the totals row is visible, it will be applied to the total cell of the column.
Setting the TotalFormula to a non-null value will clear the WorksheetTableColumn.totalLabel, and vice versa.
-
totalFormula
- .totalFormula( value:ig.excel.Formula );
Sets the formula to use in the total cell of the column.
- value
- Type:ig.excel.Formula
Exceptions
Exception Description ig.ArgumentException The value is already applied to something else, such as a cell or table column. ig.InvalidOperationException From the setter if column was removed from the table. Remarks
The total formula can be set regardless of whether or not the totals row is visible. If the totals row is hidden, the formula will not be applied anywhere. When the totals row is visible, it will be applied to the total cell of the column.
Setting the TotalFormula to a non-null value will clear the WorksheetTableColumn.totalLabel, and vice versa.
-
totalLabel
- .totalLabel( );
Gets the text label to use in the total cell of the column.
Exceptions
Exception Description ig.InvalidOperationException From the setter if column was removed from the table. Remarks
The total label can be set regardless of whether or not the totals row is visible. If the totals row is hidden, the label will not be displayed anywhere. When the totals row is visible, it will be set as the value of the total cell of the column.
Setting the WorksheetTableColumn.totalFormula to a non-null value will clear the TotalLabel, and vice versa.
-
totalLabel
- .totalLabel( value:string );
Sets the text label to use in the total cell of the column.
- value
- Type:string
Exceptions
Exception Description ig.InvalidOperationException From the setter if column was removed from the table. Remarks
The total label can be set regardless of whether or not the totals row is visible. If the totals row is hidden, the label will not be displayed anywhere. When the totals row is visible, it will be set as the value of the total cell of the column.
Setting the WorksheetTableColumn.totalFormula to a non-null value will clear the TotalLabel, and vice versa.
-
wholeColumnRegion
- .wholeColumnRegion( );
Gets the WorksheetRegion which represents the region of cells in the whole column, including the header and total cells, if visible.