Introduction to the Excel Services JavaScript OM - Part 1 – Humble beginnings

Edit: I fixed a problem with the skeleton code that made the thing not work in browsers other than IE. 

In the past few weeks, I wrote a bunch of posts about the new REST APIs in Excel Services. I wanted to take a bit of a breather from those APIs and start writing about the new JavaScript browser-side OM we are shipping with SharePoint 2010. This OM will allow developers to customize, automate and otherwise drive the EWA control and build more compelling, integrated solutions.

In the next few posts, I will show how developers can use the new OM to do all kinds of things to the EWA.

Step 1 – Creating the page

To begin though, we will need an EWA and a content-part. The example will revolve around a fictitious dashboard for my teams bugs – each post will add more features to the dashboard. For this, we will create a new Web-part page in our site – from the “Site actions” drop down, choose “More Options” (Note – all these steps are very similar to what you would do in SharePoint 2007 – I am just giving them for completeness):

Once chosen, you will be presented with a host of options of what to create – select web-part page:

After that, you can choose whatever layout you want – in this case we will select the “Header, Left Column, Body” and call it “TeamBugsDashboard”. Once it’s created, we can easily add an EWA to the page by clicking on the right column “Add a Web Part” area:

Selecting the Excel Web Access web part and then hitting add will place it on the web page. We will do the same with a web-part called “Content Editor” under the “Media and Content” category – this time adding it to the left column. The result should look similar to the following (the left circled area is the content element and the right one is the Excel Web Access web-part):

Next, we will select the workbook we want displayed in the EWA – to do that, we will use the dropdown that’s on the header of the control and choose “Edit Web Part”. This will present with a pane on the right where we can put the name of the workbook – in there we can type the URL to the workbook in the “workbook field”.

Hitting the “OK” button will allow the EWA to show the relevant workbook. This is where things become a little interesting – the content web-part will contain a reference to an HTML fragment that’s contained in another document library (we will show the actual content of the HTML fragment shortly):

This tells the content web part to get the file linked and embed it as part of the web-page.

Step 2 – adding the JavaScript OM skeleton code

Now, to start working with the JavaScript OM, there’s some standard code that needs to run – the following is the skeleton piece of code you need to add to your page when you want to interact with the EWA:

<script type="text/javascript">

    ///<reference path="https://tsdlt2/_layouts/EwaJsom.ashx?v=1"/>

    var ewa = null;

    //set page event handlers for onload and unload

    if (window.attachEvent)

    {

        window.attachEvent("onload", ewaOmPageLoad);

    }

    else

    {

        window.addEventListener("DOMContentLoaded", ewaOmPageLoad, false);

    }

    //load map

    function ewaOmPageLoad()

    {

        if (typeof (Ewa) != "undefined")

        {

            Ewa.EwaControl.add_applicationReady(ewaApplicationReady);

        }

     else

        {

            // Error - the EWA JS is not loaded.

        }

    }

    function ewaApplicationReady()

    {

        ewa = Ewa.EwaControl.getInstances().getItem(0);

    }

</script>

That’s pretty much it for the skeleton or bootstrapping code – you can pretty much copy&paste this code whenever you want to interact with an EWA on a page. In a future post I will show how to find a specific EWA control (in the case where you have more than one). The code is relatively simple – the first block in the script will execute and add an event handler to the page so that we know when it loads (the reason we use the attachEvent/addEventListener mechanism is so that we do not override the page load event). Then, when the ewaOmPageLoad() method is called, we make sure that the OM is loaded (that’s the call to typeof(Ewa) != undefinedand if it is defined, we go ahead and add another callback to an event – this time for the applicationReady event on the EWA – this is done by calling the add_applicationReady() method. This will fire once the EWA itself is up and running. When that happens, the code executing in ewaApplicationReady will store the EWA control reference in a variable so we can start using it.

Step 3 – Making the EWA recalc periodically

Now is when we start adding actual functionality. To begin, we will address one request dashboard writers sometimes express – the means to periodically recalculate the dashboard so that it shows up-to-date information w/o requiring user interaction. To do that, we will call into an OM method called recalcAsync(). Lets start simple – here’s what the ewaApplicationReady() function looks like after the call to recalcAsync:

    function ewaApplicationReady()

    {

        ewa = Ewa.EwaControl.getInstances().getItem(0);

        // Start recalcing the workbook every 5 minutes.

        setTimeout(forceRecalc, RECALC_INTERVAL);

    }

The setTimeout() call will ensure that we do not immediately start recalculating, but instead wait for however we decide we want between. Next, this is what happens in the forceRecalc() function:

function forceRecalc()

{

    ewa.getActiveWorkbook().recalcAsync(recalcComplete, null);

}

Again – a fairly simple call – we use the ewa variable to get the currently active workbook (getActiveWorkbook() ) and call into the recalcAsync() method. Now, since most operations that actually have to do with the workbook that resides on Excel Services need to get to the server and back, almost all of those are actually asynchronous methods. As part of the call, you pass in the callback (recalcComplete() in our case) and an opaque user state object (null – we don’t need it). Once the recalculation is done, the EWA will call us back. Note – in some cases, you do not really care when the call comes back – in these, you can simply pass in null (or omit the parameter) and treat this call as a “fire and forget” call. The reason we care about when the call is done in this case is because we want to make sure we queue up another calculation request when the first one is done. Here’s the recalcComplete() callback:

function recalcComplete(result)

{

    setTimeout(forceRecalc, RECALC_INTERVAL);

}

Notice that we actually ignore the result in this case – we just make sure we issue another call to the recalcAsync() method. Since this will continue to happen over and over again, we will see the EWA recalc every RECALC_INTERVAL milliseconds.

By the way: The reason we are using setTimeout() and not setInterval() is so that in the case where the server is swamped, we do not bombard it with calls to recalc – this way we guarantee that we only tell the EWA to recalculate after it has actually finished the last time we asked it to recalculate.

That’s it for this post – you now know how to start working with the new JavasScript OM Excel Services is exposing – in the next few posts, we will explore some of the other functionality exposed and how it is used.

Comments

• Anonymous
  July 27, 2013
  The comment has been removed
• Anonymous
  August 08, 2013
  I want to do something very similar to PRio,  I have several named items and my users want something more prominent than the drop down menu to indicate that there are more items to view.  Anyway, I was wondering if there is a command I could run to change the value of that drop down menu with a button or something on the  same web page