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
{
}
}