Using the JavaScript Object Model Share Point 2010

We’ve almost met the requirements of our demonstration scenario. The only item missing is a user-interface element that can be used to switch currencies. Let’s move on and take a look at how we can implement this functionality using the JavaScript Object Model(JSOM).

A Whirlwind Tour of Excel JSOM
Before we jump into adding script to the page, let’s look at the objects that are exposed via JSOM and the mechanism for connecting to EWA web parts. Ewa Control The Ewa Control is the entry point to the object model and represents an EWA web part. As well as representing a specific instance of an EWA web part, the Ewa Control has a static getI nstances method that can be used to retrieve a collection of the EWA web parts on a page. For example, the following code snippet returns an Ewa Control object that represents the first EWA web part on a page:

var my Ewa = Ewa. Ewa Control. get Instances (). getItem (0);

As well as the methods mentioned, all events exposed by JSOM are defined on the Ewa Control object. These include events such as active Cell Changed, grid Synchronized, and work book Changed. Event-handling delegates can be hooked up using JavaScript code similar to this snippet:

The Workbook object represents an Excel workbook. A reference to the workbook used by a particular Ewa Control can be retrieved using the following code: var my Workbook = my Ewa. get Active Workbook();

The Workbook object provides a number of methods that can be used to retrieve particular ranges, worksheets, or named ranges. As well as these methods, refreshing the underlying data of a workbook can be performed by calling the refreshAllAsync method. Note that to refresh a workbook, the referenced EWA web part must have the Refresh Selected Connection, Refresh All Connections option checked.

The Range object represents one or more cells. The most common use of the Range object is to get or set particular values within a workbook. The following code snippet sets the value of the first cell in a workbook:

var theRange = myWorkbook.getRange("A1", 1, 1, 1, 1);
theRange.setValuesAsync(values, myCallBack, null);

Notice a few things about this snippet. First, when calling setValuesAsync, the values property must contain a two-dimensional array of cell values. Second, all interactions with the Excel workbook are performed asynchronously. This makes sense when you think about it, since the workbook is actually being processed on the server and is being accessed via Asynchronous JavaScript and XML (AJAX).

The Sheet object represents a single worksheet within a workbook. References to specific sheets can be obtained using the getSheets method of the Workbook object, which will return a SheetCollection object, or using the getActiveSheet method of the Workbook object, which will return a reference to the currently active sheet. NamedItem The NamedItem object represents a named range. For all practical purposes, the NamedItem object is useful only for selecting a particular named range via the activateAsync method.When it comes down to reading data from a named range or writing data to a namedrange, both of these actions must be performed using a Range object. For example, thiscode snippet reads values from a named range:

Adding a Content Editor Web Part Containing JavaScript
To make our demonstration focus on the specifics of communicating with Excel via JSOM, our user interface will consist of a simple drop-down list of currencies. We can include JavaScript on a web part page in a few ways. One is to modify the page using SharePoint Designer and physically insert the script into the page. Another method, and the one that we’ll use for our demonstration, is to add a Content Editor web part that uses a link to a text file containing the appropriate script. When I covered the JSOM earlier, I mentioned that it works by leveraging a collection of JavaScript objects provided by one or more Excel Web Access web parts. As a result of this, we can access the Excel JavaScript API only on pages containing at least on EWA web part. We’ll make use of this functionality on the home page of our sample site.

  1. Navigate to http://localhost/Chapter12/Default.aspx, and choose Site Actions | Edit Page.
  2. In the Left web part zone, click Add a Web Part and then select the Content Editor web part from the Media and Content category.
  3. Select the Content Editor web part by clicking the header; then, from the Options menu, select Web Part Properties.
  4. Click OK to save the changes to the web part properties. Then click Stop Editing from the Page menu to return to View mode.

Creating JSOM Content in an External File
We’ve configured our content editor web part to read its contents from an external file; we’ll now move on to create a file containing the appropriate JSOM content. Note that we could enter the JavaScript directly into the Content Editor web part, but using an external file makes debugging and editing easier.

  1. Open Notepad.exe and enter the following text in a new document:
  2. Save the file as JSOM.Content.txt to \localhostChapter12Excel Workbooks. We can now refresh our home page to see the fruits of our labor. We can select a currency from the drop-down list and our chart will be recalculated using that currency, as shown next:

Creating JSOM Content in an External File
As you can see, when we try to select a new currency, however, things are not quite as simple as we’d hoped. For example, if we switch the currency to Japanese Yen, we see an Excel Web Access error telling us “A Setting on Excel Services does not allow the requested operation to be performed.” Errors of this type can happen quite often when using JSOM, and the reason comes back to what I said at the beginning of this section: JSOM works by using a set of objects that are exposed by an Excel Web Access web part. Although the error suggests that an Excel Services setting is responsible for our problem, more often than not it’s a property setting on the EWA web part that we’re referencing in script. In our case, the problem is that we’re trying to set the value of the CurrencyCode named range but the Interactivity Settings for the EWA web part don’t allow Typing and Formula Entry. Switch to Edit mode and modify the properties of the EWA web part to resolve this issue. Our home page will now behave as expected.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Share Point 2010 Topics