Creating an Excel Services and Bing Maps Mashup for SharePoint 2010 Using the Excel Services ECMAScript Object Model
.jpg)
Summary: Learn how to create an Excel Services and Bing Maps mashup for Microsoft SharePoint Server 2010 using the Excel ServicesJavaScript object model and the Bing Maps AJAX control.
Applies to: Excel 2010 | Office 2010 | SharePoint Foundation 2010 | SharePoint Server 2010 | Visual Studio | Visual Studio 2008 | Visual Studio 2010
Provided by: Steve Hansen, Grid Logic (Visual Studio MVP)
The Excel ServicesJavaScript 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 ServicesJavaScript 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:
The first task is to create the basic script elements:
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.
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 ServicesJavaScript 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.
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.
With the Excel ServicesJavaScript 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 ServicesJavaScript 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 ServicesJavaScript 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 ServicesJavaScript 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
![]() |
Watch the video
> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/e8e66688-aac7-487a-987b-e7de6c7bbc5a] Length: 00:12:00
About the Author Steve Hansen, Grid Logic, is a long time MVP contributor to Microsoft Office. Steve is an Office System Architect at Grid Logic and has created numerous (50+) videos for Microsoft related to Office development. Steve has authored many white papers related to Office development as well as two books─Mastering Excel 2000 Premium Edition and Mastering Excel 2003 Programming with VBA─both published by Sybex. |
.jpg)