Programming4us
         
 
 
Applications Server

SharePoint 2010 : Excel Services - Using the JavaScript Object Model

11/17/2011 9:14:22 AM
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.

EwaControl

The EwaControl 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 EwaControl has a static getInstances 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 EwaControl object that represents the first EWA web part on a page:

var myEwa = Ewa.EwaControl.getInstances().getItem(0);

As well as the methods mentioned, all events exposed by JSOM are defined on the EwaControl object. These include events such as activeCellChanged, gridSynchronized, and workbookChanged.

Event-handling delegates can be hooked up using JavaScript code similar to this snippet:

var myEwa;
_spBodyOnLoadFunctionNames.push("EwaPageLoad");
function EwaPageLoad() {
Ewa.EwaControl.add_applicationReady(GetEwa);
}
function GetEwa(){
myEwa = Ewa.EwaControl.getInstances().getItem(0);
if(myEwa)
{
myEwa.add_activeSelectionChanged(activeSelectionChangedHandler);
}
}

Workbook

The Workbook object represents an Excel workbook. A reference to the workbook used by a particular EwaControl can be retrieved using the following code:

var myWorkbook = myEwa.getActiveWorkbook();

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.

Range

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).

Sheet

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 named range, both of these actions must be performed using a Range object. For example, this code snippet reads values from a named range:

function someFunction()
{
var myEwa = Ewa.EwaControl.getInstances().getItem(0);
var myWorkbook = myEwa.getActiveWorkbook();
var theRange = myWorkbook.getRange("MyNamedRange", 1, 1, 1, 1);
var values = theRange.getValuesAsync(Ewa.ValuesFormat.Formatted, getCallBack, null);
}
function getCallBack(returnValues) {
window.status = returnValues.getReturnValue();
}



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/12/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. In the Content Link text box, enter this: http://localhost/12/Excel%20Workbooks/JSOM.content.txt.

  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:

    <select id="Select1" onchange="UpdateChart(this)">
    <option value="ARS">Argentine Peso</option>
    <option value="AUD">Australian Dollar</option>
    <option value="GBP">British Pound</option>
    <option value=vJPY">Japanese Yen</option>
    <option value="EUR">Euro</option>
    <option value="USD" selected="selected">US Dollar</option>
    </select>
    <script type="text/javascript">
    var myEwa;
    _spBodyOnLoadFunctionNames.push("EwaPageLoad");

    function EwaPageLoad()
    {
    Ewa.EwaControl.add_applicationReady(GetEwa);
    }

    function GetEwa()
    {
    myEwa = Ewa.EwaControl.getInstances().getItem(0);
    }

    function UpdateChart(sender)
    {
    if (myEwa) {
    var values = new Array();
    values[0] = sender.value;

    var values2 = new Array();
    values2[0] = values;

    var currencyCode=myEwa.getActiveWorkbook().getRange("CurrencyCode", 1, 1,
    values.length, 1);
    currencyCode.setValuesAsync(values2, setCallBack, null);
    else
    {
    window.status = 'Excel Web Access not ready';
    }
    }

    function setCallBack(returnValues) {
    myEwa.getActiveWorkbook().refreshAllAsync(refreshCallback,null);
    }

    function refreshCallback(returnValues) {
    window.status = 'Sales chart has been refreshed';
    }

    </script>


  2. Save the file as JSOM.Content.txt to \\localhost\12\Excel 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:

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.

Other -----------------
- Optimizing Exchange 2007 Servers & Monitoring Exchange Server 2007
- Optimizing an Exchange Server 2007 Environment : Analyzing Capacity and Performance
- Exchange Server 2010 : Planning Certificates for Autodiscover (part 2) - Deploying Exchange Certificates
- Exchange Server 2010 : Planning Certificates for Autodiscover (part 1) - The X.509 Certificate Standard
- Exchange Server 2010 Autodiscover : Autodiscover Concepts
- Active Directory 2008 : Proactive Directory Maintenance and Data Store Protection (part 5) - Protecting DCs as Virtual Machines
- Active Directory 2008 : Proactive Directory Maintenance and Data Store Protection (part 4) - Performing Proactive Restores
- Active Directory 2008 : Proactive Directory Maintenance and Data Store Protection (part 3) - Relying on Windows Server Backup to Protect the Directory
- Active Directory 2008 : Proactive Directory Maintenance and Data Store Protection (part 2) - Relying on Built-in Directory Protection Measures
- Active Directory 2008 : Proactive Directory Maintenance and Data Store Protection (part 1) - Twelve Categories of AD DS Administration
- BizTalk 2009 : The BizTalk Management Database
- BizTalk 2009 : Handling Failed Messages and Errors
- Microsoft Dynamics GP 2010 : Dynamics GP Utilities (part 3) - Additional steps
- Microsoft Dynamics GP 2010 : Dynamics GP Utilities (part 2) - Loading sample company data & Creating a new Dynamics GP company
- Microsoft Dynamics GP 2010 : Dynamics GP Utilities (part 1) - Completing the Dynamics GP installation
- Microsoft Dynamics GP 2010 : Creating an ODBC data source
- Microsoft Dynamics AX 2009 : Working with Forms - Storing last form values
- Microsoft Dynamics AX 2009 : Creating modal forms & Changing common form appearance
- Exchange Server 2010 : Performing Tracking and Logging Activities in an Organization (part 2) - Using Protocol Logging & Using Connectivity Logging
- Exchange Server 2010 : Performing Tracking and Logging Activities in an Organization (part 1) - Using Message Tracking
 
 
Most View
- Exchange Server 2007 : Manage MB Database Properties
- .NET Debugging : Managed Heap and Garbage Collection
- Identity and Access Management : Trust Boundaries and IAM
- Windows 7 : Checking for Updates and Security Patchess
- The Windows Sysinternals Web Site
- Windows 7 : Using Any Search Engine from the Address Bar
- Exchange Server 2010 : Planning for Anti-Spam (part 3)
- ASP.NET Security : The Membership and Role Management API (part 1)
- Managing Windows Server 2012 Storage and File Systems : Storage Management (part 4) - Installing and configuring file services - Configuring Hyper-V
- User Interface : Creating an Animated Splash Screen
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS