Excel.Worksheet class
An Excel worksheet is a grid of cells. It can contain data, tables, charts, etc. To learn more about the worksheet object model, read Work with worksheets using the Excel JavaScript API.
- Extends
Remarks
Examples
// Get a Worksheet object by its name and activate it.
await Excel.run(async (context) => {
const wSheetName = 'Sheet1';
const worksheet = context.workbook.worksheets.getItem(wSheetName);
worksheet.activate();
await context.sync();
});
Properties
| charts | Returns a collection of charts that are part of the worksheet. |
| context | The request context associated with the object. This connects the add-in's process to the Office host application's process. |
| id | Returns a value that uniquely identifies the worksheet in a given workbook. The value of the identifier remains the same even when the worksheet is renamed or moved. |
| name | The display name of the worksheet. The name must be fewer than 32 characters. |
| names | Collection of names scoped to the current worksheet. |
| pivot |
Collection of PivotTables that are part of the worksheet. |
| position | The zero-based position of the worksheet within the workbook. |
| protection | Returns the sheet protection object for a worksheet. |
| tables | Collection of tables that are part of the worksheet. |
| visibility | The visibility of the worksheet. |
Methods
| activate() | Activate the worksheet in the Excel UI. |
| delete() | Deletes the worksheet from the workbook. Note that if the worksheet's visibility is set to "VeryHidden", the delete operation will fail with an |
| get |
Gets the |
| get |
Gets the worksheet that follows this one. If there are no worksheets following this one, this method will throw an error. |
| get |
Gets the worksheet that follows this one. If there are no worksheets following this one, then this method returns an object with its |
| get |
Gets the worksheet that precedes this one. If there are no previous worksheets, this method will throw an error. |
| get |
Gets the worksheet that precedes this one. If there are no previous worksheets, then this method returns an object with its |
| get |
Gets the |
| get |
The used range is the smallest range that encompasses any cells that have a value or formatting assigned to them. If the entire worksheet is blank, this function will return the top left cell (i.e. it will not throw an error). |
| get |
The used range is the smallest range that encompasses any cells that have a value or formatting assigned to them. If the entire worksheet is blank, then this method returns an object with its |
| 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 |
| set(properties, options) | Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type. |
| set(properties) | Sets multiple properties on the object at the same time, based on an existing loaded object. |
| toJSON() | Overrides the JavaScript |
Property Details
charts
Returns a collection of charts that are part of the worksheet.
readonly charts: Excel.ChartCollection;Property Value
Remarks
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
id
Returns a value that uniquely identifies the worksheet in a given workbook. The value of the identifier remains the same even when the worksheet is renamed or moved.
readonly id: string;Property Value
string
Remarks
name
The display name of the worksheet. The name must be fewer than 32 characters.
name: string;Property Value
string
Remarks
names
Collection of names scoped to the current worksheet.
readonly names: Excel.NamedItemCollection;Property Value
Remarks
pivotTables
Collection of PivotTables that are part of the worksheet.
readonly pivotTables: Excel.PivotTableCollection;Property Value
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/38-pivottable/pivottable-get-pivottables.yaml
await Excel.run(async (context) => {
// Get the names of all the PivotTables in the current worksheet.
const pivotTables = context.workbook.worksheets.getActiveWorksheet().pivotTables;
pivotTables.load("name");
await context.sync();
// Display the names in the console.
console.log("PivotTables in the current worksheet:")
pivotTables.items.forEach((pivotTable) => {
console.log(`\t${pivotTable.name}`);
});
});
position
The zero-based position of the worksheet within the workbook.
position: number;Property Value
number
Remarks
Examples
// Set worksheet position.
await Excel.run(async (context) => {
const wSheetName = 'Sheet1';
const worksheet = context.workbook.worksheets.getItem(wSheetName);
worksheet.position = 2;
await context.sync();
});
protection
Returns the sheet protection object for a worksheet.
readonly protection: Excel.WorksheetProtection;Property Value
Remarks
Examples
// Unprotecting a worksheet with unprotect() will remove all
// WorksheetProtectionOptions options applied to a worksheet.
// To remove only a subset of WorksheetProtectionOptions use the
// protect() method and set the options you wish to remove to true.
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sheet1");
sheet.protection.protect({
allowInsertRows: false, // Protect row insertion
allowDeleteRows: true // Unprotect row deletion
});
});
tables
Collection of tables that are part of the worksheet.
readonly tables: Excel.TableCollection;Property Value
Remarks
visibility
The visibility of the worksheet.
visibility: Excel.SheetVisibility | "Visible" | "Hidden" | "VeryHidden";Property Value
Excel.SheetVisibility | "Visible" | "Hidden" | "VeryHidden"
Remarks
[ API set: ExcelApi 1.1 for reading visibility; 1.2 for setting it. ]
Method Details
activate()
Activate the worksheet in the Excel UI.
activate(): void;Returns
void
Remarks
Examples
await Excel.run(async (context) => {
const wSheetName = 'Sheet1';
const worksheet = context.workbook.worksheets.getItem(wSheetName);
worksheet.activate();
await context.sync();
});
delete()
Deletes the worksheet from the workbook. Note that if the worksheet's visibility is set to "VeryHidden", the delete operation will fail with an InvalidOperation exception. You should first change its visibility to hidden or visible before deleting it.
delete(): void;Returns
void
Remarks
Examples
await Excel.run(async (context) => {
const wSheetName = 'Sheet1';
const worksheet = context.workbook.worksheets.getItem(wSheetName);
worksheet.delete();
await context.sync();
});
getCell(row, column)
Gets the Range object containing the single cell based on row and column numbers. The cell can be outside the bounds of its parent range, so long as it stays within the worksheet grid.
getCell(row: number, column: number): Excel.Range;Parameters
- row
-
number
The row number of the cell to be retrieved. Zero-indexed.
- column
-
number
The column number of the cell to be retrieved. Zero-indexed.
Returns
Remarks
Examples
await Excel.run(async (context) => {
const sheetName = "Sheet1";
const rangeAddress = "A1:F8";
const worksheet = context.workbook.worksheets.getItem(sheetName);
const cell = worksheet.getCell(0,0);
cell.load('address');
await context.sync();
console.log(cell.address);
});
getNext(visibleOnly)
Gets the worksheet that follows this one. If there are no worksheets following this one, this method will throw an error.
getNext(visibleOnly?: boolean): Excel.Worksheet;Parameters
- visibleOnly
-
boolean
Optional. If true, considers only visible worksheets, skipping over any hidden ones.
Returns
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/reference-worksheets-by-relative-position.yaml
await Excel.run(async (context) => {
const sheets = context.workbook.worksheets;
// We don't want to include the default worksheet that was created
// when the workbook was created, so our "firstSheet" will be the one
// after the literal first. Note chaining of navigation methods.
const firstSheet = sheets.getFirst().getNext();
const lastSheet = sheets.getLast();
const firstTaxRateRange = firstSheet.getRange("B2");
const lastTaxRateRange = lastSheet.getRange("B2");
firstSheet.load("name");
lastSheet.load("name");
firstTaxRateRange.load("text");
lastTaxRateRange.load("text");
await context.sync();
let firstYear = firstSheet.name.substr(5, 4);
let lastYear = lastSheet.name.substr(5, 4);
console.log(`Tax Rate change from ${firstYear} to ${lastYear}`, `Tax rate for ${firstYear}: ${firstTaxRateRange.text[0][0]}\nTax rate for ${lastYear}: ${lastTaxRateRange.text[0][0]}`)
await context.sync();
});
getNextOrNullObject(visibleOnly)
Gets the worksheet that follows this one. If there are no worksheets following this one, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.
getNextOrNullObject(visibleOnly?: boolean): Excel.Worksheet;Parameters
- visibleOnly
-
boolean
Optional. If true, considers only visible worksheets, skipping over any hidden ones.
Returns
Remarks
getPrevious(visibleOnly)
Gets the worksheet that precedes this one. If there are no previous worksheets, this method will throw an error.
getPrevious(visibleOnly?: boolean): Excel.Worksheet;Parameters
- visibleOnly
-
boolean
Optional. If true, considers only visible worksheets, skipping over any hidden ones.
Returns
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/reference-worksheets-by-relative-position.yaml
await Excel.run(async (context) => {
const sheets = context.workbook.worksheets;
const currentSheet = sheets.getActiveWorksheet();
const previousYearSheet = currentSheet.getPrevious();
const currentTaxDueRange = currentSheet.getRange("C2");
const previousTaxDueRange = previousYearSheet.getRange("C2");
currentSheet.load("name");
previousYearSheet.load("name");
currentTaxDueRange.load("text");
previousTaxDueRange.load("text");
await context.sync();
let currentYear = currentSheet.name.substr(5, 4);
let previousYear = previousYearSheet.name.substr(5, 4);
console.log("Two Year Tax Due Comparison", `Tax due for ${currentYear} was ${currentTaxDueRange.text[0][0]}\nTax due for ${previousYear} was ${previousTaxDueRange.text[0][0]}`)
await context.sync();
});
getPreviousOrNullObject(visibleOnly)
Gets the worksheet that precedes this one. If there are no previous worksheets, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.
getPreviousOrNullObject(visibleOnly?: boolean): Excel.Worksheet;Parameters
- visibleOnly
-
boolean
Optional. If true, considers only visible worksheets, skipping over any hidden ones.
Returns
Remarks
getRange(address)
Gets the Range object, representing a single rectangular block of cells, specified by the address or name.
getRange(address?: string): Excel.Range;Parameters
- address
-
string
Optional. The string representing the address or name of the range. For example, "A1:B2". If not specified, the entire worksheet range is returned.
Returns
Remarks
Examples
// Use the range address to get the range object.
await Excel.run(async (context) => {
const sheetName = "Sheet1";
const rangeAddress = "A1:F8";
const worksheet = context.workbook.worksheets.getItem(sheetName);
const range = worksheet.getRange(rangeAddress);
range.load('cellCount');
await context.sync();
console.log(range.cellCount);
});
getUsedRange(valuesOnly)
The used range is the smallest range that encompasses any cells that have a value or formatting assigned to them. If the entire worksheet is blank, this function will return the top left cell (i.e. it will not throw an error).
getUsedRange(valuesOnly?: boolean): Excel.Range;Parameters
- valuesOnly
-
boolean
Optional. If true, considers only cells with values as used cells (ignoring formatting). [Api set: ExcelApi 1.2]
Returns
Remarks
Examples
await Excel.run(async (context) => {
const wSheetName = 'Sheet1';
const worksheet = context.workbook.worksheets.getItem(wSheetName);
const usedRange = worksheet.getUsedRange();
usedRange.load('address');
await context.sync();
console.log(usedRange.address);
});
getUsedRangeOrNullObject(valuesOnly)
The used range is the smallest range that encompasses any cells that have a value or formatting assigned to them. If the entire worksheet is blank, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.
getUsedRangeOrNullObject(valuesOnly?: boolean): Excel.Range;Parameters
- valuesOnly
-
boolean
Optional. Considers only cells with values as used cells.
Returns
Remarks
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.WorksheetLoadOptions): Excel.Worksheet;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.Worksheet;Parameters
- propertyNames
-
string | string[]
A comma-delimited string or an array of strings that specify the properties to load.
Returns
Examples
// Get worksheet properties based on sheet name.
await Excel.run(async (context) => {
const wSheetName = 'Sheet1';
const worksheet = context.workbook.worksheets.getItem(wSheetName);
worksheet.load('position')
await context.sync();
console.log(worksheet.position);
});
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?: {
select?: string;
expand?: string;
}): Excel.Worksheet;Parameters
- propertyNamesAndPaths
-
{ select?: string; expand?: string; }
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
set(properties, options)
Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.
set(properties: Interfaces.WorksheetUpdateData, options?: OfficeExtension.UpdateOptions): void;Parameters
- properties
- Excel.Interfaces.WorksheetUpdateData
A JavaScript object with properties that are structured isomorphically to the properties of the object on which the method is called.
- options
- OfficeExtension.UpdateOptions
Provides an option to suppress errors if the properties object tries to set any read-only properties.
Returns
void
Examples
// Set the color and name of the current worksheet.
await Excel.run(async (context) => {
const activeSheet = context.workbook.worksheets.getActiveWorksheet();
activeSheet.set({
tabColor: "yellow",
name: "MySheet"
});
await context.sync();
});
set(properties)
Sets multiple properties on the object at the same time, based on an existing loaded object.
set(properties: Excel.Worksheet): void;Parameters
- properties
- Excel.Worksheet
Returns
void
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.Worksheet object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.WorksheetData) that contains shallow copies of any loaded child properties from the original object.
toJSON(): Excel.Interfaces.WorksheetData;Returns
Office Add-ins