ig.excel.WorksheetTable

Represents a region of cells formatted as a table.

Remarks

Tables assist in managing and analyzing a range of related data. This management can be done separately from the rest of the data in the worksheet.

A table can have one or more columns sorted and filtered. There are various sorting and filtering criteria that can be applied to the columns. The types pertaining to filtering can be found in the Infragistics.Documents.Excel.Filtering namespace and a filter can be applied to a column by setting the WorksheetTableColumn.filter property. The types pertaining to sorting can be found in the Infragistics.Documents.Excel.Sorting namespace and a column can be sorted by setting the WorksheetTableColumn.sortCondition or by populating the SortSettings`1.sortConditions collection on the WorksheetTable.sortSettings.

A table can contain calculated columns which dynamically determine their value based on a formula. A WorksheetTableColumn can be made a calculated column by setting the WorksheetTableColumn.columnFormula.

A table can also contain a totals row which display total information about the table. This can be shown by setting WorksheetTable.isTotalsRowVisible to True. When the totals row is displayed, each column can display text or a calculated value in the totals row, by setting either the WorksheetTableColumn.totalLabel or WorksheetTableColumn.totalFormula, respectively.

Dependencies

jquery-1.4.4.js
infragistics.util.js
infragistics.ext_core.js
infragistics.ext_collections.js
infragistics.ext_collectionsExtended.js
infragistics.ext_io.js
infragistics.ext_text.js
infragistics.documents.core_core.js
infragistics.ext_ui.js
The current widget has no options.
The current widget has no events.

  • areaFormats

    .areaFormats( );

    Gets the collection of formats used for each area of the WorksheetTable.

    Remarks

    The available areas of the table which can have a format set are the whole table, 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 tables are set when the table is resized to give it more columns, the area formats of the new columns will be initialized with the area formats from the table.

    See Also

  • areaFormats

    .areaFormats( area:ig.excel.WorksheetTableArea );
    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.WorksheetTableArea
    • The area for which to get the format.

    Exceptions

    Exception Description
    ig.excel.InvalidEnumArgumentException area is not defined.

  • clearFilters

    .clearFilters( );

    Clears all filters from the columns in the table.

    Remarks

    If any filters are present and removed when this is called, all hidden rows in the data area of the table will be unhidden.

    See Also

  • clearSortConditions

    .clearSortConditions( );

    Clears all sort conditions from the columns in the table.

    Remarks

    Note: Just as in Microsoft Excel, clearing the sort conditions will not revert the table back to its original unsorted state. The table will remain in its last sorted order.

    See Also

  • columns

    .columns( );

    Gets the collection of columns in the table.

    Remarks

    Each column is represented by a WorksheetTableColumn instance and contains various settings for controlling the contents, formatting, sorting, and filtering of the column.

    See Also

  • columns

    .columns( index:number );
    Return Type:
    ig.excel.WorksheetTableColumn
    Return Type Description:
    The WorksheetTableColumn at the specified index.

    Gets the WorksheetTableColumn at the specified index.

    • index
    • Type:number
    • The 0-based index of the column to get.

    Exceptions

    Exception Description
    ig.ArgumentOutOfRangeException index is less than 0 or greater than or equal to the number of columns in the collection.

  • columns

    .columns( name:string );
    Return Type:
    ig.excel.WorksheetTableColumn
    Return Type Description:
    The WorksheetTableColumn with the specified name or null a column with the specified name doesn't exist.

    Gets the WorksheetTableColumn with the specified name or null if it doesn't exist.

    • name
    • Type:string
    • The name of the column to get.

    Remarks

    Column names are compared case-insensitively.

    See Also

  • dataAreaRegion

    .dataAreaRegion( );

    Gets the WorksheetRegion which represents the region of cells in the data area of the table.

    Remarks

    The data area of the table can be changed by using one of the Resize overloads.

    See Also

  • deleteColumns

    .deleteColumns( tableColumnIndex:number, [count:number] );

    Deletes one or more columns from the table

    • tableColumnIndex
    • Type:number
    • The deletion start relative to the first column in the table.
    • count
    • Type:number
    • Optional
    • The number of columns to delete.

    Exceptions

    Exception Description
    ig.InvalidOperationException If the operation is not allowed, e.g. if it would cause data to be shifted off the worksheet.
    ig.IndexOutOfRangeException If index is negative.
    ig.ArgumentOutOfRangeException If count is less than 1 or greater than or equal all the columns in the table.

  • deleteDataRows

    .deleteDataRows( dataRowIndex:number, [count:number] );

    Deletes one or more data rows from the table

    • dataRowIndex
    • Type:number
    • The deletion start relative to the first row in the WorksheetTable.dataAreaRegion.
    • count
    • Type:number
    • Optional
    • The number of rows to delete.

    Exceptions

    Exception Description
    ig.InvalidOperationException If the operation is not allowed, e.g. if it would cause data to be shifted off the worksheet.
    ig.IndexOutOfRangeException If index is negative.
    ig.ArgumentOutOfRangeException If count is less than 1 or greater than or equal all the rows (excluding header and total rows) in the table.

  • displayBandedColumns

    .displayBandedColumns( );

    Gets the value which indicates whether the alternate column format should be applied to the appropriate columns of the WorksheetTable.

    Remarks

    The column formats are defined by the WorksheetTableStyle applied to the WorksheetTable. These are stored in the WorksheetTableStyle.areaFormats collection and keyed by the WorksheetTableStyleArea.ColumnStripe and WorksheetTableStyleArea.AlternateColumnStripe values. If there is no area format applied for the AlternateColumnStripe value, this property has no effect on the display of the table.

    If this value is True and there is an area format for the alternate column stripe, the stripe widths are defined by the WorksheetTableStyle.columnStripeWidth and WorksheetTableStyle.alternateColumnStripeWidth values.

    See Also

  • displayBandedColumns

    .displayBandedColumns( value:boolean );

    Sets the value which indicates whether the alternate column format should be applied to the appropriate columns of the WorksheetTable.

    • value
    • Type:boolean

    Remarks

    The column formats are defined by the WorksheetTableStyle applied to the WorksheetTable. These are stored in the WorksheetTableStyle.areaFormats collection and keyed by the WorksheetTableStyleArea.ColumnStripe and WorksheetTableStyleArea.AlternateColumnStripe values. If there is no area format applied for the AlternateColumnStripe value, this property has no effect on the display of the table.

    If this value is True and there is an area format for the alternate column stripe, the stripe widths are defined by the WorksheetTableStyle.columnStripeWidth and WorksheetTableStyle.alternateColumnStripeWidth values.

    See Also

  • displayBandedRows

    .displayBandedRows( );

    Gets the value which indicates whether the alternate row format should be applied to the appropriate rows of the WorksheetTable.

    Remarks

    The row formats are defined by the WorksheetTableStyle applied to the WorksheetTable. These are stored in the WorksheetTableStyle.areaFormats collection and keyed by the WorksheetTableStyleArea.RowStripe and WorksheetTableStyleArea.AlternateRowStripe values. If there is no area format applied for the AlternateRowStripe value, this property has no effect on the display of the table.

    If this value is True and there is an area format for the alternate row stripe, the stripe widths are defined by the WorksheetTableStyle.rowStripeHeight and WorksheetTableStyle.alternateRowStripeHeight values.

    See Also

  • displayBandedRows

    .displayBandedRows( value:boolean );

    Sets the value which indicates whether the alternate row format should be applied to the appropriate rows of the WorksheetTable.

    • value
    • Type:boolean

    Remarks

    The row formats are defined by the WorksheetTableStyle applied to the WorksheetTable. These are stored in the WorksheetTableStyle.areaFormats collection and keyed by the WorksheetTableStyleArea.RowStripe and WorksheetTableStyleArea.AlternateRowStripe values. If there is no area format applied for the AlternateRowStripe value, this property has no effect on the display of the table.

    If this value is True and there is an area format for the alternate row stripe, the stripe widths are defined by the WorksheetTableStyle.rowStripeHeight and WorksheetTableStyle.alternateRowStripeHeight values.

    See Also

  • displayFirstColumnFormatting

    .displayFirstColumnFormatting( );

    Gets the value which indicates whether the first column format should be applied to the appropriate column of the WorksheetTable.

    Remarks

    The first column format is defined by the WorksheetTableStyle applied to the WorksheetTable. It is stored in the WorksheetTableStyle.areaFormats collection and keyed by the WorksheetTableStyleArea.FirstColumn value. If there is no area format applied for the FirstColumn value, this property has no effect on the display of the table.

    If there is only one column in the table and both the first and last column formatting should be applied, the last column format will take precedence.

    See Also

  • displayFirstColumnFormatting

    .displayFirstColumnFormatting( value:boolean );

    Sets the value which indicates whether the first column format should be applied to the appropriate column of the WorksheetTable.

    • value
    • Type:boolean

    Remarks

    The first column format is defined by the WorksheetTableStyle applied to the WorksheetTable. It is stored in the WorksheetTableStyle.areaFormats collection and keyed by the WorksheetTableStyleArea.FirstColumn value. If there is no area format applied for the FirstColumn value, this property has no effect on the display of the table.

    If there is only one column in the table and both the first and last column formatting should be applied, the last column format will take precedence.

    See Also

  • displayLastColumnFormatting

    .displayLastColumnFormatting( );

    Gets the value which indicates whether the last column format should be applied to the appropriate column of the WorksheetTable.

    Remarks

    The last column format is defined by the WorksheetTableStyle applied to the WorksheetTable. It is stored in the WorksheetTableStyle.areaFormats collection and keyed by the WorksheetTableStyleArea.LastColumn value. If there is no area format applied for the LastColumn value, this property has no effect on the display of the table.

    If there is only one column in the table and both the first and last column formatting should be applied, the last column format will take precedence.

    See Also

  • displayLastColumnFormatting

    .displayLastColumnFormatting( value:boolean );

    Sets the value which indicates whether the last column format should be applied to the appropriate column of the WorksheetTable.

    • value
    • Type:boolean

    Remarks

    The last column format is defined by the WorksheetTableStyle applied to the WorksheetTable. It is stored in the WorksheetTableStyle.areaFormats collection and keyed by the WorksheetTableStyleArea.LastColumn value. If there is no area format applied for the LastColumn value, this property has no effect on the display of the table.

    If there is only one column in the table and both the first and last column formatting should be applied, the last column format will take precedence.

    See Also

  • headerRowRegion

    .headerRowRegion( );
    Return Type:
    ig.excel.WorksheetRegion
    Return Type Description:
    A WorksheetRegion which represents the region of cells in the header row of the table or null if the header row is not visible.

    Gets the WorksheetRegion which represents the region of cells in the header row of the table.

    See Also

  • insertColumns

    .insertColumns( tableColumnIndex:number, [count:number] );

    Inserts one or more columns into the table

    • tableColumnIndex
    • Type:number
    • The insertion point relative to the first column in the table.
    • count
    • Type:number
    • Optional
    • The number of columns to insert

    Exceptions

    Exception Description
    ig.InvalidOperationException If the operation is not allowed, e.g. if it would cause data to be shifted off the worksheet.
    ig.IndexOutOfRangeException If index is negative.
    ig.ArgumentOutOfRangeException If count is less than 1.

  • insertDataRows

    .insertDataRows( dataRowIndex:number, [count:number] );

    Inserts one or more data rows into the table

    • dataRowIndex
    • Type:number
    • The insertion point relative to the first row in the WorksheetTable.dataAreaRegion.
    • count
    • Type:number
    • Optional
    • The number of columns to insert

    Exceptions

    Exception Description
    ig.InvalidOperationException If the operation is not allowed, e.g. if it would cause data to be shifted off the worksheet.
    ig.IndexOutOfRangeException If index is negative.
    ig.ArgumentOutOfRangeException If count is less than 1.

  • isFilterUIVisible

    .isFilterUIVisible( );

    Gets the value indicating whether to allow filtering and show filter buttons in the table headers.

    Exceptions

    Exception Description
    ig.InvalidOperationException The value assigned is True and WorksheetTable.isHeaderRowVisible is False.

    Remarks

    If WorksheetTable.isHeaderRowVisible is False, this property must be False and setting it to True will cause an error. If WorksheetTable.isHeaderRowVisible is set to True, this property will also be set to True automatically.

    See Also

  • isFilterUIVisible

    .isFilterUIVisible( value:boolean );

    Sets the value indicating whether to allow filtering and show filter buttons in the table headers.

    • value
    • Type:boolean

    Exceptions

    Exception Description
    ig.InvalidOperationException The value assigned is True and WorksheetTable.isHeaderRowVisible is False.

    Remarks

    If WorksheetTable.isHeaderRowVisible is False, this property must be False and setting it to True will cause an error. If WorksheetTable.isHeaderRowVisible is set to True, this property will also be set to True automatically.

    See Also

  • isHeaderRowVisible

    .isHeaderRowVisible( );
    Return Type:
    boolean
    Return Type Description:
    True if the row containing column headers is visible; False if it is hidden.

    Gets the value which indicates whether the row containing column headers should be displayed.

    Remarks

    When the header row is visible, the cell above each column of data will contain the WorksheetTableColumn.name value. Therefore, all header cells always contain a string value. Additionally, they will all be unique.

    See Also

  • isHeaderRowVisible

    .isHeaderRowVisible( value:boolean );
    Return Type:
    boolean
    Return Type Description:
    True if the row containing column headers is visible; False if it is hidden.

    Sets the value which indicates whether the row containing column headers should be displayed.

    • value
    • Type:boolean

    Remarks

    When the header row is visible, the cell above each column of data will contain the WorksheetTableColumn.name value. Therefore, all header cells always contain a string value. Additionally, they will all be unique.

    See Also

  • isTotalsRowVisible

    .isTotalsRowVisible( );
    Return Type:
    boolean
    Return Type Description:
    True if the row containing column totals is visible; False if it is hidden.

    Gets the value which indicates whether the row containing column totals should be displayed.

    Exceptions

    Exception Description
    ig.InvalidOperationException The value is set to True and the table occupies the last row of the worksheet.

    Remarks

    When the totals row is visible, the cell below each column of data will contain either a calculated value, a text value, or nothing. To display a calculated value in the cell, set the WorksheetTableColumn.totalFormula. To display a text label, set the WorksheetTableColumn.totalLabel. If both are set, the calculated value takes precedence.

    See Also

  • isTotalsRowVisible

    .isTotalsRowVisible( value:boolean );
    Return Type:
    boolean
    Return Type Description:
    True if the row containing column totals is visible; False if it is hidden.

    Sets the value which indicates whether the row containing column totals should be displayed.

    • value
    • Type:boolean

    Exceptions

    Exception Description
    ig.InvalidOperationException The value is set to True and the table occupies the last row of the worksheet.

    Remarks

    When the totals row is visible, the cell below each column of data will contain either a calculated value, a text value, or nothing. To display a calculated value in the cell, set the WorksheetTableColumn.totalFormula. To display a text label, set the WorksheetTableColumn.totalLabel. If both are set, the calculated value takes precedence.

    See Also

  • reapplyFilters

    .reapplyFilters( );

    Re-filters all data cells in the table based on the filters from the columns in the table.

    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 ReapplyFilters method is called.

    If no columns in the table have filters set, 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.

    See Also

  • reapplySortConditions

    .reapplySortConditions( );

    Re-sorts all data cells in the table based on the sort conditions from the columns in the table.

    See Also

  • resize

    .resize( headerAndDataRegion:ig.excel.WorksheetRegion );

    Resizes the table to a new range of data on the worksheet.

    • headerAndDataRegion
    • Type:ig.excel.WorksheetRegion
    • The new region of headers (if currently visible) and data for the table, which must contain at least one data row, and overlap and have the same top as the current table region.

    Exceptions

    Exception Description
    ig.InvalidOperationException Occurs when the WorksheetTable has previously been removed from the worksheet.
    ig.InvalidOperationException Occurs when the there is a merged cell, array formula, data table, or another table in the new region.
    ig.ArgumentNullException Occurs when headerAndDataRegion is null.
    ig.ArgumentException Occurs when headerAndDataRegion from a different worksheet or has been previously shifted off the table's worksheet.
    ig.ArgumentException Occurs when the top of the headerAndDataRegion does is not the same as the top of the table.
    ig.ArgumentException Occurs when the headerAndDataRegion does not contain at least one data row for the table.
    ig.ArgumentException Occurs when the headerAndDataRegion does not overlap the current table region.

    Remarks

    If the totals row is visible, it will be placed below the last data row automatically. If the totals row is currently inside the new table region, it will be moved out to below the new data rows, which will be shifted up by one row.

  • resize

    .resize( headerAndDataRegionAddress:string );

    Resizes the table to a new range of data on the worksheet.

    • headerAndDataRegionAddress
    • Type:string
    • The address of the new region of headers (if currently visible) and data for the table, which must contain at least one data row, and overlap and have the same top as the current table region.

    Exceptions

    Exception Description
    ig.InvalidOperationException Occurs when the WorksheetTable has previously been removed from the worksheet.
    ig.InvalidOperationException Occurs when headerAndDataRegionAddress is a relative R1C1 address. The overload taking an origin cell must be used to resolve relative R1C1 references.
    ig.InvalidOperationException Occurs when the there is a merged cell, array formula, data table, or another table in the new region.
    ig.ArgumentNullException Occurs when headerAndDataRegionAddress is null.
    ig.ArgumentException Occurs when headerAndDataRegionAddress is not a valid name or a valid cell or region address in the workbook's cell reference mode.
    ig.ArgumentException Occurs when the top of the headerAndDataRegionAddress does is not the same as the top of the table.
    ig.ArgumentException Occurs when the headerAndDataRegionAddress does not contain at least one data row for the table.
    ig.ArgumentException Occurs when the headerAndDataRegionAddress does not overlap the current table region.

    Remarks

    If the totals row is visible, it will be placed below the last data row automatically. If the totals row is currently inside the new table region, it will be moved out to below the new data rows, which will be shifted up by one row.

  • resize

    .resize( headerAndDataRegionAddress:string, cellReferenceMode:ig.excel.CellReferenceMode );

    Resizes the table to a new range of data on the worksheet.

    • headerAndDataRegionAddress
    • Type:string
    • The address of the new region of headers (if currently visible) and data for the table, which must contain at least one data row, and overlap and have the same top as the current table region.
    • cellReferenceMode
    • Type:ig.excel.CellReferenceMode
    • The cell reference mode to use to parse the region address.

    Exceptions

    Exception Description
    ig.InvalidOperationException Occurs when the WorksheetTable has previously been removed from the worksheet.
    ig.InvalidOperationException Occurs when headerAndDataRegionAddress is a relative R1C1 address. The overload taking an origin cell must be used to resolve relative R1C1 references.
    ig.InvalidOperationException Occurs when the there is a merged cell, array formula, data table, or another table in the new region.
    ig.ArgumentNullException Occurs when headerAndDataRegionAddress is null.
    ig.ArgumentException Occurs when headerAndDataRegionAddress is not a valid name or a valid cell or region address in the workbook's cell reference mode.
    ig.ArgumentException Occurs when the top of the headerAndDataRegionAddress does is not the same as the top of the table.
    ig.ArgumentException Occurs when the headerAndDataRegionAddress does not contain at least one data row for the table.
    ig.ArgumentException Occurs when the headerAndDataRegionAddress does not overlap the current table region.

    Remarks

    If the totals row is visible, it will be placed below the last data row automatically. If the totals row is currently inside the new table region, it will be moved out to below the new data rows, which will be shifted up by one row.

  • sortSettings

    .sortSettings( );

    Gets the settings which determine how the data within the table should be sorted.

    Remarks

    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.

    See Also

  • style

    .style( );
    Return Type:
    ig.excel.WorksheetTableStyle
    Return Type Description:
    The WorksheetTableStyle instance which defines the various default table area formats.

    Gets the style to use on the WorksheetTable.

    Exceptions

    Exception Description
    ig.ArgumentException The value specified is not in the Workbook.customTableStyles or Workbook.standardTableStyles collections.

    Remarks

    The WorksheetTableStyle defines formats to use in various areas of the table. These formats are used as defaults for cells which don't have their formatting properties already set.

    The area formats specified in the WorksheetTableStyle are differential formats. In other words, only the properties that are set to non-default values will be applied to the appropriate cells. An area format can define only a background color or only font information and that format will be applied to the cells while all other formatting properties on the cells will be maintained.

    If this value is set to null, the Style will be set to the Workbook.defaultTableStyle.

    See Also

  • style

    .style( value:ig.excel.WorksheetTableStyle );
    Return Type:
    ig.excel.WorksheetTableStyle
    Return Type Description:
    The WorksheetTableStyle instance which defines the various default table area formats.

    Sets the style to use on the WorksheetTable.

    • value
    • Type:ig.excel.WorksheetTableStyle

    Exceptions

    Exception Description
    ig.ArgumentException The value specified is not in the Workbook.customTableStyles or Workbook.standardTableStyles collections.

    Remarks

    The WorksheetTableStyle defines formats to use in various areas of the table. These formats are used as defaults for cells which don't have their formatting properties already set.

    The area formats specified in the WorksheetTableStyle are differential formats. In other words, only the properties that are set to non-default values will be applied to the appropriate cells. An area format can define only a background color or only font information and that format will be applied to the cells while all other formatting properties on the cells will be maintained.

    If this value is set to null, the Style will be set to the Workbook.defaultTableStyle.

    See Also

  • toString

    .toString( );
    Return Type:
    string
    Return Type Description:
    The string representation of the table.

    Gets the string representation of the table.

  • totalsRowRegion

    .totalsRowRegion( );
    Return Type:
    ig.excel.WorksheetRegion
    Return Type Description:
    A WorksheetRegion which represents the region of cells in the totals row of the table or null if the totals row is not visible.

    Gets the WorksheetRegion which represents the region of cells in the totals row of the table.

    See Also

  • wholeTableRegion

    .wholeTableRegion( );

    Gets the WorksheetRegion which represents the region of cells in the whole table, including the header and totals rows, if visible.

    Remarks

    The table region can be changed by using one of the Resize overloads.

    See Also

  • worksheet

    .worksheet( );
    Return Type:
    ig.excel.Worksheet
    Return Type Description:
    The Worksheet to which the table belongs or null if the table has been removed from the Worksheet.

    Gets the WorksheetTable.worksheet to which the table belongs.

    See Also

Copyright © 1996 - 2024 Infragistics, Inc. All rights reserved.