New classes are added to Infragistics Excel Engine to represent the newly supported structures. A collection of tables is exposed off the Worksheet class and collection of custom and standard table styles is exposed off the Workbook class.
A table in Microsoft Excel® is a named rectangular region of cells which contain related data organized into columns. A table also contains built in functionality which allows you to manage and analyze that data. In addition to its data, a table can also contain a row of headers and a row of totals for each column of data.
Main Features
Features overview
The following table summarizes the main features of the table support of Infragistics Excel Engine. Additional details are available after the summary table.
Feature
Description
Accessing tables
Tables are represented by the WorksheetTable class and are accessed primarily through the WorksheetTables collection.
Creating tables
Tables can be created in one of the following ways.
Using one of the Add overloads on the Worksheet.Tables collection, which makes a string indicating the table region and a Boolean indicating whether the table has headers.
Each table has an associated style which provides default format settings for cells in many different areas of the table. This style is represented by the WorksheetTableStyle class and is exposed on a table through the WorksheetTableStyle property.
Table columns
Each column of the table is represented by the WorksheetTableColumn class and the columns within each table are exposed by the read only WorksheetTableColumns collection.
Filtering columns
Columns in a table can be filtered by calling one of the Apply…Filter methods, defined on the WorksheetTableColumn class.
Columns in a table can be sorted by applying a sort condition to the WorksheetTableColumnSortCondition property.
For more information, refer to the Sorting Tables topic.
Accessing tables
WorksheetTables collection allows you to create and remove tables on the worksheet. In addition, you can get a table by name using Workbook's GetTable method or by getting the WorksheetCellAssociatedTable property of one of the cells within the table. Any tables in the Worksheet.Tables collection will be saved with the file when the workbook is saved and will be visible on the worksheet in Microsoft Excel when that file is opened.
Creating tables
You can customize the newly created WorksheetTable instance. The following behaviors are associated with table creation:
Behavior
Description
Merged cells in region
If there are any merged cells within the region of cells to format as a table, they will be removed from the worksheet and the table region will be expanded if necessary to include all individual cells which were in those merged cells.
Cells in a header row
If you specify that the created table has headers, all cells in the header row will have their value converted to a string and their values will be made unique across all columns in the table. This is done by appending number to the 2nd, 3rd … duplicates of a column name.
Note
Note:
Column names are compared case-insensitively to determine equality.
Cases of a default table style
If you create a table using a method overload which does not accept a WorksheetTableStyle instance, the Workbook's DefaultTableStyle will be used.
Region with one row
If a region with one row is formatted as a table and it has headers, meaning that there are no rows in the data area, the row below the headers will be an insert row. This means the cells in that row will return the table as their AssociatedTable value and they will be formatted with all the formatting contained in the table and its associated style.
Due to the fact that this expands the table to the cells in the row below, one of the following actions will be taken regarding the cells below the header row:
Case
Description
Any value or formula present below the header row
If there are any values or formulas in the cells of the row directly below the header row, and in the same column as are contained in the formatted table, the cells for the insert row will be inserted.
Note
Note:
This means cells will be inserted into the worksheet directly below the header row and all cells below them on the remainder of the worksheet will be shifted down by one row.
Cells in columns that do not intersect with the formatted table will not be affected.
No value or formula present below the header row
If there are no values or formulas in the cells of the row directly below the header row, no cells will be inserted and the insert row will simply occupy the existing cells that are below the header row.
Table styles
Each table has an associated style. This style is represented by the WorksheetTableStyle class and is exposed on a table through the WorksheetTable.Style property. The styles are exposed off the Workbook class through three properties:
This is a collection of 60 preset table styles which are not saved with the workbook. All table styles in this collection are read-only and trying to set any properties on the style itself or any sub-objects of the style will cause an exception to be thrown.
You can customize these styles by duplicating them with the Clone method and then adding the cloned instance to the CustomTableStyles collection.
This is a collection of user defined table styles which are saved with the workbook. This collection is read/write and all table styles in this collection can be modified.
Note
Note:
The names of styles in this collection must all be case-insensitively unique.
This is the style to apply to newly created tables when the style is not specified by you. It will always return a non-null WorksheetTableStyle instance. If this is not set by you or loaded from the file, it will default to the TableStyleMedium2 style from the StandardTableStyles collection.
Note
Note:
If the DefaultTableStyle is changed after one or more tables are created, those existing tables will not be affected. This property only affects newly created tables.
If this property is set to one of the custom table styles and that table style is removed from the workbook, the DefaultTableStyle will revert back to the TableStyleMedium2 style.
The table styles define many properties which allow you to control the appearance of tables at an area level (not an individual cell level).
There are 13 areas which can have a format applied with the table style. These are all optional formats, but when they are set, they are applied as differential formats. This means that only the formats values which are not default are seen in the cells. They are not directly applied to the cells though.
The cells maintain their own format and when set to non-default values, each format property of a cell will override the area formats from the table style. The following is a list of the 13 areas which can have a format applied, as defined by the WorksheetTableStyleArea enumeration. They are exposed by the WorksheetTableStyleAreaFormats collection and are listed here in order of precedence:
Area Format
Description
WholeTable
The format applied to the entire table.
ColumnStripe
The format applied to the odd numbered column stripes in the table.
AlternateColumnStripe
The format applied to the even numbered column stripes in the table.
RowStripe
The format applied to the odd numbered row stripes in the table.
AlternateRowStripe
The format applied to the even numbered row stripes in the table.
LastColumn
The format applied to the last column in the table.
FirstColumn
The format applied to the first column in the table.
HeaderRow
The format applied to the header row in the table.
TotalRow
The format applied to the total row in the table.
FirstHeaderCell
The format applied to the first header cell in the table.
LastHeaderCell
The format applied to the last header cell in the table.
FirstTotalCell
The format applied to the first total cell in the table.
LastTotalCell
The format applied to the last total cell in the table.
There is a similar AreaFormats collection on the WorksheetTable class which exposes formats for a different set of areas.
Note
Note:
The Area formats on the table function differently from the area formats on the table style. While formats on the table style serve as defaults for cells in particular areas, the area formats on the table are actually applied to the currently visible cells in the table when they are not set (cells in hidden or filtered rows will not get the format applied to them). This is not different from selecting the cells in the formatted area and setting the appropriate format properties on them directly. What is different is that formats in the AreaFormats collection on the table will be automatically applied to new cells in the table when the table is resized to be larger.
In addition to area formats, the table styles define how many columns each column stripe spans and how many rows each row stripe spans. These settings are exposed through the following properties:
If a table style is a custom table style which is later removed from the CustomTableStyles collection, the table will have its style set back to the current Workbook.DefaultTableStyle.
Table columns
Each column of the table is represented by the WorksheetTableColumn class and the columns within each table are exposed by the read-only WorksheetTable.Columns collection. The table column only represents the portion of the column which exists in the table. By contrast, the entire column of a worksheet is represented by the WorksheetColumn class and exposed by the Worksheet.Columns collection.
Table columns allow for various settings, which are listed in the table below:
Property
Description
AreaFormats
When area formats are set, they are applied to the currently visible cells in that area and to any new cells added. This collection exposes only three areas which can be customized:
DataArea
HeaderCell
TotalCell
ColumnFormula
A read-only property which exposes the formula applied to the data cells in the column. If this property is non-null and the table is resized to be larger, the new cells in the column will have this formula applied. The property is read-only because there are options which need to be supplied when it is set.
The method SetColumnFormula , allows you to modify it. SetColumnFormula has a Boolean parameter which allows you to decide whether to apply the new column formula to all cells in the column or only the cells with null values or formulas which match the previous column formula, if any.
Note
Note:
To clear a formula, use SetColumnFormula and pass null for the new formula.
Filter
This is the filter applied to the column, which is described in Filtering Tables topic.
Name
The name of the column, which is a case-insensitively unique name among the other columns in the same table.
Note
Note:
If the table has a header row visible, this property is linked to the value of the column’s header cell. Modifying one will change the other as well.
SortCondition
This is the sort condition used to sort the cells in the column, which is described in the Sorting Tables topic.
TotalFormula
This is a formula to apply to the total cell.
Note
Note:
When the totals row is visible in the owning table, this property is linked to the formula of the column’s total cell. Modifying one will change the other as well.
When the totals row is not visible, this property determines what the formula will be the next time it is made visible.
TotalLabel
Similar to the TotalFormula, but the property represents a string not a formula. Only one label can be set, setting the one clears the other.
Related Content
Topics
The following topics provide additional information related to this topic.
In this section you’ll find information that will help you to better understand the object model and the functionalities of the Infragistics Excel Engine.
This topic describes how to filter columns in tables. Columns in a table can be filtered by applying a filter condition to the WorksheetTableColumn.Filter property.
This topic describes how to sort columns in tables. Columns in a table can be sorted by applying a sort condition to the WorksheetTableColumn.SortCondition property.
This topic describes how to determine the text that would be displayed in Microsoft Excel® cell given the cell’s value, format string, column width, and worksheet options. You can determine the cell text using the WorksheetCell.GetText method.
This page has encountered an error. We've attempted to send the information to the server.
