ig.excel.WorksheetDataTable
Remarks
Data tables are a way to show the results of one or more formulas by trying many different values in the variables of the formulas at the same time, and showing the different results in a table. An excellent example of a data table application would be for a multiplication table. A multiplication table shows results for the formula =X*Y. However, it shows many different results for the formula, where each result is determined by using different values for X and Y. These results are displayed in a table, where each cell in the table shows the formula's result for specific values of X and Y, which are labeled in the row and column headers, respectively. Therefore, each cell in a row will use the same X value, and each cell in a column will use the same Y value.
The multiplication table is known as a two-variable data table. Two-variable data tables are characterized by having a single formula and values in the row and column headers of the table. The formula is entered into the top-left cell of the data table and usually references at least two cells outside the data table, known as the column-input and row-input cells. When the formula is evaluated for a specific cell in the data table, the reference to the column-input cell in the formula is replaced with the value in the cell's row header (this may seem backwards, but the values in the row headers run down the left column of the data table, which is why they are used for the column-input cell), and the reference to the row-input cell is replaced with the value in the cell's column header.
Another type of data table is the one-variable data table. A one-variable data table can be a column-oriented or a row-oriented data table. A column-oriented data table has data in the cells of the left column and formulas in the cells of the top row (anything in the top-left cell of the data table is ignored in this type of data table). Usually, the formulas in the top row all reference the same cell outside the data table, known as the column-input cell. When a cell in the table is evaluated, the formula in its column header is used, with the reference to the column-input cell replaced by the value in cell's row header.
A row-oriented one-variable data table is formed like a column-oriented data table, except the values run along the top row, the formulas run down the left column, and the cell referenced by all formulas is known as the row-input cell.
Dependencies
-
cellsInTable
- .cellsInTable( );
- Return Type:
- ig.excel.WorksheetRegion
- Return Type Description:
- The region of cells in the data table.
Gets the region of cells in the data table.
Exceptions
Exception Description ig.ArgumentNullException The value assigned is null. ig.InvalidOperationException The value is assigned after the data table has been removed from the worksheet. ig.InvalidOperationException The region specified contains the RowInputCell or the ColumnInputCell. ig.InvalidOperationException One or more of the interior cells of the value assigned (all cells except the left-most column and top row) is an interior cell of another data table or is a cell in an array formula, and the entire range of that other entity extends outside the interior cells of the value assigned. ig.InvalidOperationException The assigned value contains merged regions which are not confined to the left-most column or top row. No merged regions can exist in the interior cells of the data table and no merged regions can exist in the left-most column or top row and extend outside the data table region. ig.ArgumentException The value assigned is a region from a worksheet other than the data table's worksheet. ig.ArgumentException The assigned value is only one row tall or one column wide. The cells in the table must be at least two rows by two columns. Remarks
Any interior cells (cells not in the left-most column or top row) in this region will have their values removed when this is set. If any interior cells have array formulas with a region that crosses outside the data table, an error will occur. However, if the array formula's region is confined to cells in the interior of the data table, the array formula will have ArrayFormula.clearCellRange called on it, which will remove it from all its cells. Similarly, if an existing data table's interior cells contain some of the interior cells in this region as well as some external cells, an error will occur. However, if all interior cells of the existing data table are contained in the interior cells of the new region specified here, the existing data table will be removed from the worksheet.
After the cells in the table have been specified, the interior cells' values cannot be modified.
Note: The WorksheetDataTable.rowInputCell and WorksheetDataTable.columnInputCell cannot be with the region.
-
cellsInTable
- .cellsInTable( value:ig.excel.WorksheetRegion );
- Return Type:
- ig.excel.WorksheetRegion
- Return Type Description:
- The region of cells in the data table.
Sets the region of cells in the data table.
- value
- Type:ig.excel.WorksheetRegion
Exceptions
Exception Description ig.ArgumentNullException The value assigned is null. ig.InvalidOperationException The value is assigned after the data table has been removed from the worksheet. ig.InvalidOperationException The region specified contains the RowInputCell or the ColumnInputCell. ig.InvalidOperationException One or more of the interior cells of the value assigned (all cells except the left-most column and top row) is an interior cell of another data table or is a cell in an array formula, and the entire range of that other entity extends outside the interior cells of the value assigned. ig.InvalidOperationException The assigned value contains merged regions which are not confined to the left-most column or top row. No merged regions can exist in the interior cells of the data table and no merged regions can exist in the left-most column or top row and extend outside the data table region. ig.ArgumentException The value assigned is a region from a worksheet other than the data table's worksheet. ig.ArgumentException The assigned value is only one row tall or one column wide. The cells in the table must be at least two rows by two columns. Remarks
Any interior cells (cells not in the left-most column or top row) in this region will have their values removed when this is set. If any interior cells have array formulas with a region that crosses outside the data table, an error will occur. However, if the array formula's region is confined to cells in the interior of the data table, the array formula will have ArrayFormula.clearCellRange called on it, which will remove it from all its cells. Similarly, if an existing data table's interior cells contain some of the interior cells in this region as well as some external cells, an error will occur. However, if all interior cells of the existing data table are contained in the interior cells of the new region specified here, the existing data table will be removed from the worksheet.
After the cells in the table have been specified, the interior cells' values cannot be modified.
Note: The WorksheetDataTable.rowInputCell and WorksheetDataTable.columnInputCell cannot be with the region.
-
columnInputCell
- .columnInputCell( );
- Return Type:
- ig.excel.WorksheetCell
- Return Type Description:
- The cell used as the column-input cell in the data table.
Gets the cell used as the column-input cell in the data table.
Exceptions
Exception Description ig.ArgumentException The assigned value does not belong to the same worksheet as the data table. ig.InvalidOperationException The value is assigned after the data table has been removed from the worksheet. ig.InvalidOperationException The value assigned is within the CellsInTable region. ig.InvalidOperationException The assigned value is null and RowInputCell is null. At least one input cell must be non-null. ig.InvalidOperationException The assigned value is the same as RowInputCell. The input cells cannot be the same cell. Remarks
This represents the cell reference in all formulas of the data table to replace with row header values. If this and the WorksheetDataTable.rowInputCell are non-null, the data table is a two-variable data table. Otherwise, if only one is non-null, this is a one-variable data table.
Note: The RowInputCell and ColumnInputCell cannot be within the WorksheetDataTable.cellsInTable region.
-
columnInputCell
- .columnInputCell( value:ig.excel.WorksheetCell );
- Return Type:
- ig.excel.WorksheetCell
- Return Type Description:
- The cell used as the column-input cell in the data table.
Sets the cell used as the column-input cell in the data table.
- value
- Type:ig.excel.WorksheetCell
Exceptions
Exception Description ig.ArgumentException The assigned value does not belong to the same worksheet as the data table. ig.InvalidOperationException The value is assigned after the data table has been removed from the worksheet. ig.InvalidOperationException The value assigned is within the CellsInTable region. ig.InvalidOperationException The assigned value is null and RowInputCell is null. At least one input cell must be non-null. ig.InvalidOperationException The assigned value is the same as RowInputCell. The input cells cannot be the same cell. Remarks
This represents the cell reference in all formulas of the data table to replace with row header values. If this and the WorksheetDataTable.rowInputCell are non-null, the data table is a two-variable data table. Otherwise, if only one is non-null, this is a one-variable data table.
Note: The RowInputCell and ColumnInputCell cannot be within the WorksheetDataTable.cellsInTable region.
-
rowInputCell
- .rowInputCell( );
- Return Type:
- ig.excel.WorksheetCell
- Return Type Description:
- The cell used as the row-input cell in the data table.
Gets the cell used as the row-input cell in the data table.
Exceptions
Exception Description ig.ArgumentException The assigned value does not belong to the same worksheet as the data table. ig.InvalidOperationException The value is assigned after the data table has been removed from the worksheet. ig.InvalidOperationException The value assigned is within the CellsInTable region. ig.InvalidOperationException The assigned value is null and ColumnInputCell is null. At least one input cell must be non-null. ig.InvalidOperationException The assigned value is the same as ColumnInputCell. The input cells cannot be the same cell. Remarks
This represents the cell reference in all formulas of the data table to replace with column header values. If this and the WorksheetDataTable.columnInputCell are non-null, the data table is a two-variable data table. Otherwise, if only one is non-null, this is a one-variable data table.
Note: The RowInputCell and ColumnInputCell cannot be within the WorksheetDataTable.cellsInTable region.
-
rowInputCell
- .rowInputCell( value:ig.excel.WorksheetCell );
- Return Type:
- ig.excel.WorksheetCell
- Return Type Description:
- The cell used as the row-input cell in the data table.
Sets the cell used as the row-input cell in the data table.
- value
- Type:ig.excel.WorksheetCell
Exceptions
Exception Description ig.ArgumentException The assigned value does not belong to the same worksheet as the data table. ig.InvalidOperationException The value is assigned after the data table has been removed from the worksheet. ig.InvalidOperationException The value assigned is within the CellsInTable region. ig.InvalidOperationException The assigned value is null and ColumnInputCell is null. At least one input cell must be non-null. ig.InvalidOperationException The assigned value is the same as ColumnInputCell. The input cells cannot be the same cell. Remarks
This represents the cell reference in all formulas of the data table to replace with column header values. If this and the WorksheetDataTable.columnInputCell are non-null, the data table is a two-variable data table. Otherwise, if only one is non-null, this is a one-variable data table.
Note: The RowInputCell and ColumnInputCell cannot be within the WorksheetDataTable.cellsInTable region.
-
worksheet
- .worksheet( );
Gets the worksheet on which this data table resides.