Find Geo Location Address based on Latitude and Longitude value from Excel and update those in Excel using C# and VB.Net in ASP.Net

Hi sumitgarg,

Here i have created sample that fullfill your requirement.

For this download the Geocoding dll from the below link and add the reference in your project.

Geocoding.dll v3.6.0

Also this sample requires ClosedXML DLL File, for that you can refer below articles.

• http://aspsnippets.com/Articles/Import-data-from-Excel-to-GridView-in-ASPNet.aspx
• http://aspsnippets.com/Articles/Export-GridView-Data-to-Excel-using-OpenXml-in-ASPNet.aspx

HTML

<div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="OnRowDataBound">
        <Columns>
            <asp:TemplateField HeaderText="Id">
                <ItemTemplate>
                    <asp:Label runat="server" ID="lblId" Text='<%# Eval("SiNo") %>' />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Latitude">
                <ItemTemplate>
                    <asp:Label runat="server" ID="lblLatitude" Text='<%# Eval("Latitude") %>' />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Longitude">
                <ItemTemplate>
                    <asp:Label runat="server" ID="lblLongitude" Text='<%# Eval("Longitude") %>' />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Address">
                <ItemTemplate>
                    <asp:Label ID="lblAddress" runat="server" Text='<%# Eval("Address") %>' />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <br />
    <asp:Button Text="Download Updated Excel" runat="server" OnClick="UpdatedExcel" />
</div>

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGridView();
    }
}

/// <summary>
/// Bind Gridview.
/// </summary>
private void BindGridView()
{
    string filePath = Server.MapPath("~/Files/GeoLocation.xls");
    string Extension = "." + filePath.Split('.')[1];
    string isHDR = "YES";
    string conStr = "";
    switch (Extension)
    {
        case ".xls": //Excel 97-03
            conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            break;
        case ".xlsx": //Excel 07 and above
            conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
            break;
    }
    conStr = String.Format(conStr, filePath, isHDR);
    OleDbConnection connExcel = new OleDbConnection(conStr);
    OleDbCommand cmdExcel = new OleDbCommand();
    OleDbDataAdapter oda = new OleDbDataAdapter();
    DataTable dt = new DataTable();
    cmdExcel.Connection = connExcel;

    //Get the name of First Sheet
    connExcel.Open();
    DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
    connExcel.Close();

    //Read Data from First Sheet
    connExcel.Open();
    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
    oda.SelectCommand = cmdExcel;
    oda.Fill(dt);
    connExcel.Close();
    dt.Columns.Add("Address");
    ViewState["Data"] = dt;

    //Bind Data to GridView
    GridView1.Caption = Path.GetFileName(filePath);
    GridView1.DataSource = dt;
    GridView1.DataBind();
}

/// <summary>
/// Get address corresponding to the latitude and longitude.
/// </summary>
protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        double lat = Convert.ToDouble((e.Row.FindControl("lblLatitude") as Label).Text);
        double lng = Convert.ToDouble((e.Row.FindControl("lblLongitude") as Label).Text);
        IGeocoder geocoder = new GoogleGeocoder(ConfigurationManager.AppSettings["APIKey"]);
        IEnumerable<Address> addresses = geocoder.ReverseGeocode(lat, lng);
        (e.Row.FindControl("lblAddress") as Label).Text = ((Geocoding.Google.GoogleAddress[])(addresses))[0].FormattedAddress;
    }
}

/// <summary>
/// Download updated excel containing address corresponding to the latitude and longitude.
/// </summary>
protected void UpdatedExcel(object sender, EventArgs e)
{
    DataTable dt = new DataTable("GridView_Data");
    foreach (TableCell cell in GridView1.HeaderRow.Cells)
    {
        dt.Columns.Add(cell.Text);
    }
    foreach (GridViewRow row in GridView1.Rows)
    {
        dt.Rows.Add();
        for (int i = 0; i < row.Cells.Count; i++)
        {
            dt.Rows[dt.Rows.Count - 1][i] = (row.Cells[i].Controls.OfType<Label>().ToList()[0]).Text;
        }
    }
    using (XLWorkbook wb = new XLWorkbook())
    {
        wb.Worksheets.Add(dt);

        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx");
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs)
	If Not IsPostBack Then
		BindGridView()
	End If
End Sub

''' <summary>
''' Bind Gridview.
''' </summary>
Private Sub BindGridView()
	Dim filePath As String = Server.MapPath("~/Files/GeoLocation.xls")
	Dim Extension As String = "." + filePath.Split("."C)(1)
	Dim isHDR As String = "YES"
	Dim conStr As String = ""
	Select Case Extension
		Case ".xls"
			'Excel 97-03
			conStr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
			Exit Select
		Case ".xlsx"
			'Excel 07 and above
			conStr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
			Exit Select
	End Select
	conStr = [String].Format(conStr, filePath, isHDR)
	Dim connExcel As New OleDbConnection(conStr)
	Dim cmdExcel As New OleDbCommand()
	Dim oda As New OleDbDataAdapter()
	Dim dt As New DataTable()
	cmdExcel.Connection = connExcel

	'Get the name of First Sheet
	connExcel.Open()
	Dim dtExcelSchema As DataTable = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
	Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
	connExcel.Close()

	'Read Data from First Sheet
	connExcel.Open()
	cmdExcel.CommandText = (Convert.ToString("SELECT * From [") & SheetName) + "]"
	oda.SelectCommand = cmdExcel
	oda.Fill(dt)
	connExcel.Close()
	dt.Columns.Add("Address")
	ViewState("Data") = dt

	'Bind Data to GridView
	GridView1.Caption = Path.GetFileName(filePath)
	GridView1.DataSource = dt
	GridView1.DataBind()
End Sub

''' <summary>
''' Get address corresponding to the latitude and longitude.
''' </summary>
Protected Sub OnRowDataBound(sender As Object, e As GridViewRowEventArgs)
	If e.Row.RowType = DataControlRowType.DataRow Then
		Dim lat As Double = Convert.ToDouble(TryCast(e.Row.FindControl("lblLatitude"), Label).Text)
		Dim lng As Double = Convert.ToDouble(TryCast(e.Row.FindControl("lblLongitude"), Label).Text)
		Dim geocoder As IGeocoder = New GoogleGeocoder(ConfigurationManager.AppSettings("APIKey"))
		Dim addresses As IEnumerable(Of Address) = geocoder.ReverseGeocode(lat, lng)
		TryCast(e.Row.FindControl("lblAddress"), Label).Text = DirectCast(addresses, Geocoding.Google.GoogleAddress())(0).FormattedAddress
	End If
End Sub

''' <summary>
''' Download updated excel containing address corresponding to the latitude and longitude.
''' </summary>
Protected Sub UpdatedExcel(sender As Object, e As EventArgs)
	Dim dt As New DataTable("GridView_Data")
	For Each cell As TableCell In GridView1.HeaderRow.Cells
		dt.Columns.Add(cell.Text)
	Next
	For Each row As GridViewRow In GridView1.Rows
		dt.Rows.Add()
		For i As Integer = 0 To row.Cells.Count - 1
			dt.Rows(dt.Rows.Count - 1)(i) = (row.Cells(i).Controls.OfType(Of Label)().ToList()(0)).Text
		Next
	Next
	Using wb As New XLWorkbook()
		wb.Worksheets.Add(dt)

		Response.Clear()
		Response.Buffer = True
		Response.Charset = ""
		Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
		Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx")
		Using MyMemoryStream As New MemoryStream()
			wb.SaveAs(MyMemoryStream)
			MyMemoryStream.WriteTo(Response.OutputStream)
			Response.Flush()
			Response.[End]()
		End Using
	End Using
End Sub

Namespace

C#

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web.UI.WebControls;
using ClosedXML.Excel;
using Geocoding;
using Geocoding.Google;

VB.Net

Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Linq
Imports System.Web.UI.WebControls
Imports ClosedXML.Excel
Imports Geocoding
Imports Geocoding.Google

Web.config

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.0"/>
  </system.web>
  <connectionStrings>
    <add name="Excel03ConString" 
    connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
    <add name="Excel07ConString" 
    connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
  </connectionStrings>
  <appSettings>
    <add key="APIKey" value="Your Google API Key"/>
  </appSettings>
</configuration>

Input (Excel Data)

OutPut (After downloaded the excel)