private void ExecuteInForeground(Object obj) { FormMain theForm = (FormMain)obj; try { List <Nameface> theListNameface = Nameface.GetAll(); foreach (var worksheet in theForm.pck.Workbook.Worksheets) { Log(theForm, "Обрабатываем страницу: " + worksheet.Name); if (worksheet.Dimension == null) { Log(theForm, "Страница пуста - пропускаем"); continue; } worksheet.InsertColumn(2, 3); var rowCnt = worksheet.Dimension.End.Row; Log(theForm, "Кол-во строк в странице: " + rowCnt.ToString()); for (int i = 2; i <= rowCnt; i++) { Application.DoEvents(); if (theForm.isStop == true) { Log(theForm, "Принудительное завершение"); theForm.pck.Save(); return; } if (worksheet.Cells[i, 1].Value == null) { Log(theForm, "Значение в " + i.ToString() + " строке - отсутствует - пропускаем "); continue; } string NameInCell = ""; if (worksheet.Cells[i, 5].Value != null) { NameInCell = worksheet.Cells[i, 5].Value.ToString(); } string tel = IndexMobileCore.Helper.Telephone.Normalize(worksheet.Cells[i, 1].Value.ToString()); if (tel.Length < 10) { Log(theForm, "Значение в " + i.ToString() + " строке - меньше 10 по длине - пропускаем "); continue; } string _Code = tel.Substring(0, 3); string _Number = tel.Substring(3, 7); Log(theForm, "_Code - " + _Code); Log(theForm, "_Number - " + _Number); int Code = 0; Convert.ToInt32(_Code); int Number = 0; Convert.ToInt32(_Number); try { Code = Convert.ToInt32(_Code); Number = Convert.ToInt32(_Number); } catch { Log(theForm, "Ошибка при определении Code и Number"); } Log(theForm, "Вызываем: IndexMobileCore.Helper.Telephone.Operator(" + Code.ToString() + ", " + Number.ToString() + ")"); //string tel_operator = IndexMobileCore.Helper.Telephone.Operator(Code, Number); var theDEF = DEF.GetOperator(Code, Number); if (theDEF != null) { Log(theForm, "Получено значение: tel_operator = " + theDEF.Operator + " - " + theDEF.Region); worksheet.Cells[i, 2].Value = theDEF.Operator; worksheet.Cells[i, 3].Value = theDEF.Region; } else { Log(theForm, "Значение отсутствует"); } string theName = ""; foreach (var item in NameInCell.Split(new char[] { ' ', '(' }, StringSplitOptions.RemoveEmptyEntries)) { if (item.Length == 1) { continue; } var list = theListNameface.Where(x => x.NameOff.ToLower() == item.ToLower()); if (list.Count() > 0) { theName = list.ToList <Nameface>()[0].NameOn; break; } else { var check = theListNameface.Where(x => x.NameOn.ToLower() == item.ToLower()); if (check.Count() > 0) { theName = check.ToList <Nameface>()[0].NameOn; break; } } } worksheet.Cells[i, 4].Value = theName; Log(theForm, "Завершено!"); } } } catch (Exception ex) { string err = ex.Message; err += ex.StackTrace; Log(theForm, err); } finally { Log(theForm, "Выход"); theForm.pck.Save(); th.Abort(); th = null; theForm.button1.Enabled = true; theForm.button3.Enabled = false; theForm.button3_Click(null, null); theForm.Update(); this.button1.Enabled = true; this.button3.Enabled = false; } }
private void button1_Click(object sender, EventArgs e) { try { if (this.openFileDialog1.ShowDialog() != DialogResult.OK) { return; } this.button1.Enabled = false; this.button3.Enabled = true; this.isStop = false; Log("Начало обработки"); ulong ProcentTotal = 0; ulong ProcentCurr = 0; Dictionary <string, int> theTipor = new Dictionary <string, int>(); foreach (var pathSelect in this.openFileDialog1.FileNames) { FileInfo newFile = new FileInfo(pathSelect); pck = new ExcelPackage(newFile); try { foreach (var worksheet in this.pck.Workbook.Worksheets) { if (worksheet.Dimension == null) { continue; } var rowCnt = (ulong)worksheet.Dimension.End.Row; ProcentTotal += rowCnt; } } catch { } } foreach (var pathSelect in this.openFileDialog1.FileNames) { Log("Выбран файл: " + Path.GetFileName(this.openFileDialog1.FileName)); FileInfo newFile = new FileInfo(pathSelect); pck = new ExcelPackage(newFile); try { List <Nameface> theListNameface = Nameface.GetAll(); foreach (var worksheet in this.pck.Workbook.Worksheets) { Log("Обрабатываем страницу: " + worksheet.Name); if (worksheet.Dimension == null) { Log("Страница пуста - пропускаем"); continue; } worksheet.InsertColumn(2, 4); var rowCnt = worksheet.Dimension.End.Row; Log("Кол-во строк в странице: " + rowCnt.ToString()); for (int i = 2; i <= rowCnt; i++) { ProcentCurr++; this.labelProcent.Text = ((decimal)ProcentCurr / (decimal)ProcentTotal * 100).ToString("0"); Application.DoEvents(); if (this.isStop == true) { Log("Принудительное завершение"); this.pck.Save(); return; } if (worksheet.Cells[i, 1].Value == null) { Log("Значение в " + i.ToString() + " строке - отсутствует - пропускаем "); continue; } string NameInCell = ""; if (worksheet.Cells[i, 6].Value != null) { NameInCell = worksheet.Cells[i, 6].Value.ToString(); } string tel = IndexMobileCore.Helper.Telephone.Normalize(worksheet.Cells[i, 1].Value.ToString()); if (tel.Length < 10) { Log("Значение в " + i.ToString() + " строке - меньше 10 по длине - пропускаем "); worksheet.DeleteRow(i); i--; rowCnt--; continue; } if (tel[0] != '9') { Log("Не пройдена проверка: не имеют сотового номера (89..,+79..,79) "); worksheet.DeleteRow(i); i--; rowCnt--; continue; } string _Code = tel.Substring(0, 3); string _Number = tel.Substring(3, 7); Log("_Code - " + _Code); Log("_Number - " + _Number); int Code = 0; Convert.ToInt32(_Code); int Number = 0; Convert.ToInt32(_Number); try { Code = Convert.ToInt32(_Code); Number = Convert.ToInt32(_Number); } catch { Log("Ошибка при определении Code и Number"); } Log("Вызываем: IndexMobileCore.Helper.Telephone.Operator(" + Code.ToString() + ", " + Number.ToString() + ")"); string NumberTel = String.Format("8{0}{1}", _Code, _Number); if (theTipor.ContainsKey(NumberTel)) { worksheet.DeleteRow(i); i--; rowCnt--; continue; } else { theTipor.Add(NumberTel, i); } worksheet.Cells[i, 1].Value = NumberTel; var theDEF = DEF.GetOperator(Code, Number); if (theDEF != null) { Log("Получено значение: tel_operator = " + theDEF.Operator + " - " + theDEF.Region); worksheet.Cells[i, 4].Value = theDEF.Operator; worksheet.Cells[i, 5].Value = theDEF.Region; } else { Log("Значение отсутствует"); } string theName = ""; foreach (var item in NameInCell.Split(new char[] { ' ', '(' }, StringSplitOptions.RemoveEmptyEntries)) { if (item.Length == 1) { continue; } var list = theListNameface.Where(x => x.NameOff.ToLower() == item.ToLower() || Nameface.NameOffTranslate(item).ToLower() == x.NameOff.ToLower()); if (list.Count() > 0) { theName = list.ToList <Nameface>()[0].NameOn; break; } else { var check = theListNameface.Where(x => x.NameOn.ToLower() == item.ToLower()); if (check.Count() > 0) { theName = check.ToList <Nameface>()[0].NameOn; break; } } } worksheet.Cells[i, 2].Value = theName; if (theName.Length > 0) { if ((IndexMobileCore.Helper.Telephone.Reverse(theName)[0] == 'а' || IndexMobileCore.Helper.Telephone.Reverse(theName)[0] == 'А' || IndexMobileCore.Helper.Telephone.Reverse(theName)[0] == 'я' || IndexMobileCore.Helper.Telephone.Reverse(theName)[0] == 'Я') && theName.ToUpper() != "ИЛЬЯ") { worksheet.Cells[i, 3].Value = "Ж"; } else { worksheet.Cells[i, 3].Value = "M"; } } else { worksheet.Cells[i, 3].Value = "M"; } } worksheet.Cells[1, 1].Value = "Телефон"; worksheet.Cells[1, 2].Value = "Имя"; worksheet.Cells[1, 3].Value = "Пол"; worksheet.Cells[1, 4].Value = "Оператор"; worksheet.Cells[1, 5].Value = "Регион"; } pck.Save(); } catch (Exception ex) { string err = ex.Message; err += ex.StackTrace; Log(err); } } this.labelProcent.Text = "100%"; Log("Завершено!"); } catch (Exception ex) { string err = ex.Message; err += ex.StackTrace; Log(err); } finally { } }