如何：为地理编码添加 Excel 插件（批量地理编码）
4.33/5 (3投票s)
用于 Excel 的简单插件，可为结构化或非结构化地址生成地理编码。
引言
本简单教程介绍了如何添加 MS Excel 加载项，并通过三个服务提供商获取地理编码，即位置点（纬度、经度）或验证地址的准确性
- 必应地图 API。
- Google 地理编码 API。
- SmartyStreets LiveAddress API。
这个简单的解决方案可以帮助您避免在不同界面之间跳转来完成相同的任务。更重要的是，它是批量进行的。因此，只需在 MS Excel 中点击一下按钮，其中包含所有地址（无论结构化还是非结构化），就可以使用 API 获取地理编码（纬度、经度）。
背景
任何有 .NET 平台经验的开发人员都应该能快速掌握本文。在开始编写代码之前，请确保您对 C# 语言有扎实的了解。并且在实际从上述地理编码服务提供商获取地理编码之前，您需要注册并获取个人 API 密钥以访问它们。
除了 Visual Studio，您还需要
- VSTO (Visual Studio Tools for Office) 入门
使用代码
我将尽量简短，不会涉及 API 本身的技术细节，因为我将介绍三个不同的地理编码服务提供商。我将提供 API 文档的链接，以便您可以阅读它们并根据您的业务需求进行实现。 让我们开始编写代码。
步骤 1：获取 API 密钥。
1. 对于 Microsoft Bing Maps API，您需要 Azure 帐户。在 30 天内使用 ₹13,300 的额度开始免费使用任何 Azure 产品，如果您想学习 Azure 平台并实现真实世界的示例，这是一个不错的选择。创建 Azure 帐户并进入门户仪表板后，您需要在搜索框中输入 Bing Maps API 来搜索它。建议的链接应将您带到面向企业的必应地图 API。创建新的 API 并导航到密钥管理，然后复制主密钥。
必应地图 API 本身是一个非常庞大的库，我们可以探索的部分是地理编码数据流 API，其文档链接 在此。
2. 对于 Google Maps GeoCoding API，您需要注册 Google 开发者帐户。注册并获取 API 密钥对于 Google 开发者帐户来说更容易。只需按照此 链接 并获取 API 密钥。相同的链接也可用于文档。
3. 对于 SmartyStreets LiveAddress API，您需要注册 SmartyStreets 帐户，注册后进入帐户页面，您应该会在左侧看到 API 密钥链接，并且已经有一个 API 密钥可供使用。请记住！SmartyStreets 仅提供美国地址的免费 API，每月仅限 250 次请求。当然，您总是可以选择升级。这些人主要处理地址相关的服务，因此他们在该领域提供的东西比其他两个提供商都要多。他们有用于验证地址列表的 Web 界面，这有时对业务人员（而不是开发人员）很有用。
步骤 2：进入 Visual Studio
首先，让我们添加一个简单的类，以相同的方式获取和设置所有服务提供商的纬度和经度值。所有 API 返回的响应都不同，但我们希望得到通用的输出。
添加类 LatitudeLongitude
public class LatitudeLongitude
{
public int Id { get; set; }
public string Latitude { get; set; }
public string Longitude { get; set; }
}
现在添加 Views 文件夹，然后添加用户控件，如下所示。
为了简单起见，并为业务用户提供配置选项，让他们可以选择使用哪个 API，我们将添加一个配置文件，即 App.Config。这里我将提供总共 5 个可配置设置
- GeoLocationService：用户想要选择的服务，目前只能输入三个选项：Microsoft、Google、LiveAddress
- BingMapsAPIKey：您从 Azure 门户的必应地图企业版获取的密钥。
- GoogleGeoCodingAPIKey：您从 Google 开发者指南获取的密钥。
- SmartyStreetsAPIKey：您从 SmartyStreets 仪表板获取的 AuthCode。
- SmartyStreetsAuthToken：您从 SmartyStreets 仪表板获取的授权令牌。
您的配置文件应该看起来像这样。
现在，通过从工具箱拖动一个按钮控件，并将其命名为 generateBtn。
现在，通过双击按钮或从按钮控件的属性窗口进入，为此按钮添加点击事件。
步骤 3：添加服务
让我们先构建消耗 API 的逻辑，我们已经从服务提供商那里生成了 API 密钥，然后我们将回到按钮的点击事件。
消耗 Google Maps Geocode API
为此，请创建 Services 文件夹，然后首先添加一个名为 GoogleAPI 的类。
然后添加 GetGoogleAPILocations 方法，该方法接受以下参数
- addressList
- outputList
由于 Google 不提供批量地理编码 API，因此我们必须遍历 AddressList 中构建的所有地址，逐个读取响应，并将它们添加到 outputList 中。
Google 地理编码 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;
}
如果您遵循 Google Maps API 文档中的 XML 响应，您会发现我们关注的点位于以下 XPATH 中。
GeocodeResponse>result>geometry>location>lat 和 GeocodeResponse>result>geometry>location>lng
因此，上面的代码正是这样做的，并将它们添加到我们的 LatitudeLongitude 类列表中。
现在，以类似的方式，让我们添加必应地图 API。
消耗 Bing Maps API
必应地图 API 的优点在于它提供了许多微服务，您可以根据您的需求选择您真正需要的功能。这里我们有两个选项，我们可以选择
http://dev.virtualearth.net/REST/v1/Locations?q=http://spatial.virtualearth.net/REST/v1/dataflows/geocode- 这个提供批量地理编码
添加名为 GetBingGeoLocations 的方法，并带有以下参数
- addressList
- outputList
因此，就像我们为 Google API 所做的一样，我们遵循相同的逻辑，但这里有一个棘手的地方。Microsoft Bing Maps API 服务提供了 DataContracts，可以帮助我们将响应以对象方式读取。所以这里我们需要 BingMapsRESTToolkit 的额外引用，您可以从 Nuget 包管理器中获取，一旦完成，您的 Response 对象就应该可以访问了。
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;
}
这有点复杂，因为这个 API 要求我们将数据以文本文件、CSV 文件或 PIPELINE 格式文件的形式发送。
为了方便起见，我将创建 CSV 格式的文件，并将其添加到流中，然后在处理完成后读取数据。在此期间，我会不断检查作业的处理是否完成。一旦状态完成，我们就需要下载结果并将其放入成功和失败的类别中。
既然这需要我们发送 csv，那么我们构建的 csv 的格式显然有讲究。虽然你们中的许多人可能会想，为什么我们要从 Excel 文件中已有的数据中构建 CSV 文件，原因很简单，我们实际上是在 Excel 中创建加载项。
添加 GetBingGeoBatchLocations 方法，并带有以下参数
- addressList
- 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;
}
如果您遵循文档，您会看到 CreateJob、DownloadResults 和 CheckStatus 是我从文档中提取的方法。 示例代码
所以你可以直接复制粘贴，完全无需更改。
同样，您可以轻松地使用与 Google Maps API 相同的逻辑来消耗 SmartyStreets Live Address 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"
好了，您已经准备好了三个不同的 API，可以插入点击事件，该事件以 AddressList 作为输入，并返回 LatitudeLongitude 类型的 OutputList。
步骤 4：将点击事件与 API 连接
按钮的点击事件看起来像这样
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);
}
所以所有发生的事情是三个简单的步骤
- 构建 addresslist
- CallGeoLocationAPI
- FillExcelData
BuildAddressList 方法。在这里，我们首先需要确保我们不会读取超出最后一行。
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 = "";
}
}
添加 CallGeoLocationAPI 方法
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;
}
添加 FillExcelData 方法
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++;
}
}
对于本文，我使用的 Excel 格式是
结果
就是这样！
关注点
由于必应地图 API 确实提供批量地理编码，而我需要显示的结果是 Excel 格式而不是 CSV 输出，因此我不得不通过添加正则表达式来修改 DownResults 方法，以便从每一行读取纬度和经度，然后按预期顺序将其添加到输出列表中。如果您实际上要使用必应地图 API，您会发现返回的结果顺序可能不是您期望的，这在您想直接将结果放入 Excel 时可能会有问题，这就是为什么我必须在 LatitudeLongitude 类中创建 Id 属性，并将其与输入的 CSV 文件进行映射。
历史
我已通过添加缺失的截图和一些关于可配置性的要点更新了本文。
祝您编码愉快！！