public new void Processing()
{
XLWorkbook wb = new XLWorkbook(FilePath);
foreach (var page in wb.Worksheets)
{
if (page.Name.ToUpper().Trim() != "СТАТИСТИКА" && page.Name.ToUpper().Trim() != "СВОДНАЯ")
{
const int numColPoint = 4;
IXLCell CellDate = page.Cell(1, numColPoint + 1);
while (CellDate.GetString() == "" && CellDate.Address.ColumnNumber <= page.LastColumnUsed().ColumnNumber())
{
CellDate = CellDate.CellRight();
}
DateTime curDate;
//if (!DateTime.TryParse(CellDate.GetValue<string>(), out curDate))
//{
// CellDate = CellDate.CellAbove();
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
//}
Regex rComment = new Regex(@"КОРРЕКЦИИ");
int corrRow = 5;
Match Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper());
while (!Mcomment.Success)
{
corrRow++;
Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper());
}
List <Call> calls = new List <Call>();
while (!(CellDate.CellBelow().IsEmpty() && CellDate.CellBelow().CellRight().IsEmpty()))
{
if (CellDate.GetValue <string>() != "")
{
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
}
string phoneNumber = CellDate.CellBelow().GetValue <string>();
var phoneCell = CellDate.CellBelow();
if (phoneNumber != "")
{
TimeSpan duration;
string link = "";
if (phoneCell.HasHyperlink)
{
link = phoneCell.Hyperlink.ExternalAddress.AbsoluteUri;
}
IXLCell CellPoint = CellDate.CellBelow().CellBelow().CellBelow();
if (CellPoint.DataType == XLDataType.DateTime)
{
CellPoint.DataType = XLDataType.TimeSpan;
}
TimeSpan.TryParse(CellPoint.GetString(), out duration);
IXLCell CellNamePoint;
List <Point> points = new List <Point>();
Point curPoint;
int markOfPoint;
CellPoint = CellPoint.CellBelow();
string DealName = "";
string comment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).GetString();
bool redComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Red ? true : false;
var Color = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor;
bool greenComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Lime ? true : false;
int maxMark;
page.Cell(corrRow - 3, CellPoint.Address.ColumnNumber).TryGetValue(out maxMark);
if (!CellPoint.TryGetValue <int>(out markOfPoint))
{
if (CellPoint.GetString() != "")
{
DealName = CellPoint.GetString();
}
}
else
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
curPoint.ColorForRNR = CellNamePoint.Style.Fill.BackgroundColor;
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
int weightPoint;
int numchl;
while (page.Cell(CellPoint.Address.RowNumber, 3).TryGetValue <int>(out numchl) || page.Cell(CellPoint.Address.RowNumber, 3).GetString() == "б\\н")
{
page.Cell(CellPoint.Address.RowNumber, 2).TryGetValue <int>(out weightPoint);
if (CellPoint.TryGetValue <int>(out markOfPoint))
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
if (error)
{
}
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
curPoint.ColorForRNR = CellNamePoint.Style.Fill.BackgroundColor;
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
}
bool outgoing = true;
if (Regex.Match(page.Name.ToUpper(), "ВХОДЯЩ").Success)
{
outgoing = false;
}
string Objections = "";
string howProcessObj = "";
string DealState = "";
string DateOfNext = "";
string doneObj = "";
if (curDate > new DateTime(2020, 5, 6))
{
Objections = page.Cell(corrRow + 2, CellPoint.Address.ColumnNumber).GetString();
howProcessObj = page.Cell(corrRow + 4, CellPoint.Address.ColumnNumber).GetString();
DealState = page.Cell(corrRow + 5, CellPoint.Address.ColumnNumber).GetString();
DateOfNext = page.Cell(corrRow + 6, CellPoint.Address.ColumnNumber).GetString();
DateTime ddateNext;
if (DateOfNext != "")
{
if (DateTime.TryParse(DateOfNext, out ddateNext))
{
DateOfNext = ddateNext.ToString("dd.MM.yyyy");
}
}
doneObj = page.Cell(corrRow + 3, CellPoint.Address.ColumnNumber).GetString();
}
if (points.Count > 0)
{
var curCall = new Call(phoneNumber, maxMark, duration, comment, DealName, points, redComment, curDate, outgoing, greenComment, Objections, howProcessObj, DealState, link, DateOfNext, doneObj);
calls.Add(curCall);
var testt = curCall.getAVGPersent();
if (testt > 1)
{
}
}
}
CellDate = CellDate.CellRight();
}
stages.Add(new Stage(page.Name, calls));
}
}
}
public new void Processing()
{
XLWorkbook wb = new XLWorkbook(FilePath);
foreach (var page in wb.Worksheets)
{
if (page.Name.ToUpper().Trim() != "СТАТИСТИКА" && page.Name.ToUpper().Trim() != "СВОДНЫЕ" && page.Name.ToUpper().Trim() != "СВОДНАЯ" && page.Name.ToUpper().Trim() != "СТАТИСТИКИ")
{
const int numColPoint = 4;
IXLCell CellDate = page.Cell(1, numColPoint + 1);
DateTime curDate;
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
Regex rComment = new Regex(@"КОРРЕКЦИИ");
int corrRow = 5;
Match Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper());
while (!Mcomment.Success)
{
corrRow++;
Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper());
}
List <Call> calls = new List <Call>();
while (!(CellDate.CellBelow().IsEmpty() && CellDate.CellBelow().CellRight().IsEmpty() && CellDate.CellBelow().CellBelow().IsEmpty() && CellDate.CellBelow().CellBelow().CellRight().IsEmpty()))
{
if (CellDate.GetValue <string>() != "")
{
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
}
string phoneNumber = CellDate.CellBelow().GetValue <string>();
var phoneCell = CellDate.CellBelow();
if (phoneNumber == "")
{
phoneNumber = CellDate.CellBelow().CellBelow().GetValue <string>();
phoneCell = CellDate.CellBelow().CellBelow();
}
if (phoneNumber != "")
{
string link = "";
if (phoneCell.HasHyperlink)
{
link = phoneCell.Hyperlink.ExternalAddress.AbsoluteUri;
}
TimeSpan duration;
TimeSpan wrongtime1 = new TimeSpan(1, 0, 0, 0);
TimeSpan wrongtime2 = new TimeSpan();
IXLCell CellPoint = CellDate.CellBelow().CellBelow().CellBelow();
if (CellPoint.DataType == XLDataType.DateTime)
{
CellPoint.DataType = XLDataType.TimeSpan;
}
TimeSpan.TryParse(CellPoint.GetString(), out duration);
IXLCell CellNamePoint;
List <Point> points = new List <Point>();
Point curPoint;
int markOfPoint;
if (wrongtime1 <= duration || duration == wrongtime2)
{
duration = wrongtime2;
if (CellPoint.TryGetValue <int>(out markOfPoint))
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
points.Add(curPoint);
}
}
CellPoint = CellDate.CellBelow().CellBelow().CellBelow().CellBelow();
string DealName = "";
string comment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).GetString();
bool redComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Red ? true : false;
int maxMark;
page.Cell(corrRow - 3, CellPoint.Address.ColumnNumber).TryGetValue(out maxMark);
if (!CellPoint.TryGetValue <int>(out markOfPoint))
{
if (CellPoint.GetString() != "")
{
DealName = CellPoint.GetString();
}
}
else
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
//int i = 0;
//while (page.Cell(CellPoint.Address.RowNumber - i, 1).GetString() == "")
//{
// i++;
//}
//curPoint.stageForBelfan = page.Cell(CellPoint.Address.RowNumber - i, 1).GetString();
curPoint.stageForBelfan = CellPoint.Address.RowNumber.ToString();
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
while (CellPoint.Address.RowNumber < corrRow - 4)
{
if (CellPoint.TryGetValue <int>(out markOfPoint))
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
//int i = 0;
//while (page.Cell(CellPoint.Address.RowNumber - i,1).GetString() == "")
//{
// i++;
//}
//curPoint.stageForBelfan = page.Cell(CellPoint.Address.RowNumber - i, 1).GetString();
curPoint.stageForBelfan = CellPoint.Address.RowNumber.ToString();
points.Add(curPoint);
}
else
{
string answer = CellPoint.GetString().ToLower();
if (answer == "нет" || answer == "да")
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), answer == "нет" ? 0 : 1, error, true);
curPoint.stageForBelfan = CellPoint.Address.RowNumber.ToString();
points.Add(curPoint);
}
}
CellPoint = CellPoint.CellBelow();
}
bool outgoing = true;
string Objections = "";
string howProcessObj = "";
string DealState = "";
string DateOfNext = "";
string doneObj = "";
if (curDate > new DateTime(2020, 5, 6))
{
Objections = page.Cell(corrRow + 2, CellPoint.Address.ColumnNumber).GetString();
howProcessObj = page.Cell(corrRow + 4, CellPoint.Address.ColumnNumber).GetString();
DealState = page.Cell(corrRow + 5, CellPoint.Address.ColumnNumber).GetString();
DateOfNext = page.Cell(corrRow + 6, CellPoint.Address.ColumnNumber).GetString();
doneObj = page.Cell(corrRow + 3, CellPoint.Address.ColumnNumber).GetString();
}
DateTime ddateNext;
if (DateOfNext != "")
{
if (DateTime.TryParse(DateOfNext, out ddateNext))
{
DateOfNext = ddateNext.ToString("dd.MM.yyyy");
}
}
if (Regex.Match(phoneNumber.ToUpper(), "ВХОДЯЩ").Success)
{
outgoing = false;
}
bool greenComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Lime ? true : false;
if (points.Count > 0)
{
calls.Add(new Call(phoneNumber, maxMark, duration, comment, DealName, points, redComment, curDate, outgoing, greenComment, Objections, howProcessObj, DealState, link, DateOfNext, doneObj));
}
}
CellDate = CellDate.CellRight();
}
stages.Add(new Stage(page.Name.Trim(), calls));
}
}
}
public new void Processing()
{
XLWorkbook wb = new XLWorkbook(FilePath);
foreach (var page in wb.Worksheets)
{
if (page.Name.ToUpper().Trim() != "СТАТИСТИКА" && page.Name.ToUpper().Trim() != "СВОДНАЯ")
{
const int numColPoint = 4;
IXLCell CellDate = page.Cell(2, numColPoint + 1);
DateTime curDate;
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
Regex rComment = new Regex(@"КОРРЕКЦИИ");
int corrRow = 5;
Match Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper());
while (!Mcomment.Success)
{
corrRow++;
Mcomment = rComment.Match(page.Cell(corrRow, 1).GetString().ToUpper());
}
List <Call> calls = new List <Call>();
while (!(CellDate.CellBelow().IsEmpty() && CellDate.CellBelow().CellRight().IsEmpty()))
{
if (CellDate.GetValue <string>() != "")
{
DateTime.TryParse(CellDate.GetValue <string>(), out curDate);
}
string phoneNumber = CellDate.CellBelow().GetValue <string>();
if (phoneNumber != "")
{
TimeSpan duration;
IXLCell CellPoint = CellDate.CellBelow().CellBelow();
if (CellPoint.DataType == XLDataType.DateTime)
{
CellPoint.DataType = XLDataType.TimeSpan;
}
TimeSpan.TryParse(CellPoint.GetString(), out duration);
IXLCell CellNamePoint;
List <Point> points = new List <Point>();
Point curPoint;
int markOfPoint;
CellPoint = CellDate.CellBelow().CellBelow().CellBelow();
string DealName = "";
string comment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).GetString();
bool redComment = page.Cell(corrRow, CellPoint.Address.ColumnNumber).Style.Fill.BackgroundColor
== XLColor.Red ? true : false;
int maxMark;
page.Cell(corrRow - 3, CellPoint.Address.ColumnNumber).TryGetValue(out maxMark);
if (!CellPoint.TryGetValue <int>(out markOfPoint))
{
if (CellPoint.GetString() != "")
{
DealName = CellPoint.GetString();
}
}
else
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
while (CellPoint.Address.RowNumber < corrRow - 4)
{
if (CellPoint.TryGetValue <int>(out markOfPoint))
{
CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint);
bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red;
curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error);
points.Add(curPoint);
}
CellPoint = CellPoint.CellBelow();
}
bool outgoing = true;
if (Regex.Match(page.Name.ToUpper(), "ВХОДЯЩ").Success)
{
outgoing = false;
}
if (points.Count > 0)
{
calls.Add(new Call(phoneNumber, maxMark, duration, comment, DealName, points, redComment, curDate, outgoing));
}
}
CellDate = CellDate.CellRight();
}
stages.Add(new Stage(page.Name, calls));
}
}
}