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