<%@ 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 DataTablexlstable = null;
public static string baseUri = "http://maps.googleapis.com/maps/api/geocode/xml?latlng={0},{1}&sensor=false";
protected void Button1_Click(objectsender, EventArgs e)
{
string filename = FileUpload1.PostedFile.FileName;
FileUpload1.PostedFile.SaveAs(Server.MapPath("~/upload" + @"\"+ filename));
stringconnectionstring = 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();
DataTabledtExcelSchema;
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 indtdata.Columns)
{
RetrieveFormatedAddress(dtdata); break;
}
}
public void RetrieveFormatedAddress(DataTabledtlatlog)
{
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;
intlocalitycount = 0;
int Citycount = 0;
int Statecount = 0;
intSublocalitycount = 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 = newWebClient())
{
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(objectsender, EventArgs e)
{
//DataTable xlstable = dtaddress1;
stringxlsFileName = "GEO_City_Report";
HttpContext context = HttpContext.Current;
context.Response.Clear();
foreach (DataColumn col inxlstable.Columns)
{
context.Response.Write(col.ColumnName + "\t");
}
context.Response.Write(Environment.NewLine);
foreach (DataRow row inxlstable.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();
}
}
02:34
Unknown
