TableCollection Object (JavaScript API for Excel)
Represents a collection of all the tables that are part of the workbook or worksheet, depending on how it was reached.
Properties
| Property | Type | Description | Req. Set |
|---|---|---|---|
| count | int | Returns the number of tables in the workbook. Read-only. | 1.1 |
| items | Table[] | A collection of table objects. Read-only. | 1.1 |
See property access examples.
Relationships
None
Methods
| Method | Return Type | Description | Req. Set |
|---|---|---|---|
| add(address: object, hasHeaders: bool) | Table | Create a new table. The range object or source address determines the worksheet under which the table will be added. If the table cannot be added (e.g., because the address is invalid, or the table would overlap with another table), an error will be thrown. | 1.1 |
| getCount() | int | Gets the number of tables in the collection. | 1.4 |
| getItem(key: number or string) | Table | Gets a table by Name or ID. | 1.1 |
| getItemAt(index: number) | Table | Gets a table based on its position in the collection. | 1.1 |
| getItemOrNullObject(key: number or string) | Table | Gets a table by Name or ID. If the table does not exist, will return a null object. | 1.4 |
Method Details
add(address: object, hasHeaders: bool)
Create a new table. The range object or source address determines the worksheet under which the table will be added. If the table cannot be added (e.g., because the address is invalid, or the table would overlap with another table), an error will be thrown.
Syntax
tableCollectionObject.add(address, hasHeaders);
Parameters
| Parameter | Type | Description |
|---|---|---|
| address | object | A Range object, or a string address or name of the range representing the data source. If the address does not contain a sheet name, the currently-active sheet is used. |
| hasHeaders | bool | Boolean value that indicates whether the data being imported has column labels. If the source does not contain headers (i.e,. when this property set to false), Excel will automatically generate header shifting the data down by one row. |
Returns
Examples
Excel.run(function (ctx) {
var table = ctx.workbook.tables.add('Sheet1!A1:E7', true);
table.load('name');
return ctx.sync().then(function() {
console.log(table.name);
});
}).catch(function(error) {
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
});
getCount()
Gets the number of tables in the collection.
Syntax
tableCollectionObject.getCount();
Parameters
None
Returns
int
getItem(key: number or string)
Gets a table by Name or ID.
Syntax
tableCollectionObject.getItem(key);
Parameters
| Parameter | Type | Description |
|---|---|---|
| key | number or string | Name or ID of the table to be retrieved. |
Returns
Examples
Excel.run(function (ctx) {
var tableName = 'Table1';
var table = ctx.workbook.tables.getItem(tableName);
table.load('name');
return ctx.sync().then(function() {
console.log(table.name);
});
}).catch(function(error) {
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
});
Examples
Excel.run(function (ctx) {
var table = ctx.workbook.tables.getItemAt(0);
table.load('name');
return ctx.sync().then(function() {
console.log(table.name);
});
}).catch(function(error) {
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
});
getItemAt(index: number)
Gets a table based on its position in the collection.
Syntax
tableCollectionObject.getItemAt(index);
Parameters
| Parameter | Type | Description |
|---|---|---|
| index | number | Index value of the object to be retrieved. Zero-indexed. |
Returns
Examples
Excel.run(function (ctx) {
var table = ctx.workbook.tables.getItemAt(0);
table.load('name');
return ctx.sync().then(function() {
console.log(table.name);
});
}).catch(function(error) {
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
});
getItemOrNullObject(key: number or string)
Gets a table by Name or ID. If the table does not exist, will return a null object.
Syntax
tableCollectionObject.getItemOrNullObject(key);
Parameters
| Parameter | Type | Description |
|---|---|---|
| key | number or string | Name or ID of the table to be retrieved. |
Returns
Property access examples
Excel.run(function (ctx) {
var tables = ctx.workbook.tables;
tables.load();
return ctx.sync().then(function() {
console.log("tables Count: " + tables.count);
for (var i = 0; i < tables.items.length; i++)
{
console.log(tables.items[i].name);
}
});
}).catch(function(error) {
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
});
Get the number of tables
Excel.run(function (ctx) {
var tables = ctx.workbook.tables;
tables.load('count');
return ctx.sync().then(function() {
console.log(tables.count);
});
}).catch(function(error) {
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
});