/// <summary>
/// Location container for LAT-LON coordinates. They are doubles
/// </summary>  
public struct mylocation
       {
/// <summary>
/// Holds the latitude information
/// </summary>
public double latitude;
/// <summary>
/// Holds the longitude information
/// </summary>
public double longitude;
 }

/// <summary>    

/// This routine converts the national characters to standard.
/// Goolgle api can parse the national characters, but my experinece is better change to standard chars.
/// This method is especially converts hungarian national chars.
/// If You use another national chars, please overwrite this methos.
/// </summary>
/// <param name="be">Input parameter for convert</param>
/// <returns>
/// string
/// </returns>

public string convuri(string be) 
{
    if (be == "")
    {
        return null;
public string convuri(string be)
        {
if (be == "")
            {

return null;
            }
// Output will be the input in default case
string ret = be;
// Old, national chars to be changed
string[] oldchars = { "á", "í", "ű", "ő", "ü", 
  "ö", "ú", "ó", "é", " ", "Á", "Í", 
  "Ű", "Ő", "Ü", "Ö", "Ú", "Ó", "É", "." };
// New chars to change for
string[] newchars = { "a", "i", "u", "o", "u", "o", 
  "u", "o", "e", " ", "A", "I", "U", 
  "O", "U", "O", "U", "O", "E", "." };
// Changing cycle
for (int i = 0; i < oldchars.Length - 1; i++)
            {
                ret = ret.Replace(oldchars[i], newchars[i]);
            }
//converts the space to hex 20 (32) that means the space in URL
            ret = ret.Replace(
" ", "%20");
 return ret;
        }
    }
    string ret = be;
    string[] oldchars = { "á", "í", "ű", "ő", "ü", "ö", 
      "ú", "ó", "é", " ", "Á", "Í", "Ű", 
      "Ő", "Ü", "Ö", "Ú", "Ó", "É", "." };
    string[] newchars = { "a", "i", "u", "o", "u", "o", 
      "u", "o", "e", " ", "A", "I", "U", 
      "O", "U", "O", "U", "O", "E", "." };
    for (int i = 0; i < oldchars.Length - 1; i++)
    {
        ret = ret.Replace(oldchars[i], newchars[i]);
    }
    //converts the space to hex 20 (32) that means the space in URL
    ret = ret.Replace(" ", "%20");
    return ret;
}

//Define wbrequest, and webresponse. Response will be an xml document.

System.Net.HttpWebRequest webRequest;
System.Net.HttpWebResponse webResponse;

//create address from excel                  
string cim = hsz.Trim() + " " + utca.Trim() + " " + zip.Trim() + 
             " " + varos.Trim() + " " + orszag.Trim();

//convert and clean url  
  cim = convuri(cim);
  Uri myuri = new Uri("http://maps.googleapis.com/maps/api/geocode/xml?address=" + 
                      cim + "&sensor=true");

 webRequest = (HttpWebRequest)(WebRequest.Create(myuri));
 webRequest.Credentials = CredentialCache.DefaultCredentials;

 // Companies mostly uses proxy servers. If You use it from home, proxy is not necessary
 WebProxy p = new WebProxy(textBox2.Text, Convert.ToInt32(textBox3.Text));
 //You can use default (login) credentials, or some else credential, like this
 // p.Credentials = new NetworkCredential("user","password","domain")

p.Credentials = CredentialCache.DefaultCredentials;
if (checkBox1.Checked)
{
    webRequest.Proxy = p;
}

webRequest.Proxy = p;
webRequest.Method = "GET";
webRequest.ContentType = "text/xml";
                   
//Call a normal WEB request. Response will be a standard xml file.
webResponse = (System.Net.HttpWebResponse)(webRequest.GetResponse());
webResponse.GetResponseStream();
StreamReader sr = new StreamReader(webResponse.GetResponseStream());

//Create a new xml document named  resxml
XmlDocument resxml = new XmlDocument();
//Load stream content into resxml variable
                resxml.LoadXml(sr.ReadToEnd());
//Closes the stream.
               sr.Close();
//Converts xml content into a string
string sss = resxml.InnerXml.ToString();
//Not needed
               resxml = null;
//Parsing the xml contetnt with linq.
// xml nodes result - geometry - location and values what we needed is lat, and lon. 
//There are NOT attributes.
//<result>
//....
//    <geometry>
//...
//        <location>
//             <lat> 47,19745464</lat>
//             <lon> 19,15678664</lon>
//         </location>
//etc                
// </summary>
XDocument xdoc = XDocument.Parse(sss);
//Linq question. Result will be a LAT-LON struct, like mylocatin struct
var aaas = from aaa in xdoc.Descendants("result").Descendants("geometry").Descendants("location")
select new
 {
    lat = aaa.Element("lat").Value,
    lon = aaa.Element("lng").Value
  };
//Cycle for get linq query's values, and put into a mylocation typed struct.
foreach (var aaa in aaas)
{
    ret.latitude = Convert.ToDouble(aaa.lat);
    ret.longitude = Convert.ToDouble(aaa.lon);
}

//Call Excel interop. The excel application represents oApp variable
Microsoft.Office.Interop.Excel.Application oApp = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = null;
Worksheet ws = null;

// Excel will NOT visible is this routine
oApp.Visible = false;
// Its maybe necessary when use excel from a program.
oApp.UserControl = false;

// The best method to change the culture info to en-Us when using excel.
// I had problems with excel interoperation with another culture info.
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Globalization.CultureInfo Cli = new System.Globalization.CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCulture = Cli;

// Creates a stringbuilder method. The result kml will be created into sb variable.
StringBuilder sb = new StringBuilder();
// Creates the kml file's header. In fact kml is a special xml.
// The own map's name can give in textbox5.text on form.

sb.AppendLine("");
sb.AppendLine("<kml xmlns="http://www.google.com/earth/kml/2">");
sb.AppendLine("<document>");
sb.AppendLine("<name>" + textBox5.Text + "</name>");

try
{
    // Opens an excel file, name given in textbox1.text
    wb = oApp.Workbooks.Open(textBox1.Text);
    // Sets worksheets object
    Sheets sheets = wb.Worksheets;
    // To set diplayalerts to fals is necessary. If you not set to false,
    // excel will ask to "Save document" before quiting.
    oApp.DisplayAlerts = false;
    // Sets the first worksheet to work on.
    ws = (Worksheet)sheets.get_Item(1);

    // Ask userd range of excel. 
    Range ur = ws.UsedRange;
    Range cell;
    // Declares some variables
    string orszag;
    string nev;
    string varos;
    string utca;
    string hsz;
    string zip;
    string lat;
    string lon;

    // Cycle to read excel row by row in used range

    for (int i = 1; i <= ur.Cells.Rows.Count; i++)
    {
        // First column is the name of POI. Writes into the form into label1
        cell = (Range)ws.Cells[i, 1];
        nev = cell.Value.ToString();
        label1.Text = nev;

        // Second column column is the ZIP of POI
        cell = (Range)ws.Cells[i, 2];
        zip = cell.Value.ToString();

        // Third column column is the varos of POI
        cell = (Range)ws.Cells[i, 3];
        varos = cell.Value.ToString();
        // Fourth column is the street of POI
        cell = (Range)ws.Cells[i, 4];
        utca = cell.Value.ToString();
        // Fifth column is the housenumber of POI
        cell = (Range)ws.Cells[i, 5];
        hsz = cell.Value.ToString();
        // Sixth column is the country of POI
        cell = (Range)ws.Cells[i, 6];
        orszag = cell.Value.ToString();
        // Seventh column is the LAT value of POI
        cell = (Range)ws.Cells[i, 7];
        lat = cell.Value.ToString();
        // Eighth column is the LAT value of POI
        cell = (Range)ws.Cells[i, 8];
        lon = cell.Value.ToString();

        // Appends parameters into sb stringbuilder in kml format
        sb.AppendLine("<placemark>");
        sb.AppendLine("<name>" + nev + "</name>");
        sb.AppendLine("<description>" + varos.Trim() + ", " + zip.Trim() + 
          " " + utca.Trim() + " " + hsz.Trim() + "</description>");
        sb.AppendLine("<point>");
        sb.AppendLine("<coordinates>" + lon + "," + lat + ",0</coordinates>");
        sb.AppendLine("</point>");
        sb.AppendLine("</placemark>");

    }
}
catch (Exception ex)
{
     // In case of any problem shows a message
     MessageBox.Show(ex.Message);
}
// Finally will do this with excel either everithing was good or not.
finally
{
    
    // Saves back the worksheet
    wb.Save();
    // Quits from excel
    oApp.Quit();
    
    // Sets back excelapp displayalerts property to true
     oApp.DisplayAlerts = true;

    // Sets back the original cultureinfo
    System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;


    // Releases COM objects,  the workbook, worksheet and excelapp objects
    Marshal.ReleaseComObject(ws);
    Marshal.ReleaseComObject(wb);
    Marshal.ReleaseComObject(oApp);

}

// Appends the end of kml into sb stringbuilder 

sb.AppendLine("</document>");
sb.AppendLine("</kml>");