Adding markers to Bing Maps from Excel

Put addresses on a map as markers get it now

We've already looked at how to geoCode addresses using various APIs and how to create maps with Google Maps from Excel data.  Now let's look at using Bing Maps  to put markers on a Map using an Excel worksheet as input. As usual you can find the completed example in the googleMapping.xlsm download. Before running this please check the parameter worksheet and change the filename value  for the place that the application will generate a web page to something relevant for your machine. Look for the  filename in the Bing Marker html section of the Parameter Worksheet.



Bing Maps versus Google Maps

Well the Maps are certainly beautiful, and the interface is nice. However the API is not as straightforward as Google, Yahoo or even OVI.  Because, as usual, Microsoft is 'different' to everyone else, I had to compromise a bit on the pop up boxes and mouseovers since I wanted the input data to be common between each version. My biggest gripe was with the separation of box contents into html type content, and regular content.  Since for the other APIs I have used html for the contents I wanted to leave it that way. When you use the Html content for Bing, all the nice features like a pointer on the box and standard formatting go away so I had to mess around with styles etc to get a presentable box, and still didn't end up where the others took me without too much effort. There was also a documentation error where the mouseleave event (as everyone else calls it), it called mouseout by Microsoft, yet it is documented as mouseleave. Between that and the html business what should have been a half hour job became a couple of hours.  This along with the poor geoCoding in Bing means I probably wont spend much more time with this one. Having said that the major differences were dealt with in the javaScript and the Excel stuff remains the same.

Here is the Google Code
' This will take the geocoded data and mark it
Option Explicit
Public Sub googleMarkingExample()
    ' turns out these are all the same except for the paramer block to use
    genericMarking cMarkerHtml

End Sub

versus the Bing Code
' This will take the geocoded data and mark it
Option Explicit
Public Sub bingMarkingExample()
    ' turns out these are all the same except for the paramer block to use
    genericMarking cBingMarkerHtml

End Sub

Bing needs a key, so I used the one that is on the parameter sheet. Please get your own and replace the one on the sheet with your one.

How does it look

The Bing Maps are gorgeous, but the popups are disappointing since I had to build my own to keep the input data standard between APIs. If I spent a little longer I could probably improve them a bit.



Compare Bing Maps to others

I've implemented this exact same thing on other APIs as below.  Take a look to compare. My recommendation is to stick with Google Maps.


For help and more information join our forumfollow the blogfollow me on twitter
The generated file is shown below.

<!DOCTYPE html>
<html>
<head>
<meta name='viewport' content='initial-scale=1.0, user-scalable=no' /><style type='text/css'>
  html { height: 100% }
  body { height: 100%; margin: 0; padding: 0 }
  #map_canvas { height: 100%}
</style>
<script type='text/javascript' src='http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=7.0'></script>
<script type='text/javascript'>
function mcpherGetCredentials() {
  return 'AmVoAsOUH9QHTL4-Zc7qF7MjU8tm7zR9rDdXsA5QRsgudEwRJmz_a_NkGMmTUn3I';}
function mcpherDataPopulate() { 
var mcpherData = {"cJobject":[{"title":"Acme","content":"\<b\>Acme\</b\>\<br\>1600 Amphitheatre Pkwy\<br\> Mountain View\<br\> CA 94043\<br\> USA\<br\>","lat":"37.4220279","lng":"-122.0840677"}
,{"title":"Smiths","content":"\<b\>Smiths\</b\>\<br\>Boulevard de Waterloo 16\<br\> 1000 City of Brussels\<br\> Belgium\<br\>","lat":"50.838199","lng":"4.3597606"}
,{"title":"Schneider","content":"\<b\>Schneider\</b\>\<br\>Athens\<br\> Greece\<br\>","lat":"37.97918","lng":"23.716647"}
,{"title":"Jones","content":"\<b\>Jones\</b\>\<br\>Edinburgh\<br\> City of Edinburgh\<br\> UK\<br\>","lat":"55.9501755","lng":"-3.1875359"}
,{"title":"google russia","content":"\<b\>google russia\</b\>\<br\>Ulitsa Balchug\<br\> 7\<br\> Moscow\<br\> Russian Federation\<br\> 115035\<br\>","lat":"55.7469456","lng":"37.6262074"}
,{"title":"google ghana","content":"\<b\>google ghana\</b\>\<br\>Independence Ave\<br\> Accra\<br\> Ghana\<br\>","lat":"5.5659043","lng":"-0.1934785"}
,{"title":"Google India Pvt Ltd","content":"\<b\>Google India Pvt Ltd\</b\>\<br\>DLF Cyber City\<br\> Gurgaon\<br\> Haryana\<br\> India\<br\>","lat":"28.4917362","lng":"77.093139"}
,{"title":"google argentina","content":"\<b\>google argentina\</b\>\<br\>Buenos Aires\<br\> Capital Federal\<br\> Argentina\<br\>","lat":"-34.6084175","lng":"-58.3731613"}
,{"title":"google guangzhou","content":"\<b\>google guangzhou\</b\>\<br\>Tianhe District\<br\> Guangzhou\<br\> Guangdong\<br\> China\<br\> 510620\<br\>","lat":"23.1352029","lng":"113.3250301"}
,{"title":"Google Cairo","content":"\<b\>Google Cairo\</b\>\<br\>Heliopolis\<br\> Cairo\<br\> Egypt\<br\>","lat":"30.0909837","lng":"31.322709"}
,{"title":"","content":"","lat":"","lng":""}
 ]

};
return mcpherData; };
//------
function initialize() {
    var mcpherData = mcpherDataPopulate();


    if (mcpherData.cJobject.length > 0) {
     var myOptions = {
   center: new Microsoft.Maps.Location(parseFloat(mcpherData.cJobject[0].lat), 
    parseFloat(mcpherData.cJobject[0].lng)), 
        mapTypeId: Microsoft.Maps.MapTypeId.auto,
        zoom : 2,
   credentials : mcpherGetCredentials()
     };

    // get parameters if any
     var qparams = mcpherGetqparams();
     if (qparams['zoom']) myOptions['zoom'] = parseInt(qparams['zoom']);
    
  // create the map
      var mapContainer = document.getElementById("map_canvas");
      map = new Microsoft.Maps.Map(mapContainer,
   {credentials: myOptions['credentials'],
    center: myOptions['center'],
    zoom: myOptions['zoom'],
    mapTypeId: myOptions['mapTypeId']}
   );
     map.entities.clear(); 
    
// add the excel data

     for ( var i = 0 ; i < mcpherData.cJobject.length;i++) 
                    mcpherAddMarker ( map, mcpherData.cJobject[i] );
     }
};
function mq() {
 return String.fromCharCode(34);
}
function mcpherAddMarker(gMap, cj ) {

var pushpin= new Microsoft.Maps.Pushpin(map.getCenter(), null); 

  infoBoxStyle = mq() + 
   'background-color:White; border: medium solid DarkOrange; font-family: Sans-serif; width:auto; font-size: 70%;' + mq();

  hoverBoxStyle = mq() + 
   'background-color:Cornsilk;  border-width:0; width:auto; font-size: 70%; font-family: Sans-serif;' + mq();

  var gp = new Microsoft.Maps.Location(parseFloat(cj.lat), parseFloat(cj.lng));
  var marker = new Microsoft.Maps.Pushpin(gp,null); 
  gMap.entities.push(marker);
  var hoverBox = new Microsoft.Maps.Infobox(gp,  { 
  htmlContent: '<div style='+ hoverBoxStyle + '>' + cj.title + '</div>',visible:false} ); 
  gMap.entities.push(hoverBox);

  Microsoft.Maps.Events.addHandler(marker, 'mouseover', function() {
    hoverBox.setOptions({visible:true}); });
  Microsoft.Maps.Events.addHandler(marker, 'mouseout', function() {
    hoverBox.setOptions({visible:false}); 
                   infoBox.setOptions({visible:false}); });
  
 if (cj.content){
   var infoBox = new Microsoft.Maps.Infobox(gp, { 
  htmlContent: '<div style='+ infoBoxStyle + '>' + cj.content + '</div>',visible:false} ); 
   gMap.entities.push(infoBox);
        Microsoft.Maps.Events.addHandler(marker, 'click', function() {
    infoBox.setOptions({visible:true}); });
        Microsoft.Maps.Events.addHandler(marker, 'mouseout', function() {
    hoverBox.setOptions({visible:false}); 
                   infoBox.setOptions({visible:false}); });
      }
 else
    Microsoft.Maps.Events.addHandler(marker, 'mouseout', function() {
    hoverBox.setOptions({visible:false});  });

  return marker;

};

function mcpherGetqparams(){
      var qparams = new Array();
   var htmlquery = window.location.search.substring(1);
   var htmlparams = htmlquery.split('&');
   for ( var i=0; i < htmlparams.length;i++) {
     var k = htmlparams[i].indexOf('=');
     if (k > 0) qparams[ htmlparams[i].substring(0,k) ] = decodeURI(htmlparams [i].substring(k+1));
 }    
 return qparams;
   };  
</script>
</head>
<body onload="initialize()">
  "Testing Excel to Bing  mapping - http://rambings.mcpher.com"
  <div id="map_canvas" style="width:100%; height:100%"></div>
</body>
</html>



Comments