WorksheetCollection Object (JavaScript API for Excel)
Represents a collection of worksheet objects that are part of the workbook.
Properties
| Property | Type | Description | Req. Set |
|---|---|---|---|
| items | Worksheet[] | A collection of worksheet objects. Read-only. | 1.1 |
See property access examples.
Relationships
None
Methods
| Method | Return Type | Description | Req. Set |
|---|---|---|---|
| add(name: string) | Worksheet | Adds a new worksheet to the workbook. The worksheet will be added at the end of existing worksheets. If you wish to activate the newly added worksheet, call ".activate() on it. | 1.1 |
| getActiveWorksheet() | Worksheet | Gets the currently active worksheet in the workbook. | 1.1 |
| getCount(visibleOnly: bool) | int | Gets the number of worksheets in the collection. | 1.4 |
| getFirst(visibleOnly: bool) | Worksheet | Gets the first worksheet in the collection. | 1.5 |
| getItem(key: string) | Worksheet | Gets a worksheet object using its Name or ID. | 1.1 |
| getItemOrNullObject(key: string) | Worksheet | Gets a worksheet object using its Name or ID. If the worksheet does not exist, will return a null object. | 1.4 |
| getLast(visibleOnly: bool) | Worksheet | Gets the last worksheet in the collection. | 1.5 |
Method Details
add(name: string)
Adds a new worksheet to the workbook. The worksheet will be added at the end of existing worksheets. If you wish to activate the newly added worksheet, call ".activate() on it.
Syntax
worksheetCollectionObject.add(name);
Parameters
| Parameter | Type | Description |
|---|---|---|
| name | string | Optional. The name of the worksheet to be added. If specified, name should be unqiue. If not specified, Excel determines the name of the new worksheet. |
Returns
Examples
Excel.run(function (ctx) {
var wSheetName = 'Sample Name';
var worksheet = ctx.workbook.worksheets.add(wSheetName);
worksheet.load('name');
return ctx.sync().then(function() {
console.log(worksheet.name);
});
}).catch(function(error) {
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
});
getActiveWorksheet()
Gets the currently active worksheet in the workbook.
Syntax
worksheetCollectionObject.getActiveWorksheet();
Parameters
None
Returns
Examples
Excel.run(function (ctx) {
var activeWorksheet = ctx.workbook.worksheets.getActiveWorksheet();
activeWorksheet.load('name');
return ctx.sync().then(function() {
console.log(activeWorksheet.name);
});
}).catch(function(error) {
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
});
getCount(visibleOnly: bool)
Gets the number of worksheets in the collection.
Syntax
worksheetCollectionObject.getCount(visibleOnly);
Parameters
| Parameter | Type | Description |
|---|---|---|
| visibleOnly | bool | Optional. Considers only the visible cells. |
Returns
int
getFirst(visibleOnly: bool)
Gets the first worksheet in the collection.
Syntax
worksheetCollectionObject.getFirst(visibleOnly);
Parameters
| Parameter | Type | Description |
|---|---|---|
| visibleOnly | bool | Optional. If true, considers only visible worksheets, skipping over any hidden ones. |
Returns
getItem(key: string)
Gets a worksheet object using its Name or ID.
Syntax
worksheetCollectionObject.getItem(key);
Parameters
| Parameter | Type | Description |
|---|---|---|
| key | string | The Name or ID of the worksheet. |
Returns
getItemOrNullObject(key: string)
Gets a worksheet object using its Name or ID. If the worksheet does not exist, will return a null object.
Syntax
worksheetCollectionObject.getItemOrNullObject(key);
Parameters
| Parameter | Type | Description |
|---|---|---|
| key | string | The Name or ID of the worksheet. |
Returns
getLast(visibleOnly: bool)
Gets the last worksheet in the collection.
Syntax
worksheetCollectionObject.getLast(visibleOnly);
Parameters
| Parameter | Type | Description |
|---|---|---|
| visibleOnly | bool | Optional. If true, considers only visible worksheets, skipping over any hidden ones. |
Returns
Property access examples
Excel.run(function (ctx) {
var worksheets = ctx.workbook.worksheets;
worksheets.load('items');
return ctx.sync().then(function() {
for (var i = 0; i < worksheets.items.length; i++)
{
console.log(worksheets.items[i].name);
console.log(worksheets.items[i].index);
}
});
}).catch(function(error) {
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
});