Class WorksheetTable

Represents a region of cells formatted as a table.

Inheritance
System.Object
NamedReferenceBase
WorksheetTable
Inherited Members
Namespace: Infragistics.Documents.Excel
Assembly: IgniteUI.Blazor.Documents.Excel.dll
Syntax
public class WorksheetTable : NamedReferenceBase, IAreaFormatsOwner<WorksheetTableArea>, IWorksheetCellFormatProxyOwner, IGenericCachedCollectionEx, ISortSettingsOwner, IWorkbookProvider, IWorksheetProvider
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 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 SortCondition or by populating the 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 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 TotalLabel or TotalFormula, respectively.

Properties

AreaFormats

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

Declaration
public WorksheetTableAreaFormatsCollection<WorksheetTableArea> AreaFormats { get; }
Property Value
Type Description
WorksheetTableAreaFormatsCollection<WorksheetTableArea>
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

Columns

Gets the collection of columns in the table.

Declaration
public WorksheetTableColumnCollection Columns { get; }
Property Value
Type Description
WorksheetTableColumnCollection
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

DataAreaRegion

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

Declaration
public WorksheetRegion DataAreaRegion { get; }
Property Value
Type Description
WorksheetRegion
Remarks

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

See Also

DisplayBandedColumns

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

Declaration
public bool DisplayBandedColumns { get; set; }
Property Value
Type Description
System.Boolean
Remarks

The column formats are defined by the WorksheetTableStyle applied to the WorksheetTable. These are stored in the 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 ColumnStripeWidth and AlternateColumnStripeWidth values.

See Also

DisplayBandedRows

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

Declaration
public bool DisplayBandedRows { get; set; }
Property Value
Type Description
System.Boolean
Remarks

The row formats are defined by the WorksheetTableStyle applied to the WorksheetTable. These are stored in the 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 RowStripeHeight and AlternateRowStripeHeight values.

See Also

DisplayFirstColumnFormatting

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

Declaration
public bool DisplayFirstColumnFormatting { get; set; }
Property Value
Type Description
System.Boolean
Remarks

The first column format is defined by the WorksheetTableStyle applied to the WorksheetTable. It is stored in the 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

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

Declaration
public bool DisplayLastColumnFormatting { get; set; }
Property Value
Type Description
System.Boolean
Remarks

The last column format is defined by the WorksheetTableStyle applied to the WorksheetTable. It is stored in the 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

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

Declaration
public WorksheetRegion HeaderRowRegion { get; }
Property Value
Type Description
WorksheetRegion

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 Also

IsFilterUIVisible

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

Declaration
public bool IsFilterUIVisible { get; set; }
Property Value
Type Description
System.Boolean
Remarks

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.

Exceptions
Type Condition
System.InvalidOperationException

The value assigned is True and IsHeaderRowVisible is False.
See Also

IsHeaderRowVisible

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

Declaration
public bool IsHeaderRowVisible { get; set; }
Property Value
Type Description
System.Boolean

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

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

See Also

IsTotalsRowVisible

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

Declaration
public bool IsTotalsRowVisible { get; set; }
Property Value
Type Description
System.Boolean

True if the row containing column totals is visible; False if it is hidden.
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 TotalFormula. To display a text label, set the TotalLabel. If both are set, the calculated value takes precedence.

Exceptions
Type Condition
System.InvalidOperationException

The value is set to True and the table occupies the last row of the worksheet.
See Also

SortSettings

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

Declaration
public SortSettings<WorksheetTableColumn> SortSettings { get; }
Property Value
Type Description
SortSettings<WorksheetTableColumn>
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 ReapplySortConditions() method is called.

See Also

Style

Gets or sets the style to use on the WorksheetTable.

Declaration
public WorksheetTableStyle Style { get; set; }
Property Value
Type Description
WorksheetTableStyle

The WorksheetTableStyle instance which defines the various default table area formats.
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 DefaultTableStyle.

Exceptions
Type Condition
System.ArgumentException

The value specified is not in the CustomTableStyles or StandardTableStyles collections.
See Also

TotalsRowRegion

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

Declaration
public WorksheetRegion TotalsRowRegion { get; }
Property Value
Type Description
WorksheetRegion

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 Also

WholeTableRegion

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

Declaration
public WorksheetRegion WholeTableRegion { get; }
Property Value
Type Description
WorksheetRegion
Remarks

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

See Also

Worksheet

Gets the Worksheet to which the table belongs.

Declaration
public Worksheet Worksheet { get; }
Property Value
Type Description
Worksheet

The Worksheet to which the table belongs or null if the table has been removed from the Worksheet.
See Also

Methods

ClearFilters()

Clears all filters from the columns in the table.

Declaration
public void ClearFilters()
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()

Clears all sort conditions from the columns in the table.

Declaration
public void ClearSortConditions()
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

DeleteColumns(Int32, Int32)

Deletes one or more columns from the table

Declaration
public void DeleteColumns(int tableColumnIndex, int count = 1)
Parameters
Type Name Description
System.Int32 tableColumnIndex

The deletion start relative to the first column in the table.
System.Int32 count

The number of columns to delete.
Exceptions
Type Condition
System.InvalidOperationException

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

If index is negative.
System.ArgumentOutOfRangeException

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

DeleteDataRows(Int32, Int32)

Deletes one or more data rows from the table

Declaration
public void DeleteDataRows(int dataRowIndex, int count = 1)
Parameters
Type Name Description
System.Int32 dataRowIndex

The deletion start relative to the first row in the DataAreaRegion.
System.Int32 count

The number of rows to delete.
Exceptions
Type Condition
System.InvalidOperationException

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

If index is negative.
System.ArgumentOutOfRangeException

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

InsertColumns(Int32, Int32)

Inserts one or more columns into the table

Declaration
public void InsertColumns(int tableColumnIndex, int count = 1)
Parameters
Type Name Description
System.Int32 tableColumnIndex

The insertion point relative to the first column in the table.
System.Int32 count

The number of columns to insert
Exceptions
Type Condition
System.InvalidOperationException

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

If index is negative.
System.ArgumentOutOfRangeException

If count is less than 1.

InsertDataRows(Int32, Int32)

Inserts one or more data rows into the table

Declaration
public void InsertDataRows(int dataRowIndex, int count = 1)
Parameters
Type Name Description
System.Int32 dataRowIndex

The insertion point relative to the first row in the DataAreaRegion.
System.Int32 count

The number of columns to insert
Exceptions
Type Condition
System.InvalidOperationException

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

If index is negative.
System.ArgumentOutOfRangeException

If count is less than 1.

ReapplyFilters()

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

Declaration
public void ReapplyFilters()
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()

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

Declaration
public void ReapplySortConditions()
See Also

Resize(WorksheetRegion)

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

Declaration
public void Resize(WorksheetRegion headerAndDataRegion)
Parameters
Type Name Description
WorksheetRegion headerAndDataRegion

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

Exceptions
Type Condition
System.InvalidOperationException

Occurs when the WorksheetTable has previously been removed from the worksheet.
System.ArgumentNullException

Occurs when headerAndDataRegion is null.
System.ArgumentException

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

Occurs when the top of the headerAndDataRegion does is not the same as the top of the table.
System.ArgumentException

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

Occurs when the headerAndDataRegion does not overlap the current table region.
System.InvalidOperationException

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

Resize(String)

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

Declaration
public void Resize(string headerAndDataRegionAddress)
Parameters
Type Name Description
System.String headerAndDataRegionAddress

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

Exceptions
Type Condition
System.InvalidOperationException

Occurs when the WorksheetTable has previously been removed from the worksheet.
System.ArgumentNullException

Occurs when headerAndDataRegionAddress is null.
System.ArgumentException

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

Occurs when headerAndDataRegionAddress is a relative R1C1 address. The overload taking an origin cell must be used to resolve relative R1C1 references.
System.ArgumentException

Occurs when the top of the headerAndDataRegionAddress does is not the same as the top of the table.
System.ArgumentException

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

Occurs when the headerAndDataRegionAddress does not overlap the current table region.
System.InvalidOperationException

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

Resize(String, CellReferenceMode)

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

Declaration
public void Resize(string headerAndDataRegionAddress, CellReferenceMode cellReferenceMode)
Parameters
Type Name Description
System.String headerAndDataRegionAddress

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

Exceptions
Type Condition
System.InvalidOperationException

Occurs when the WorksheetTable has previously been removed from the worksheet.
System.ArgumentNullException

Occurs when headerAndDataRegionAddress is null.
System.ArgumentException

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

Occurs when headerAndDataRegionAddress is a relative R1C1 address. The overload taking an origin cell must be used to resolve relative R1C1 references.
System.ArgumentException

Occurs when the top of the headerAndDataRegionAddress does is not the same as the top of the table.
System.ArgumentException

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

Occurs when the headerAndDataRegionAddress does not overlap the current table region.
System.InvalidOperationException

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

ToString()

Gets the string representation of the table.

Declaration
public override string ToString()
Returns
Type Description
System.String

The string representation of the table.

See Also

FormatAsTable(System.Boolean)