worksheet.md

Worksheet

An Excel worksheet is a grid of cells. It can contain data, tables, charts, etc.

Properties

Property	Type	Description
id	string	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.
name	string	The display name of the worksheet.
position	int	The zero-based position of the worksheet within the workbook.
visibility	string	The Visibility of the worksheet, Read-only. Possible values are: Visible, Hidden, VeryHidden.

Relationships

Relationship	Type	Description
charts	ChartCollection	Returns collection of charts that are part of the worksheet. Read-only.
tables	TableCollection	Collection of tables that are part of the worksheet. Read-only.

Methods

Method	Return Type	Description
activate()	void	Activate the worksheet in the Excel UI.
delete()	void	Deletes the worksheet from the workbook.
getCell(row: number, column: number)	Range	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's stays within the worksheet grid.
getRange(address: string)	Range	Gets the range object specified by the address or name.
getUsedRange()	Range	The used range is the smallest range than encompasses any cells that have a value or formatting assigned to them. If the worksheet is blank, this function will return the top left cell.
load(param: object)	void	Fills the proxy object created in JavaScript layer with property and object values specified in the parameter.

API Specification

activate()

Activate the worksheet in the Excel UI.

Syntax

worksheetObject.activate();

Parameters

None

Returns

void

Examples

var ctx = new Excel.RequestContext();
var wSheetName = 'Sheet1';
var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
worksheet.activate();
ctx.executeAsync();

Back

delete()

Deletes the worksheet from the workbook.

Syntax

worksheetObject.delete();

Parameters

None

Returns

void

Examples

var wSheetName = 'Sheet1';
var ctx = new Excel.RequestContext();
var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
worksheet.delete();
ctx.executeAsync();

Back

getCell(row: number, column: number)

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's stays within the worksheet grid.

Syntax

worksheetObject.getCell(row, column);

Parameters

Parameter	Type	Description
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

Range

Examples

var sheetName = "Sheet1";
var rangeAddress = "A1:F8";
var ctx = new Excel.RequestContext();
var worksheet = ctx.workbook.worksheets.getItem(sheetName);
var cell = worksheet.getCell(0,0);
ctx.load(cell);
ctx.executeAsync().then(function() {
	Console.log(cell.address);
});

Back

getRange(address: string)

Gets the range object specified by the address or name.

Syntax

worksheetObject.getRange(address);

Parameters

Parameter	Type	Description
address	string	Optional. The address or the name of the range. If not specified, the entire worksheet range is returned.

Returns

Range

Examples

Below example uses range address to get the range object.

var sheetName = "Sheet1";
var rangeAddress = "A1:F8";
var ctx = new Excel.RequestContext();
var worksheet = ctx.workbook.worksheets.getItem(sheetName);
var range = worksheet.getRange(rangeAddress);
ctx.load(range);
ctx.executeAsync().then(function() {
	Console.log(range.cellCount);
});

Below example uses a named-range to get the range object.

var sheetName = "Sheet1";
var rangeName = 'MyRange';
var ctx = new Excel.RequestContext();
var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeName);
ctx.load(range);
ctx.executeAsync().then(function() {
	Console.log(range.address);
});

Back

getUsedRange()

The used range is the smallest range than encompasses any cells that have a value or formatting assigned to them. If the worksheet is blank, this function will return the top left cell.

Syntax

worksheetObject.getUsedRange();

Parameters

None

Returns

Range

Examples

var ctx = new Excel.RequestContext();
var wSheetName = 'Sheet1';
var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
var usedRange = worksheet.getUsedRange();
ctx.load(usedRange);
ctx.executeAsync().then(function () {
		Console.log(usedRange.address);
});

Back

load(param: object)

Fills the proxy object created in JavaScript layer with property and object values specified in the parameter.

Syntax

object.load(param);

Parameters

Parameter	Type	Description
param	object	Optional. Accepts parameter and relationship names as delimited string or an array. Or, provide loadOption object.

Returns

void

Examples

Back

Getter and Setter Examples

Get worksheet properties based on sheet name.

var ctx = new Excel.RequestContext();
var wSheetName = 'Sheet1';
var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
ctx.executeAsync().then(function () {
		Console.log(worksheet.index);
});

Set worksheet position.

var ctx = new Excel.RequestContext();
var wSheetName = 'Sheet1';
var worksheet = ctx.workbook.worksheets.getItem(wSheetName);
worksheet.position = 0;
ctx.executeAsync();

Back