Using the Excel Services JavaScript API to Work with Embedded Excel Workbooks

Article
07/24/2014

Applies to: SharePoint Server 2010

In this article
Working with an embedded document
Examining the OneDrive JavaScript embed code
Using the Excel Services JavaScript API

An embedded Microsoft Excel workbook is a workbook that is stored on Microsoft OneDrive, but displayed in a host web page. One way to embed a workbook is to display the workbook in an HTML <iframe> element. You can also embed an Excel workbook that is stored on OneDrive using ECMAScript (JavaScript, JScript) and an HTML <div> element. One advantage of embedding a workbook in a <div> element over embedding the workbook in an <iframe> element is that you can programmatically manipulate the workbook to create custom applications and mashups.

Note

For more information about how to embed a workbook on a web page using an HTML <iframe>, see Embed an Excel workbook on a web page

In this topic, you learn how you can use the Excel ServicesECMAScript (JavaScript, JScript) (JavaScript) API to programmatically work with an embedded Excel workbook that is stored on OneDrive.

Working with an embedded document

To work programmatically with an embedded workbook, you must do two things: store the Excel workbook on OneDrive; and insert ECMAScript (JavaScript, JScript) code into the web page to programmatically embed the workbook.

Store the workbook on OneDrive

Before you can embed a workbook on your web page the workbook must be available to view publicly. The workbook must be shared to the public from OneDrive. For more information about how to share a workbook that is stored on OneDrive, see How do I change who can access my folders, files, and photos?.

Create the JavaScript embed code snippet

To display and programmatically work with the workbook on a host web page, you must create and insert embed code into the HTML of the host web page. The embed code defines a <div> element, includes a reference to the Excel ServicesECMAScript (JavaScript, JScript) library, specifies the workbook (or chart, named object, or range) to embed within the <div> element, specifies various options for the display and interactivity of the workbook, and then embeds the workbook.

Although you can write your own code to embed a workbook, the easiest way to write the workbook ECMAScript (JavaScript, JScript) embed code is to use the Embed dialog box available through the Excel Web App Share menu. The Embed dialog box user interface enables you to set embedding options on a workbook and to automatically generate the HTML and ECMAScript (JavaScript, JScript) code to embed the workbook with options that specify the display and interactivity for the workbook. For more information about how to use the Embed dialog box to automatically generate embed code, see Make a mashup: extend Excel to your web app.

SkyDrive embed dialog box

The following section discusses the OneDrive JavaScript embed code.

Examining the OneDrive JavaScript embed code

In this section, you will learn about the basic elements of the JavaScript code that you use to embed a workbook and work with it programmatically. The following code example shows all of the basic elements of the required embed code. The code shown here was created by using the Embed dialog box mentioned in the previous section of this topic.

<div id="myExcelDiv" style="width: 402px; height: 346px"></div>
<script type="text/javascript" src="http://r.office.microsoft.com/r/rlidExcelWLJS?v=1&kip=1"></script>
<script type="text/javascript">
/*
 * This code uses the Microsoft Office Excel JavaScript object model to programmatically insert the
 * Excel Web App into a div with id=myExcelDiv. The full API is documented at
 * https://msdn.microsoft.com/en-us/library/hh315812.aspx. There you can find out how to get
 * values programmatically from your Excel file and how to use the rest of the object model. 
 */

// Use this file token to reference Book1.xlsx in the Excel APIs
var fileToken = "SD206C5E037684EDE5!1119/2336345675664780773/";

// Run the Excel load handler on page load
if (window.attachEvent) {
window.attachEvent("onload", loadEwaOnPageLoad);
} else {
window.addEventListener("DOMContentLoaded", loadEwaOnPageLoad, false);
}

function loadEwaOnPageLoad() {
var props = {
uiOptions: {
showGridlines: false,
showRowColumnHeaders: false,
showParametersTaskPane: false
},
interactivityOptions: {
allowTypingAndFormulaEntry: false,
allowParameterModification: false,
allowSorting: false,
allowFiltering: false,
allowPivotTableInteractivity: false
}
};

Ewa.EwaControl.loadEwaAsync(fileToken, "myExcelDiv", props, onEwaLoaded);
}

function onEwaLoaded(result) {
/*
 * Add code here to interact with the embedded Excel web app.
 * For more information, see https://msdn.microsoft.com/en-us/library/hh315812.aspx.
 */
}
</script>

Define the <div> element for the embedded workbook

The first line of the embed code defines a <div> element that will hold the embedded workbook.

<divid="myExcelDiv" style="width: 402px; height: 346px"></div>

The id attribute specifies the identifier for the <div> element. The id is used later in the ECMAScript (JavaScript, JScript) code to specify where to embed the workbook.

The <div> element also contains a style attribute that specifies the dimensions of the <div> element.

Include a reference to the Excel Services JavaScript object model library

To work programmatically with the embedded workbook, you must have a reference to the Excel ServicesECMAScript (JavaScript, JScript) API. The embed code in the example inserts a <script> element that points to the Excel ServicesECMAScript (JavaScript, JScript) API that is hosted on OneDrive.

<script type="text/javascript" src="http://r.office.microsoft.com/r/rlidExcelWLJS? v=1&kip=1"></script>

The src attribute of the <script> element specifies the library location on OneDrive. When working with embedded workbooks that are stored on OneDrive, the src attribute must always point to the OneDriveExcel ServicesECMAScript (JavaScript, JScript) API library location.

The URI for the src attribute contains two querystring parameters: v, for "version"; and kip, for "keep inbound protocol."

v indicates the version of the Excel ServicesECMAScript (JavaScript, JScript) API. Currently, only version 1 is available.

kip specifies that redirection should keep the protocol used, either http or https.

Specify the Microsoft Excel workbook to embed

The embed code shown earlier in this topic includes a script element that contains the ECMAScript (JavaScript, JScript) that does most of the work to embed and configure the workbook. The first line of ECMAScript (JavaScript, JScript) code initializes a variable with the identifier for the workbook stored in the OneDrive public folder.

<script type="text/javascript">
// Use this file token to reference Book1.xlsx in the Excel APIs
var fileToken = "SD206C5E037684EDE5!1119/2336345675664780773/";

In this code example, fileToken holds the string value that specifies a workbook named Book1.xlsx that is stored on OneDrive.

Important

To get the unique file token for the workbook that you want to embed, use the file token provided in the Embed dialog box code.

Set display and interactivity options for the embedded workbook

You can specify how you want the embedded workbook to display and how users can interact with it when it is displayed. The options available in the API correspond with many options that you can set in the Embed dialog box user interface.

In the following code example, the display and interactivity options are set within the function loadEwaOnPageLoad.

function loadEwaOnPageLoad() {
var props = {
uiOptions: {
showGridlines: false,
showRowColumnHeaders: false,
showParametersTaskPane: false
},
interactivityOptions: {
allowTypingAndFormulaEntry: false,
allowParameterModification: false,
allowSorting: false,
allowFiltering: false,
allowPivotTableInteractivity: false
}
};

Ewa.EwaControl.loadEwaAsync(fileToken, "myExcelDiv", props, onEwaLoaded);
}

loadEwaonPageLoad is called by the page onLoad event.

// Run the Excel load handler on page load
if (window.attachEvent) {
    window.attachEvent("onload", loadEwaOnPageLoad);
} 
else {
    window.addEventListener("DOMContentLoaded", loadEwaOnPageLoad, false);
}
function loadEwaOnPageLoad() {
    …
}

Note

The if…else block provides two tests to see which method for attaching events is recognized by the browser. This is to account for the fact that most modern browsers support either the attachEvent method or the addEventListener method. Including both methods ensures that the ECMAScript (JavaScript, JScript) code will work on most modern browsers.

In the loadEwaonPageLoad function, the code defines an object, props, that contains two objects as properties: uiOptions and interactivityOptions. uiOptions contains properties that affect the display of the embedded workbook (such as whether to show row and column headers) while interactivityOptions contains properties that affect how the user interacts with the workbook (such as enabling typing and formula entry).

Embed the workbook

The final step that the embed code takes in the previous example is to embed the workbook within the <div> that was created earlier. This is performed using a call to the asynchronous Ewa.EwaControl.loadEwaAsync method.

Ewa.EwaControl.loadEwaAsync(fileToken, "myExcelDiv", props, onEwaLoaded);

The method signature for the loadEwaAsync method is as shown in the following code example.

loadEwaAsync(fileID, divID, embedOptions, callback, userContext)

The loadEwaAsync method defines the following parameters:

fileID – A string value that contains the file token for the workbook.
divID – A string value that contains the identifier for the <div> element where the workbook is displayed.
embedOptions – An object that specifies the options for display and interactivity of the embedded workbook.
callback – A callback that is invoked when the loadEwaAsync method has finished running.
userContext - An object provided as a way for callers to pass state through the asynchronous call.