<%@ Page Language="C#"
AutoEventWireup="true"
CodeBehind="WebForm1.aspx.cs"
Inherits="GEO_CODE.WebForm1"
%>
DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
>
<head id="Head1"
runat="server">
<title>Google Maps GeoCodertitle>
<style type="text/css">
.style1 {
width: 100%;
}
.style2
{
width: 104px;
}
style>
head>
<body>
<form id="form1"
runat="server">
<div>
<table class="style1">
<tr>
<td class="style2">
<asp:FileUpload ID="FileUpload1"
runat="server"
/> td>
<td>
<asp:Button ID="Button1"
runat="server"
Text="submit"
onclick="Button1_Click"
/>
<asp:Button ID="Button2"
runat="server"
Text="export"
onclick="Button2_Click" />
td>
tr>
<tr>
<td class="style2">
td>
<td>
<asp:GridView ID="GridView1"
runat="server">
asp:GridView>
td>
tr>
table>
div>
form>
body>
html>
public static DataTable
xlstable = null;
public static string baseUri
= "http://maps.googleapis.com/maps/api/geocode/xml?latlng={0},{1}&sensor=false";
protected void Button1_Click(object
sender, EventArgs e)
{
string filename =
FileUpload1.PostedFile.FileName;
FileUpload1.PostedFile.SaveAs(Server.MapPath("~/upload" + @"\"
+ filename));
string
connectionstring = string.Empty;
if (Path.GetExtension(filename).ToUpper() == ".XLS")
{
connectionstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + Server.MapPath("~/upload"
+ @"\" + filename) + ";Extended Properties=Excel 4.0;";
}
if (Path.GetExtension(filename).ToUpper() == ".XLSX")
{
connectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" + Server.MapPath("~/upload"
+ @"\" + filename) + ";Extended Properties=Excel 8.0";
}
DataTable dtdata = new DataTable();
OleDbConnection connExcel
= new OleDbConnection(connectionstring);
OleDbCommand cmdExcel =
new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection =
connExcel;
//Get the name
of First Sheet
connExcel.Open();
DataTable
dtExcelSchema;
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 [Sheet1$]";
oda.SelectCommand =
cmdExcel;
oda.Fill(dtdata);
connExcel.Close();
foreach (DataColumn dc in
dtdata.Columns)
{
RetrieveFormatedAddress(dtdata);
break;
}
}
public void RetrieveFormatedAddress(DataTable
dtlatlog)
{
DataTable dtaddress
= new DataTable();
string requestUri
= string.Empty;
dtaddress.Columns.Add("Lat", typeof(string));
dtaddress.Columns.Add("Log", typeof(string));
dtaddress.Columns.Add("Route", typeof(string));
dtaddress.Columns.Add("Sub_Locality", typeof(string));
dtaddress.Columns.Add("Locality", typeof(string));
dtaddress.Columns.Add("City", typeof(string));
dtaddress.Columns.Add("State", typeof(string));
dtaddress.Columns.Add("Address", typeof(string));
dtaddress.Columns.Add("Google_Lat", typeof(string));
dtaddress.Columns.Add("Google_Log", typeof(string));
for (int i = 0; i < dtlatlog.Rows.Count; i++)
{
int routecount
= 0;
int
localitycount = 0;
int Citycount
= 0;
int Statecount
= 0;
int
Sublocalitycount = 0;
requestUri = string.Format(baseUri, dtlatlog.Rows[i]["log"].ToString(), dtlatlog.Rows[i]["lat"].ToString());
DataSet ds = new DataSet(); DataRow row = null;
row = dtaddress.NewRow();
using (WebClient wc = new
WebClient())
{
DataTable dt = new DataTable();
WebRequest wreq = WebRequest.Create(requestUri);
WebResponse wres =
wreq.GetResponse();
XmlTextReader r = new XmlTextReader(wres.GetResponseStream());
ds.ReadXml(r);
row["Lat"]
= dtlatlog.Rows[i]["lat"].ToString();
row["Log"]
= dtlatlog.Rows[i]["log"].ToString();
if
(ds.Tables.Count > 1)
{
if
(ds.Tables.Contains("address_component"))
{
for (int j = 0; j < ds.Tables["type"].Rows.Count;
j++)
{
if
(ds.Tables["type"].Rows[j]["type_Text"].ToString() == "route")
{
if
(ds.Tables["type"].Rows[j]["address_component_Id"].ToString() != "")
{
if
(routecount == 0)
{
row["Route"]
= ds.Tables["address_component"].Rows[Convert.ToInt32(ds.Tables["type"].Rows[j]["address_component_Id"])]["long_name"];
routecount++;
}
}
}
if
(ds.Tables["type"].Rows[j]["type_Text"].ToString() == "locality")
{
if
(ds.Tables["type"].Rows[j]["address_component_Id"].ToString() != "")
{
if
(localitycount == 0)
{
row["Locality"]
= ds.Tables["address_component"].Rows[Convert.ToInt32(ds.Tables["type"].Rows[j]["address_component_Id"])]["long_name"];
localitycount++;
}
}
}
if
(ds.Tables["type"].Rows[j]["type_Text"].ToString() == "administrative_area_level_2")
{
if
(ds.Tables["type"].Rows[j]["address_component_Id"].ToString() != "")
{
if (Citycount
== 0)
{
row["City"]
= ds.Tables["address_component"].Rows[Convert.ToInt32(ds.Tables["type"].Rows[j]["address_component_Id"])]["long_name"];
Citycount++;
}
}
}
if
(ds.Tables["type"].Rows[j]["type_Text"].ToString() == "administrative_area_level_1")
{
if
(ds.Tables["type"].Rows[j]["address_component_Id"].ToString() != "")
{
if
(Statecount == 0)
{
row["State"]
= ds.Tables["address_component"].Rows[Convert.ToInt32(ds.Tables["type"].Rows[j]["address_component_Id"])]["long_name"];
Statecount++;
}
}
}
if
(ds.Tables["type"].Rows[j]["type_Text"].ToString() == "sublocality")
{
if
(ds.Tables["type"].Rows[j]["address_component_Id"].ToString() != "")
{
if
(Sublocalitycount == 0)
{
row["Sub_Locality"]
= ds.Tables["address_component"].Rows[Convert.ToInt32(ds.Tables["type"].Rows[j]["address_component_Id"])]["long_name"];
Sublocalitycount++;
}
}
}
}
}
if
(ds.Tables.Contains("result"))
row["Address"]
= ds.Tables["result"].Rows[0]["formatted_address"];
else
row["Address"]
= "NO Result";
row["Google_Lat"]
= ds.Tables["location"].Rows[0]["lat"];
row["Google_Log"]
= ds.Tables["location"].Rows[0]["lng"];
}
else
row["Address"]
= "NO Result";
dtaddress.Rows.Add(row);
dtaddress.AcceptChanges();
}
Thread.Sleep(1000);
}
GridView1.DataSource =
dtaddress;
GridView1.DataBind();
xlstable = dtaddress;
}
protected void Button2_Click(object
sender, EventArgs e)
{
//DataTable
xlstable = dtaddress1;
string
xlsFileName = "GEO_City_Report";
HttpContext context = HttpContext.Current;
context.Response.Clear();
foreach (DataColumn col in
xlstable.Columns)
{
context.Response.Write(col.ColumnName
+ "\t");
}
context.Response.Write(Environment.NewLine);
foreach (DataRow row in
xlstable.Rows)
{
for (int i = 0; i < xlstable.Columns.Count; i++)
{
string content =
row[i].ToString().Replace("\t", string.Empty) + "\t";
context.Response.Write(content);
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType
= "application/vnd.ms-excel";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + xlsFileName + ".xls");
try
{
context.Response.End();
//HttpContext.Current.ApplicationInstance.CompleteRequest();
}
catch (Exception ex)
{
string error =
ex.Message.ToString();
}
finally
{
this.Dispose();
}
}
