Excel JavaScript APIs

Objects

• Workbook: Workbook is the top level object which contains related workbook objects such as worksheets, tables, ranges, etc. It can be used to list related references.
• Worksheet: The Worksheet object is a member of the Worksheets collection. The Worksheets collection contains all the Worksheet objects in a workbook.
  • Worksheet Collection: A collection of all the Workbook objects that are part of the workbook.
• Range: Range represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells.
• Table: Represents collection of organized cells designed to make management of the data easy.
  • Table Collection: A collection of Tables in a workbook or worksheet.
  • TableColumn Collection: A collection of all the columns in a Table.
  • TableRow Collection: A collection of all the rows in a Table.
• Chart: Represents a chart object in a workbook, which is a visual representation of underlying data.
  • Chart Collection: A collection of charts in a workbook or a worksheet.
• NamedItem: Represents a defined name for a range of cells or a value. Names can be primitive named objects (as seen in the type below), range object, etc.
  • NamedItem Collection: a collection of named items of a workbook.
• Binding: An abstract class that represents a binding to a section of the workbook.
  • Binding Collection:A collection of all the Binding objects that are part of the workbook.
• Reference Collection: Reference collection allows add-ins to add and remove temporary references on range.
• Request Context: The RequestContext object facilitates requests to the Excel application.

Also read the following programming notes:

• Programming Notes: Provide important programming details related to Excel APIs.
• Error Messages: Provide important programming details related to Excel APIs.

top

Programming Notes

Following sections provide important programming details related to Excel APIs.

• The Basics
• Properties and Relations Selection
• Document Binding
• Reference Binding
• Null-Input
• Null-Response
• Blank Input and Output
• Unbounded-Range
• Large-Range
• Single Input Copy
• Throttling

top

The Basics

This section introduces three key concepts to help get started with the Excel API. Namely, RequestContext, executeAsync and load statements.

RequestContext

The RequestContext object facilitates requests to the Excel application. Since the Office add-in and the Excel application run in two different processes, request context is required to get access to Excel and related objects such as worksheets, tables, etc. from the add-in.

A request context object is created as shown bellow:

var ctx = new Excel.RequestContext();

executeAsync()

The Excel JavaScript objects created in the add-ins are local proxy objects. Any method invocation or setting of properties queues commands in JavaScript, but does not submit them until executeAsync() is called. executeAsync submits the request queue to Excel and returns a promise object, which can be used for chaining further actions.

Example

The following example shows how to write values from an array to a range. First RequestContext() is created to get access to the workbook. Then a worksheet is added. Range A1:B2 on the sheet is retrieved afterwards. Finally we assign the values stored in the array to this range. All these commands are queued and will run when ctx.executeAsync() is called. executeAsync() returns a promise which can be used to chain it with other operations.

	var ctx = new Excel.RequestContext();
	var sheet = ctx.workbook.worksheets.add();
	var values = [
				 ["Type", "Estimate"],
				 ["Transportation", 1670]
				 ];
	var range = sheet.getRange("A1:B2");
	range.values = values;
	//statements queued above will not be executed until the executeAsync() is called. 
	ctx.executeAsync()
		.then(function () {   			
			console.log("Done");
		 })
		.catch(function(error) {
			console. error(JSON.stringify(error));
		});

load()

Load method is used to fill in the Excel proxy objects created in the add-in JavaScript layer. When trying to retrieve an object, say, a worksheet, a local proxy object is created first in the JavaScript layer. Such an object can be used to queue up setting of its properties and invoking methods. However, for reading object properties or relations, the load() method and executeAsync() needs to be invoked first. Load method takes in the parameters and relations that needs to be loaded when the executeAsync is called.

Syntax

object.load(properties);
//or
object.load({loadOption});

Where,

• properties is the list of properties and/or relationship names to be loaded specified as comma delimited strings or array of names. See .load() methods under each object for details.
• loadOption specifies selection, expansion, top, and skip options. See loadOption object for details.

Example

The following example shows how to copy the values from Range A1:A2 to B1:B2 by using load() method on the range object.

var ctx = new Excel.RequestContext();
var range = ctx.workbook.worksheets.getActiveWorksheet().getRange("A1:A2");
range.load ("address, values, range/format"); 
// same as range.load (["address", "values", "range/format"]); 

ctx.executeAsync()
	.then(function () {
	var myvalues=range.values;
	ctx.workbook.worksheets. getActiveWorksheet().getRange("B1:B2").values= myvalues;
	ctx.executeAsync()
  		.then(function () {
			console.log(range.address);
			console.log(range.values);
			console.log(range.format.wrapText);				
		})
		.catch(function(error) {
			console. error(JSON.stringify(error));
		})
});

The following example shows how to copy the values from Range A1:A2 to B1:B2 by using load() method on the context object.

var ctx = new Excel.RequestContext();
var range = ctx.workbook.worksheets.getActiveWorksheet().getRange("A1:A2");
range.load ({"select": "address, values", "expand" : "range/format"});
ctx.executeAsync()
	.then(function () {
	var myvalues=range.values;
	ctx.workbook.worksheets. getActiveWorksheet().getRange("B1:B2").values= myvalues;
	ctx.executeAsync()
  		.then(function () {
			console.log(range.address);
			console.log(range.values);
			console.log(range.format.wrapText);
		})
		.catch(function(error) {
			console. error(JSON.stringify(error));
		})
});

Summary

1. Getting a RequestContext is the first step to interact with Excel.
2. All JavaScript objects are local proxy objects. Any method invocation or setting of properties queues commands in JavaScript, but does not submit them until executeAsync() is called.
3. Load is a special type of command for retrieval of properties. Properties can only be accesed after invoking executeAsync().
4. For performance reasons, avoid loading objects without specifying individual properties that will be used.

top

Properties and Relations Selection

• By default load() selects all scalar/complex properties of the object which is being loaded. The relations are not loaded by default. Exceptions: any binary, XML, etc properties are not returned.
• The select option specifies a subset of properties and/or relations to include in the response.
• Default Select behavior:
  • Does not select any property
  • Need to specify every property that needs to be returned
  • Relations/Navigation properties are also allowed to be included in the list. Use expand syntax to
• The properties to be selected are provided during the load statement.
• Select will essentially get the users into optimized mode of handpicking what they want.
• Property names are listed as a parameter to the select property. Support two kinds of inputs
  • Property names are separated by comma.
  • Provide an array of property name strings

object.load  (<var1>,<relation1/var2>);

// Pass the parameter as an array.
object.load (["var1", "relation1/var2"]);

Examples

var sheetName = "Sheet1";
var rangeAddress = "A1:B2";
var ctx = new Excel.ExcelClientContext();
var myRange = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);

//load statement below loads the address, values, numberFormat properties of the Range and then expands on the format, format/background, entireRow relations
 
myRange.load (["address", "values", "numberFormat", "format", "format/background", "entireRow"]);

ctx.executeAsync().then(function () {
		console.log (myRange.address); //ok
		console.log (myRange.cellCount); //not-ok
		console.log (myRange.format.wrapText); //ok
		console.log (myRange.format.background.color); //ok
		console.log (myRange.format.font.color); //not-ok
		console.log (myRange.entireRow.address); //ok
		console.log (myRange.entireColumn.address); //not-ok
// . . . 

//load statement below loads all the properties of the Range and then expands on the format, format/background, entireRow relations.  
 
myRange.load(["address", "format", "format/background", "entireRow" ]);

ctx.executeAsync().then(function () {
		console.log (myRange.address); //ok
		console.log (myRange.format.wrapText); //o