private void loadXML(string file) { try { addressList.Clear(); Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWB; Excel.Worksheet xlSht; xlWB = xlApp.Workbooks.Open(file); xlSht = (Excel.Worksheet)xlWB.Worksheets[1]; Excel.Range last = xlSht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); var arrData = (object[, ])xlSht.get_Range("A1", last).Value; xlWB.Close(false); xlApp.Quit(); int rowCount = arrData.GetUpperBound(0); int colCount = arrData.GetUpperBound(1); for (int i = 2; i <= rowCount; i++) { if (arrData[i, 17] != null && arrData[i, 18] != null) { AddressListElement aAddressListElement = new AddressListElement(); aAddressListElement.row = i; aAddressListElement.latid = getStringFromXML(arrData[i, 17]).Replace(',', '.'); aAddressListElement.longit = getStringFromXML(arrData[i, 18]).Replace(',', '.'); addressList.Add(aAddressListElement); } } resultInfoElement aResultInfoElement = new resultInfoElement(); aResultInfoElement.fileName = file.Split('\\').Last(); aResultInfoElement.filePath = file; aResultInfoElement.addressCount = addressList.Count; aResultInfoElement.comlete = false; resultList.Add(aResultInfoElement); resultInfoElementBindingSource.ResetBindings(true); addressListElementBindingSource.ResetBindings(true); } catch (Exception ex) { throw new Exception("Ошибка загрузки Excel файла: " + ex.Message); } }
async Task reverseGeocodinYandex(int addressIndex, bool CheckCity) { AddressListElement aAddressListElement = addressList[addressIndex]; aAddressListElement.isChecking = true; addressList[addressIndex] = aAddressListElement; addressListElementBindingSource.ResetItem(addressIndex); checkingAddresses++; try { if (aAddressListElement.latid != null & aAddressListElement.latid != "" & aAddressListElement.longit != null & aAddressListElement.longit != "") { var httpClientHandler = new HttpClientHandler(); // Finally, create the HTTP client object var client = new HttpClient(handler: httpClientHandler, disposeHandler: true); client.BaseAddress = new Uri("https://geocode-maps.yandex.ru"); client.DefaultRequestHeaders.Add("User-Agent", "C# console program"); client.Timeout = TimeSpan.FromMilliseconds(3000); //geocode-maps.yandex.ru/1.x/?apikey=d4a52bbe-5323-4938-ad75-d228bf49210b&geocode=47.595025,42.095995 var uri = "/1.x/?"; string apiKey = "d4a52bbe-5323-4938-ad75-d228bf49210b"; //string alat = aAddressListElement.latid.Contains("°") ? convertGrad(aAddressListElement.latid) : aAddressListElement.latid; //string alog = aAddressListElement.longit.Contains("°") ? convertGrad(aAddressListElement.longit) : aAddressListElement.longit; //uri = uri + "&lat=" + alat; //uri = uri + "&lon=" + alog; //uri = uri + "apikey=" + apiKey + "&geocode=" + alog + "," + alat; uri = uri + "apikey=" + apiKey + "&geocode=" + aAddressListElement.longit + "," + aAddressListElement.latid; uri = uri.Replace(" ", ""); var response = await client.GetAsync(uri); if (response.IsSuccessStatusCode) { var responseString = await response.Content.ReadAsStringAsync(); XmlDocument doc = new XmlDocument(); string area = ""; string hamlet = ""; string road = ""; string house_number = ""; doc.LoadXml(responseString); XmlNodeList nodeList = doc.GetElementsByTagName("Component"); foreach (XmlNode node in nodeList) { string kindString = checkXmlNode(node.FirstChild); switch (kindString) { case "area": area = checkXmlNode(node.LastChild); break; case "locality": hamlet = checkXmlNode(node.LastChild); break; case "street": road = checkXmlNode(node.LastChild); break; case "house": house_number = checkXmlNode(node.LastChild); break; } } if (CheckCity) { if (hamlet != "") { yandexCity = hamlet; } else { yandexCity = area; } aAddressListElement.isChecking = false; addressList[addressIndex] = aAddressListElement; addressListElementBindingSource.ResetItem(addressIndex); return; } if (hamlet != "") { aAddressListElement.city = hamlet; } else { aAddressListElement.city = area; } if (aAddressListElement.city != "") { if (validCity.ContainsKey(aAddressListElement.city)) { validCity[aAddressListElement.city] = validCity[aAddressListElement.city] + 1; } else { validCity.Add(aAddressListElement.city, 1); } } aAddressListElement.road = road; aAddressListElement.house_number = house_number; aAddressListElement.Checked = true; checkedAddresses++; aAddressListElement.isChecking = false; addressList[addressIndex] = aAddressListElement; addressListElementBindingSource.ResetItem(addressIndex); } } else { aAddressListElement.Checked = true; checkedAddresses++; addressList[addressIndex] = aAddressListElement; addressListElementBindingSource.ResetItem(addressIndex); } } catch (Exception ex) { aAddressListElement.Checked = false; aAddressListElement.isChecking = false; addressList[addressIndex] = aAddressListElement; addressListElementBindingSource.ResetItem(addressIndex); } }
async Task reverseGeocoding(int addressIndex, int proxyIndex) { try { var stopWatch = Stopwatch.StartNew(); //var proxy = new WebProxy("88.247.153.104", 8080); ProxyListElement aProxyListElement = proxyLixt[proxyIndex]; var proxy = aProxyListElement.proxy; AddressListElement aAddressListElement = addressList[addressIndex]; aAddressListElement.isChecking = true; addressList[addressIndex] = aAddressListElement; addressListElementBindingSource.ResetItem(addressIndex); checkingAddresses++; if (aAddressListElement.latid != null & aAddressListElement.latid != "" & aAddressListElement.longit != null & aAddressListElement.longit != "") { // Now create a client handler which uses that proxy var httpClientHandler = new HttpClientHandler(); httpClientHandler.Proxy = proxy; httpClientHandler.UseProxy = useProxy; // Finally, create the HTTP client object var client = new HttpClient(handler: httpClientHandler, disposeHandler: true); client.BaseAddress = new Uri("https://nominatim.openstreetmap.org"); client.DefaultRequestHeaders.Add("User-Agent", "C# console program"); client.Timeout = TimeSpan.FromMilliseconds(3000); //nominatim.openstreetmap.org/reverse?lat=42.095995&lon=47.595025&accept-language=ru&zoom=18&[email protected]&addressdetails=1format=xml nominatim.openstreetmap.org/reverse?accept-language=ru&zoom=18&format=xml&lat=44.948674&lon=45.854249 //nominatim.openstreetmap.org/reverse?accept-language=ru&zoom=18&format=xml&lat=41.882855&lon=48.072434" var uri = "/reverse?accept-language=ru&zoom=18&format=xml"; string alat = aAddressListElement.latid.Contains("°")? convertGrad(aAddressListElement.latid) : aAddressListElement.latid; string alog = aAddressListElement.longit.Contains("°") ? convertGrad(aAddressListElement.longit) : aAddressListElement.longit; uri = uri + "&lat=" + alat; uri = uri + "&lon=" + alog; uri = uri.Replace(" ", ""); try { var response = await client.GetAsync(uri); if (response.IsSuccessStatusCode) { var responseString = await response.Content.ReadAsStringAsync(); XmlDocument doc = new XmlDocument(); try { doc.LoadXml(responseString); var hamlet = checkXmlNode(doc.DocumentElement.SelectSingleNode("/reversegeocode/addressparts/hamlet")); var city = checkXmlNode(doc.DocumentElement.SelectSingleNode("/reversegeocode/addressparts/city")); var county = checkXmlNode(doc.DocumentElement.SelectSingleNode("/reversegeocode/addressparts/county")); var road = checkXmlNode(doc.DocumentElement.SelectSingleNode("/reversegeocode/addressparts/road")); var house_number = checkXmlNode(doc.DocumentElement.SelectSingleNode("/reversegeocode/addressparts/house_number")); var fullAddress = checkXmlNode(doc.DocumentElement.SelectSingleNode("/reversegeocode/result")); if (hamlet != "") { aAddressListElement.city = hamlet; } else if (city != "") { aAddressListElement.city = city; } else { aAddressListElement.city = county; } if (useYandexCity & yandexCity != "") { aAddressListElement.city = yandexCity; } if (aAddressListElement.city != "") { if (validCity.ContainsKey(aAddressListElement.city)) { validCity[aAddressListElement.city] = validCity[aAddressListElement.city] + 1; } else { validCity.Add(aAddressListElement.city, 1); } } aAddressListElement.road = road; aAddressListElement.house_number = house_number; aAddressListElement.Checked = true; checkedAddresses++; Console.WriteLine(fullAddress); } catch (Exception ex) { Console.WriteLine(responseString + " " + ex.Message); } } else { if (response.StatusCode == System.Net.HttpStatusCode.BadRequest) { aAddressListElement.Checked = true; checkedAddresses++; } loging(2, "Ошибка запроса, проверьте координаты в строке " + aAddressListElement.row); Console.WriteLine(response.StatusCode); } } catch (Exception ex) { if (useProxy) { aProxyListElement.raiting--; } Console.WriteLine(ex.Message); } long responseTime = stopWatch.ElapsedMilliseconds; aAddressListElement.isChecking = false; aProxyListElement.responseTime = responseTime; proxyLixt[proxyIndex] = aProxyListElement; proxyListElementBindingSource.ResetItem(proxyIndex); } else { aAddressListElement.Checked = true; checkedAddresses++; } aAddressListElement.isChecking = false; addressList[addressIndex] = aAddressListElement; addressListElementBindingSource.ResetItem(addressIndex); checkingAddresses--; await Task.Delay(10); } catch (Exception ex) { AddressListElement aAddressListElement = addressList[addressIndex]; aAddressListElement.isChecking = false; addressList[addressIndex] = aAddressListElement; addressListElementBindingSource.ResetItem(addressIndex); } }
private void loadXML(string file) { try { addressList.Clear(); Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWB; Excel.Worksheet xlSht; Excel.Worksheet xlSht2; Excel.Worksheet xlSht3; xlWB = xlApp.Workbooks.Open(file); xlSht = (Excel.Worksheet)xlWB.Worksheets[1]; xlSht2 = (Excel.Worksheet)xlWB.Worksheets[2]; xlSht3 = (Excel.Worksheet)xlWB.Worksheets[3]; Excel.Range last = xlSht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); Excel.Range last2 = xlSht2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); Excel.Range last3 = xlSht3.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); var arrData = (object[, ])xlSht.get_Range("A1", last).Value; var arrData2 = (object[, ])xlSht2.get_Range("A1", last2).Value; var arrData3 = (object[, ])xlSht3.get_Range("A1", last3).Value; xlWB.Close(false); xlApp.Quit(); int rowCount = arrData.GetUpperBound(0); int colCount = arrData.GetUpperBound(1); int rowCount2 = arrData2.GetUpperBound(0); int rowCount3 = arrData3.GetUpperBound(0); int errShift = 0; //int maxVal = 0; //int minVal = Int32.MaxValue; for (int i = 2; i <= rowCount; i++) { bool outOfRange = (i + errShift) > rowCount2; string f2Value = "0"; string l2Value = "0"; while (!outOfRange & f2Value == "0") { f2Value = getStringFromXML(arrData2[i + errShift, 6]); l2Value = getStringFromXML(arrData2[i + errShift, 12]); l2Value = l2Value.IndexOf(',') != -1 ? l2Value.Substring(0, l2Value.IndexOf(',')) : l2Value; if (f2Value == "0") { errShift++; } outOfRange = (i + errShift) > rowCount2; int intVal = 0; try { intVal = Convert.ToInt32(l2Value); } catch (Exception Ex) { intVal = 0; } if (intVal != 0) { if (maxVal < intVal) { maxVal = intVal; } if (minVal > intVal) { minVal = intVal; } } } if (arrData[i, 15] != null && arrData[i, 16] != null && arrData[i, 12] != null) { AddressListElement aAddressListElement = new AddressListElement(); aAddressListElement.row = i; //var ss = getStringFromXML(arrData[i, 15]); aAddressListElement.latid = getStringFromXML(arrData[i, 15]).Replace(',', '.'); aAddressListElement.longit = getStringFromXML(arrData[i, 16]).Replace(',', '.'); aAddressListElement.M = getStringFromXML(arrData[i, 13]) == "" ? "АС" : getStringFromXML(arrData[i, 13]); aAddressListElement.J = getStringFromXML(arrData[i, 10]); aAddressListElement.K = getStringFromXML(arrData[i, 11]); if (aAddressListElement.K == "0" | aAddressListElement.K == "") { continue; } aAddressListElement.L = getStringFromXML(arrData[i, 12]); aAddressListElement.fider_number = getStringFromXML(arrData[i, 3]); if (aAddressListElement.K == "0") { continue; } aAddressListElement.L2 = l2Value; //aAddressListElement.K2 = outOfRange ? "" : getStringFromXML(arrData2[i, 11]); addressList.Add(aAddressListElement); } } if (minVal == Int32.MaxValue) { minVal = 4; } if (maxVal == 0) { maxVal = 34; } maxVal++; Random rnd = new Random(); for (int i = 2; i <= rowCount3; i++) { if (arrData3[i, 15] != null && arrData3[i, 16] != null && arrData3[i, 12] != null) { AddressListElement aAddressListElement = new AddressListElement(); aAddressListElement.row = i; //var ss = getStringFromXML(arrData[i, 15]); aAddressListElement.latid = getStringFromXML(arrData3[i, 15]).Replace(',', '.'); aAddressListElement.longit = getStringFromXML(arrData3[i, 16]).Replace(',', '.'); aAddressListElement.M = getStringFromXML(arrData3[i, 13]) == "" ? "АС" : getStringFromXML(arrData3[i, 13]); aAddressListElement.J = getStringFromXML(arrData3[i, 10]); aAddressListElement.K = getStringFromXML(arrData3[i, 11]); if (aAddressListElement.K == "0" | aAddressListElement.K == "") { continue; } aAddressListElement.L = getStringFromXML(arrData3[i, 12]); aAddressListElement.fider_number = getStringFromXML(arrData3[i, 3]); aAddressListElement.L2 = rnd.Next(minVal, rnd.Next(minVal, maxVal)).ToString(); // с уклоном в меньшую сторону //aAddressListElement.K2 = outOfRange ? "" : getStringFromXML(arrData2[i, 11]); addressList.Add(aAddressListElement); } } resultInfoElement aResultInfoElement = new resultInfoElement(); aResultInfoElement.fileName = file.Split('\\').Last(); aResultInfoElement.addressCount = addressList.Count; aResultInfoElement.comlete = false; resultList.Add(aResultInfoElement); resultInfoElementBindingSource.ResetBindings(true); addressListElementBindingSource.ResetBindings(true); } catch (Exception ex) { throw new Exception("Ошибка загрузки Excel файла: " + ex.Message); } }