Excel.TableRowCollection class
Represents a collection of all the rows that are part of the table.
Note that unlike ranges or columns, which will adjust if new rows or columns are added before them, a TableRow object represents the physical location of the table row, but not the data. That is, if the data is sorted or if new rows are added, a table row will continue to point at the index for which it was created.
- Extends
Remarks
Properties
| context | The request context associated with the object. This connects the add-in's process to the Office host application's process. |
| count | Returns the number of rows in the table. |
| items | Gets the loaded child items in this collection. |
Methods
| add(index, values, always |
Adds one or more rows to the table. The return object will be the top of the newly added row(s). Note that unlike ranges or columns, which will adjust if new rows or columns are added before them, a |
| add |
Adds one or more rows to the table. The returned object will be the top row of the newly added row or rows. Unlike Note that unlike ranges or columns, which will adjust if new rows or columns are added before them, a |
| delete |
Delete multiple rows from a table. These rows don't need to be sequential. This method will throw the |
| delete |
Delete a specified number of rows from a table, starting at a given index. This method will throw the |
| get |
Gets the number of rows in the table. |
| get |
Gets a row based on its position in the collection. Note that unlike ranges or columns, which will adjust if new rows or columns are added before them, a |
| load(options) | Queues up a command to load the specified properties of the object. You must call |
| load(property |
Queues up a command to load the specified properties of the object. You must call |
| load(property |
Queues up a command to load the specified properties of the object. You must call |
| toJSON() | Overrides the JavaScript |
Property Details
context
The request context associated with the object. This connects the add-in's process to the Office host application's process.
context: RequestContext;Property Value
count
Returns the number of rows in the table.
readonly count: number;Property Value
number
Remarks
items
Gets the loaded child items in this collection.
readonly items: Excel.TableRow[];Property Value
Method Details
add(index, values, alwaysInsert)
Adds one or more rows to the table. The return object will be the top of the newly added row(s).
Note that unlike ranges or columns, which will adjust if new rows or columns are added before them, a TableRow object represents the physical location of the table row, but not the data. That is, if the data is sorted or if new rows are added, a table row will continue to point at the index for which it was created.
add(index?: number, values?: Array<Array<boolean | string | number>> | boolean | string | number, alwaysInsert?: boolean): Excel.TableRow;Parameters
- index
-
number
Optional. Specifies the relative position of the new row. If null or -1, the addition happens at the end. Any rows below the inserted row are shifted downwards. Zero-indexed.
- values
-
Array<Array<boolean | string | number>> | boolean | string | number
Optional. A 2D array of unformatted values of the table row.
- alwaysInsert
-
boolean
Optional. Specifies whether the new rows will be inserted into the table when new rows are added. If true, the new rows will be inserted into the table. If false, the new rows will be added below the table. Default is true.
Returns
Remarks
Examples
await Excel.run(async (context) => {
const tables = context.workbook.tables;
const values = [["Sample", "Values", "For", "New", "Row"]];
const row = tables.getItem("Table1").rows.add(null, values);
row.load('index');
await context.sync();
console.log(row.index);
});
addAsJson(index, values, alwaysInsert)
Adds one or more rows to the table. The returned object will be the top row of the newly added row or rows. Unlike add(), addAsJson() takes any type of cell value, such as image or entity data types.
Note that unlike ranges or columns, which will adjust if new rows or columns are added before them, a TableRow object represents the physical location of the table row, but not the data. That is, if the data is sorted or if new rows are added, a table row will continue to point at the index for which it was created.
addAsJson(index?: number, values?: CellValue[][], alwaysInsert?: boolean): Excel.TableRow;Parameters
- index
-
number
Optional. Specifies the relative position of the new row. If null or -1, the addition happens at the end. Any rows below the inserted row are shifted downwards. Zero-indexed.
- values
-
Excel.CellValue[][]
Optional. A 2D array of cell values of the table row.
- alwaysInsert
-
boolean
Optional. Specifies whether the new rows will be inserted into the table when new rows are added. If true, the new rows will be inserted into the table. If false, the new rows will be added below the table. Default is true.
Returns
Remarks
deleteRows(rows)
Delete multiple rows from a table. These rows don't need to be sequential. This method will throw the InvalidArgument error if a chosen row has already been deleted or doesn't exist. This method will throw the InsertDeleteConflict error if the table on which the method is called has a filter applied.
deleteRows(rows: number[] | TableRow[]): void;Parameters
- rows
-
number[] | Excel.TableRow[]
An array of row index numbers or row objects to delete from the table.
Returns
void
Remarks
deleteRowsAt(index, count)
Delete a specified number of rows from a table, starting at a given index. This method will throw the InsertDeleteConflict error if the table on which the method is called has a filter applied.
deleteRowsAt(index: number, count?: number): void;Parameters
- index
-
number
The index value of the row to be deleted. Note: Row indexes update each time that a preceding row in the table is deleted, after each delete operation. Ensure that the index of the row that you want to delete hasn't changed between the time that you determined the value and the time that you run the operation.
- count
-
number
Number of rows to delete. By default, a single row will be deleted.
Returns
void
Remarks
getCount()
Gets the number of rows in the table.
getCount(): OfficeExtension.ClientResult<number>;Returns
OfficeExtension.ClientResult<number>
Remarks
getItemAt(index)
Gets a row based on its position in the collection.
Note that unlike ranges or columns, which will adjust if new rows or columns are added before them, a TableRow object represents the physical location of the table row, but not the data. That is, if the data is sorted or if new rows are added, a table row will continue to point at the index for which it was created.
getItemAt(index: number): Excel.TableRow;Parameters
- index
-
number
Index value of the object to be retrieved. Zero-indexed.
Returns
Remarks
Examples
await Excel.run(async (context) => {
const tablerow = context.workbook.tables.getItem('Table1').rows.getItemAt(0);
tablerow.load('values');
await context.sync();
console.log(tablerow.values);
});
load(options)
Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.
load(options?: Excel.Interfaces.TableRowCollectionLoadOptions & Excel.Interfaces.CollectionLoadOptions): Excel.TableRowCollection;Parameters
Provides options for which properties of the object to load.
Returns
load(propertyNames)
Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.
load(propertyNames?: string | string[]): Excel.TableRowCollection;Parameters
- propertyNames
-
string | string[]
A comma-delimited string or an array of strings that specify the properties to load.
Returns
Examples
await Excel.run(async (context) => {
const tablerows = context.workbook.tables.getItem('Table1').rows;
tablerows.load('items');
await context.sync();
console.log("tablerows Count: " + tablerows.count);
for (let i = 0; i < tablerows.items.length; i++) {
console.log(tablerows.items[i].index);
}
});
load(propertyNamesAndPaths)
Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.
load(propertyNamesAndPaths?: OfficeExtension.LoadOption): Excel.TableRowCollection;Parameters
- propertyNamesAndPaths
- OfficeExtension.LoadOption
propertyNamesAndPaths.select is a comma-delimited string that specifies the properties to load, and propertyNamesAndPaths.expand is a comma-delimited string that specifies the navigation properties to load.
Returns
toJSON()
Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.TableRowCollection object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.TableRowCollectionData) that contains an "items" array with shallow copies of any loaded properties from the collection's items.
toJSON(): Excel.Interfaces.TableRowCollectionData;Returns
Office Add-ins