Work with Worksheets using the Excel JavaScript API
This article provides code samples that show how to perform common tasks with worksheets using the Excel JavaScript API. For the complete list of properties and methods that the Worksheet and WorksheetCollection objects support, see Worksheet Object (JavaScript API for Excel) and WorksheetCollection Object (JavaScript API for Excel).
Note: The information in this article applies only to regular worksheets; it does not apply to "chart" sheets or "macro" sheets.
Get worksheets
The following code sample gets the collection of worksheets, loads the name property of each worksheet, and writes a message to the console.
Excel.run(function (context) {
var sheets = context.workbook.worksheets;
sheets.load("items/name");
return context.sync()
.then(function () {
if (sheets.items.length > 1) {
console.log(`There are ${sheets.items.length} worksheets in the workbook:`);
} else {
console.log(`There is one worksheet in the workbook:`);
}
for (var i in sheets.items) {
console.log(sheets.items[i].name);
}
});
}).catch(errorHandlerFunction);
Note: The id property of a worksheet uniquely identifies the worksheet in a given workbook and its value will remain the same even when the worksheet is renamed or moved. When a worksheet is deleted from a workbook in Excel for Mac, the id of the deleted worksheet may be reassigned to a new worksheet that is subsequently created.
Get the active worksheet
The following code sample gets the active worksheet, loads its name property, and writes a message to the console.
Excel.run(function (context) {
var sheet = context.workbook.worksheets.getActiveWorksheet();
sheet.load("name");
return context.sync()
.then(function () {
console.log(`The active worksheet is "${sheet.name}"`);
});
}).catch(errorHandlerFunction);
Set the active worksheet
The following code sample sets the active worksheet to the worksheet named Sample, loads its name property, and writes a message to the console. If there is no worksheet with that name, the activate() method throws an ItemNotFound error.
Excel.run(function (context) {
var sheet = context.workbook.worksheets.getItem("Sample");
sheet.activate();
sheet.load("name");
return context.sync()
.then(function () {
console.log(`The active worksheet is "${sheet.name}"`);
});
}).catch(errorHandlerFunction);
Reference worksheets by relative position
These examples show how to reference a worksheet by its relative position.
Get the first worksheet
The following code sample gets the first worksheet in the workbook, loads its name property, and writes a message to the console.
Excel.run(function (context) {
var firstSheet = context.workbook.worksheets.getFirst();
firstSheet.load("name");
return context.sync()
.then(function () {
console.log(`The name of the first worksheet is "${firstSheet.name}"`);
});
}).catch(errorHandlerFunction);
Get the last worksheet
The following code sample gets the last worksheet in the workbook, loads its name property, and writes a message to the console.
Excel.run(function (context) {
var lastSheet = context.workbook.worksheets.getLast();
lastSheet.load("name");
return context.sync()
.then(function () {
console.log(`The name of the last worksheet is "${lastSheet.name}"`);
});
}).catch(errorHandlerFunction);
Get the next worksheet
The following code sample gets the worksheet that follows the active worksheet in the workbook, loads its name property, and writes a message to the console. If there is no worksheet after the active worksheet, the getNext() method throws an ItemNotFound error.
Excel.run(function (context) {
var currentSheet = context.workbook.worksheets.getActiveWorksheet();
var nextSheet = currentSheet.getNext();
nextSheet.load("name");
return context.sync()
.then(function () {
console.log(`The name of the sheet that follows the active worksheet is "${nextSheet.name}"`);
});
}).catch(errorHandlerFunction);
Get the previous worksheet
The following code sample gets the worksheet that precedes the active worksheet in the workbook, loads its name property, and writes a message to the console. If there is no worksheet before the active worksheet, the getPrevious() method throws an ItemNotFound error.
Excel.run(function (context) {
var currentSheet = context.workbook.worksheets.getActiveWorksheet();
var previousSheet = currentSheet.getPrevious();
previousSheet.load("name");
return context.sync()
.then(function () {
console.log(`The name of the sheet that precedes the active worksheet is "${previousSheet.name}"`);
});
}).catch(errorHandlerFunction);
Add a worksheet
The following code sample adds a new worksheet named Sample to the workbook, loads its name and position properties, and writes a message to the console. The new worksheet is added after all existing worksheets.
Excel.run(function (context) {
var sheets = context.workbook.worksheets;
var sheet = sheets.add("Sample");
sheet.load("name, position");
return context.sync()
.then(function () {
console.log(`Added worksheet named "${sheet.name}" in position ${sheet.position}`);
});
}).catch(errorHandlerFunction);
Delete a worksheet
The following code sample deletes the final worksheet in the workbook (as long as it's not the only sheet in the workbook) and writes a message to the console.
Excel.run(function (context) {
var sheets = context.workbook.worksheets;
sheets.load("items/name");
return context.sync()
.then(function () {
if (sheets.items.length === 1) {
console.log("Unable to delete the only worksheet in the workbook");
} else {
var lastSheet = sheets.items[sheets.items.length - 1];
console.log(`Deleting worksheet named "${lastSheet.name}"`);
lastSheet.delete();
return context.sync();
};
});
}).catch(errorHandlerFunction);
Rename a worksheet
The following code sample changes the name of the active worksheet to New Name.
Excel.run(function (context) {
var currentSheet = context.workbook.worksheets.getActiveWorksheet();
currentSheet.name = "New Name";
return context.sync();
}).catch(errorHandlerFunction);
Move a worksheet
The following code sample moves a worksheet from the last position in the workbook to the first position in the workbook.
Excel.run(function (context) {
var sheets = context.workbook.worksheets;
sheets.load("items");
return context.sync()
.then(function () {
var lastSheet = sheets.items[sheets.items.length - 1];
lastSheet.position = 0;
return context.sync();
});
}).catch(errorHandlerFunction);
Set worksheet visibility
These examples show how to set the visibility of a worksheet.
Hide a worksheet
The following code sample sets the visibility of worksheet named Sample to hidden, loads its name property, and writes a message to the console.
Excel.run(function (context) {
var sheet = context.workbook.worksheets.getItem("Sample");
sheet.visibility = Excel.SheetVisibility.hidden;
sheet.load("name");
return context.sync()
.then(function () {
console.log(`Worksheet with name "${sheet.name}" is hidden`);
});
}).catch(errorHandlerFunction);
Unhide a worksheet
The following code sample sets the visibility of worksheet named Sample to visible, loads its name property, and writes a message to the console.
Excel.run(function (context) {
var sheet = context.workbook.worksheets.getItem("Sample");
sheet.visibility = Excel.SheetVisibility.visible;
sheet.load("name");
return context.sync()
.then(function () {
console.log(`Worksheet with name "${sheet.name}" is visible`);
});
}).catch(errorHandlerFunction);
Get a cell within a worksheet
The following code sample gets the cell that is located in row 2, column 5 of the worksheet named Sample, loads its address and values properties, and writes a message to the console. The values that are passed into the getCell(row: number, column:number) method are the zero-indexed row number and column number for the cell that is being retrieved.
Excel.run(function (context) {
var sheet = context.workbook.worksheets.getItem("Sample");
var cell = sheet.getCell(1, 4);
cell.load("address, values");
return context.sync()
.then(function() {
console.log(`The value of the cell in row 2, column 5 is "${cell.values[0][0]}" and the address of that cell is "${cell.address}"`);
})
}).catch(errorHandlerFunction);
Get a range within a worksheet
For examples that show how to get a range within a worksheet, see Work with Ranges using the Excel JavaScript API.