Excel Services: Combining the EWA and API using AJAX

Today’s author: Dan Parish, a program manager on the Excel Services team.  Dan is going to walk through how to enable cell editing in Excel Services using a little code.  The post assumes some base knowledge of Excel Services.

Today I'm going to show how, through a combination of the EWA and API, you can enable editing of any cell in Excel Web Access (EWA) via JavaScript.

Before we start, I'd like to recap a few things about the EWA:

1. The EWA is the web part component of Excel Services that renders your workbooks in your web browser using only HTML and JavaScript
2. It is targeted at the exploration and consumption of workbooks, and therefore while you can sort, filter, drill down on PivotTables, and more, you can't just type into any cell, or enter formulas. You can enter data into pre-defined ‘parameters' in your workbook, or connect them to other SharePoint web parts however.
3. Whenever you are working with Excel Services, either via the EWA, or via the Web Services, you are working in your own session identified using a ‘session id'. That session id is hidden from you in the EWA, but it is an integral part of programming using the Web Services, and will be important in this demonstration as well.

What I'm going to show is how to do six things:

1. Construct the dashboard so that you can do all of this without writing a custom web part
2. Cause the EWA to reload
3. Get the current session id that the EWA is using to display the workbook
4. Get the currently selected cell in the EWA
5. Set value in the selected cell
6. Show the result in the EWA

In order to do some of these things, I will be using some of the JavaScript functions exposed by the EWA (specifically EwaReloadWorkbook, EwaGetSessionId, and getHighlightedCellA1Reference).  These JavaScript functions are not officially supported and may not be supported or available in future releases.

Throughout this post I'm going to use the scenario of a retail store that wants to keep track of the status of the tasks that need to be done each day.  Here's what the finished page will look like:

Creating the dashboard

To create this dashboard, first create a web part page in SharePoint, using any layout you wish, and then add two web parts to the page (from top to bottom):

1. Content Editor Web Part
2. Excel Web Access Web Part

The first thing we'll do is configure the EWA web part.  To do this, first click on the "Click here to open the tool pane" link.  In the tool pane that appears, click the ... icon next to the "Workbook" textbox and browse for the workbook, which you can download as part of the zip file at the end of this blog post.

Next, we want the EWA to only display the specific portion of the workbook that contains our task list.  To do this, enter "OverallTaskList" in the "Named Item" field.  OverallTaskList is a named range that encompasses everything in the workbook you want shown.  Finally, we'll remove some of the UI elements to make the dashboard seamless:

1. Set "Type of Toolbar" to "None"
2. In the "Appearance" section:

1. Select "Yes" to if the web part should have a fixed height, and enter 525 pixels
2. Select "Yes" to if the web part should have a fixed width, and enter 375 pixels
3. For "Chrome Type" select "None"

That's it, so click ok.

Everything else we'll be doing is in the Content Editor web part.  The Content Editor web part allows you to enter either rich text or source code.  We'll be using the source code ability to enter our code right into the page.

Click on the "open the tool pane" link in the Content Editor web part to open its web part tool pane.  First we'll set up its looks, so in the Appearance section, set the "Chrome Type" to "None".  Now click the "Source Editor..." button.  This will open the dialog where we'll be entering all of the code that follows.

Getting the web part id and reloading the workbook

When the manager opens the store in the morning, she walks to their main computer and clicks "Start a new day".  This causes the EWA to reload the workbook that it's displaying, which resets all of the tasks to "Not Started".

This link works by calling the EwaReloadWorkbook JavaScript function.  This function causes whatever workbook is currently displayed in the EWA to reload and takes one parameter, which is the id of the EWA to reload.  This parameter is important because if there are multiple EWAs on the page, you need to be able to specify which you want to control.

To get the id of the web part, you can either write custom code on the server side, or you can simply look in the HTML of the page itself.  Note that the id of a web part can change if it is added or removed from a page, so it is best to setup your web part page the way you want and then look for the id to use.

To get the id of an EWA web part by looking at the HTML, simply do the following:

1. Right-click on the page outside of the EWA and select "View Source"
2. Search for "Excel Web Access"
3. The first hit (or keep going if you want a different EWA) will be a line that looks like this: ...'Excel Web Access - Test 1', false)" relatedWebPart="WebPartWPQ3"...
4. The "relatedWebPart" is the id of the EWA (so "WebPartWPQ3")

Now that you have the id of the EWA web part, we are ready to start constructing the HTML that will control our EWA web part.  First, construct the "Start a new day" link with the following code:

<a href="#" onclick="NewDay()">Start a new day</a>

This link calls the NewDay function, which will simply ask the manager if they are really sure (they will be wiping their data after all), and then will reload the workbook.  The code for this is:

function NewDay() {

    if(confirm("Are you sure?")) {

          EwaReloadWorkbook('WebPartWPQ3');

    }

}

Note that script code such as this needs to be within <script> tags.  In the zip file contained at the end of this blog post is the full HTML and script that you can embed directly in the Content Editor web part.

Creating the update form

Now we need to create a mechanism for users to select what they want to update the status to.  For this, we'll use a simple dropdown of the user's choices, and a button they can click to perform the actual update.

Creating the update form is just some simple HTML:

<br><br>

Click on the status field for the task you want to update, select the new status, and then click update.<br>

<select id="TaskStatus">

    <option selected value="Not Started">Not Started</option>

    <option selected value="In Progress">In Progress</option>

    <option selected value="Done">Done</option>

</select>

<input type="button" id="UpdateButton" onclick="UpdateTaskList()" value="Update">

We have a list of three statuses that can be chosen from, and a button that calls an UpdateTaskList() JavaScript function we'll be creating in the next section.  The list of three statuses is a form of data validation, something that isn't supported natively by Excel Services.

Getting the EWA's session id

The first thing that we need to do before we can start updating values in the EWA is to get the session id that it is using.  This is because we'll be using the Web Service under the covers, and it needs to know the session id to tell the Excel Calculation Service to update.  This can be done using the EwaGetSessionId JavaScript function.  It, like the EwaReloadWorkbook JavaScript function, also requires the web part id of the EWA you want to get the session id from.  Luckily, we already got that above.

We'll need to start our UpdateTaskList function then by getting the session id, like so:

function UpdateTaskList() {

    // Get the session that the EWA is using

    var sid = null;

    sid = EwaGetSessionId('WebPartWPQ3');

"sid" now contains the session id that the EWA is working against.  Next, we need to get the currently selected cell that we want to update.

Getting the currently selected cell in the EWA

To get the currently selected cell in the EWA, we are going to use a third EWA JavaScript function: getHighlightedCellA1Reference.  This JavaScript function, like both of the others, also requires the web part id.  It always returns the selected cell in the form: ‘SheetName'!CellAddress.  For example: ‘TaskList'!C5.  It will not return the name of a named range if one is selected, and if anything goes wrong (no cell was selected, the EWA is still loading the workbook, etc.) it returns null.

With that knowledge then, and the knowledge that the Status column is in column C of the workbook, let's get the selected cell and do some validation for the user:

    // Get the cell the user selected and make sure it is in the Status column

    var selCell = null;

    if ((selCell = getHighlightedCellA1Reference('WebPartWPQ3')) == null) {

        alert("You must first select a cell in the Status column of the workbook below that you would like to update.");

        return;

    }

    selCell = selCell.substring(selCell.indexOf("!") + 1);

    if (selCell.substring(0, 1) != "C") {

        alert("You must select a cell in the Status column that you would like to update.");

        return;

    }

All this code does is first check to make sure we got a cell back, and then trim that result to just the cell address (we removed the sheet name).  It then checks to make sure that the cell is in column C (which is the Status column), and if so, continues on.  If either of the checks failed, the user gets notified and the function returns.

At this point we now have:

1. The session id that the EWA is using
2. The currently selected cell in the EWA

All we need to do now is update the actual value in that cell with the user's selection in the dropdown.

Updating cells in the EWA via JavaScript

To do this, we are going to rely on the AJAX JavaScript library.  This JavaScript library, written by Shahar Prish, one of our developers, provides an AJAX wrapper around the Excel Web Service that allows us to call it easily from JavaScript.  In this case we are going to be using three things from this library:

1. ExcelServices: a class that wraps the Excel Web Services API
2. ExcelServicesSession: a class that wraps the session id we are using that allows us to make calls easier
3. setCellA1: a JavaScript method to set the value in a cell. It takes four parameters: the sheet name, the cell to set, the value to set it to, and, since this is an AJAX call, the function to return to when it is complete

Usually, when using the web service, the first thing we would do would be to call OpenWorkbook to open a workbook and get the session id.  In this case though, the EWA has already done that for us, and we are just going to use its session id.  So all we need to do is instantiate an ExcelServices object, and then using that and the session id we got from the EWA, instantiate an ExcelServicesSession object which we will use to set the value with the user's selected status.  The code for this is as follows:

    // Set the selected cell with the value the user chose

    var es = new ExcelServices();

    var session = null;

    session = new ExcelServicesSession(sid, es);

    var selStatus = document.getElementById('TaskStatus').options[document.getElementById('TaskStatus').selectedIndex].value;

That concludes our UpdateTaskList function as well.  The last thing we need to do is catch the event that the set cell is finished, and show the results in the EWA.

Showing the results in the EWA

Since there is no JavaScript function to just update the EWA's display, and because our JavaScript code just updated the EWA's session under the covers, the easiest thing to do is to refresh the entire page which will cause the EWA to fetch its updated session from the server.  We can do this in the function that lets us know that the set cell completed, like so:

function SetCellFinished(webMethodCall)

{

    window.location.reload(true);

    return;

}

That's it!

Using the task list

To use the task list, simply click on a cell in the Status column, select a status, and click Update to set the status.  When all tasks are set to "Done", you'll get a message to go home!

Resources

ZIP containing workbook and sample code.

AJAX JavaScript library