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.

[ API set: ExcelApi 1.1 ]

Extends
OfficeExtension.ClientObject

Properties

autoFilter

Represents the AutoFilter object of the worksheet. Read-Only.

[ API set: ExcelApi 1.9 ]

charts

Returns a collection of charts that are part of the worksheet. Read-only.

[ API set: ExcelApi 1.1 ]

comments

Returns a collection of all the Comments objects on the worksheet. Read-only.

[ API set: ExcelApi 1.10 ]

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

enableCalculation

Gets or sets the enableCalculation property of the worksheet. True if Excel recalculates the worksheet when necessary. False if Excel doesn't recalculate the sheet.

[ API set: ExcelApi 1.9 ]

freezePanes

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

[ API set: ExcelApi 1.7 ]

horizontalPageBreaks

Gets the horizontal page break collection for the worksheet. This collection only contains manual page breaks.

[ API set: ExcelApi 1.9 ]

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

[ API set: ExcelApi 1.1 ]

name

The display name of the worksheet.

[ API set: ExcelApi 1.1 ]

names

Collection of names scoped to the current worksheet. Read-only.

[ API set: ExcelApi 1.4 ]

pageLayout

Gets the PageLayout object of the worksheet.

[ API set: ExcelApi 1.9 ]

pivotTables

Collection of PivotTables that are part of the worksheet. Read-only.

[ API set: ExcelApi 1.3 ]

position

The zero-based position of the worksheet within the workbook.

[ API set: ExcelApi 1.1 ]

protection

Returns sheet protection object for a worksheet. Read-only.

[ API set: ExcelApi 1.2 ]

shapes

Returns the collection of all the Shape objects on the worksheet. Read-only.

[ API set: ExcelApi 1.9 ]

showGridlines

Gets or sets the worksheet's gridlines flag. This flag determines whether gridlines are visible to the user.

[ API set: ExcelApi 1.8 ]

showHeadings

Gets or sets the worksheet's headings flag. This flag determines whether headings are visible to the user.

[ API set: ExcelApi 1.8 ]

slicers

Returns a collection of slicers that are part of the worksheet. Read-only.

[ API set: ExcelApi 1.10 ]

standardHeight

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

[ API set: ExcelApi 1.7 ]

standardWidth

Returns or sets 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.

[ API set: ExcelApi 1.7 ]

tabColor

Gets or sets the worksheet tab color. 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 "#123456" When setting the color, use an empty-string to set an "auto" color, or a real color otherwise.

[ API set: ExcelApi 1.7 ]

tables

Collection of tables that are part of the worksheet. Read-only.

[ API set: ExcelApi 1.1 ]

verticalPageBreaks

Gets the vertical page break collection for the worksheet. This collection only contains manual page breaks.

[ API set: ExcelApi 1.9 ]

visibility

The Visibility of the worksheet.

[ API set: ExcelApi 1.1 for reading visibility; 1.2 for setting it. ]

Methods

activate()

Activate the worksheet in the Excel UI.

[ API set: ExcelApi 1.1 ]

calculate(markAllDirty)

Calculates all cells on a worksheet.

[ API set: ExcelApi 1.6 ]

copy(positionType, relativeTo)

Copies a worksheet and places it at the specified position.

[ API set: ExcelApi 1.7 ]

copy(positionTypeString, relativeTo)

Copies a worksheet and places it at the specified position.

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

[ API set: ExcelApi 1.1 ]

findAll(text, criteria)

Finds all occurrences of the given string based on the criteria specified and returns them as a RangeAreas object, comprising one or more rectangular ranges.

[ API set: ExcelApi 1.9 ]

findAllOrNullObject(text, criteria)

Finds all occurrences of the given string based on the criteria specified and returns them as a RangeAreas object, comprising one or more rectangular ranges.

[ API set: ExcelApi 1.9 ]

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.

[ API set: ExcelApi 1.1 ]

getNext(visibleOnly)

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

[ API set: ExcelApi 1.5 ]

getNextOrNullObject(visibleOnly)

Gets the worksheet that follows this one. If there are no worksheets following this one, this method will return a null object.

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

[ API set: ExcelApi 1.5 ]

getPreviousOrNullObject(visibleOnly)

Gets the worksheet that precedes this one. If there are no previous worksheets, this method will return a null objet.

[ API set: ExcelApi 1.5 ]

getRange(address)

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

[ API set: ExcelApi 1.1 ]

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.

[ API set: ExcelApi 1.7 ]

getRanges(address)

Gets the RangeAreas object, representing one or more blocks of rectangular ranges, specified by the address or name.

[ API set: ExcelApi 1.9 ]

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

[ API set: ExcelApi 1.1 ]

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, this function will return a null object.

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

replaceAll(text, replacement, criteria)

Finds and replaces the given string based on the criteria specified within the current worksheet.

[ API set: ExcelApi 1.9 ]

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.

showOutlineLevels(rowLevels, columnLevels)

Shows row or column groups by their outline levels. Outlines group and summarize a list of data in the worksheet. The rowLevels and columnLevels parameters specify how many levels of the outline will be displayed. The acceptable argument range is between 0 and 8. A value of 0 does not change the current display. A value greater than the current number of levels displays all the levels.

[ API set: ExcelApi 1.10 ]

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.

[ API set: ExcelApi 1.7 ]

onCalculated

Occurs when the worksheet is calculated.

[ API set: ExcelApi 1.8 ]

onChanged

Occurs when data changed on a specific worksheet.

[ API set: ExcelApi 1.7 ]

onColumnSorted

Occurs when one or more columns have been sorted. This happens as the result of a left-to-right sort operation.

[ API set: ExcelApi 1.10 ]

onDeactivated

Occurs when the worksheet is deactivated.

[ API set: ExcelApi 1.7 ]

onFiltered

Occurs when filter is applied on a specific worksheet.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

onFormatChanged

Occurs when format changed on a specific worksheet.

[ API set: ExcelApi 1.9 ]

onRowHiddenChanged

Occurs when the hidden state of one or more rows has changed on a specific worksheet.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

onRowSorted

Occurs when one or more rows have been sorted. This happens as the result of a top-to-bottom sort operation.

[ API set: ExcelApi 1.10 ]

onSelectionChanged

Occurs when the selection changes on a specific worksheet.

[ API set: ExcelApi 1.7 ]

onSingleClicked

Occurs when a left-clicked/tapped action happens in the worksheet. This event will not be fired when clicking in the following cases:

  • The user drags the mouse for multi-selection.

  • The user selects a cell in the mode when cell arguments are selected for formula references.

[ API set: ExcelApi 1.10 ]

Property Details

autoFilter

Represents the AutoFilter object of the worksheet. Read-Only.

[ API set: ExcelApi 1.9 ]

readonly autoFilter: Excel.AutoFilter;

Property Value

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const farmData = sheet.getUsedRange();

    // This filter will only show the rows with the top 25% of values in column 3.
    sheet.autoFilter.apply(farmData, 3, { criterion1: "25", filterOn: Excel.FilterOn.topPercent });
    await context.sync();
});

charts

Returns a collection of charts that are part of the worksheet. Read-only.

[ API set: ExcelApi 1.1 ]

readonly charts: Excel.ChartCollection;

Property Value

comments

Returns a collection of all the Comments objects on the worksheet. Read-only.

[ API set: ExcelApi 1.10 ]

readonly comments: Excel.CommentCollection;

Property Value

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

enableCalculation

Gets or sets the enableCalculation property of the worksheet. True if Excel recalculates the worksheet when necessary. False if Excel doesn't recalculate the sheet.

[ API set: ExcelApi 1.9 ]

enableCalculation: boolean;

Property Value

boolean

freezePanes

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

[ API set: ExcelApi 1.7 ]

readonly freezePanes: Excel.WorksheetFreezePanes;

Property Value

horizontalPageBreaks

Gets the horizontal page break collection for the worksheet. This collection only contains manual page breaks.

[ API set: ExcelApi 1.9 ]

readonly horizontalPageBreaks: Excel.PageBreakCollection;

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

[ API set: ExcelApi 1.1 ]

readonly id: string;

Property Value

string

name

The display name of the worksheet.

[ API set: ExcelApi 1.1 ]

name: string;

Property Value

string

names

Collection of names scoped to the current worksheet. Read-only.

[ API set: ExcelApi 1.4 ]

readonly names: Excel.NamedItemCollection;

Property Value

pageLayout

Gets the PageLayout object of the worksheet.

[ API set: ExcelApi 1.9 ]

readonly pageLayout: Excel.PageLayout;

Property Value

pivotTables

Collection of PivotTables that are part of the worksheet. Read-only.

[ API set: ExcelApi 1.3 ]

readonly pivotTables: Excel.PivotTableCollection;

Property Value

position

The zero-based position of the worksheet within the workbook.

[ API set: ExcelApi 1.1 ]

position: number;

Property Value

number

protection

Returns sheet protection object for a worksheet. Read-only.

[ API set: ExcelApi 1.2 ]

readonly protection: Excel.WorksheetProtection;

Property Value

shapes

Returns the collection of all the Shape objects on the worksheet. Read-only.

[ API set: ExcelApi 1.9 ]

readonly shapes: Excel.ShapeCollection;

Property Value

showGridlines

Gets or sets the worksheet's gridlines flag. This flag determines whether gridlines are visible to the user.

[ API set: ExcelApi 1.8 ]

showGridlines: boolean;

Property Value

boolean

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.showGridlines = true;

    await context.sync();
});

showHeadings

Gets or sets the worksheet's headings flag. This flag determines whether headings are visible to the user.

[ API set: ExcelApi 1.8 ]

showHeadings: boolean;

Property Value

boolean

slicers

Returns a collection of slicers that are part of the worksheet. Read-only.

[ API set: ExcelApi 1.10 ]

readonly slicers: Excel.SlicerCollection;

Property Value

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Pivot");
    const slicer = sheet.slicers.add(
        "Farm Sales", /* The slicer data source. For PivotTables, this can be the PivotTable object reference or name. */
        "Type" /* The field in the data source to filter by. For PivotTables, this can be a PivotField object reference or ID. */
    );
    slicer.name = "Fruit Slicer";
    await context.sync();
});

standardHeight

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

[ API set: ExcelApi 1.7 ]

readonly standardHeight: number;

Property Value

number

standardWidth

Returns or sets 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.

[ API set: ExcelApi 1.7 ]

standardWidth: number;

Property Value

number

tabColor

Gets or sets the worksheet tab color. 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 "#123456" When setting the color, use an empty-string to set an "auto" color, or a real color otherwise.

[ API set: ExcelApi 1.7 ]

tabColor: string;

Property Value

string

Examples

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

[ API set: ExcelApi 1.1 ]

readonly tables: Excel.TableCollection;

Property Value

verticalPageBreaks

Gets the vertical page break collection for the worksheet. This collection only contains manual page breaks.

[ API set: ExcelApi 1.9 ]

readonly verticalPageBreaks: Excel.PageBreakCollection;

Property Value

visibility

The Visibility of the worksheet.

[ API set: ExcelApi 1.1 for reading visibility; 1.2 for setting it. ]

visibility: Excel.SheetVisibility | "Visible" | "Hidden" | "VeryHidden";

Property Value

Excel.SheetVisibility | "Visible" | "Hidden" | "VeryHidden"

Method Details

activate()

Activate the worksheet in the Excel UI.

[ API set: ExcelApi 1.1 ]

activate(): void;

Returns

void

Examples

Excel.run(function (ctx) { 
    var wSheetName = 'Sheet1';
    var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
    worksheet.activate();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

calculate(markAllDirty)

Calculates all cells on a worksheet.

[ API set: ExcelApi 1.6 ]

calculate(markAllDirty: boolean): void;

Parameters

markAllDirty
boolean

True, to mark all as dirty.

Returns

void

copy(positionType, relativeTo)

Copies a worksheet and places it at the specified position.

[ API set: ExcelApi 1.7 ]

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.

Examples

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.

[ API set: ExcelApi 1.7 ]

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.

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.

[ API set: ExcelApi 1.1 ]

delete(): void;

Returns

void

Examples

Excel.run(function (ctx) { 
    var wSheetName = 'Sheet1';
    var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
    worksheet.delete();
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

findAll(text, criteria)

Finds all occurrences of the given string based on the criteria specified and returns them as a RangeAreas object, comprising one or more rectangular ranges.

[ API set: ExcelApi 1.9 ]

findAll(text: string, criteria: Excel.WorksheetSearchCriteria): Excel.RangeAreas;

Parameters

text
string

The string to find.

criteria
Excel.WorksheetSearchCriteria

Additional search criteria, including whether the search needs to match the entire cell or be case sensitive.

Returns

A RangeArea object, comprising one or more rectangular ranges, that matches the search criteria. If no cells meet this criteria, an ItemNotFound error will be thrown.

findAllOrNullObject(text, criteria)

Finds all occurrences of the given string based on the criteria specified and returns them as a RangeAreas object, comprising one or more rectangular ranges.

[ API set: ExcelApi 1.9 ]

findAllOrNullObject(text: string, criteria: Excel.WorksheetSearchCriteria): Excel.RangeAreas;

Parameters

text
string

The string to find.

criteria
Excel.WorksheetSearchCriteria

Additional search criteria, including whether the search needs to match the entire cell or be case sensitive.

Returns

A RangeArea object, comprising one or more rectangular ranges, that matches the search criteria. If there are no matches, this function will return a null object.

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const foundRanges = sheet.findAllOrNullObject("Complete", {
        completeMatch: true,
        matchCase: false
    });

    await context.sync();

    if (foundRanges.isNullObject) {
        console.log("No complete projects");
    } else {
        foundRanges.format.fill.color = "green"
    }
});

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.

[ API set: ExcelApi 1.1 ]

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

Examples

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var worksheet = ctx.workbook.worksheets.getItem(sheetName);
    var cell = worksheet.getCell(0,0);
    cell.load('address');
    return ctx.sync().then(function() {
        console.log(cell.address);
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getNext(visibleOnly)

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

[ API set: ExcelApi 1.5 ]

getNext(visibleOnly?: boolean): Excel.Worksheet;

Parameters

visibleOnly
boolean

Optional. If true, considers only visible worksheets, skipping over any hidden ones.

Returns

Examples

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, this method will return a null object.

[ API set: ExcelApi 1.5 ]

getNextOrNullObject(visibleOnly?: boolean): Excel.Worksheet;

Parameters

visibleOnly
boolean

Optional. If true, considers only visible worksheets, skipping over any hidden ones.

Returns

getPrevious(visibleOnly)

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

[ API set: ExcelApi 1.5 ]

getPrevious(visibleOnly?: boolean): Excel.Worksheet;

Parameters

visibleOnly
boolean

Optional. If true, considers only visible worksheets, skipping over any hidden ones.

Returns

Examples

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, this method will return a null objet.

[ API set: ExcelApi 1.5 ]

getPreviousOrNullObject(visibleOnly?: boolean): Excel.Worksheet;

Parameters

visibleOnly
boolean

Optional. If true, considers only visible worksheets, skipping over any hidden ones.

Returns

getRange(address)

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

[ API set: ExcelApi 1.1 ]

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

Examples

// Below example uses range address to get the range object.
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "A1:F8";
    var worksheet = ctx.workbook.worksheets.getItem(sheetName);
    var range = worksheet.getRange(rangeAddress);
    range.load('cellCount');
    return ctx.sync().then(function() {
        console.log(range.cellCount);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
// Below example uses a named-range to get the range object.
Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeName = 'MyRange';
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeName);
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

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.

[ API set: ExcelApi 1.7 ]

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

getRanges(address)

Gets the RangeAreas object, representing one or more blocks of rectangular ranges, specified by the address or name.

[ API set: ExcelApi 1.9 ]

getRanges(address?: string): Excel.RangeAreas;

Parameters

address
string

Optional. A string containing the comma-separated addresses or names of the individual ranges. For example, "A1:B2, A5:B5". If not specified, an RangeArea object for the entire worksheet is returned.

Returns

Examples

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

    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const specifiedRanges = sheet.getRanges("D3:D5, G3:G5");
    specifiedRanges.format.fill.color = "pink";

    await context.sync();
})

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

[ API set: ExcelApi 1.1 ]

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

Examples

Excel.run(function (ctx) { 
    var wSheetName = 'Sheet1';
    var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
    var usedRange = worksheet.getUsedRange();
    usedRange.load('address');
    return ctx.sync().then(function() {
            console.log(usedRange.address);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

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, this function will return a null object.

[ API set: ExcelApi 1.4 ]

getUsedRangeOrNullObject(valuesOnly?: boolean): Excel.Range;

Parameters

valuesOnly
boolean

Optional. Considers only cells with values as used cells.

Returns

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.
Excel.run(function (ctx) { 
    var wSheetName = 'Sheet1';
    var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
    worksheet.load('position')
    return ctx.sync().then(function() {
            console.log(worksheet.position);
    });
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
// Set worksheet position. 
Excel.run(function (ctx) { 
    var wSheetName = 'Sheet1';
    var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
    worksheet.position = 2;
    return ctx.sync(); 
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

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

replaceAll(text, replacement, criteria)

Finds and replaces the given string based on the criteria specified within the current worksheet.

[ API set: ExcelApi 1.9 ]

replaceAll(text: string, replacement: string, criteria: Excel.ReplaceCriteria): OfficeExtension.ClientResult<number>;

Parameters

text
string

String to find.

replacement
string

String to replace the original with.

criteria
Excel.ReplaceCriteria

Additional Replace Criteria.

Returns

OfficeExtension.ClientResult<number>

The number of replacements performed.

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

Remarks

This method has the following additional signature:

set(properties: Excel.Worksheet): void

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

showOutlineLevels(rowLevels, columnLevels)

Shows row or column groups by their outline levels. Outlines group and summarize a list of data in the worksheet. The rowLevels and columnLevels parameters specify how many levels of the outline will be displayed. The acceptable argument range is between 0 and 8. A value of 0 does not change the current display. A value greater than the current number of levels displays all the levels.

[ API set: ExcelApi 1.10 ]

showOutlineLevels(rowLevels: number, columnLevels: number): void;

Parameters

rowLevels
number

The number of row levels of an outline to display.

columnLevels
number

The number of column levels of an outline to display.

Returns

void

Examples

Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // This collapses the entire outline.
    sheet.showOutlineLevels(1, 1);
    await context.sync();
});
Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // This shows the top 3 outline levels; collapsing any additional sublevels.
    sheet.showOutlineLevels(3, 3);
    await context.sync();
});

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.

[ API set: ExcelApi 1.7 ]

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

Returns

OfficeExtension.EventHandlers<Excel.WorksheetActivatedEventArgs>

Examples

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

onCalculated

Occurs when the worksheet is calculated.

[ API set: ExcelApi 1.8 ]

readonly onCalculated: OfficeExtension.EventHandlers<Excel.WorksheetCalculatedEventArgs>;

Returns

OfficeExtension.EventHandlers<Excel.WorksheetCalculatedEventArgs>

Examples

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    sheet.onCalculated.add(function (event) {
        return Excel.run((context) => {
            console.log("The worksheet has recalculated.");
            return context.sync();
        });
    });
    return context.sync();
});

onChanged

Occurs when data changed on a specific worksheet.

[ API set: ExcelApi 1.7 ]

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

Returns

OfficeExtension.EventHandlers<Excel.WorksheetChangedEventArgs>

Examples

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

    await context.sync();
    console.log("A handler has been registered for the onChanged event.");
});

onColumnSorted

Occurs when one or more columns have been sorted. This happens as the result of a left-to-right sort operation.

[ API set: ExcelApi 1.10 ]

readonly onColumnSorted: OfficeExtension.EventHandlers<Excel.WorksheetColumnSortedEventArgs>;

Returns

OfficeExtension.EventHandlers<Excel.WorksheetColumnSortedEventArgs>

Examples

await Excel.run(async (context) => {
    console.log("Adding column handler");
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // This will fire whenever a column has been moved as the result of a sort action.
    sheet.onColumnSorted.add((event) => {
        return Excel.run((context) => {
            console.log("Column sorted: " + event.address);
            const sheet = context.workbook.worksheets.getActiveWorksheet();

            // Clear formatting for section, then highlight the sorted area.
            sheet.getRange("A1:E5").format.fill.clear();
            if (event.address !== "") {
                sheet.getRanges(event.address).format.fill.color = "yellow";
            }

            return context.sync();
        });
    });
});

onDeactivated

Occurs when the worksheet is deactivated.

[ API set: ExcelApi 1.7 ]

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

Returns

OfficeExtension.EventHandlers<Excel.WorksheetDeactivatedEventArgs>

Examples

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

onFiltered

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Occurs when filter is applied on a specific worksheet.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly onFiltered: OfficeExtension.EventHandlers<Excel.WorksheetFilteredEventArgs>;

Returns

OfficeExtension.EventHandlers<Excel.WorksheetFilteredEventArgs>

onFormatChanged

Occurs when format changed on a specific worksheet.

[ API set: ExcelApi 1.9 ]

readonly onFormatChanged: OfficeExtension.EventHandlers<Excel.WorksheetFormatChangedEventArgs>;

Returns

OfficeExtension.EventHandlers<Excel.WorksheetFormatChangedEventArgs>

onRowHiddenChanged

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Occurs when the hidden state of one or more rows has changed on a specific worksheet.

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

readonly onRowHiddenChanged: OfficeExtension.EventHandlers<Excel.WorksheetRowHiddenChangedEventArgs>;

Returns

OfficeExtension.EventHandlers<Excel.WorksheetRowHiddenChangedEventArgs>

onRowSorted

Occurs when one or more rows have been sorted. This happens as the result of a top-to-bottom sort operation.

[ API set: ExcelApi 1.10 ]

readonly onRowSorted: OfficeExtension.EventHandlers<Excel.WorksheetRowSortedEventArgs>;

Returns

OfficeExtension.EventHandlers<Excel.WorksheetRowSortedEventArgs>

Examples

await Excel.run(async (context) => {
    console.log("Adding row handler");
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // This will fire whenever a row has been moved as the result of a sort action.
    sheet.onRowSorted.add((event) => {
        return Excel.run((context) => {
            console.log("Row sorted: " + event.address);
            const sheet = context.workbook.worksheets.getActiveWorksheet();

            // Clear formatting for section, then highlight the sorted area.
            sheet.getRange("A1:E5").format.fill.clear();
            if (event.address !== "") {
                sheet.getRanges(event.address).format.fill.color = "yellow";
            }

            return context.sync();
        });
    });
});

onSelectionChanged

Occurs when the selection changes on a specific worksheet.

[ API set: ExcelApi 1.7 ]

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

Returns

OfficeExtension.EventHandlers<Excel.WorksheetSelectionChangedEventArgs>

Examples

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

onSingleClicked

Occurs when a left-clicked/tapped action happens in the worksheet. This event will not be fired when clicking in the following cases:

  • The user drags the mouse for multi-selection.

  • The user selects a cell in the mode when cell arguments are selected for formula references.

[ API set: ExcelApi 1.10 ]

readonly onSingleClicked: OfficeExtension.EventHandlers<Excel.WorksheetSingleClickedEventArgs>;

Returns

OfficeExtension.EventHandlers<Excel.WorksheetSingleClickedEventArgs>

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.onSingleClicked.add((event) => {
        return Excel.run((context) => {
            console.log(`Click detected at ${event.address} (pixel offset from upper-left cell corner: ${event.offsetX}, ${event.offsetY})`);
            return context.sync();
        });
    });

    console.log("The worksheet click handler is registered.");

    await context.sync();
});