Hands-On Lab
Solution Development with Excel Services
Lab version: 1.0.0
Last updated: 3/29/2011
Contents
Overview 3
Exercise 1: Basic Excel Services ECMAScript Object Model Usage 5
Task 1 – Reviewing Starting Materials and Setup Test Page 5
Task 2 – Adding Initialization Code to the TCO JavaScript File 9
Task 3 – Adding Navigation Functionality 9
Exercise 1 Verification 12
Exercise 2: Advanced Excel Services ECMAScript Object Model Usage 13
Task 1 – Providing a Reset Feature 14
Task 2 – Adding a Machine Selection Feature 18
Exercise 2 Verification 22
Exercise 3: Interacting with the REST API and Excel Web Services 22
Task 1 – Implementing a File Save Feature 23
Task 2 – Enhancing the UI with a Chart 25
Exercise 3 Verification 27
Summary 29
Overview
Excel Services, part of SharePoint Server 2010, offers new capabilities to developers looking to create server-based Excel solutions either on-premise or in the cloud with Office 365. This Hands-On Lab will explore some of the common tasks that developers perform while working with Excel Services.
Objectives
The objective of this Hands-On Lab is to provide you with a foundation for developing solutions that utilize or interact with Excel Services. In particular, you will
· Learn how to use the Excel Services ECMAScript Object Model
· Understand how to access resources within an Excel workbook using the Excel Services REST API
· Learn the technique for calling into the Excel Web services using ECMA Script
System Requirements
Note: To work this lab, there is an assumption that you have a SharePoint environment running Excel Services in place. This lab was designed to run on the “2010 Information Worker Demonstration and Evaluation Virtual Machine (RTM).” Here is the URL at the time this document was created, but it’s best to search for the above name on Bing to make sure you have the most current version. http://www.microsoft.com/downloads/en/details.aspx?FamilyID=751fa0d1-356c-4002-9c60-d539896c66ce&displaylang=en. For instructions on how to setup this environment, see this video, http://channel9.msdn.com/Shows/SharePointSideshow/Setting-Up-a-SharePoint-Developer-Virtual-Machine. Or to setup SharePoint on a test Windows 7 machine, see this video, http://channel9.msdn.com/Shows/SharePointSideshow/Building-a-SharePoint-Development-Machine-Using-the-Easy-Setup-Script.
The steps in this Hands-On Lab require the following:
· SharePoint 2010
· Excel 2010
· Visual Studio 2010
Setup
This Hands-On Lab assumes that the Hands-On Lab files are located in a folder at C:\Student\ExcelServices. To get these files there, perform the following steps
1. Open Windows Explorer
2. Right-click on the Local Disk (C:) item and choose NewàFolder
3. Name the new folder Student
4. Right-click on the Student folder and choose NewàFolder
5. Name the new folder ExcelServices
6. Open up the zip file named ExcelServices.zip (found in the %Office2010DeveloperTrainingKitPath%\Labs\SolutionDevelopmentExcelServices\Source\Starter folder for this lab)
7. Drag the items in the zip folder into the ExcelServices folder
8. Open a browser and navigate to the SharePoint site http://intranet.contoso.com/Shared%20Documents/
9. Click Add document.
10. Browse to C:\Student\ExcelServices\ and select the file named Contoso TCO.xlsx
11. Click Open
12. Click OK to begin uploading the document.
Exercises
This Hands-On Lab comprises the following exercises:
1. Basic Excel Services ECMAScript Object Model Usage
2. Advanced Excel Services ECMAScript Object Model Usage
3. Interacting with the REST API and Excel Web Services
Estimated time to complete this lab: 60 minutes.
Starting Materials
This Hands-On Lab includes the following starting materials.
· ContosoTCO.xlsx – This is an Excel workbook which serves as the focus of the exercise.
· ContosoTCO.js – A JavaScript file containing some base functionality that you will expand upon.
Exercise 1: Basic Excel Services ECMAScript Object Model Usage
The programmability story around Excel Services increased dramatically in SharePoint Server 2010. In addition to the expanded capabilities of the Excel Web Services, there is a REST API for accessing resources in Excel workbooks using a URL and an ECMAScript or JavaScript object model (JSOM). You can use all three options within a single solution to create sophisticated web-based applications. In Exercise 1, you will learn the basics of using the Excel Services JavaScript Object Model (JSOM).
Note: The Excel Services ECMAScript object model is available in Office 365. All of the techniques demonstrated in exercise 1 are cloud friendly.
Task 1 – Reviewing Starting Materials and Setup Test Page
In order to provide some structure and help clarify future tasks, this Hands-On Lab starts with a JavaScript file that contains minimal functionality along with place holders for code you need to complete. In order to test your work, you need to setup a web part page in SharePoint that includes two web parts: an Excel Web Access web part that points to a TCO model, and a Content Editor Web part that refers to the TCO JavaScript file.
1. Review the TCO Model
a. TCO stands for Total Cost of Ownership. Companies often use TCO models in industries that sell expensive machinery where the acquisition cost is just a fraction of the overall cost of operating a piece of machinery. This TCO model is an example of one you might find in the Farm Machinery industry.
b. In Internet Explorer, navigate to http://intranet.contoso.com/Shared%20Documents/
c. Click on the Contoso TCO to open the TCO workbook in the browser.
Figure 1
TCO Calculator
d. Note the notification about Unsupported Features at the top of the window. Many non-trivial workbooks will contain items that Excel Services does not fully support. In particular, the TCO workbook contains a few Shape objects that Excel Services does not render.
e. Click on the Start, Machine Inputs, TCO Result, and Data worksheets and briefly review the contents. The worksheets not called out are not relevant to this lab.
i. The Start worksheet contains inputs specific to a customer.
ii. The Machine Inputs worksheet contains the inputs associated with the machines that you want the model to compare.
iii. The TCO Result worksheet provides concise information regarding the results of the model calculations.
iv. The Data worksheet stores information related to machines. The information on this worksheet serves as the default values supplied to the Machine Inputs worksheet.
2. Review the TCO JavaScript file
a. Open Visual Studio 2010.
b. Open the file named TCOModel START.js
c. Create a working copy named TCO.js
i. FileàSave As…
ii. Save the file as C:\Student\ExcelServices \TCO.js
d. Near the top of the file, observe the numerous <div /> elements. You will use these items to provide a basic UI within a Content Editor Web part in SharePoint.
e. Within the script section, notice the comments beginning with the text “TO DO”. You will flush these sections out throughout the lab.
3. Create the TCO Reporting web part page
a. In Internet Explorer, navigate to http://intranet.contoso.com/SiteAssets/
b. Click Add document
c. Upload C:\Student\ExcelServices \TCO.js.
d. Select Site ActionsàMore Options…
e. Choose Web Part Page and click Create
i. Name: TCO Calculator
ii. Layout: Header, Left Column, Body
iii. Save Location: Site Pages
f. In the Body container, click Add a Web Part
i. In the Business Data category, select Excel Web Access and click Add
ii. Within the Excel Web Access part, click the link labeled Click here to open the tool pane.
iii. Change the following properties:
a. Workbook: http://intranet.contoso.com/Shared Documents/Contoso TCO.xlsx
b. Type of Toolbar: None
c. Height: 650
d. Chrome Type: None
e. Click OK to save changes and close tool pane
g. In the Left Column container, click Add a Web Part
i. In the Media and Content category, select Content Editor and click Add
ii. In the Content Editor part, click on the drop-down button next to the label Content Editor and choose Edit Web Part
iii. Change the following properties:
a. Content Link: http://intranet.contoso.com/SiteAssets/TCO.js
b. Title: Contoso TCO Calculator
c. Width: 300 Pixels
d. Chrome Type: Title Only
e. Click OK to save changes and close tool pane
h. Click Stop Editing in the ribbon to save your changes and exit page edit mode. At this point, you have a framework for testing your changes as you proceed through the lab. As you make changes to the TCO.js file, you can test them by saving the TOC.js file to the SiteAssets library and then refreshing the TCO Calculator web part page.
Figure 2
TCO Calculator
4. Save a blank Excel document to C:\Student\ExcelJSOM\Started and name the document EwaTest.xlsx.
Task 2 – Adding Initialization Code to the TCO JavaScript File
The first step in any script that uses the Excel Services JSOM is to add code that obtains a reference to the EwaControl, the primary object of the JSOM, and then use the EwaControl to obtain a reference to the Excel Web Access web part that you want to add functionality too.
1. If it’s not already open, open the TCO.js file in Visual Studio 2010
2. Within the <script /> node, locate the initial group of comments beginning with the comment // TO DO: Add standard Excel JSOM initialization.
3. Add the following code underneath the comment // GetEwa(). This code is responsible for associating the script with the Excel Services JSOM and then obtaining a reference to the Excel Web Access web part on the same page that is displaying the Contoso TCO workbook.
JavaScript
// SCRIPT LEVEL VARIABLES
var xlWebPart;
// Set the page event handlers for onload and unload.
if (window.attachEvent) {
window.attachEvent("onload", Page_Load);
}
else {
// For some browsers window.attachEvent does not exist.
window.addEventListener("DOMContentLoaded", Page_Load, false);
}
// Load the page.
function Page_Load() {
Ewa.EwaControl.add_applicationReady(GetEwa);
}
// Get handle to EWA instance, set event handlers and initialize content
function GetEwa() {
xlWebPart = Ewa.EwaControl.getInstances().getItem(0);
// TO DO: xlWebPart.add_activeSelectionChanged(onSelectionChange);
// TO IMPLEMENT: getChartImage("get");
// TO IMPLEMENT: fillMachineDropdowns("get");
// TO IMPLEMENT: createHyperLinks();
}
Task 3 – Adding Navigation Functionality
One of the easiest features to add using the Excel Services JSOM is workbook navigation. Workbook navigation provides users with a simple way to navigate complex workbooks. In the Excel client, you can use hyperlinks, embedded controls, or shapes associated with macros to provide workbook navigation. In the browser, there are two options: hyperlinks and cells formatted to look like buttons that are associated with a selection event handler.
1. Locate the group of comments beginning with the comment // TO DO: Add navigation functionality
2. Add the following code underneath the comment // createHyperLinks(). This code uses the Excel Services JSOM to loop through the visible worksheets in the Excel Web Access web part, creating a hyperlink for each sheet, and adding it to the ‘hyperlinks’ <div /> element.
JavaScript
// Gets the sheets associated with the workbook (visible sheets only) and creates a
// hyperlink for navigation purposes.
function createHyperLinks()
{
var sheets = xlWebPart.getActiveWorkbook().getSheets();
document.getElementById('hyperlinks').innerHTML =
'<h3 style="color:#018FC3;margin-bottom:2px;' +
'padding-bottom:2px;">Model Navigation</h3>';
for(var i = 0; i < sheets.getCount(); i++)
{
sheet = sheets.getItem(i);
document.getElementById('hyperlinks').innerHTML +=
'<a style="color:#018FC3;" href=\'javascript:hyperlinksheet_onclick("'
+ sheet.getName() + '")\'>' + sheet.getName() + '</a<br />';
}
document.getElementById('hyperlinks').innerHTML +=
'<h3 style="color:#018FC3;margin-bottom:2px;padding-bottom:2px;">Other</h3>';
document.getElementById('hyperlinks').innerHTML +=
'<div id="machinehl"<a style="color:#018FC3;" ' +
'href=\'javascript:hyperlink_onclick("machinedd")\'>' +
'Show Machine Selection</a</div>';
document.getElementById('hyperlinks').innerHTML +=
'<a style="color:#018FC3;" ' +
'href=\'javascript:hyperlink_onclick("savediv")\'>' +
'Save Workbook Copy</a>';
document.getElementById('hyperlinks').innerHTML +=
'<br /<a style="color:#018FC3;" ' +
'href=\'javascript:hyperlink_onclick("refreshChart")\'>Refresh Chart</a>';
//Set default file name copy value of file name input box
var workbookPath = xlWebPart.getActiveWorkbook().getWorkbookPath();
var workbookName = workbookPath.substring(workbookPath.lastIndexOf("/") + 1,
workbookPath.lastIndexOf("."));
document.getElementById('fileName').value = workbookName + '-Copy';
}
Add the following code underneath the comment // hyperlinksheet_onclick(). The hyperlinksheet_onclick function is the event handler called when you click on one of the sheet hyperlinks within the ‘hyperlinks’ <div /> element. The hyperlink_onclick function is the event handler for click on the hyperlinks that do not refer to worksheets.
//Event handler for hyperlinks related to sheet navigation
function hyperlinksheet_onclick(sheetName)
{
var cell = 'A1';
xlWebPart.getActiveWorkbook().getRangeA1Async("\'" + sheetName + "\'!" + cell,
getRangeComplete);
}
// Event handler for hyperlink unrelated to workbook sheet navigation
// Handles displaying/hiding the machine selection drop downs, refreshing the chart,
// and displaying the save content
function hyperlink_onclick(name)
{
if(name == 'savediv')
{
document.getElementById('savediv').style.display = 'block';
document.getElementById('saveresult').innerHTML = '';
}
else if(name == 'machinedd')
{
if(document.getElementById('machinedd').style.display == 'block')
{
document.getElementById('machinehl').innerHTML =
'<a style="color:#018FC3;" ' +
'href=\'javascript:hyperlink_onclick("machinedd")\'' +
'>Show Machine Selection</a>';
document.getElementById('machinedd').style.display = 'none';
}
else
{
document.getElementById('machinehl').innerHTML =
'<a style="color:#018FC3;" ' +
'href=\'javascript:hyperlink_onclick("machinedd")\'' +
'>Hide Machine Selection</a>';
document.getElementById('machinedd').style.display = 'block';
}
}
else if(name == 'refreshChart')
{
getChartImage("get");
}
}
3. Add the following code underneath the comment // getRangeComplete(). Calls to most functions within the JSOM are asynchronous. This function is the callback that the JSOM calls after it returns with the range specified in the getRangeA1Async method call (called in hyperlinksheet_onclick in Step 3). The purpose of this function is to activate the desired range thereby forcing the Excel Web Access web part to navigate to the desired worksheet.
JavaScript
// Callback function that sets the focus of the range
function getRangeComplete(asyncResult)
{
var range = asyncResult.getReturnValue();
range.activateAsync();
}
4. Locate the GetEwa() function you added in Task 2, Step 3 and modify the line // TO IMPLEMENT: createHyperLinks() as shown below.
JavaScript
createHyperLinks();
Exercise 1 Verification
To check your work thus far, perform the following steps.
1. In Visual Studio 2010, press CTRL + S to save your work.
2. Open Internet Explorer and navigate to http://intranet.contoso.com/SiteAssets/
3. Click Add document
4. Click Browse and select the TCO.js file in C:\Student\Excel Services
5. Click Open
6. Click OK to upload the document (make sure Overwrite existing files is checked)
7. Navigate to http://intranet.contoso.com/SitePages/TCO Model.aspx
Figure 3
TCO Calculator
8. Click on the various links under Model Navigation and verify that the Excel Web Access web part displays the correct worksheet. Note that the links under the section labeled “Other” will not work until you add the necessary functionality later in this lab.
Exercise 2: Advanced Excel Services ECMAScript Object Model Usage
In the last exercise you learned how to provide basic workbook navigation using the JSOM. A slightly more advanced use of the JSOM is to manipulate the data within a workbook. Additionally, you can use the JSOM to extract data out of a workbook for other uses such as populating controls in other web parts on a web page. In Exercise two, you’ll utilize both of these techniques in order to provide “Reset” functionality and to provide a way to change which machines are selected in the model.