Thomas Sabo Stud Earrings Introducing the JavaScri

Thanks to Christian Stich, a consultant working on Excel Services projects, for putting together this series on the Excel Services JavaScript Object Model.
Excel Services 2010 has added two entirely new programming APIs: The REST API which I covered in an earlier series of posts and the JavaScript Object Model (JSOM) which I will discuss in this post.
The JavaScript Object Model (JSOM) enables a whole new set of solutions using Excel Services. With the JSOM it is possible to detect and react to a user's interactions with an Excel Web Access (EWA) web part and to programmatically interact with one or multiple EWA web parts.
How Do I Use It?
Using the JSOM is done by inserting JavaScript code on the page that contains the EWA web part(s). This can be done either by adding the code to the web part page using the Content Editor web part or by directly editing the .aspx page itself.
What Does It Offer?
The JSOM enables the developer to access items such as sheets, ranges, tables, PivotTables, and charts. It is possible to set and retrieve values from individual cells or from Excel style A1 ranges or named ranges. The JSOM also provides for events that are raised when the user changes the active selection or active cell or when the user starts editing a cell. Moreover, the JSOM can be used to scroll to a different region and to switch the displayed sheet or named item.
To see the class diagram for the JSOM please click the thumbnail below.

Please note that the class diagram is preliminary and may have some changes at the time when Excel Services 2010 is officially released.
Using the JSOM - A Simple Example
Often people want to use an EWA web part as a data source. EWA web parts can consume data via SharePoint's web part filter framework. However the opposite, using EWA web parts as data sources is not supported by SharePoint 2010.
The example showcases using the JSOM for the detection of user events in one EWA and using those user events and corresponding data to programmatically drive another EWA. This can be used to effectively use an EWA as a data source.
All the developer or user has to do apart from building a web part page with two EWA web parts is to add a Content Editor web part and link it to a file containing the JavaScript code such as the example below.
<script type="text/javascript">
// initialize variables
var EWA1 = null;
var EWA2 = null;
//set page event handlers for onload, proceed to the PageLoad function defined below once the page has loaded if (window.attachEvent)
{
window.attachEvent("onload", PageLoad);
}
//proceed to the GetEWA function defined below once the Excel Services JavaScript OM is 'ready'
function PageLoad()
{
Ewa.EwaControl.add_applicationReady(GetEwa);
}
//attach to the individual Excel Web Access (EWA) web parts
function GetEwa()
{
EWA1 = Ewa.EwaControl.getInstances().getItem(0);
EWA2 = Ewa.EwaControl.getInstances().getItem(1);
if(EWA1 && EWA2)
{
// add the event handler for the ActiveSelectionChanged event
EWA1.add_activeSelectionChanged(activeSelectionCha ngedHandler);
}
}
// This is the handler for the ActiveSelectionChanged event.
// When the user changes the selection in the EWA web part, this event is raised
function activeSelectionChangedHandler(rangeArgs)
{
var sel = rangeArgs.getRange();
var sheet = sel.getSheet().getName();
var values = rangeArgs.getFormattedValues();
// Make sure that the user selected cells on Sheet1 - we may not want any or a different action taken if the user is on a different sheet
if(sheet = "Sheet1")
{
// Check to see if the user has clicked on cells A1, A2, B1,Thomas Sabo Stud Earrings, or B2 which act as buttons to select the view of the other web part
if(sel.getColumn() >= 1 && sel.getColumn() <=2 && sel.getRow() >= 1 && sel.getRow() <= 2)
{
EWA2.getActiveWorkbook().getNamedItems().getItemBy Name(values[0][0]).activateAsync(activateCallBack,values[0][0]);
}
// Check to see if the user has clicked on cells A5 through A14 which contain the company names - copy the selected names to the other web part
if(sel.getColumn() == 1 && sel.getColumnCount() == 1 && sel.getRow() >= 5 && sel.getRowCount() <= 10)
{
EWA2.getActiveWorkbook().getRange('Stocks',1,1,Office 2010 Home And Student Product Key,values.length,1).setValuesAsync(values,setCallBac k,null);
}
}
}
function activateCallBack(returnValues)
{
// All we are doing here is to set the status of the window to indicate that the named item has been activated in the second web part
// We could also do nothing in this case - in other scenarios, it may be desirable or necessary to run code once an asynchronous function completes running
window.status = 'Activated Named Item "' + returnValues.getUserContext() +'" in "Detailed Info" EWA Web Part';
}

function setCallBack(returnValues)
{
// All we are doing here is to set the status of the window to indicate that the data was copied from the first web part to the second web part
window.status = 'Selected values have been copied from the "StockInfo" EWA Web Part to the "Detailed Info" EWA Web Part';
}
</script>
So, let's see what these few lines of JavaScript code that we have embedded on the web part page can do.
The web part on the left side contains four "buttons" (cells A1,Office Standard Product Key, A2, B1, and B2) which contain strings that correspond to named items present in the web part on the right hand side. "Market Cap",north face jacket, "Volume", "P/E Ratio" are charts and "Stocks" is a named range.
Below these "buttons" is a list of company names - these do not have to be fixed and could be changed or new ones could be added by a viewer of the web part page. Next to the company names is the most recent share price of each company's stock. This data as well as the other stock market data used in this example could be live data retrieved either via Excel Services external data support,Thomas Sabo Bead Bracelet, or via User Defined Functions.

The viewer of the web part page can select one or more companies by simply using the mouse to select the cells with the companies' names. In this case, the user has selected "Microsoft" and "Contoso" in the left web part - the JavaScript code detects the change of the active selection and copies these company names ("Microsoft" and "Contoso") to the right web part.

The user then clicks on the Market Cap "button" (cell A1) - again the JavaScript code detects the change in the active selection and accordingly switches the right web part to display the Market Cap chart.

Next, the user highlights all four companies - this change in the active selection is again detected and the selected companies are copied from the left web part to the right web part. This then results in the Market Cap chart being updated to show all four companies.

Finally, the user clicks on the Volume "button" (cell B1) - the JavaScript code thus causes the web part on the right to display the stock trade Volume chart, showing all four companies that had been selected.

Summary
The JSOM is a powerful new API that expands Excel Services capabilities and that enables Excel Services to be used for completely new solutions. The JSOM can be used by itself to provide a means for automating Excel Web Access, or to provide part-to-part communication between different EWA web parts. It can also be used with other APIs such as the new Visio Services JavaScript Object Model, Bing Maps SDK and many others to build custom solutions/mash-ups.
<div