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.
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)
SiNo |
Latitude |
Longitude |
1 |
19.137309 |
72.8677942 |
2 |
19.1405116 |
72.8421555 |
3 |
18.9322453 |
72.8264378 |
4 |
19.113645 |
72.8697339 |
OutPut (After downloaded the excel)
Id |
Latitude |
longitude |
Address |
1 |
19.137309 |
72.8677941999999 |
5, Poonam Nagar, MMRDA Colony, Poonam Nagar, Jogeshwari East, Mumbai, Maharashtra 400093, India |
2 |
19.1405116 |
72.8421554999999 |
6, Masjid Aqsa Rd, Shastri Nagar, Jogeshwari West, Mumbai, Maharashtra 400102, India |
3 |
18.9322453 |
72.8264378 |
125, Jamshedji Tata Road, Churchgate, Mumbai, Maharashtra 400020, India |
4 |
19.113645 |
72.8697339 |
Rk Mandir Rd, MIDC Industrial Estate, Bhim Nagar, Andheri East, Mumbai, Maharashtra 400069, India |