How To Add Excel Add-In For GeoCoding (Batch GeoCoding)

Introduction

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

Bing Maps API.
Google GeoCode API.
SmartyStreets LiveAddress API.

This simple solution helps you get rid of jumping around different interfaces to do the same job. More importantly, it does it in batches. So just by one click of a button right inside MS Excel where all the addresses (no matter structured or unstructured) are mentioned, we can have geocodes (Latitude, Longitude) also 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 achieve the geocodes from the above mentioned geocoding service providers, you need to sign up and get personal API keys to access them. The thing you need other than Visual Studio is:

1. VSTO (Visual Studio Tools for Office) for 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.

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

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

For Google Maps GeoCoding API, you need to sign up for Google developer's account. Sign up and getting it 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.

For SmartyStreets LiveAddress API, you need to sign up for a SmartyStreets account and once you do that and land on the Account page, you should see API keys link on the left-hand side and one API key should already be there to use. Remember! SmartyStreets provides free API for US addresses only and that too is 250 per request/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 providers do in this area. They have a web interface for validating the 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 a simple class to get and set the Latitude and Longitude values across all service providers in a similar fashion. All API's return different responses, 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 the 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,

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

Here's how your config file should look like.

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

Now, add a 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,

addressList
outputList

Since Google does not provide batch geocoding API, we have to loop over all the addresses that we built in AddressList and we will read the responses 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 the Bing Maps API is that it provides 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 provides batch geocoding. Let's add a method for consuming dev.virtualearth.net API.

Add a method named GetBingGeoLocations with the following parameters.

addressList
outputList

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

using BingMapsRESTToolkit;
public static List < LatitudeLongitudeClass > GetBingGeoLocations(List < string > AdressList, List < LatitudeLongitudeClass > outputList) {
string response;
foreach(var item in AdressList) {
string requestUri = "http://dev.virtualearth.net/REST/v1/Locations?q=" + Uri.EscapeDataString(item) + "&key=AmQIF1IE_78hrjIdOBG3nxHiz6SMATgQUCVeGtDDgQdC3CzFSIWYlMtO9o-cbBdu";
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 LatitudeLongitudeClass {
Latitude = location.Point.Coordinates[0].ToString(),
Longitude = location.Point.Coordinates[1].ToString()
});
}
return outputList;
}

Let's add a method for consuming http://spatial.virtualearth.net/REST/v1/dataflows/geocode API. This is bit complex, because this API requires us to send the data in text file or CSV file or PIPELINE formatted file.

For ease, 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 the job is completed or not. Once the status is completed, we need to download the results and put it into Success and Failed manner.

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

Add GetBingGeoBatchLocations method with following parameters,

addressList
outputList
1. public static List < LatitudeLongitude > GetBingGeoBatchLocations(List < string > AdressList, List < LatitudeLongitude > outputList) {
2. int id = 1;
3. //before your loop
4. var csv = new StringBuilder();
5. 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");
6. csv.AppendLine(firstRow);
7. var key = ConfigurationManager.AppSettings["BingMapsAPIKey"];
8. foreach(var item in AdressList) {
9. var first = id;
10. var second = item;
11. var third = item;
12. var newLine = string.Format("{0},{1},{2},,,,,", first, second, third);
13. csv.AppendLine(newLine);
14. id++;
15. }
16. var paths = Path.GetTempPath();
17. var dataFilePath = Path.Combine(paths, "SampleInput.csv");
18. File.WriteAllText(dataFilePath, csv.ToString());
19. string dataflowJobLocation = CreateJob(dataFilePath, "csv", key, string.Empty);
20. Console.WriteLine("Dataflow Job Location: {0}", dataflowJobLocation);
21. if (File.Exists(dataFilePath)) {
22. File.Delete(dataFilePath);
23. }
24. //Continue to check the dataflow job status until the job has completed
25. DownloadDetails statusDetails = new DownloadDetails();
26. do {
27. statusDetails = CheckStatus(dataflowJobLocation, key);
28. Console.WriteLine("Dataflow Job Status: {0}", statusDetails.jobStatus);
29. if (statusDetails.jobStatus == "Aborted") throw new Exception("Job was aborted due to an error.");
30. Thread.Sleep(30000); //Get status every 30 seconds
31. }
32. while (statusDetails.jobStatus.Equals("Pending"));
33. //When the job is completed, get the results
34. //Two files are created to record the results:
35. // Success.xml contains the data that was successfully geocoded
36. // Failed.mxl contains the data that could not be geocoded
37. outputList = DownloadResults(statusDetails, key, outputList);
38. return outputList;
39. }

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 in three simple steps,

Build the addresslist
CallGeoLocationAPI
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, the 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 the output list in same order as expected. If you are actually going to use Bing Maps API, you will see, the results returned are not in the 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.