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);
}
}
