Class WorksheetTable

Represents a region of cells formatted as a table.

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 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 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.

see

Worksheet.tables

see

WorksheetRegion.formatAsTable

see

WorksheetRegion.formatAsTable

see

WorksheetCell.associatedTable

see

WorksheetRow.getCellAssociatedTable

Hierarchy

Implements

Constructors

constructor

  • new WorksheetTable(a: string, b: number, c: number, d: number, e: number, f: number): WorksheetTable

Properties

$type

$type: Type

Static $t

$t: Type = markType(WorksheetTable, 'WorksheetTable', (<any>NamedReferenceBase).$, [IAreaFormatsOwner$1_$type.specialize(WorksheetTableArea_$type), IChangeInfoContext_$type, ISortSettingsOwner_$type])

Static nextHashCode

nextHashCode: number

Accessors

comment

  • get comment(): string
  • set comment(a: string): void
  • Gets or sets the comment associated with the named reference or table. The comment associated with the named reference or table.

    throws

    [[ArgumentException]] The value assigned is greater than 255 characters in length.

    Returns string

  • Gets or sets the comment associated with the named reference or table. The comment associated with the named reference or table.

    Parameters

    • a: string

    Returns void

dataAreaRegion

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

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

    see

    resize

    see

    resize

    Returns WorksheetRegion

displayBandedColumns

  • get displayBandedColumns(): boolean
  • set displayBandedColumns(a: boolean): void

displayBandedRows

  • get displayBandedRows(): boolean
  • set displayBandedRows(a: boolean): void

displayFirstColumnFormatting

  • get displayFirstColumnFormatting(): boolean
  • set displayFirstColumnFormatting(a: boolean): void
  • Gets or sets the value which indicates whether the first column format should be applied to the appropriate column of the WorksheetTable.

    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

    displayLastColumnFormatting

    see

    style

    see

    WorksheetTableStyle.areaFormats

    see

    WorksheetTableStyleArea

    Returns boolean

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

    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.

    Parameters

    • a: boolean

    Returns void

displayLastColumnFormatting

  • get displayLastColumnFormatting(): boolean
  • set displayLastColumnFormatting(a: boolean): void
  • Gets or sets the value which indicates whether the last column format should be applied to the appropriate column of the WorksheetTable.

    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

    displayFirstColumnFormatting

    see

    style

    see

    WorksheetTableStyle.areaFormats

    see

    WorksheetTableStyleArea

    Returns boolean

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

    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.

    Parameters

    • a: boolean

    Returns void

headerRowRegion

  • Gets the WorksheetRegion which represents the region of cells in the header row of the table. A WorksheetRegion which represents the region of cells in the header row of the table or null if the header row is not visible.

    see

    isHeaderRowVisible

    see

    resize

    see

    resize

    Returns WorksheetRegion

isFilterUIVisible

  • get isFilterUIVisible(): boolean
  • set isFilterUIVisible(a: boolean): void
  • Gets or sets the value indicating whether to allow filtering and show filter buttons in the table headers.

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

    throws

    [[InvalidOperationException]] The value assigned is True and isHeaderRowVisible is False.

    see

    isHeaderRowVisible

    Returns boolean

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

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

    Parameters

    • a: boolean

    Returns void

isHeaderRowVisible

  • get isHeaderRowVisible(): boolean
  • set isHeaderRowVisible(a: boolean): void
  • Gets or sets the value which indicates whether the row containing column headers should be displayed.

    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.

    True if the row containing column headers is visible; False if it is hidden.
    see

    WorksheetTableColumn.name

    Returns boolean

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

    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.

    True if the row containing column headers is visible; False if it is hidden.

    Parameters

    • a: boolean

    Returns void

isTotalsRowVisible

  • get isTotalsRowVisible(): boolean
  • set isTotalsRowVisible(a: boolean): void
  • Gets or sets the value which indicates whether the row containing column totals should be displayed.

    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.

    True if the row containing column totals is visible; False if it is hidden.
    throws

    [[InvalidOperationException]] The value is set to True and the table occupies the last row of the worksheet.

    see

    WorksheetTableColumn.totalFormula

    see

    WorksheetTableColumn.totalLabel

    Returns boolean

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

    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.

    True if the row containing column totals is visible; False if it is hidden.

    Parameters

    • a: boolean

    Returns void

name

  • get name(): string
  • set name(a: string): void
  • Gets or sets the name of the reference.

    See the overview on scope for details on how to access a named reference by name in formulas.

    The name of the reference.
    throws

    [[ArgumentNullException]] The value assigned is null or empty.

    throws

    [[ArgumentException]] The value assigned is longer than 255 characters.

    throws

    [[ArgumentException]] The value assigned is not a valid named reference. The name must begin with a letter, underscore (), or a backslash (). All other characters in the name must be letters, numbers, periods, underscores (), or backslashes (). The name cannot be a an A1 cell reference (1 to 3 letters followed by 1 to 6 numbers). In addition, the name cannot be 'r', 'R', 'c', or 'C' or start with a row or column reference in R1C1 cell reference mode ('R' followed by 1 to 6 numbers or 'C' followed by 1 to 6 numbers).

    throws

    [[InvalidOperationException]] The value assigned is the name of another named reference with the same Scope. Names are compared case-insensitively.

    Returns string

  • Gets or sets the name of the reference.

    See the overview on scope for details on how to access a named reference by name in formulas.

    The name of the reference.

    Parameters

    • a: string

    Returns void

scope

  • get scope(): any
  • Gets the scope of the named reference.

    This can either be the workbook which the named reference belongs to or one of the worksheets in the workbook.

    The scope determines how formulas need to preface a name in order to use the named reference.

    If the scope is the workbook, formulas in any cell in the workbook can reference the named reference by specifying just the name or the workbook's file name, an exclamation point, and the name: =MyWorkbookName ='C:\MyWorkbook.xls'!MyWorkbookName When cells in other workbook's want to reference the named reference, they must use the second format by first specifying the file name when the workbook-scoped named reference exists.

    If the scope is a worksheet, formulas in cells of the worksheet can reference the named reference by specifying just the name. In addition, they can fully qualify the named reference with the worksheet name and, optionally, the workbook file name: =MyWorksheetName =Sheet1!MyWorksheetName ='C:\[MyWorkbook.xls]Sheet1'!MyWorksheetName Formulas in cells of other worksheets in the same workbook can use the named reference as well, but they must specify the worksheet name and, optionally, the workbook file name: =Sheet2!OtherWorksheetName ='C:\[MyWorkbook.xls]Sheet2'!OtherWorksheetName Formulas in cells of other workbooks can also used the named reference, but they must specify the workbook file name, worksheet name, and named reference name.

    The scope of the named reference.

    Returns any

sortSettings

style

  • Gets or sets the style to use on the WorksheetTable.

    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.

    The WorksheetTableStyle instance which defines the various default table area formats.
    throws

    [[ArgumentException]] The value specified is not in the Workbook.customTableStyles or Workbook.standardTableStyles collections.

    see

    Workbook.defaultTableStyle

    see

    Workbook.customTableStyles

    see

    Workbook.standardTableStyles

    Returns WorksheetTableStyle

  • Gets or sets the style to use on the WorksheetTable.

    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.

    The WorksheetTableStyle instance which defines the various default table area formats.

    Parameters

    Returns void

totalsRowRegion

  • Gets the WorksheetRegion which represents the region of cells in the totals row of the table. A WorksheetRegion which represents the region of cells in the totals row of the table or null if the totals row is not visible.

    see

    isTotalsRowVisible

    see

    resize

    see

    resize

    Returns WorksheetRegion

wholeTableRegion

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

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

    see

    resize

    see

    resize

    Returns WorksheetRegion

worksheet

  • Gets the worksheet to which the table belongs. The Worksheet to which the table belongs or null if the table has been removed from the Worksheet.

    see

    Worksheet.tables

    Returns Worksheet

Methods

areaFormats

clearFilters

  • clearFilters(): void
  • Clears all filters from the columns in the table.

    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

    reapplyFilters

    see

    WorksheetTableColumn.filter

    Returns void

clearSortConditions

  • clearSortConditions(): void
  • Clears all sort conditions from the columns in the table.

    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

    sortSettings

    see

    WorksheetTableColumn.sortCondition

    Returns void

columns

  • Gets the WorksheetTableColumn at the specified index.

    throws

    [[ArgumentOutOfRangeException]] 'index' is less than 0 or greater than or equal to the number of columns in the collection.

    Parameters

    • index: number

      The 0-based index of the column to get.

    Returns WorksheetTableColumn

    The WorksheetTableColumn at the specified index.

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

    Column names are compared case-insensitively.

    see

    WorksheetTableColumn.name

    Parameters

    • name: string

      The name of the column to get.

    Returns WorksheetTableColumn

    The WorksheetTableColumn with the specified name or null a column with the specified name doesn't exist.

  • Gets the collection of columns in the table.

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

    see

    WorksheetTableColumn

    Returns WorksheetTableColumnCollection

deleteColumns

  • deleteColumns(tableColumnIndex: number, count?: number): void
  • Deletes one or more columns from the table

    throws

    [[InvalidOperationException]] If the operation is not allowed, e.g. if it would cause data to be shifted off the worksheet.

    throws

    [[IndexOutOfRangeException]] If index is negative.

    throws

    [[ArgumentOutOfRangeException]] If count is less than 1 or greater than or equal all the columns in the table.

    Parameters

    • tableColumnIndex: number

      The deletion start relative to the first column in the table.

    • Default value count: number = 1

      The number of columns to delete.

    Returns void

deleteDataRows

  • deleteDataRows(dataRowIndex: number, count?: number): void
  • Deletes one or more data rows from the table

    throws

    [[InvalidOperationException]] If the operation is not allowed, e.g. if it would cause data to be shifted off the worksheet.

    throws

    [[IndexOutOfRangeException]] If index is negative.

    throws

    [[ArgumentOutOfRangeException]] If count is less than 1 or greater than or equal all the rows (excluding header and total rows) in the table.

    Parameters

    • dataRowIndex: number

      The deletion start relative to the first row in the dataAreaRegion.

    • Default value count: number = 1

      The number of rows to delete.

    Returns void

equals

  • equals(other: any): boolean
  • Parameters

    • other: any

    Returns boolean

getHashCode

  • getHashCode(): number
  • Returns number

insertColumns

  • insertColumns(tableColumnIndex: number, count?: number): void
  • Inserts one or more columns into the table

    throws

    [[InvalidOperationException]] If the operation is not allowed, e.g. if it would cause data to be shifted off the worksheet.

    throws

    [[IndexOutOfRangeException]] If index is negative.

    throws

    [[ArgumentOutOfRangeException]] If count is less than 1.

    Parameters

    • tableColumnIndex: number

      The insertion point relative to the first column in the table.

    • Default value count: number = 1

      The number of columns to insert

    Returns void

insertDataRows

  • insertDataRows(dataRowIndex: number, count?: number): void
  • Inserts one or more data rows into the table

    throws

    [[InvalidOperationException]] If the operation is not allowed, e.g. if it would cause data to be shifted off the worksheet.

    throws

    [[IndexOutOfRangeException]] If index is negative.

    throws

    [[ArgumentOutOfRangeException]] If count is less than 1.

    Parameters

    • dataRowIndex: number

      The insertion point relative to the first row in the dataAreaRegion.

    • Default value count: number = 1

      The number of columns to insert

    Returns void

memberwiseClone

  • memberwiseClone(): Base
  • Returns Base

reapplyFilters

  • reapplyFilters(): void
  • Re-filters all data cells in the table based on the filters from the columns in the table.

    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

    clearFilters

    see

    WorksheetTableColumn.filter

    Returns void

reapplySortConditions

  • reapplySortConditions(): void
  • Re-sorts all data cells in the table based on the sort conditions from the columns in the table.

    see

    sortSettings

    see

    WorksheetTableColumn.sortCondition

    Returns void

resize

  • resize(headerAndDataRegion: WorksheetRegion): void
  • resize(headerAndDataRegionAddress: string): void
  • resize(headerAndDataRegionAddress: string, cellReferenceMode: CellReferenceMode): void
  • Resizes the table to a new range of data on the worksheet.

    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.

    throws

    [[InvalidOperationException]] Occurs when the WorksheetTable has previously been removed from the worksheet.

    throws

    [[ArgumentNullException]] Occurs when 'headerAndDataRegion' is null.

    throws

    [[ArgumentException]] Occurs when 'headerAndDataRegion' from a different worksheet or has been previously shifted off the table's worksheet.

    throws

    [[ArgumentException]] Occurs when the top of the 'headerAndDataRegion' does is not the same as the top of the table.

    throws

    [[ArgumentException]] Occurs when the 'headerAndDataRegion' does not contain at least one data row for the table.

    throws

    [[ArgumentException]] Occurs when the 'headerAndDataRegion' does not overlap the current table region.

    throws

    [[InvalidOperationException]] Occurs when the there is a merged cell, array formula, data table, or another table in the new region.

    Parameters

    • headerAndDataRegion: 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.

    Returns void

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

    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.

    throws

    [[InvalidOperationException]] Occurs when the WorksheetTable has previously been removed from the worksheet.

    throws

    [[ArgumentNullException]] Occurs when 'headerAndDataRegionAddress' is null.

    throws

    [[ArgumentException]] Occurs when 'headerAndDataRegionAddress' is not a valid name or a valid cell or region address in the workbook's cell reference mode.

    throws

    [[InvalidOperationException]] Occurs when 'headerAndDataRegionAddress' is a relative R1C1 address. The overload taking an origin cell must be used to resolve relative R1C1 references.

    throws

    [[ArgumentException]] Occurs when the top of the 'headerAndDataRegionAddress' does is not the same as the top of the table.

    throws

    [[ArgumentException]] Occurs when the 'headerAndDataRegionAddress' does not contain at least one data row for the table.

    throws

    [[ArgumentException]] Occurs when the 'headerAndDataRegionAddress' does not overlap the current table region.

    throws

    [[InvalidOperationException]] Occurs when the there is a merged cell, array formula, data table, or another table in the new region.

    Parameters

    • headerAndDataRegionAddress: 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.

    Returns void

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

    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.

    throws

    [[InvalidOperationException]] Occurs when the WorksheetTable has previously been removed from the worksheet.

    throws

    [[ArgumentNullException]] Occurs when 'headerAndDataRegionAddress' is null.

    throws

    [[ArgumentException]] Occurs when 'headerAndDataRegionAddress' is not a valid name or a valid cell or region address in the workbook's cell reference mode.

    throws

    [[InvalidOperationException]] Occurs when 'headerAndDataRegionAddress' is a relative R1C1 address. The overload taking an origin cell must be used to resolve relative R1C1 references.

    throws

    [[ArgumentException]] Occurs when the top of the 'headerAndDataRegionAddress' does is not the same as the top of the table.

    throws

    [[ArgumentException]] Occurs when the 'headerAndDataRegionAddress' does not contain at least one data row for the table.

    throws

    [[ArgumentException]] Occurs when the 'headerAndDataRegionAddress' does not overlap the current table region.

    throws

    [[InvalidOperationException]] Occurs when the there is a merged cell, array formula, data table, or another table in the new region.

    Parameters

    • headerAndDataRegionAddress: 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: CellReferenceMode

      The cell reference mode to use to parse the region address.

    Returns void

toString

  • toString(): string
  • Gets the string representation of the table.

    Returns string

    The string representation of the table.

Static compare

  • compare(item1: any, item2: any): number
  • Parameters

    • item1: any
    • item2: any

    Returns number

Static compareSimple

  • compareSimple(item1: any, item2: any): number
  • Parameters

    • item1: any
    • item2: any

    Returns number

Static equalsSimple

  • equalsSimple(item1: any, item2: any): boolean
  • Parameters

    • item1: any
    • item2: any

    Returns boolean

Static equalsStatic

  • equalsStatic(a: any, b: any): boolean
  • Parameters

    • a: any
    • b: any

    Returns boolean

Static getArrayOfProperties

  • getArrayOfProperties(obj: any): any[]
  • Parameters

    • obj: any

    Returns any[]

Static getArrayOfValues

  • getArrayOfValues(obj: any): any[]
  • Parameters

    • obj: any

    Returns any[]

Static getHashCodeStatic

  • getHashCodeStatic(obj: any): number
  • Parameters

    • obj: any

    Returns number

Static referenceEquals

  • referenceEquals(a: any, b: any): boolean
  • Parameters

    • a: any
    • b: any

    Returns boolean

Static staticInit

  • staticInit(): void
  • Returns void