Introduction to the Excel Services JavaScript OM - Part 2 – listening to events

The previous post showed how to start using the Excel Services JavaScript OM to automate the EWA web-part. The example given showed how to cause periodic recalculation on a workbook that’s on a SharePoint web-page.

In this post, we will add our first event to the EWA and see how we can get an even more integrated experience. The feature we are going to code in this step is to give users the ability to click in our dashboard on a specific person alias in one of the sheets and use that value to show a picture of that person on the side of the web-part page.

Step 1 – Listening to an event

But before we can do all this image handling, first thing’s first – we need to listen to events. Hooking up event is typically done in the ewaApplicationReady() method described before – that’s where the EWA is ready and able:

function ewaApplicationReady()

{

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

    // Start recalcing the workbook every 5 minutes.

    setTimeout(forceRecalc, RECALC_INTERVAL);

    ewa.add_activeCellChanged(cellChanged);

}

 

Note the line that was added to the method – ewa.add_activeCellChanged() – in this case we are hooking up to get notification whenever the active cell changes on the workbook. Now, each time a cell is selected, the EWA will call this function. Lets take a look at what the prototype of this function looks like:

function cellchanged(rangeArgs)

{

}

The rangeArgs parameter in the function will contain information on the range clicked. Specifically, it will contain the following three methods:

getRange() – This returns the Range object that’s behind the cell that was selected.

getWorkbook() – The workbook object that is currently loaded in the EWA.

getFormattedValues() – The value that’s in the cell.

In our case, we will use the getFormattedValues() to figure out what the text is in the cell and use that to display the image.

Step 2 – Adding the image tag so that we can display an image

In the first post, the HTML fragment we are using inside the content-part contains a single <script> tag containing our code. It is obviously quite possible to also have any other HTML in there – in this case, we will add an IMG tag:

<img id="dashboardPersonPicture" />

Step 3 – Setting the image due to cell change

We will modify the cellchanged() function in the following manner:

function cellchanged(rangeArgs)

{

    var img = document.getElementById("dashboardPersonPicture");

    img.src = "/PersonalPages/" + encodeURIComponent(rangeArgs.getFormattedValues()) + ".jpg";

}

What this does is take the formatted value of the cell that was selected and uses it in a URL that is set into the img tag – in this case, the URL will be /personalpages/shaharp.jpg when my alias is picked. And here’s how this looks in the browser:

That’s great! But watch what happens when a different random cell is selected (say, the one that says “Alias”):

The problem is that we are indiscriminately setting the image. Instead, we need to make sure that the range where the user clicked makes sense.

Step 4 – Looking at the Range object

To do this, we will take the Range object that comes back as part of the rangeArgs parameter and use it to check the coordinates of the cell that was selected. If it falls under our scenario (the column in question), only then will we actually set the image:

function cellchanged(rangeArgs)

{

    var sheetName = rangeArgs.getRange().getSheet().getName();

    var col = rangeArgs.getRange().getColumn();

    var row = rangeArgs.getRange().getRow();

    var value = rangeArgs.getFormattedValues();

    if (sheetName == "Sheet1" && col == 1 && row > 1 && value && value != "")

    {

        var img = document.getElementById("dashboardPersonPicture");

        img.src = "/PersonalPages/" + encodeURIComponent(value) + ".jpg";

    }

}

The code uses the getSheet().getName() , getColumn() and getRow() calls on the range to figure out exactly where the selection is, and if it falls under the range we are interested in, only then will we go and set the URL for the image.

And that’s it for this post – we saw how to hook up events and then we saw how to use the argument in the event to figure out where the user clicked in the document and what the content of the selection is. Next up, we will see how we can write back values into the workbook.

Comments

• Anonymous
  April 09, 2012
  Your example is working greate, but i have other issue. My excel is using Data validation and sheet protection, so there is a bug when trying display in browser. It can be displayed in excel. but of course after displaying with excel, event listners are not working. Is there any way to make it work? I mean scenario:

1. User is opening sheet with excel.
2. changing some cells
3. saving sheet
4. now we have some event listner that is updating our fields and reading from sheet? or only way to do it is:
5. create some web service,
6. ask every few second if excel was modified
7. if modified then update opened item.