Creating an Excel Services and Bing Maps Mashup for SharePoint 2010 Using the Excel Services ECMAScript Object Model

The Excel Services JavaScript object model enables you to interact with an Excel Web Access Web Part. Among other things, you can retrieve and modify cell values and respond to events that occur within the Excel Web Access Web Part. This enables you to create interesting and convenient user experiences such as integrating data in an Excel Web Access Web Part with a Bing Map to offer users a way to visualize data.
This Visual How To shows how to use a Bing Maps AJAX control to interact with an Excel Web Access Web Part using the Excel Services JavaScript object model. The scenario for this example is a company that owns many convenience stores. The company currently uses an Excel workbook to view financial results for the various stores. The company wants to view this data directly from SharePoint as shown in Figure 1.
Figure 1. Contoso Company Excel Services and Bing Maps Mashup



There are three main tasks to complete in order to create this mashup:

1. Perform some initialization such as associating the JavaScript with the Excel Web Access Web Part.
2. Create the map and retrieve store information from the Excel workbook so that pins can be positioned on the Bing Map where the stores are located.
3. Add an event handler to the map pins to update the data shown on the page based on the pin that is clicked.

The first task is to create the basic script elements:

• Create HTML <div> elements to hold the map and store information.
• Set a reference to the Bing Maps AJAX control.
• Define script variables.
• Obtain a reference to an Excel Web Access Web Part.

J#

Copy

<div id='mapDiv' style="position:relative; width:400px; height:400px;"></div>
<div id='storeInfo'></div>
<script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=7.0"></script>
<script type="text/javascript">

    // Script variables to refer to the Excel Web Access Web Part,
    // the Bing map, and a multi-dimensional array of 
    // Store information. 
    var ewaPart;
    var map;
    var storeData;
    
    // Columns in the array of store information
    var ID_COL = 0;
    var NAME_COL = 1;
    var ADDRESS_COL = 3;
    var CITY_COL = 4;
    var STATE_COL = 5;
    var ZIP_COL = 6;
    var PHONE_COL = 8;
    var MGR_COL = 9;
    var LAT_COL = 10;
    var LONG_COL = 11;

    // Set the page event handlers for onload and unload.
    if (window.attachEvent) {
        window.attachEvent("onload", Page_Load);
    }
    else {
        // For some browsers window.attachEvent does not exist.
        window.addEventListener("DOMContentLoaded", Page_Load, false);
    }

    // Page load event handler
    function Page_Load() {
        Ewa.EwaControl.add_applicationReady(GetEwa);
    }

    // Excel Web Access Control application ready event handler
    function GetEwa() {
        ewaPart = Ewa.EwaControl.getInstances().getItem(0);
        GetMap();
        GetStoreInfo();
        ewaPart.getActiveWorkbook().getRangeA1Async('Forecast!Store'
          , InitializeStoreDetail);
    }
</script>

Setting Up the Bing Map
The next step is to create the initial view of the map and then add pins that represent the store locations. The following code example shows how to add a map using the Bing Maps AJAX control. To use the Bing Maps API, you must obtain a developer key. For more information about how to create an account and obtain a developer key, see Create a Bing Maps Account.

J#

Copy

// Setup the initial Bing Map view.
function GetMap()
{   
    map = new Microsoft.Maps.Map(document.getElementById("mapDiv"), 
           {credentials: "YOUR BING MAPS KEY",
            center: new Microsoft.Maps.Location(44.88, -93.218171),
            mapTypeId: Microsoft.Maps.MapTypeId.road,
            zoom: 10});
    Microsoft.Maps.Events.addHandler(map, "mousemove", function (e) 
    {
        var mapElem = map.getRootElement();
        if (e.targetType === "map") 
        {
          mapElem.style.cursor = "default";
        } else {
          mapElem.style.cursor = "pointer";
        }
    });
}

The Store Reporting workbook contains a table of store information including the latitude and longitude of each store on a worksheet named Store Data. The range that contains the data is named "StoreData". To add pins to the map, use the Excel Services JavaScript object model to retrieve the latitude and longitude for each store. The functions to retrieve ranges and range values by using the JavaScript object model are asynchronous, so you must use callback functions to add pins to the map.

J#

Copy

// Function to initiate the retrieval of store information
function GetStoreInfo()
{
    ewaPart.getActiveWorkbook().getRangeA1Async(
        'StoreData', GotStoreInfo); 
}

// Callback used for getRangeA1Async when fetching StoreData range 
function GotStoreInfo(asyncResult)
{
    asyncResult.getReturnValue().getValuesAsync(
        'Formatted', CreateStorePins);
}

// Callback used for getValuesAsync when retrieving StoreData values
function CreateStorePins(result)
{
    storeData = result.getReturnValue();
    var row;
    // The first row contains column headings
    for (row = 1; row < storeData.length; ++row)
{
        var location = new Microsoft.Maps.Location(
            storeData[row][LAT_COL], storeData[row][LONG_COL]);
        var label = storeData[row][ID_COL].toString();
        var pin = new Microsoft.Maps.Pushpin(location, {text: label}); 

        Microsoft.Maps.Pushpin.prototype.title = null;
        pin.title = storeData[row][NAME_COL];
        Microsoft.Maps.Pushpin.prototype.description = null;
        pin.description = storeData[row][MGR_COL] + ' - ' +
                          storeData[row][PHONE_COL];

        // Add handler for the pushpin click event.
        Microsoft.Maps.Events.addHandler(
            pin, 'click', DisplayStoreData);

        map.entities.push(pin);  
}
}

Updating the Excel Services Web Access Web Part
In the prior CreateStorePins function, after each pin is added to the map, an event handler is added to the pin to handle the click event. The event handler instructs the Bing Maps AJAX Control to call the function DisplayStoreData when a pin is clicked. There are two primary tasks to perform in this event handler. First, the store ID associated with the pin must be sent to the Excel Web Access Web Part so that the Excel model can be updated. Next, the details associated with the store shown under the map must be refreshed based on the current pin.
To update the Excel Web Access Web Part, obtain the range associated with the Store ID (the range is named Forecast!Store) using getRangeA1Async and then use the setValuesAsync function to send the store ID to the range. The Excel Web Access Web Part automatically recalculates the workbook to show the correct values.
The process that is associated with placing the pins on the map retrieved all of the store information from the workbook and stored it in the storeData script variable. ShowStoreDetails loops through the storeData array and locates the required store information, builds an HTML fragment, and then assigns this HTML to the inner HTML of the storeInfo <div> element.

J#

Copy

// Map pin click event handler 
function DisplayStoreData(mouseEventArgs)
{
    var storeID = mouseEventArgs.target.getText();
    ewaPart.getActiveWorkbook().getRangeA1Async(
        'Forecast!Store', GotStore, storeID);
    ShowStoreDetails(storeID);
}

// Callback used for getRangeA1Async when fetching 
// the 'Forecast!Store' range
function GotStore(asyncResult)
{      
    asyncResult.getReturnValue().setValuesAsync(
        asyncResult.getUserContext())
}

// Callback used for getRangeA1Async when fetching 
// the range Forecast!Store
function InitializeStoreDetail(asyncResult)
{
    asyncResult.getReturnValue().getValuesAsync(
        'unformatted', GotStoreID);
}

// Callback used for getValuesAsync when retrieving 
// the value of the Forecast!Store range
function GotStoreID(asyncResult)
{
    var result = asyncResult.getReturnValue();
    ShowStoreDetails(result[0][0]);
}

// Retrieve individual store information and display
// below map in the 'storeInfo' div.
function ShowStoreDetails(store)
{
   var row;
    // The first row contains column headings
    for (row = 1; row < storeData.length; ++row)
{
        if(storeData[row][ID_COL]==store)
        {
            var output = "<br/><strong>" + 
                storeData[row][NAME_COL] + "</strong><br/>";
            output = output + storeData[row][ADDRESS_COL] + "<br/>";
            output = output + storeData[row][CITY_COL] + ", " 
                            + storeData[row][STATE_COL] + " " 
                            + storeData[row][ZIP_COL] + "<br/>";
            output = output + "<br/>Manager:<br/>" +
                     storeData[row][MGR_COL] + "<br/>";
            output = output + storeData[row][PHONE_COL] + "<br/>";
            document.getElementById('storeInfo').innerHTML = output;
            break;
        } 
} 
}

With the Excel Services JavaScript object model, developers can now integrate other web-based assets or services together with an Excel Web Access Web Part using nothing more than JavaScript. The object model enables you to read and write values to Excel ranges and create event handlers for events such as active cell changed, active selection changed, and workbook changed.
In this Visual How To, the Excel Services JavaScript object model is used to create an Excel Services and Bing Maps mashup. The main purpose use of the object model is to read and write values to ranges in the Excel Web Access Web Part.
You can also use the object model to add navigational UI directly to a workbook. By formatting cells to resemble buttons, you can handle the activeCellChanged event to detect when the "button" is clicked, and then perform the desired task. You can also have VBA code that performs the same task so that the user gets the same experience whether they are using the workbook in Excel 2010 with VBA or experiencing it in a browser with the Excel Services JavaScript object model.
Deploying and Debugging a Script
To test a script, simply save the script to a document library in SharePoint 2010. Then, create a new page or Web Part page in SharePoint 2010 and add two Web Parts to the page: an Excel Web Access Web Part and a Content Editor Web Part. Edit the Excel Web Access part and set the value of the Workbook property to the URL of the workbook that you want to display in the Excel Web Access Web Part as shown in Figure 2.
Figure 2. Excel Web Access Web Part Workbook Display setting

Next, edit the Content Editor Web Part and set the value of the Content Link to the URL of the JavaScript file that you saved as shown in Figure 3.
Figure 3. Content Editor Web Part Content Link setting

Now, the page is set and the two Web Parts should be communicating using the Excel Services JavaScript object model. To debug issues, press F12 in Internet Explorer to view the Internet Explorer developer tools. Click the Script tab on the menu and then scroll down until you see your script to add breakpoints.
If there is an issue, the console window on the right half of the screen will identify what the issue is, as shown in Figure 4.
Figure 4. The Internet Explorer Developer Tools Script Editor