Using the Excel Services Ecmascript Object Model Lab
Hands-On Lab
Using the Excel Services ECMAScript Object Model
Lab version: 1.0.0
Last updated: 3/31/2011
Contents
Overview 3
Exercise 1: Experimenting with the ECMAScript object model 4
Task 1 – Setting up SharePoint Web Part Page 4
Task 2 – Modifying FabriKamReporting.js 7
Exercise 1 Verification 9
Exercise 2: Deploying and debugging an Excel Services ECMAScript 10
Task 1 – Using Internet Explorer 8 for Debugging 10
Task 2 – Using a “Logging” Mechanism for Debugging 11
Exercise 3: Adding Navigation capabilities to a workbook using ECMAScript 13
Task 1 – Examining Navigation with Macros within Microsoft Excel and Excel Web Access 13
Task 2 – Adding Funtionality to the Buttons Using the Excel Services JSOM 14
Exercise 3 Verification 17
Summary 18
Overview
In this Hands-On Lab you will learn how to use the Excel Services ECMAScript Object Model to automate and interact with an Excel Web Access web part.
Objectives
The objective of this Hands-On Lab is to provide you with a foundation for using the ECMAScript Object Model to automate and interact with an Excel Web Access (EWA) web part programmatically. In particular, you will
· Experiment with the Excel Services ECMAScript object model
· Develop debugging skills to diagnose and resolve development issues
· Learn how to deploy your scripts and setup web part pages that consume the scripts
System Requirements
The steps in this Hands-On Lab require the following:
· SharePoint 2010
· Excel 2010
· Visual Studio 2010
· This exercise assumes the use of the 2010 Information Worker Demonstration and Evaluation Virtual Machine (RTM), http://www.microsoft.com/downloads/en/details.aspx?FamilyID=751fa0d1-356c-4002-9c60-d539896c66ce&displaylang=en. If you are working with your own SharePoint server then make the necessary adjustments.
Setup
This Hands-On Lab assumes that the Hands-On Lab files are located in a folder at %Office2010DeveloperTrainingKitPath%\Labs\ExcelJSOM
Exercises
This Hands-On Lab comprises the following exercises:
1. Experimenting with the ECMASript object model
2. Deploying and debugging an Excel Services ECMAScript
3. Adding Navigation capabilities to a workbook using the Excel Services ECMAScript object model
Estimated time to complete this lab: 60 minutes.
Starting Materials
This Hands-On Lab includes the following starting materials.
· Fabrikam_Reporting.xlsm – This is used as an example in exercise 3.
· FabriKamReporting.txt – This is used as an example in exercise 3.
Exercise 1: Experimenting with the ECMAScript object model
In this exercise you will be taking a look at how you can expand the functionality of your Excel documents stored on a SharePoint site through the use of an Excel Web Access web part and the Excel Services ECMAScript object model (aka Excel Services JSOM).
Task 1 – Setting up SharePoint Web Part Page
In this task you will set up a SharePoint web part page to incorporate the Excel document and your ECMA script.
1. Open a new workbook in Microsoft Excel
2. Save a blank Excel document to %Office2010DeveloperTrainingKitPath%\Labs\ExcelJSOM\Starter and name the document EwaTest.xlsx.
3. Open the SharePoint site at http://intranet.contoso.com/
4. Go to LibrariesàShared Documents
5. Click Add document, navigate to %Office2010DeveloperTrainingKitPath%\Labs\ExcelJSOM\Starter and upload the FabriKamReporting.js and EwaTest.xlsx documents to the Shared Documents folder on the SharePoint site.
6. Click the Site Actions dropdown and select More Options.
a. Filter by Page, select Web Part Page and click Create.
b. Title the page EWATest and for the Layout Template, select Header, Left Column, Body.
c. Click the Document Library drop down and select Site Pages.
d. Click Create.
Figure 1
Creating a new Web Part Page
Figure 2
New Web Part Page
7. Select Add a Web Part in the ‘Body’ section of the page.
a. View the Categories section and select Business Data. View the Web Parts section, select Excel Web Access, and then click Add.
i. Select the newly added Excel Web Access web part and click the Options tab at the top of the page.
ii. Select Web Part Properties and an options box should appear on the side of the screen.
iii. View the Workbook Display section, under Workbook navigate to the shared documents section and select EwaTest.xlsm document that you loaded in step 1.
iv. View the Toolbar and Title Bar section, under Type of Toolbar, select None.
v. Expand the Navigation and Interactivity section; check the box next to Typing and Formula Entry.
vi. Expand the Appearance section, set the Height to 600 and change the Chrome Type to None and then click OK to save the changes.
vii. Uncheck web part selection box (upper right hand corner) in Body section
8. Select Add a Web Part in the Left Column.
a. View the Categories section and select Media and Content. View Web Parts and select Content Editor and then click Add.
i. In the Left Column, select the drop down next to Content Editor, and select Edit Web Part.
ii. Open new tab in web browser and navigate to the SharePoint Home page and then to the Shared Documents section.
iii. Find the document titled FabriKamReporting.js, loaded in step 5, right click and select Copy Shortcut.
iv. From the Content Editor section, paste the link to the FabriKamReporting.js.
v. From the Appearance section, set the width to 300 Pixels, click Yes and click OK to save the changes.
9. From the Page tab at the top, click Stop Editing to complete the web page part setup.
Figure 3
EwaTest spreadsheet
Note: At this point, you have performed the bare minimum required to associate an Excel Service JSOM script with an Excel Web Access web part.
Task 2 – Modifying FabriKamReporting.js
This task involves modifying the FabriKamReporting.js file to experiment with the events that you can tap into so that your script can interact with an Excel Web Access web part.
1. Open up FabriKamReporting.js in Visual Studio.
a. Add the following code between the two script tags. In this code, we are doing a couple key things. First off, we are adding an event handler that will call Page_Load() once the web page has been loaded. Secondly, we are adding a function to handle the output logging to the Content Editor web part, writelog().
var logItems = true;
if (window.attachEvent) {
window.attachEvent("onload", Page_Load);
}
else {
window.addEventListener("DOMContentLoaded", Page_Load, false);
}
function Page_Load() {
}
function writelog(output) {
if(logItems) {
output = output + "<br />";
document.getElementById('resultdiv').innerHTML = output + document.getElementById('resultdiv').innerHTML;
}
}
b. Next, add the following line of code to Page_Load(). This will add the callback function that Excel Services calls once the Excel document has loaded.
Ewa.EwaControl.add_applicationReady(GetEwa);
c. Below the Page_Load() function, add the following function. This is the callback function (set in Page_Load()) that will get a reference an Excel Web Access control instance and add an event handler for the document.
function GetEwa() {
om = Ewa.EwaControl.getInstances().getItem(0);
om.add_activeSelectionChanged(onSelectionChange);
}
d. Add the following event handler after function GetEwa(). The purpose of this event handler is to capture selection changes within the document.
function onSelectionChange(rangeArgs) {
writelog('onSelectionChange Address:' + rangeArgs.getRange().getAddressA1());
}
2. Save your changes and upload FabriKamReporting.js to the SharePoint site. See Task 1 – Step 5 if you forgot where you uploaded the documents before. Make sure to overwrite the original.
3. Navigate back to Site Pages and load EwaTest.aspx (or refresh the page if you already have it displayed in your browser).
Exercise 1 Verification
To verify that your script and the Excel Web Access part are working together, perform the following steps
1. Click on cell B2 in Sheet1
2. Look in the Content Editor section and you should see the following output:
onSelectionChange Address:’Sheet1’!B2
3. Click on additional cells and you should see the equivalent results based on the sheet and cell selected.
Figure 4
Verifying results
Exercise 2: Deploying and debugging an Excel Services ECMAScript
In this exercise we will be going over a couple different options to effectively debug your ECMAScript at runtime. This will help you diagnose issues at the source.
Task 1 – Using Internet Explorer 8 for Debugging
In this task we will be taking a look at the IE 8 developer tools that can come in handy when debugging ECMAScript on the fly.
1. Press F12 to open up the Developer Tools window in IE.
2. Click on the Script tab and use search script search bar to locate the ECMAScript we loaded in Exercise 1 (FabriKamReporting.js). Input ‘writelog’ into search bar and execute.
3. Find the following line of code within onSelectionChange() and set a breakpoint by clicking on the line number.
writelog('onSelectionChange Address:' + rangeArgs.getRange().getAddressA1());
4. Click on the Breakpoints tab below the Search Script search bar and you should see the breakpoint that you just set.
5. Once you have your breakpoint set, click the Start Debugging button at the top. Click OK if prompted to Refresh Webpage.
6. Go back to the IE window and click on cell B2 in Sheet1 to trigger the event and hit the breakpoint.
7. Go back to the Developer Tools window and click the Locals tab below the Search Script search bar.
Figure 5
Locals tab
a. Expand rangeArgs
b. Expand $1x_0
c. Verify the value next to $RF_0 is “’Sheet1’!B2” (the cell you clicked on in step 5).
8. Press the Continue button (little green button to the left of the Stop Debugging button) or F5 to resume execution.
9. Go back to the Developer Tools Window and stop debugging by clicking Stop Debugging at the top or close the Developer Tools window.
Task 2 – Using a “Logging” Mechanism for Debugging
In this task you will take a look at another method you can use to debug our ECMAScript. This method involves creating functionality within the script itself to relay information about program flow. If you noticed before in FabriKamReporting.js we had a function called writelog() that output information to a Content Editor web part to as events occurred. We can use this function in the same manner across the script to verify the functionality.
1. Go back to Visual Studio and add the following lines of code to FabriKamReporting.js:
a. At the beginning of function Page_Load() :
writelog("Page_Load called");
writelog("Ewa object: " + Ewa);
b. At the beginning of function GetEwa():
writelog("GetEwa called");
2. Save changes to FabriKamReporting.js
3. Upload FabriKamReporting.js (making sure to overwrite the original).
4. Load the web part page EwaTest.aspx we created in Exercise 1
5. Look at the Content Editor section and verify the following result:
a. GetEwa called - This shows you that the funtion GetEwa() was called.
b. Ewa object: [object Object] - This is verifying that the Ewa object is valid. If the object was invalid, the result would be undefined, null, or nothing would be returned.
c. Page_Load called - This shows you that the function Page_Load() was called.
Figure 6
Content Editor
Exercise 3: Adding Navigation capabilities to a workbook using ECMAScript
In this exercise you will be taking a look at how you can recreate navigation implemented with VBA within a Microsoft Excel workbook using the Excel Services JSOM with an Excel Web Access web part.
Task 1 – Examining Navigation with Macros within Microsoft Excel and Excel Web Access
In this task you will be taking a look how the navigation is handled within Microsoft Excel and what functionality cannot be ported directly over to Excel Web Access without the use of ECMAScript.
1. In Excel open up Fabrikam_Reporting.xlsm in rExcelJSOM\Started.
a. Click on Balance Sheets
b. Click on Return to get back to the Menu sheet.
c. Click View Data
d. Click on Return to get back to the Menu sheet.
Note: Notice how you managed to navigate throughout the document with these embedded buttons. In the background there are macros that handle this functionality. Since VBA does not run in Excel Services, when you open the document on the server, the navigation functionality will not work.
Navigation is a common task in complex workbooks that consist of numerous worksheets. By providing basic navigation, you can make complex workbooks easier to use and understand to casual users of the workbook.
2. Click on the File tab
3. Click on Save & Send
4. Click Save to SharePoint and double-click Browse for a location
5. Click in the File name box and enter in
http://intranet.contoso.com/Shared%20Documents/Fabrikam_Reporting.xlsm
6. Click Save (Note: if there are any issues saving the document from the client to SharePoint, then close the client and upload the document from the Shared Docuements library in SharePoint.)
7. If a new SharePoint window opens with the new Fabrikam document, close the web page.
8. Create a new web part page identical to that in exercise 1, task 1. Make sure to set the Excel Web Access web part to Fabrikam_Reporting.xlsm instead of EwaTest.xlsx (exercise 1 – task 1) and name the page FabrikamReporting.
9. Click on the nagivation links that you were using in Excel (steps 1.a-d)
Note: As you’ve noticed, the navigation no longer works once the document has been loaded on the SharePoint site. The macros that were once handling navigation, are no longer functional. You can easily reimplement this functionality using the Excel Services JSOM.
Task 2 – Adding Funtionality to the Buttons Using the Excel Services JSOM
In this task you will be restoring navigation to the Fabrikam_Reporting.xlsm workbook displayed on the server using an Excel Web Access web part.
1. Open up FabriKamReporting.js in Visual Studio.
a. Add the following line of code after var logItems = true;
var currentSheetName = "";
b. Begin by adding the next piece of code to the end of function GetEwa(). You are now adding another event handler and retrieving the value at the named range LOG_JSOM in the Inputs sheet.
om.add_workbookChanged(onWorkbookChange);
om.getActiveWorkbook().getRangeA1Async('\'Inputs\'!LOG_JSOM', getInputsComplete);
c. Add the following callback function getRangeComplete() after the end of function GetEwa(). This will handle changing the cell focus for navigation changes.