Export (0) Print
Expand All

How to: Create a Bing Maps content app for Excel in Visual Studio

apps for Office

Create a Bing Maps app for Office in Excel 2013 by using Visual Studio.

Last modified: June 30, 2014

Applies to: Excel 2013 | Excel 2013 RT | Excel 2013 SP1

   Office.js: v1.0, v1.1

   Apps for Office manifests schema: v1.0, v1.1

In this article
Overview of creating a Bing Maps content app for Excel 2013
Creating a content app project for Excel in Visual Studio
Creating the HTML file and adding JavaScript code to the app
Building and debugging the app
Additional resources

This walkthrough shows you how to create a content app for Excel 2013 that works against the Bing Maps service by completing the following tasks:

  • Create a project in Visual Studio 2012 or Visual Studio 2013.

  • Write HTML code to create the UI.

  • Write JavaScript code to perform the following functions:

    • Initialize the app and display a default view of the map.

    • Insert and set data as a table at the current selection by using the setSelectedDataAsync method of the Document object.

    • Create a binding for new table by using the addFromSelectionAsync method of the Bindings object.

    • Add an event handler for the DataChanged event of the binding by using the select method of the Office object and the addHandlerAsync method of the Binding object.

    • Save the map's display mode as a custom setting by using set and saveAsync methods of the Settings object.

  • Build and debug the project to test it.

This section shows how to get started creating a content app in Visual Studio. For an introduction to creating content and task pane apps in Visual Studio 2012, see How to: Create your first task pane or content app by using Visual Studio.

To create a content app project for Excel

  1. Open Visual Studio.

  2. On the File menu, choose New, Project.

    The New Project dialog box opens.

  3. In the New Project dialog box, under Installed Templates, expand Office/SharePoint, choose Apps, and then choose App for Office 2013.

  4. Name the project BingMaps, and then choose the OK button.

  5. For the type of app to create, select Content app in Excel.

To customize your app, you create the HTML page for its UI, and then add code and event handlers for the buttons on the page.

To create the HTML page

  1. In Solution Explorer, open the BingMaps.html file to view the default HTML page for the app.

  2. Delete the opening and closing <body> tags, and replace them with the following HTML code to create a div that will be used to display the map, and four buttons to run code.

    <body>
        <div id="mapDiv" style="height:85%;width:100%"></div>
        <hr/>
        <button id="task1" style="height:10%; width:24%" onclick=doWrite()>Write</button>
        <button id="task2" style="height:10%; width:24%" onclick=doBind()>Bind</button>
        <button id="task3" style="height:10%; width:24%" onclick=doEvent()>Event</button>
        <button id="task4" style="height:10%; width:24%" onclick=doSettings()>Settings</button>
        <div id="message"></div>
    </body>
    
  3. Inside the opening and closing <head> tags of the Default.html file, insert the following <script> tags below the last set of <script> tags in the default HTML page.

    <script type="text/javascript" src="../Scripts/MapService.js"></script>
    <script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=7.0"></script>
    
    

To add code that initializes the app and connects to the Bing Maps service

  1. In Solution Explorer, open the BingMaps.js file to edit the default JavaScript file for the app.

  2. Delete the contents of the file, and then paste the following JavaScript code into it. This code initializes the app and creates event handlers for the buttons on the BingMaps.html page.

    var map;
    var mapDiv;
    var currentMode;
    
    function getPastCities() { return [['Berlin'], ['Rome'], ['Tokyo', ]] }
    
    // Load a default view of the map. Depends on code from MapService.js.
    function loadMap() {
    
        map = new Map(write);
    
        //Try to load cities, if no binding exists, then load blank map.
        drawMapFromBinding(Office.select("bindings#MyCities",
            //No binding exists, load blank map
            function (result) {
                map.setAndRender(
                        mapDiv,
                        [],         // an array of addresses to map
                        currentMode // could also be "aerial"
                );
    
            })
        );
    }
    
    // Draw map from values in the binding object passed in.
    function drawMapFromBinding(binding) {
        if (binding) {
    
            binding.getDataAsync(function (dataResult) {
                var cities = dataResult.value;
                if (cities.rows)
                    map.setAndRender(mapDiv, cities.rows, currentMode);
                else
                    map.setAndRender(mapDiv, cities, currentMode);
            });
        }
    }
    
    // Get div to display the map, get the current map display mode
    // from the Settings object property bag (or set to "road" if not saved),
    // and then call loadMap() to render the map.
    function init() {
        mapDiv = document.getElementById('mapDiv');
        currentMode = Office.context.document.settings.get("mode") || "road";
        loadMap();
    }
    
    // Initialize the app.
    Office.initialize = function (reason) {
        $(document).ready(function () {
            init();
        });
    }
    
    // Toggle the current display mode, save 
    // the current display mode as a custom setting,
    // and then re-render the map.
    function doSettings() {
        if (currentMode == "aerial")
            currentMode = "road";
        else
            currentMode = "aerial"
        Office.context.document.settings.set("mode", currentMode);
        Office.context.document.settings.saveAsync();
        drawMapFromBinding(Office.select("bindings#MyCities", function (result) { map.setAndRender(mapDiv, [], currentMode); }));
    }
    
    // Create a single-column table data structure with the heading "Cities"
    // and the values from getPastCities(), and then add it to the document
    // at the currently selected cell.
    function doWrite() {
        var myTable = new Office.TableData();
        myTable.headers = [["Cities"]];
        myTable.rows = getPastCities();
        Office.context.document.setSelectedDataAsync(myTable, { coercionType: 'table' },
            function (result) {
                if (result.error)
                    write(result.error.message);
            }
        );
    }
    
    // Function that writes to a div with id='message' on the page.
    function write(message) {
        document.getElementById('message').innerText += message;
    }
    
    // Retrieve the "MyCities" binding, and add an event handler that redraws
    // the pins on the map when a value is changed in the binding.
    function doEvent() {
        Office.select("bindings#MyCities").addHandlerAsync("bindingDataChanged", MyHandler);
    }
    
    function MyHandler(evt) {
        drawMapFromBinding(evt.binding);
    }
    
    // Create a binding named "MyCities" for the currently selected table,
    // and then draw pins on the map using the values in the table.
    function doBind() {
        Office.context.document.bindings.addFromSelectionAsync("table", { id: 'MyCities' },
            function (result) {
                drawMapFromBinding(result.value);
            }
        );
    }
    
    
  3. In Solution Explorer, open the shortcut menu for Scripts, and then choose Add, New Item.

  4. In the New Item dialog box, choose JavaScript File, name the file MapService.js, and then choose the Add button.

  5. Implement the code to render the map using the Bing Maps service by adding the following JavaScript code to MapService.js.

    function Map(errorMessageHandler, mapType, width, height){
        var credentialsKey = <Replace with your BingMaps developer key>;
    
        this._mapType = new MapType(mapType ? mapType : 'road');
    
        this._dimension = {
            width : width ? width : 600,
            height : height ? height : 400
        }
    
        this._locations = new Array();
    
        this._addressToLatLngAsync = function (strAddress, callback, context) {
            var latLng = null;
    
            $.ajax({
                type: 'GET',
                url: 'http://dev.virtualearth.net/REST/v1/Locations?query=' + strAddress + '&key=' + credentialsKey + '&jsonp=?',
                dataType: 'jsonp',
                success: function (result) {
                            if (result.resourceSets[0]) {
                                var loc = result.resourceSets[0].resources[0].point.coordinates;
                                latLng = new LatLng(loc[0], loc[1]);      
                            }
                            callback.call(context, latLng);
                        },
                error: function (result) {
                            if (errorMessageHandler) {
                                errorMessageHandler('Error getting data coordinates for address: ' + strAddress);
                            }
                            callback.call(context, latLng);
                        }
            });
    
        };
    
        this._addLocationFromAddressAsync = function(strAddress, callback, context) {
            this._addressToLatLngAsync(
                strAddress,
                function(latLng)
                {
                    if (latLng) {
                        this._locations.push(latLng);
                    }
                    callback.call(context);
                },
                this
            );
        };
    
        this._findBounds = function(){
            var bounds;
            if(this._locations.length == 0){
                bounds = new LatLngBounds(
                    new LatLng(25, -125),
                    new LatLng(50, -65)
                );
            }
            else if(this._locations.length == 1){
                var lat = this._locations[0].lat;
                var lng = this._locations[0].lng;
    
                bounds = new LatLngBounds(
                    new LatLng(lat - 1, lng - 1),
                    new LatLng(lat + 1, lng + 1)
                );
            }
            else{
                var minLat = 91;
                var maxLat = -91;
                var minLng = 181;
                var maxLng = -181;
    
                for(var i = 0; i < this._locations.length; i++){
                    var lat = this._locations[i].lat;
                    var lng = this._locations[i].lng;
    
                    minLat = lat < minLat ? lat : minLat;
                    maxLat = lat > maxLat ? lat : maxLat;
                    minLng = lng < minLng ? lng : minLng;
                    maxLng = lng > maxLng ? lng : maxLng;
                }
    
                bounds = new LatLngBounds(
                    new LatLng(minLat - .01, minLng - .01 ),
                    new LatLng(maxLat + .01, maxLng + .01)
                );
            }
            
            return bounds;
        };
    
        this.setAddressesAsync = function (arrAddresses, boolAppend, callback, context) {
            var leftToResolve = arrAddresses.length;
            if (!boolAppend) {
                this._locations = new Array();
            }
            if (arrAddresses.length == 0 /*|| typeof arrAddresses != 'array'*/) {
                if (callback) callback.call(context);
            }
            for (var i = 0; i < arrAddresses.length; i++) {
                this._addLocationFromAddressAsync(
                    arrAddresses[i],
                    function () {
                        if (--leftToResolve == 0) {
                            if (callback) callback.call(context);
                        }
                    }
                );
            }
        };
    
        this.setMapType = function(type) {
            this._mapType.setType(type);
        }
    
        this.setDimension = function(width, height) {
            this._dimension = {width: width, height: height};
        }
    
        this.render = function(mapDiv){
            while (mapDiv.childNodes.length > 0) {
                mapDiv.removeChild(mapDiv.firstChild);
            }
    
            var latLng = new LatLng(48,-122);
            var opts = {
                credentials: credentialsKey,
                disableUserInput: false,
                showDashboard: false,
                zoom: 8,
                center: latLng.getForMapService(),
                mapTypeId: this._mapType.getForMapService(),
                width: this._dimension.width,
                height: this._dimension.height
            };
    
            var map = new Microsoft.Maps.Map(mapDiv, opts);
                
            for(var i = 0; i < this._locations.length; i++){
                this._renderMarker(map, this._locations[i], i);
            }
    
            var bounds = this._findBounds().getForMapService();
            map.setView({ bounds: bounds });
        };
    
        this._renderMarker = function(map, loc, index){
            map.entities.push(
                        new Microsoft.Maps.Pushpin(
                            loc.getForMapService(),
                            {
                                zIndex: index
                            }
                        )
                    );
        };
    
        this.setAndRender = function(mapDiv, arrAddresses, mapType, width, height){
            if(mapType) this.setMapType(mapType);
            if(width && height) this.setDimension(width, height);
    
            this.setAddressesAsync(
                arrAddresses,
                false,
                function () {
                    this.render(mapDiv);
                },
                this
            );
        };
    };
    
    function MapType(type){
        this._type = type;
    
        this.setType = function(type){
            this._type = type;
        }
    
        this.getForMapService = function(){
            var ret;
            switch(this._type){
                case 'aerial':
                    ret = Microsoft.Maps.MapTypeId.aerial;
                    break;
    
                case 'road':
                default:
                    ret = Microsoft.Maps.MapTypeId.road;
                    break;
            }
            return ret;
        }
    }
    
    function LatLng(lat, lng){
        this.lat = typeof lat == 'string' ? parseFloat(lat) : lat;
        this.lng = typeof lng == 'string' ? parseFloat(lng) : lng;
    
        this.getForMapService = function(){
            return new Microsoft.Maps.Location(
                        this.lat,
                        this.lng
            );
        }
    }
    
    function LatLngBounds(sw, ne){
        this.sw = sw;
        this.ne = ne;
    
        this.getForMapService = function(){
            return ret = Microsoft.Maps.LocationRect.fromEdges(
                            this.ne.lat, // north
                            this.sw.lng, // west
                            this.sw.lat, // south
                            this.ne.lng  // east
            );
        }
    }
    
    

To change the size of the content app

  1. In Solution Explorer, open the BingMaps.xml file.

  2. Change the size in <requestedwidth> element to 600.

  3. Change the size in <requestedheight> to 600.

To add your Bing Maps developer key to the Map function

  1. Open the Bing Maps APIs, SDK, and application development tools page.

  2. In "Step 1" under "Start Developing," choose the Get an account link, and then follow the steps to create a Bing Maps Account.

  3. On the Bing Maps Account Center page, choose the Create or view key link under My Account, and the follow the steps to create a Bing Maps API developer key.

  4. In the MapService.js file, locate this line of code and add your developer key to it:

    var credentialsKey = <Replace with your BingMaps developer key>;
    

Build and run the project to verify that the doWrite(), doBind(), doEvent(), and doSettings() functions work to interact with the data in the worksheet and display cities on the Bing map.

To build and debug

  1. On the Debug menu, choose Start Debugging, or press the F5 key.

    Excel opens with the app inserted into the worksheet.

  2. Select a cell in the worksheet, and then choose the Write button at the bottom of the app.

    The doWrite() function executes to insert a single-column table into the worksheet with values set to Berlin, Rome, and Tokyo.

  3. With the table still selected, choose the Bind button.

    The doBind() function executes to create a table binding named MyCities on the current selection, and then re-renders the map with pins for Berlin, Rome, and Tokyo.

  4. Choose the Event button.

    The doEvent() function executes to create an event handler for the DataChanged event of the MyCities binding, which is associated with the Cities table.

  5. Change or add values to the Cities table to change the cities "pinned" in the map. For example, change "Tokyo" to "Madrid", and then add "Paris" and "Vienna" to the table. As each change or addition is made, the MyHandler function executes to update the pins on the map.

  6. Choose the Settings button.

    The doSettings() function executes to toggle the value of the current display mode, saves the currentMode setting in the Settings object property bag, and then re-renders the map using the currentMode display setting. When you save, close, and re-open the workbook, the saved display mode is used.

Show:
© 2014 Microsoft