Introduction to the Excel Services JavaScript OM - Part 3 – writing to the workbook

So far we saw how one can call into the EWA at arbitrary times and respond to events by affecting the page around the EWA. This time around we will combine the two mechanisms to make modifications to the workbook when the selection changes.

If you will recall, in part 1, we listened to events over the list of aliases in the workbook, giving the dashboard the ability to show the picture of the relevant person. This time around, we will also take the name and “write” it into another cell in the workbook. That in turn will update the EWA and show us the updated result for that person.

Step 1 – Figure out the range we want to write to

Working with ranges is an inherently asynchronous operation – before you can actually write to it, the system needs to know where it is. For that, we need to make a call to the getRangeA1Async() method of the workbook. Note that if we wanted to go by coordinates, we could call the getRange() method which is not asynchronous and thus may be more fitting for some scenarios:

function cellchanged(rangeArgs)

{

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

    if (lastSheet == sheetName)

    {

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

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

        var value = rangeArgs.getFormattedValues()[0][0];

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

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

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

            ewa.getActiveWorkbook().getRangeA1Async("Sheet2!Alias", getRangeComplete, value);

        }

    }

    lastSheet = sheetName;

    writelog('Address:' + rangeArgs.getRange().getAddressA1(), 1);

    writelog('Value:' + rangeArgs.getFormattedValues(), 1);

    writelog('Cell changed event fired', 0);

}

The highlighted code is what we added over the existing method from the previous post. In this case, we ask for the cell named “Alias” and we tell the EWA to call into getRangeComplete() when it has the object ready. Note that we also pass in value as a parameter – we do that so that we later know what value to put into the cell (we could also have used a global variable for this if we were so inclined).

On top of that, notice that we added a condition where the code executes only in the case where the sheet name has changed. This is important because the cell-changed event also occurs when a sheet changes. This means that the second the user tries to switch back to the first sheet (where they originally clicked), since the active cell is still the same one, the event will fire, the code will run and immediately move the user again to the other sheet. Checking that lastSheet equals the sheet name that we currently are on makes sure we only run the code for actual navigation within the sheet.

Step 2 – Write the new value into the cell

Once the callback gets executed, we need to call into the setValuesAsync() method:

function getRangeComplete(asyncResult)

{

    var range = asyncResult.getReturnValue();

    var value = asyncResult.getUserContext();

  var values = [[value]];

    range.setValuesAsync(values);

}

The first step in the callback of getting the range is to get the values out of the asyncResult parameter – getReturnValue() is the standard method we use to get the result of the operation. getUserContext() is the method we use to get the user state passed as the last parameter to all asynchronous methods.

We then create an array out of the value ([[value]] creates a jagged array with a single item in it). That is passed into the setValuesAsync() method which will update the EWA. No callback is being passed to the setValuesAsync() method – this is because we don’t need to take further action. If we were to add more error handling, we would have had to use that method.

Clicking on “shaharp” and flipping to sheet2 (which is where the Alias named range is) will show us that the cell has indeed changed and with it changed the chart:

Step 3 – Navigating to Sheet2 for the user

The final step is to make sure the EWA actually shows the relevant information when the user clicks on the cells. For that we will use the range.activateAsync() method. We could have waited for the setValuesAsync()   to complete before making this call, but there’s really no reason – Excel Services can take multiple calls at the same time. This means we simply add the to the getRangeComplete() function:

function getRangeComplete(asyncResult)

{

    var range = asyncResult.getReturnValue();

    var value = asyncResult.getUserContext();

    var values = [[value]];

    range.setValuesAsync(values);

    range.activateAsync();

}

 

And that’s it! Clicking on “danyh” for example, will cause sheet2 to get activated and the “alias” cell to change to “danyh” which in turn will cause the chart to change accordingly:

And we are done!

As you can see, the JavaScript OM gives developers the abilities to add more capabilities to their workbooks and code around them. Do not expect an OM as rich as Excel’s, however, the capabilities are nice to start with – I would love to hear from you through the comments what do you think is missing from the OM.

Comments

• Anonymous
  November 10, 2011
  Hi, I have a requirement that my UDF is returning mulitple values, so I need to modify multiple cells by a udf. Is it possible??

• Anonymous
  December 21, 2011
  Hi Shahar: Is there a javascript method available for openworkbookforediting ?