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

[ API set: ExcelApi 1.1 ]

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.

freezePanes

Gets an object that can be used to manipulate frozen panes on the worksheet.

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.

pivotTables

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.

standardHeight

Returns the standard (default) height of all the rows in the worksheet, in points.

standardWidth

Specifies the standard (default) width of all the columns in the worksheet. One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.

tabColor

The tab color of the worksheet. When retrieving the tab color, if the worksheet is invisible, the value will be null. If the worksheet is visible but the tab color is set to auto, an empty string will be returned. Otherwise, the property will be set to a color, in the form #RRGGBB (e.g., "FFA500"). When setting the color, use an empty-string to set an "auto" color, or a real color otherwise.

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.

calculate(markAllDirty)

Calculates all cells on a worksheet.

copy(positionType, relativeTo)

Copies a worksheet and places it at the specified position.

copy(positionTypeString, relativeTo)

Copies a worksheet and places it at the specified position.

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.

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.

getNext(visibleOnly)

Gets the worksheet that follows this one. If there are no worksheets following this one, this method will throw an error.

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.

getPrevious(visibleOnly)

Gets the worksheet that precedes this one. If there are no previous worksheets, this method will throw an error.

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.

getRange(address)

Gets the Range object, representing a single rectangular block of cells, specified by the address or name.

getRangeByIndexes(startRow, startColumn, rowCount, columnCount)

Gets the Range object beginning at a particular row index and column index, and spanning a certain number of rows and columns.

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).

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.

load(options)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNames)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNamesAndPaths)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

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 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.

Events

onActivated

Occurs when the worksheet is activated.

onChanged

Occurs when data changes in a specific worksheet.

onDeactivated

Occurs when the worksheet is deactivated.

onSelectionChanged

Occurs when the selection changes on a specific worksheet.

Property Details

charts

Returns a collection of charts that are part of the worksheet.

readonly charts: Excel.ChartCollection;

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

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

freezePanes

Gets an object that can be used to manipulate frozen panes on the worksheet.

readonly freezePanes: Excel.WorksheetFreezePanes;

Property Value

Remarks

[ API set: ExcelApi 1.7 ]

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

[ API set: ExcelApi 1.1 ]

name

The display name of the worksheet. The name must be fewer than 32 characters.

name: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.1 ]

names

Collection of names scoped to the current worksheet.

readonly names: Excel.NamedItemCollection;

Property Value

Remarks

[ API set: ExcelApi 1.4 ]

pivotTables

Collection of PivotTables that are part of the worksheet.

readonly pivotTables: Excel.PivotTableCollection;

Property Value

Remarks

[ API set: ExcelApi 1.3 ]

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

[ API set: ExcelApi 1.1 ]

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

[ API set: ExcelApi 1.2 ]

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
  });
});

standardHeight

Returns the standard (default) height of all the rows in the worksheet, in points.

readonly standardHeight: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.7 ]

standardWidth

Specifies the standard (default) width of all the columns in the worksheet. One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.

standardWidth: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.7 ]

tabColor

The tab color of the worksheet. When retrieving the tab color, if the worksheet is invisible, the value will be null. If the worksheet is visible but the tab color is set to auto, an empty string will be returned. Otherwise, the property will be set to a color, in the form #RRGGBB (e.g., "FFA500"). When setting the color, use an empty-string to set an "auto" color, or a real color otherwise.

tabColor: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.7 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/tab-color.yaml

await Excel.run(async (context) => {
    const activeSheet = context.workbook.worksheets.getActiveWorksheet();
    activeSheet.tabColor = "#FF0000";

    await context.sync();
});

tables

Collection of tables that are part of the worksheet.

readonly tables: Excel.TableCollection;

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

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

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const wSheetName = 'Sheet1';
    const worksheet = context.workbook.worksheets.getItem(wSheetName);
    worksheet.activate();
    await context.sync(); 
});

calculate(markAllDirty)

Calculates all cells on a worksheet.

calculate(markAllDirty: boolean): void;

Parameters

markAllDirty

boolean

True, to mark all as dirty.

Returns

void

Remarks

[ API set: ExcelApi 1.6 ]

copy(positionType, relativeTo)

Copies a worksheet and places it at the specified position.

copy(positionType?: Excel.WorksheetPositionType, relativeTo?: Excel.Worksheet): Excel.Worksheet;

Parameters

positionType
Excel.WorksheetPositionType

The location in the workbook to place the newly created worksheet. The default value is "None", which inserts the worksheet at the beginning of the worksheet.

relativeTo
Excel.Worksheet

The existing worksheet which determines the newly created worksheet's position. This is only needed if positionType is "Before" or "After".

Returns

The newly created worksheet.

Remarks

[ API set: ExcelApi 1.7 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/worksheet-copy.yaml

await Excel.run(async (context) => {

    let myWorkbook = context.workbook;
    let sampleSheet = myWorkbook.worksheets.getActiveWorksheet();
    let copiedSheet = sampleSheet.copy("End")

    sampleSheet.load("name");
    copiedSheet.load("name");

    await context.sync();

    console.log("'" + sampleSheet.name + "' was copied to '" + copiedSheet.name + "'")
});

copy(positionTypeString, relativeTo)

Copies a worksheet and places it at the specified position.

copy(positionTypeString?: "None" | "Before" | "After" | "Beginning" | "End", relativeTo?: Excel.Worksheet): Excel.Worksheet;

Parameters

positionTypeString

"None" | "Before" | "After" | "Beginning" | "End"

The location in the workbook to place the newly created worksheet. The default value is "None", which inserts the worksheet at the beginning of the worksheet.

relativeTo
Excel.Worksheet

The existing worksheet which determines the newly created worksheet's position. This is only needed if positionType is "Before" or "After".

Returns

The newly created worksheet.

Remarks

[ API set: ExcelApi 1.7 ]

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

[ API set: ExcelApi 1.1 ]

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

[ API set: ExcelApi 1.1 ]

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

[ API set: ExcelApi 1.5 ]

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

[ API set: ExcelApi 1.5 ]

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

[ API set: ExcelApi 1.5 ]

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

[ API set: ExcelApi 1.5 ]

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

[ API set: ExcelApi 1.1 ]

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);
});

getRangeByIndexes(startRow, startColumn, rowCount, columnCount)

Gets the Range object beginning at a particular row index and column index, and spanning a certain number of rows and columns.

getRangeByIndexes(startRow: number, startColumn: number, rowCount: number, columnCount: number): Excel.Range;

Parameters

startRow

number

Start row (zero-indexed).

startColumn

number

Start column (zero-indexed).

rowCount

number

Number of rows to include in the range.

columnCount

number

Number of columns to include in the range.

Returns

Remarks

[ API set: ExcelApi 1.7 ]

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

[ API set: ExcelApi 1.1 ]

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

[ API set: ExcelApi 1.4 ]

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

options
Excel.Interfaces.WorksheetLoadOptions

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

Event Details

onActivated

Occurs when the worksheet is activated.

readonly onActivated: OfficeExtension.EventHandlers<Excel.WorksheetActivatedEventArgs>;

Event Type

Remarks

[ API set: ExcelApi 1.7 ]

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    sheet.onActivated.add(function (event) {
        return Excel.run(async (context) => {
            console.log("The activated worksheet ID is: " + event.worksheetId);
            await context.sync();
        });
    });
    await context.sync();
});

onChanged

Occurs when data changes in a specific worksheet.

readonly onChanged: OfficeExtension.EventHandlers<Excel.WorksheetChangedEventArgs>;

Event Type

Remarks

[ API set: ExcelApi 1.7 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/30-events/events-worksheet.yaml

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    sheet.onChanged.add(onChange);
    await context.sync();

    console.log("Added a worksheet-level data-changed event handler.");
});

onDeactivated

Occurs when the worksheet is deactivated.

readonly onDeactivated: OfficeExtension.EventHandlers<Excel.WorksheetDeactivatedEventArgs>;

Event Type

Remarks

[ API set: ExcelApi 1.7 ]

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    sheet.onDeactivated.add(function (event) {
        return Excel.run(async (context) => {
            console.log("The deactivated worksheet is: " + event.worksheetId);
            await context.sync();
        });
    });
    await context.sync();
});

onSelectionChanged

Occurs when the selection changes on a specific worksheet.

readonly onSelectionChanged: OfficeExtension.EventHandlers<Excel.WorksheetSelectionChangedEventArgs>;

Event Type

Remarks

[ API set: ExcelApi 1.7 ]

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    sheet.onSelectionChanged.add(function (event) {
        return Excel.run(async (context) => {
            console.log("The selected range has changed to: " + event.address);
            await context.sync();
        });
    });
    await context.sync();
});