How To: Add Excel AddIn for GeoCoding (Batch GeoCoding)

Introduction

This simple tutorial is about adding MS Excel AddIn and get the geocodes i.e. Location Points (Latitude, Longitude) or to verify the accuracy of address using three Service Providers:

1. Bing Maps API.
2. Google GeoCode API.
3. SmartyStreets LiveAddress API.

This simple solution helps you get rid of jumping around into different interfaces to do same job. More importantly, it does in batch wise. So just by one click of button right inside from MS Excel where all the addresses(no matter structured or unstructured) are mentioned, can also have geo codes (Latitude, Longitude) using APIs.

Background

Any developer having experience on .NET platform should be able to grasp this article quickly. Before you jump into the code, make sure you have good knowledge of C# language. And before you could actually acheive the geocodes from above mentioned geocoding service providers you need to sign up and get personal API keys to access them. 

Things you need other than visual Studio is:

1. VSTO (Visual Studio Tools for Office) Getting Started

Using the code

I will try to be brief and won't be touching any technical details of API itself, as I am covering three different Service Providers for getting GeoCodes. I will provide links to API documentation so that you can read them and implement the same based on your business needs. Let's get started with code.

Step 1: Get the API keys.

1. For Microsoft Bing Maps API, you need Azure account. Start free with ₹13,300 in credit to use on any Azure products for 30 days, which is good deal if you want to learn Azure platform and implement the real world examples. Once you create Azure account and lands on portal dashboard, you need to search for Bing Maps API by typing in search box. The suggested link should land you on Bing Maps API for enterprise. Create new API and navigate to key managment and copy the primary key.

Bing Maps API itself is very vast library to explore, the part we are going to touch is Geocode Dataflow API and its documentation link is here.

2. For Google Maps GeoCoding API, you need to sign up for Google developer's account. Sign up and getting is easier with google developer's account. Just follow this link and get the API key. The same link can be used for documentation as well.

3. For SmartyStreets LiveAddress API, you need to sign up for SmartyStreets account and once you do that and lands on Account page, you should see API keys link on left hand side and one API key should already be there to use. Remember! SmartyStreets provide free API for US addresses only and that too 250request/month. Though you always get the option to upgrade. These guys are pretty much dealing with address related services so they provide a lot more than what other two provider do in this area. They have web interface for validating list of addresses, which do come handy sometimes for business guys and not for developers.

Step 2: Get into Visual Studio

First let's just add simple class to get and set the Latitude and Longitude values accross all service providers in similar fashion. All API's returns different response, but we want to make it generic output.

Add class LatitudeLongitude

public class LatitudeLongitude
{
     public int Id { get; set; }
     public string Latitude { get; set; }
     public string Longitude { get; set; }
}

Now Add Views folder and then add user control as shown belows.

 

For our simplicity, and to provide the configurability to business user, so that they can choose what API they want to go with, we are going to add a configuration file i.e. App.Config. Here I am going to provide total 5 configurable settings

1. GeoLocationService: The service which user want to choose, as of now, only three options can be entered, Microsoft, Google, LiveAddress
2. BingMapsAPIKey: The key you got from Bing Maps for Enterprise on azure portal.
3. GoogleGeoCodingAPIKey: the key you got from Google developer's guide.
4. SmartyStreetsAPIKey: The AuthCode you got from SmartyStreets dashboard.
5. SmartyStreetsAuthToken: The authorization token you got from SmartyStreets dashboard.

Here's how you config file should look like.

Now Add a button control by dragging from Toolbox and name it generateBtn.

Now add click event to this button either by double clicking on button or by going from Properties window of button control.

Step 3: Add Services

Let's just first build the logic to consume the API's for which we generated API from service providers and then we will come back to click event of button.

Consuming Google Maps Geocode API

To do that, create Services folder and first add class named GoogleAPI

Then Add GetGoogleAPILocations method that take following parameters:

1. addressList
2. outputList

Since google does not provide batch geocoding api, so we have to loop over all the addresses that we built in AddressList and we will read the response one by one and keep adding the same in outputList.

Google geocoding API URL: 

https://maps.googleapis.com/maps/api/geocode/xml?key=<your API key>&address=<address you want to geocode>&sensor=false

 public static List<LatitudeLongitudeClass> GetGoogleAPILocations(List<string> AdressList, List<LatitudeLongitudeClass> outputList)
        {
            var key = ConfigurationManager.AppSettings["GoogleGeoCodingAPIKey"];
            string requestUri;
            foreach (var item in AdressList)
            {
                requestUri = string.Format("https://maps.googleapis.com/maps/api/geocode/xml?key={0}&address={1}&sensor=false", key, Uri.EscapeDataString(item));
                WebRequest request = WebRequest.Create(requestUri);
                WebResponse response = request.GetResponse();

                XDocument xdoc = XDocument.Load(response.GetResponseStream());

                XElement result = xdoc.Element("GeocodeResponse").Element("result");
                XElement locationElement = result.Element("geometry").Element("location");
                var lat = locationElement.Element("lat").Value.ToString();
                var lng = locationElement.Element("lng").Value.ToString();
                outputList.Add(new LatitudeLongitudeClass { Latitude = lat, Longitude = lng });

            }
            return outputList;
        }

If you follow the XML response in Google Maps API documentation, you will see our point of concern is in following XPATH.

GeocodeResponse>result>geometry>location>lat and GeocodeResponse>result>geometry>location>lng

so above code is doing exactly that and adding the same in our list of LatitudeLongitude class.

Now following similar approach let's add Bing Maps API.

Consuming Bing Maps API

Beauty of Bing Maps API is that it provide many micro services, and based on your requirement you get to choose what you actually need. Here we have two options, either we can choose:

• http://dev.virtualearth.net/REST/v1/Locations?q=
• http://spatial.virtualearth.net/REST/v1/dataflows/geocode - this one provide batch geocoding

Add method named GetBingGeoLocations with following parameters:

1. addressList
2. outputList

So just like what we did for Google API, we follow same logic, but here is something tricky. Microsoft Bing Maps API service provide a DataContracts that helps us to read the response in object manner. so here we need extra reference of BingMapsRESTToolkit which you can get it from Nuget package Manager, once you do that your Response object should be accessible.

using BingMapsRESTToolkit;

public static List<LatitudeLongitude> GetBingGeoLocations(List<string> addressList, List<LatitudeLongitude> outputList)
        {
            string response;
            string requestUri;
            var key = ConfigurationManager.AppSettings["BingMapsAPIKey"];
            foreach (var item in addressList)
            {
                requestUri = "http://dev.virtualearth.net/REST/v1/Locations?q=" + Uri.EscapeDataString(item) + "&key=" + key;
                using (var client = new WebClient())
                {
                    response = client.DownloadString(requestUri);
                }
                DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(Response));
                Response mapResponse;
                using (var es = new MemoryStream(Encoding.Unicode.GetBytes(response)))
                {
                    mapResponse = (ser.ReadObject(es) as Response); //Response is one of the Bing Maps DataContracts
                }

                Location location = (Location)mapResponse.ResourceSets.First().Resources.First();
                outputList.Add(new LatitudeLongitude
                {
                    Latitude = location.Point.Coordinates[0].ToString(),
                    Longitude = location.Point.Coordinates[1].ToString()
                });
            }
            return outputList;
        }

 

This is bit complex, because this API require us to send the data in text file or CSV file or PIPELINE formatted file.

For ease purpose I am going to create the CSV format file, and add it the to stream and then read the data once processing is done, in between I will keep checking whether the processing of job is completed or not, once status is completed, we need to download the results and put it into Success and Failed manner. 

As this require us to send a csv, so it is obvious there has to be some format in which we have build our csv. Though most of you might think, why we are building CSV file from data that is already there in excel file, and reason is simple, we are actually creating a Add-In right within the Excel. 

Add GetBingGeoBatchLocations method with following parameters:

1. addressList
2. outputList

public static List<LatitudeLongitude> GetBingGeoBatchLocations(List<string> AdressList, List<LatitudeLongitude> outputList)
        {
            int id = 1;
            //before your loop
            var csv = new StringBuilder();
            var firstRow = string.Format("{0},{1},{2},{3},{4},{5},{6},{7}", "Id", "GeocodeRequest/Address/AddressLine", "GeocodeRequest/Address/FormattedAddress", " GeocodeResponse/Point/Latitude", " GeocodeResponse/Point/Longitude", " StatusCode", " FaultReason", " TraceId");
            csv.AppendLine(firstRow);
            var key = ConfigurationManager.AppSettings["BingMapsAPIKey"];
            foreach (var item in AdressList)
            {

                //in your loop
                var first = id;
                var second = item;
                var third = item;
                //Suggestion made by KyleMit
                var newLine = string.Format("{0},{1},{2},,,,,", first, second, third);
                csv.AppendLine(newLine);
                id++;
            }
            var paths = Path.GetTempPath();
            var dataFilePath = Path.Combine(paths, "SampleInput.csv");
            File.WriteAllText(dataFilePath, csv.ToString());

            string dataflowJobLocation = CreateJob(dataFilePath, "csv", key, string.Empty);
            Console.WriteLine("Dataflow Job Location: {0}", dataflowJobLocation);
            if(File.Exists(dataFilePath))
            {
                File.Delete(dataFilePath);
            }

            //Continue to check the dataflow job status until the job has completed
            DownloadDetails statusDetails = new DownloadDetails();
            do
            {
                statusDetails = CheckStatus(dataflowJobLocation, key);
                Console.WriteLine("Dataflow Job Status: {0}", statusDetails.jobStatus);
                if (statusDetails.jobStatus == "Aborted")
                    throw new Exception("Job was aborted due to an error.");
                Thread.Sleep(30000); //Get status every 30 seconds
            }
            while (statusDetails.jobStatus.Equals("Pending"));

            //When the job is completed, get the results
            //Two files are created to record the results:
            //  Success.xml contains the data that was successfully geocoded
            //  Failed.mxl contains the data that could not be geocoded

            outputList = DownloadResults(statusDetails, key, outputList);
            return outputList;
        }

If you are following the documentation, you will see the CreateJob, DownloadResults and CheckStatus are methods that I picked from documentation. Sample Code

So you can just copy paste from that as it is, no change at all.

 

So similarly you can easily consume SmartyStreets Live Address API using same logic as for Google Maps API.

API URL:

https://international-street.api.smartystreets.com/verify?auth-id=1<auth-id>&auth-token=<auth token>&freeform=<address string>"&country=<country name> +"&geocode=true&method=GET"

 

There you go, you have three different API ready to be plugged with click event that takes AddressList as input and returns OutputList of LatitudeLongitude type.

Step 4: Plug your click event with API

Click event of button looks like:

private void generateBtn_Click(object sender, EventArgs e)
        {
            var tbl = new System.Data.DataTable();
            Excel.Worksheet CurrentSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
            Excel.Range SheetFirstRow = CurrentSheet.get_Range("A1");

            Excel.Range last = CurrentSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
            Excel.Range range = CurrentSheet.get_Range("A1", last);

            int lastUsedRow = last.Row;
            int lastUsedColumn = last.Column;

            List<string> AdressList = new List<string>();
            BuildAddressList(CurrentSheet, AdressList, lastUsedRow);
            List<LatitudeLongitude> outputList = new List<LatitudeLongitude>();
            var serviceProvider = ConfigurationManager.AppSettings["GeoLocationService"];
            outputList = CallGeoLocationAPI(AdressList, outputList, serviceProvider);
            FillExcelData(CurrentSheet, outputList.OrderBy(x=>x.Id).ToList(), lastUsedRow);

        }

So all is happening is three simple steps:

1. Build the addresslist
2. CallGeoLocationAPI
3. FillExcelData

BuildAddressList Method. Here first we need to make sure we are not reading beyond last row.

private static void BuildAddressList(Excel.Worksheet CurrentSheet, List<string> AdressList,int lastUsedRow)
        {
            string test = "";
            for (int i = 2; i <= lastUsedRow; i++)
            {
                for (int j = 1; j <= 5; j++)
                {
                    object rangeObject = CurrentSheet.Cells[i, j];
                    Microsoft.Office.Interop.Excel.Range ran = (Microsoft.Office.Interop.Excel.Range)rangeObject;
                    object Address = ran.Value2 ?? "";
                    string cellV = Address.ToString();
                    test = test + " " + cellV;
                }
                if (test != "")
                {
                    AdressList.Add(test);
                }
                test = "";
            }
        }

Add CallGeoLocationAPI method

private static List<LatitudeLongitude> CallGeoLocationAPI(List<string> AdressList, List<LatitudeLongitude> outputList, string serviceProvider)
        {
            switch (serviceProvider)
            {
                case "Google":
                    outputList = GoogleAPI.GetGoogleAPILocations(AdressList, outputList);
                    break;
                case "Microsoft":
                    outputList = BingMapsAPI.GetBingGeoBatchLocations(AdressList, outputList);
                    break;
                case "LiveAddress":
                    outputList = LiveAddressAPI.GetLiveAddressLocations(AdressList, outputList);
                    break;
                default:
                    break;
            }

            return outputList;
        }

Add FillExcelData method

private static void FillExcelData(Excel.Worksheet CurrentSheet, List<LatitudeLongitude> outputList,int lastUsedRow)
        {
            int counter = 0;
            for (int i = 2; i <= lastUsedRow; i++)
            {
                object rangeObject1 = CurrentSheet.Cells[i, 6];
                Microsoft.Office.Interop.Excel.Range ran1 = (Microsoft.Office.Interop.Excel.Range)rangeObject1;
                ran1.Value2 = outputList[counter].Latitude;

                object rangeObject2 = CurrentSheet.Cells[i, 7];
                Microsoft.Office.Interop.Excel.Range ran2 = (Microsoft.Office.Interop.Excel.Range)rangeObject2;
                ran2.Value2 = outputList[counter].Longitude;
                counter++;
            }
        }

 

For this article, excel format I used is:

 

Results

That's it.!! 

Points of Interest

Since Bing Maps API does provide batch geocoding, and results I needed to show was in excel and not csv output, I had to tweak the DownResults method by adding regular expression to read the Latitude and Longitude from each line and then add it to output list in same orde as expected. If you are actually going to use Bing Maps API, you will see, the results returned are not in same order as you would expect, and that can create problem if you want to put the results in excel directly, so that is why I had to create Id property in LatitudeLongitude class and map it with input csv files.

History

I have updated the article by adding the missing screenshots and few points for configurability.

 

Happy coding!!