How Do I Use It?
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.
// 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)
//attach to the individual Excel Web Access (EWA) web parts
EWA1 = Ewa.EwaControl.getInstances().getItem(0);
EWA2 = Ewa.EwaControl.getInstances().getItem(1);
if(EWA1 && EWA2)
// add the event handler for the ActiveSelectionChanged event
// This is the handler for the ActiveSelectionChanged event.
// When the user changes the selection in the EWA web part, this event is raised
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, 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)
// 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)
// 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’;
// 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’;
The web part on the left side contains four “buttons” (cells A1, A2, B1, and B2) which contain strings that correspond to named items present in the web part on the right hand side. “Market Cap”, “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, or via User Defined Functions.
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.