Share via

  • Article

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
    1. 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.
    2. In Internet Explorer, navigate to https://intranet.contoso.com/Shared%20Documents/

    3. Click on the Contoso TCO to open the TCO workbook in the browser.

      Figure 1

      TCO Calculator

    4. 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.
    5. 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.
      1. The Start worksheet contains inputs specific to a customer.
      2. The Machine Inputs worksheet contains the inputs associated with the machines that you want the model to compare.
      3. The TCO Result worksheet provides concise information regarding the results of the model calculations.
      4. 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
    1. Open Visual Studio 2010.
    2. Open the file named TCOModel START.js
    3. Create a working copy named TCO.js
      1. FileSave As…
      2. Save the file as C:\Student\ExcelServices \TCO.js
    4. 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.
    5. 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
    1. In Internet Explorer, navigate to https://intranet.contoso.com/SiteAssets/
    2. Click Adddocument
    3. Upload C:\Student\ExcelServices \TCO.js.
    4. Select Site ActionsMore Options…
    5. Choose Web Part Page and click Create
      1. Name: TCO Calculator
      2. Layout: Header, Left Column, Body
      3. Save Location: Site Pages
    6. In the Body container, click Add a Web Part
      1. In the Business Data category, select Excel Web Access and click Add
      2. Within the Excel Web Access part, click the link labeled Click here to open the tool pane.
      3. Change the following properties:
      4. Workbook: https://intranet.contoso.com/Shared Documents/Contoso TCO.xlsx
      5. Type of Toolbar: None
      6. Height: 650
      7. Chrome Type: None
      8. Click OK to save changes and close tool pane
    7. In the Left Column container, click Add a Web Part
      1. In the Media and Content category, select Content Editor and click Add
      2. In the Content Editor part, click on the drop-down button next to the label Content Editor and choose Edit Web Part
      3. Change the following properties:
      4. Content Link: https://intranet.contoso.com/SiteAssets/TCO.js
      5. Title: Contoso TCO Calculator
      6. Width: 300 Pixels
      7. Chrome Type: Title Only
      8. Click OK to save changes and close tool pane

    8. 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
    FakePre-ba5f5e5e491f4a8ab86a0bf2607499cc-57bbaa1aa8ad444a8d6b7bd0f18317fbFakePre-af1bbff3f2cc431a84fc26ba52f2d685-5f6c391f16a74df6b41ba3478c48a015FakePre-f2ccf057271846e8a6a468dc2bc206dd-fae2a0a58d24410196e4a8798e93e4e4FakePre-cdcea3963be04e1bbeceadf96eb7f09e-8db8cb9407174dff97a0c399c95b7435FakePre-59fc9440d00d425ab6f049169c59027d-146754bc2d5d4d0fbed3278f22fbe0b7FakePre-53e088e1d5a848c5af926d0b7d96f7ba-6849c002b632419991a3bb54cffa0b1fFakePre-c788ba29c13c428095dad9681cc5f158-a1f9922c1d6d4001a423ca6cb70a0b68FakePre-825ef75bbaf64c329a598af6ce35734a-fb07867a400241dbbbae355685fc2aa3FakePre-55c753ec88ec45b2a0333ba165f6a6cd-9f85ed37b3684fcba2ba9a332c3a834cFakePre-a00a782dffe84a08813bf677a61f2630-7eb13a9a22244812af03a641cbcb5da8FakePre-fd06fa4c3d974358b7aad0e931e4e9db-9127202421ec492d83b058815dad48a5FakePre-f7e83ff0cfea4ed8a9b2970634bd4b4b-4cb33027a95944b2901472876ceec57dFakePre-5c674a0ade504441a1985f30f33194ae-eb80f3f6046e43c98a16d062494994d1FakePre-bb7a69baf1354cc09374d435a1008a55-200760c8341447c79cd43eab0a0f71a8FakePre-45e3ad1c24a94e41b136f1e49d49b3fc-0590874b3bb944a7a0c39995005b47b8FakePre-221af240d16848c38a3063e3c019154d-7cf9e95cf3d04754b406a1f5d4242ac4FakePre-974afa0c96ec43368828ddccb57e8ee7-81456275bbc54e83bb2b6182d0b0b4dcFakePre-75b2f4ae70844b548d1455d0136a8a3c-e9508d2f5c774cd9a63036e6a16f61edFakePre-91c48126287a47c68a000b37deba1e61-120f805871c6475cb52ec268deaa7153FakePre-1e6423e4e05e4f849057c716d316390f-8fe3eea8d44048b0baea7a05439dff87FakePre-e89e5fa9fe774f2ebe5aba9c550e1673-4e7a8d4e27684d2b9e1bfdad72d33b9dFakePre-2f56c1cf39ca408db6a9040dec453e16-95a3052da7c344f9ab76dbf5b23e152fFakePre-e784dc3392f845d9b556163ea6ae2617-503e16feabe44309a6018801ad016571FakePre-927a2008e6094d269b5efd5450800e45-e7d55086054e455493df55046bbeeefaFakePre-b75e81e6dff648c8a332995e40987c90-c8d926573ea244d8b0656fae29788e07FakePre-c3d8e22b88eb4da78ad3e5bb69ce72e9-90f9f7c403a54c6d90633670000510ffFakePre-dc7f0f2c14fa4e2d9fdd826d8d88264e-6a8aa0726be7439a8e9e9b4461f3b0fdFakePre-570afdb5808e4afdbebb1e427eabac5d-295e42833e144772a5913175bc33498dFakePre-2aede7ff1bc84fa085efc7264b22b267-84d86259104d42e5b73fdf954dcbf7a2FakePre-e4ff8319485147519a3438c33920dca4-b4a071ef1e9a4726b06861946200d5b1FakePre-b694fd9366ef42a9bb2bb3886c4f745d-b43ab1c203b84c62818aae6d21b9df22FakePre-3287a1aedf284ad799cab4a591e5157c-fbb4a56abe974d099b11d86ae38c8d32FakePre-6cd3cec4982f4a57921cc3bdcc6f2771-e5d50c221ac44558a0bf8b3f002f1288FakePre-dbbba6895dae44c995026d801f114752-0182d0198c054bdca198d2fc49c716a9FakePre-d3dba1e4584340fb9b1cdef37b2709d7-23c9cd0dcee646df8d7178267c9c8d75FakePre-ff811878fce34a019b6a355d3ddef279-eff3d634303f48dd95b6b8b61b24864bFakePre-e93cc2d671104584851486152bfae72c-d16c1fed66c34e4d821f801ce177feddFakePre-a2c77840c7f94a538ec33c02e1869263-81dcd09dd8ce4a3cae25a4e5a68a7fc5FakePre-65a660bbd27140fd89affd6307eb94cd-cbdb820e13c045d6b2060c0ad4965b47FakePre-14168115f56d44beb1cc4ad19f69bcba-9fa1915575024fbb9563b3a8cd41da56FakePre-0507fa2e54e043048a393b8ea8890218-ca1576a449544066bb71830fbe8163a6FakePre-b6d92ab60d6149a38846a80d827095e7-0fdbad14051e4cdf937a1fab8ad34f7eFakePre-4c1bc8c183c64689a4176b4125c11a44-1098b2b3667f4b75a903c96da169e776FakePre-1aa01a5089aa4fc6a50b3a069b59cf38-20803b2fe80a43c78c168aa10a41ce3aFakePre-274dcc8cc9f043de9e80bd46b313fe2d-a0dcaf73294741f9b5dc586fc20773bcFakePre-4e2ba35156024213adf2b4a6d70bd81c-a1cdfd70051346a59d5e27ae3a47c7adFakePre-a31a44a8405a4bd8ad32d400d676045e-5d1ad7bdee564164b9c96e545e2aacc0FakePre-d95265f698b44b1887a4790906e8d393-a865339f20a740069930ae0ff4afd947FakePre-7f780211426b424f86b16b7c1abe2791-9f71c39cf27f4e7391556c122f2a3fc5FakePre-55c93765e26b4d4b9c9bd76767d42d1a-fdde97e9a8cb4ad98bb8859ea331f9b7FakePre-ecf1f2cef1fc405e861c617093f23640-51b4737fddef48d0a4566f15eebae43bFakePre-325730c8a5d34b6aaee21e1f5fb0073e-71294b49b4984d43a05bc1c1a8a9ea6eFakePre-4f302014b4754c5a9a74d27d75d6b491-2645636785564bbc8b42e9401b347629FakePre-940c123a69ba4d168d5c10be37d2ff2f-19b419a4977d4fab9f83b8d470016246FakePre-83378134165648168ac508bf1b278e30-7fb3edf7e5ac42229f1693e4670f5bc2FakePre-cd4e022d88ac40c39c016e21df528960-4ea9ba0f39c4444ab55d2575e9474c65FakePre-ecc24b9320b04f799732eff42a88357c-b752abd67cab4ee6a6a2f4f7a441d24eFakePre-08bc6d917bd04ba89bee213117d420b2-60a7066cdb0e462fb6b2931fd82fd780FakePre-4fad9932d3d843308e455104ff0e3922-d12b4cbe9cb8436a9e3ca2379d9b70fcFakePre-e4c86df7448c42249c4fdf536c6743fe-945ee08dd8be4466858839ed2d4ccf6cFakePre-fa9c68d8ebfd4133b87d754e094bc752-df5367432125473f9215b03f9f9ba626FakePre-5006cbc5d8384195a5a05a4ffe60b11a-b69f8197415f4613ab70c776b1082c99FakePre-d1915b9cf4364a9397302c8268f95681-d7e3c73007fe4068bc3ae7f41176d25eFakePre-a633d0c9a26e4d3e97e52187de7723b6-921b984f49df4c199b9b85d5277603eeFakePre-e39b996de45c428a822ad684c58dd8d8-0850a7f02c2a4024a989ee8443c8a2f9FakePre-4f4f000971f54b65b283e7125ea14648-7ceed040e8e648a6827eaa0937193898FakePre-4430663a35054150ad6de9092c5b1503-1c7e3f773ed942119427243e7c336c85FakePre-c4ef1b8607a44832bdca97c40ff48d55-7f01aa45a43347f8abac07257317c330FakePre-8042956a44c74ba5a29dc0a4abcbcd06-5bcfdb6c047846429fda71d350cd3092FakePre-7dfb494623e649af81e71ef62980a63b-144214cf6b7e4329b65f092b143f81d3FakePre-7c9d34dd6fb84a2f986a45844dc31761-d861ed5fc9b84d738d6565f5d0988e0aFakePre-cf7a676738ef4f6c9be3702aebd29943-1130ce5d28194589a612e9a2fd1fbb08FakePre-1098437308a54c96b16da87e7e866007-1f7919e26c3c484fbf5396ce98e52471FakePre-125965d7f2e548eb951e1f58c19040d6-39a8398d59f8422eb81919caccc25a2cFakePre-d297ef23c56d4d168af28bb397195550-0e31551b27384d539839261dcfa04c2fFakePre-edc805ca438c469e92742e8735552a33-55596a12b92a458e87bd19149360d45cFakePre-930c32e2b2ce40d28db09b865c4aece9-eb71ddd436734398bcae7977936a7aedFakePre-1f716cf96b0242268180d6301e136199-e84d83b0b0dd49c99b2664e89d4864e3FakePre-27224492ea2b4502ae5e6737066b2802-2641ec97bea740469b05db753f8017b4FakePre-a5e9e54ffa174df1b003c8e115582951-16843d01de4e4164bba8f4beb7394b4dFakePre-e8fdf0ca913a4c4fbfe2fa8e220683b9-c847a2504ec94ea699526c0411bf7a32FakePre-591166c22fcd47cca0321770e9d3e7c1-ce2b697439b645ff9ad59caace3647caFakePre-dc30c4b3877f47dd8804c403543e2e30-3c28720749584c5697877881aec7fe05FakePre-898516fdc7e04025a919aa71cf62eaee-9abd01350bc6464c86159c1c64fec905FakePre-f84014b730d8464faf658d679fdaf729-4f036f5a06184a2ba2cc001328fe25f1FakePre-48ab77a8eee8400d80f2bf9546b11a5a-18aadf8ef71e467aa0c9c1c93b1428daFakePre-757282dc7b784d079886dcb964a89112-76880a97dd5e49f7958e09c8c6419164FakePre-8ef802811d9449f1a9ad7c0258cf1689-fbe5b50b1f07428589a503fbd4f4e88f

  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 https://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 https://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.

< previous | next >

Downloads

Download The Offline Training Kit
Download Hands-on Lab Document (DOCX)
Download Hands-on Lab Source Files

Contents