Plot Marker on Google map form excel sheet
Below the .aspx code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<!DOCTYPE html>
<html>
<head>
<%-- Google api --%>
<script src="http://maps.google.com/maps/api/js?sensor=false"
type="text/javascript"></script>
</head>
<body>
<div id="map" style="width: 1290px; height: 570px;"></div>
<script type="text/javascript">
<%=Locations %>
var map = new google.maps.Map(document.getElementById('map'), {
zoom: 3,
center: new google.maps.LatLng(28.692997,77.233429),
mapTypeId: google.maps.MapTypeId.ROADMAP
});
var infowindow = new google.maps.InfoWindow();
var marker, i;
for (i = 0; i < locations.length; i++) {
marker = new google.maps.Marker({
position: new google.maps.LatLng(locations[i][1], locations[i][2]),
map: map
});
google.maps.event.addListener(marker, 'click', (function(marker, i) {
return function() {
infowindow.setContent(locations[i][0]);
infowindow.open(map, marker);
}
})(marker, i));
}
</script>
</body>
</html>
Note : -
<%=Locations %> here is code represent the lat ,long and adresses of location which is gerated from code behind page i.e dynamically.
For simple static Google map you can replace the
<%=Locations %> by
var locations = [
['9043, Malviya Nagar', 28.548338, 77.213287,1],
['Road No :1, Banjara Hills', 17.4126362, 78.4502041,2],
['Near Clock Tower, Market Street', 17.5169711, 78.5150626,3];
Format of Location : -
['address', lat, lon,ID]
for static google map no need of code behind page.
Code behind page
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Data.OleDb;
public partial class Default3 : System.Web.UI.Page
{
string OledbConnectionString;
public string Desc;
public double lat;
public double lon;
public string GoogleAPIKey;
public int ZoomLevel;
public String Locations;
protected void Page_Load(object sender, EventArgs e)
{
// Excel 2007
string location = Server.MapPath("~/Excel/") + ConfigurationManager.AppSettings["filename"].ToString().Trim();
ZoomLevel = Int32.Parse("10");
OledbConnectionString = string.Empty;
// Get extension of excel file
string ext = Path.GetExtension(ConfigurationManager.AppSettings["filename"].ToString().Trim());
if (ext == ".xls")
{
OledbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + location + ";Extended Properties=Excel 8.0;";
}
else if (ext == ".xlsx")
{
OledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + location + ";Extended Properties=Excel 12.0;";
}
else
{ }
try
{
OleDbConnection objConn = null;
objConn = new OleDbConnection(OledbConnectionString);
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
OleDbDataAdapter da = new OleDbDataAdapter("select Latitude as [Lat], Longitude as [Long] ,Address as [Address] from [Sheet2$]", objConn);
//Latitude Longitude Address
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
lat = 24.20689;
lon = 78.046875;
int i = 0;
Locations = "var locations = [";
foreach (DataRow dr in dt.Rows)
{
i++;
if (dr["lat"].ToString().Trim() != "")
{
Locations += Environment.NewLine + "['" + dr["Address"].ToString().Trim().Replace("/", "").Replace("'", "").Replace("\n", "").Replace("@", "").Replace("&", "") + "', " + dr["Lat"].ToString().Trim() + ", " + dr["Long"].ToString().Trim() + "," + i + "],";
//
}
if (i == 1200)
{
break;
}
}
Locations = Locations.Substring(0, Locations.Length - 1);
Locations +="];";
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
}
Put appsetting in webconfingWebconfig
Note : - put hospital.xlsx file in excel folder.....file having three column adress ,latitude, longitude
<appSettings>
<add key="class" value="server=.;database=Hcl;uid=sa;pwd=sa123"/>
<add key="filename" value="hospital.xlsx"/>
</appSettings>
No comments:
Post a Comment