private static DateTime FormatDateCell(IXLCell cell) { cell.SetDataType(XLDataType.DateTime); var value = cell.GetDateTime().ToString(FormatoDataExcel, Execute.CulturaPtBr); return(DateTime.Parse(value)); }
public IDictionary <ExcelDataType, object> ReadExcelNamedRange(IXLWorkbook workbook, string namedRange) { IXLNamedRange range = workbook.NamedRange(namedRange); if (range == null) { throw new ClosedXMLReadException($"There is no named range called {namedRange} in this workbook"); } int count = range.Ranges.First().Cells().Count(); //if solo then don't return collection. if (count.Equals(1)) { IXLCell cell = range.Ranges.First().Cells().First(); switch (cell.DataType) { case XLDataType.DateTime: return(new Dictionary <ExcelDataType, object> { { ExcelDataType.Numeric, cell.GetDateTime().ToOADate() } }); case XLDataType.Number: return(new Dictionary <ExcelDataType, object> { { ExcelDataType.Numeric, cell.GetDouble() } }); case XLDataType.Text: string textValue = GetSingleCellTextValue(cell); KeyValuePair <ExcelDataType, object> parsed = ParseString(textValue); switch (parsed.Key) { case ExcelDataType.Numeric: return(new Dictionary <ExcelDataType, object> { { ExcelDataType.Numeric, (double)parsed.Value } }); case ExcelDataType.Text: string[,] array = new string[1, 1]; array[0, 0] = textValue; return(new Dictionary <ExcelDataType, object> { { ExcelDataType.Text, array } }); default: throw new NotImplementedException("I haven't implemented formulas yet"); } } } IXLTable table = range.Ranges.First().AsTable(); return(ReadTable(table)); }
public void ParserCheckLists(IEnumerable <IFormFile> files) { using (var stream = files.First().OpenReadStream()) { XLWorkbook wb = new XLWorkbook(stream); FillStageDictionary(wb); } foreach (var file in files) { string Manager = Regex.Match(file.FileName, @"(\w+)").Groups[1].Value; using (var stream = file.OpenReadStream()) { XLWorkbook wb = new XLWorkbook(stream); foreach (var page in wb.Worksheets) { var statisticMatch = Regex.Match(page.Name.ToUpper().Trim(), "СТАТИСТИК"); var LastTableMatch = Regex.Match(page.Name.ToUpper().Trim(), "СВОДН"); if (!statisticMatch.Success && !LastTableMatch.Success) { IXLCell cell = page.Cell(1, 5); DateTime curDate; bool normalDate = false; if (cell.DataType == XLDataType.DateTime) { curDate = cell.GetDateTime(); normalDate = true; } else { if (!DateTime.TryParse(cell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate)) { normalDate = DateTime.TryParse(cell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out curDate); } else { normalDate = true; } } string phoneNumber; int corrRow = 5; Match Mcomment = Regex.Match(page.Cell(corrRow, 1).GetString().ToUpper(), @"КОРРЕКЦИИ"); while (!Mcomment.Success) { corrRow++; Mcomment = Regex.Match(page.Cell(corrRow, 1).GetString().ToUpper(), @"КОРРЕКЦИИ"); } while (!(cell.CellBelow().IsEmpty() && cell.CellBelow().CellRight().IsEmpty() && cell.CellBelow().CellBelow().IsEmpty() && cell.CellBelow().CellBelow().CellRight().IsEmpty())) { if (cell.GetValue <string>() != "") { if (cell.DataType == XLDataType.DateTime) { curDate = cell.GetDateTime(); } else { if (!DateTime.TryParse(cell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate)) { DateTime.TryParse(cell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out curDate); } } } phoneNumber = cell.CellBelow().GetValue <string>().ToUpper().Trim(); var CellPhoneNumber = cell.CellBelow(); string link; if (CellPhoneNumber.HasHyperlink) { link = CellPhoneNumber.GetHyperlink().ExternalAddress.AbsoluteUri; } else { link = ""; } if (link == "") { } if (phoneNumber != "") { Regex rx = new Regex("ВХОДЯЩ"); Match m = rx.Match(page.Name.ToUpper().Trim()); var exCallSeq = processedCalls.Where(c => (c.Client == phoneNumber && link == "") || (c.Link == link && link != "")); var exCall = new ProcessedCall(); //exCall.StartDateAnalyze = curDate.AddDays(-1); if (exCallSeq.Count() > 0) { exCall = exCallSeq.First(); //exCall.StartDateAnalyze = curDate.AddDays(-1); } else { exCall.ClientState = ""; exCall.StartDateAnalyze = DateTime.MinValue; } if ((curDate > exCall.StartDateAnalyze || ( exCall.ClientState.ToUpper() == "В РАБОТЕ") && exCall.StartDateAnalyze < DateTime.Today.AddDays(1) ) && normalDate) { DateTime DateNext = new DateTime(); var NextContactCell = page.Cell(corrRow + 6, cell.Address.ColumnNumber); if (NextContactCell.GetString() != "") { if (NextContactCell.DataType == XLDataType.DateTime) { DateNext = NextContactCell.GetDateTime(); } else { if (!DateTime.TryParse(NextContactCell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out DateNext)) { DateTime.TryParse(NextContactCell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out DateNext); } } } if (curDate > new DateTime(2020, 5, 5)) { phones.AddCall(new FullCall(phoneNumber, link, page.Name.ToUpper().Trim(), curDate, !m.Success, page.Cell(corrRow, cell.Address.ColumnNumber).GetString(), Manager, page.Cell(corrRow + 5, cell.Address.ColumnNumber).GetString(), DateNext)); } else { phones.AddCall(new FullCall(phoneNumber, link, page.Name.ToUpper().Trim(), curDate, !m.Success, page.Cell(corrRow, cell.Address.ColumnNumber).GetString(), Manager)); } } } cell = cell.CellRight(); } phones.CleanSuccess(ref processedCalls); } } } } }
public 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() != "СТАТИСТИКИ") { 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>() != "") { if (CellDate.DataType == XLDataType.DateTime) { curDate = CellDate.GetDateTime(); } else { if (!DateTime.TryParse(CellDate.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate)) { DateTime.TryParse(CellDate.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, 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); } 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); 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; 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); 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); int weightPoint = CellNamePoint.CellLeft().CellLeft().GetValue <int>(); bool error = CellPoint.Style.Fill.BackgroundColor == XLColor.Red; curPoint = new Point(CellNamePoint.GetString(), markOfPoint, error, CellPoint.Address.RowNumber.ToString()); //if (notTakenPoint(CellNamePoint.GetString())) // maxMark -= weightPoint; //else points.Add(curPoint); } else { string answer = CellPoint.GetString().ToLower(); if (answer == "нет" || answer == "да") { CellNamePoint = page.Cell(CellPoint.Address.RowNumber, numColPoint); curPoint = new Point(CellNamePoint.GetString(), answer == "нет" ? 0 : 1, answer == "нет" ? true : false, true); 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(); 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 (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, greenComment, Objections, howProcessObj, DealState, link, DateOfNext, doneObj)); } } CellDate = CellDate.CellRight(); } stages.Add(new Stage(page.Name, calls)); } } }
public void ParserCheckLists(IEnumerable <IFormFile> files) { using (var stream = files.First().OpenReadStream()) { XLWorkbook wb = new XLWorkbook(stream); FillStageDictionary(wb); } foreach (var file in files) { string Manager = Regex.Match(file.FileName, @"(\w+)").Groups[1].Value; using (var stream = file.OpenReadStream()) { XLWorkbook wb = new XLWorkbook(stream); IXLWorksheet page = wb.Worksheets.First(); IXLCell cell = page.Cell(1, 5); DateTime curDate; if (cell.DataType == XLDataType.DateTime) { curDate = cell.GetDateTime(); } else { if (!DateTime.TryParse(cell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate)) { DateTime.TryParse(cell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out curDate); } } string phoneNumber; IXLCell phoneCell; while (!(cell.IsEmpty() && cell.CellRight().IsEmpty() && !cell.IsMerged())) { if (cell.GetString() != "") { if (cell.DataType == XLDataType.DateTime) { curDate = cell.GetDateTime(); } else { if (!DateTime.TryParse(cell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out curDate)) { DateTime.TryParse(cell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out curDate); } } } phoneCell = cell.CellBelow(); if (phoneCell.GetString() == "") { phoneCell = phoneCell.CellBelow(); } if (phoneCell.GetString() != "") { string link; if (phoneCell.HasHyperlink) { link = phoneCell.GetHyperlink().ExternalAddress.AbsoluteUri; } else { link = ""; } Match outgoing = Regex.Match(phoneCell.GetString().ToUpper(), @"ИСХОДЯЩИЙ"); phoneNumber = Regex.Replace(phoneCell.GetString().ToUpper(), @"[^\d]", String.Empty); string oldphonenum = phoneNumber; oldphonenum = "8 (" + oldphonenum.Substring(1, 3) + ") " + oldphonenum.Substring(4, 3) + "-" + oldphonenum.Substring(7, 2) + "-" + oldphonenum.Substring(9); while (phoneNumber[0] == '0') { phoneNumber = phoneNumber.Substring(1); } if (phoneNumber[0] == '9') { phoneNumber = '8' + phoneNumber; } if (phoneNumber[0] == '7' || phoneNumber[0] == '8') { phoneNumber = "8 (" + phoneNumber.Substring(1, 3) + ") " + phoneNumber.Substring(4, 3) + "-" + phoneNumber.Substring(7, 2) + "-" + phoneNumber.Substring(9); } if (processedCalls.Exists(c => c.Client == oldphonenum) && oldphonenum != phoneNumber) { var testCall = processedCalls.Where(c => c.Client == oldphonenum).First(); processedCalls.Remove(testCall); testCall.Client = phoneNumber; processedCalls.Add(testCall); } if (processedCalls.Exists(c => c.Client == phoneNumber && c.Link == "")) { var testCall = processedCalls.Where(c => c.Client == phoneNumber).First(); testCall.Link = link; } var CellStage = page.Cell("A5"); Regex rx = new Regex("ИТОГ"); int corrRow = 5; Match Mcomment = Regex.Match(page.Cell(corrRow, 1).GetString().ToUpper(), @"КОРРЕКЦИИ"); while (!Mcomment.Success) { corrRow++; Mcomment = Regex.Match(page.Cell(corrRow, 1).GetString().ToUpper(), @"КОРРЕКЦИИ"); } while (!rx.Match(CellStage.GetString().ToUpper()).Success&& !rx.Match(CellStage.CellRight().CellRight().CellRight().GetString().ToUpper()).Success) { if (CellStage.GetString() != "" && page.Cell(CellStage.Address.RowNumber, cell.Address.ColumnNumber).GetString() != "") { var exCallSeq = processedCalls.Where(c => (c.Client == phoneNumber)); var exCall = new ProcessedCall(); if (exCallSeq.Count() > 0) { exCall = exCallSeq.First(); //exCall.StartDateAnalyze = curDate.AddDays(-1); } else { exCall.ClientState = ""; exCall.StartDateAnalyze = DateTime.MinValue; } if (curDate >= exCall.StartDateAnalyze || ( exCall.ClientState.ToUpper() == "В РАБОТЕ") && exCall.StartDateAnalyze < DateTime.Today.AddDays(1) ) { DateTime DateNext = new DateTime(); var NextContactCell = page.Cell(corrRow + 6, cell.Address.ColumnNumber); if (NextContactCell.GetString() != "") { if (NextContactCell.DataType == XLDataType.DateTime) { DateNext = NextContactCell.GetDateTime(); } else { if (!DateTime.TryParse(NextContactCell.GetString(), new CultureInfo("ru-RU"), DateTimeStyles.None, out DateNext)) { DateTime.TryParse(NextContactCell.GetString(), new CultureInfo("en-US"), DateTimeStyles.None, out DateNext); } } } if (curDate > DateTime.Now.AddMonths(-1) && Regex.Match(file.Name, "Гакова|Малькова|Лукина|Кожевникова|Рыбачук", RegexOptions.IgnoreCase).Success) { phones.AddCall(new FullCall(phoneNumber, link, Regex.Replace(CellStage.GetString(), @"[\d()]", String.Empty).Trim(), curDate, outgoing.Success, page.Cell(corrRow, cell.Address.ColumnNumber).GetString(), Manager, page.Cell(corrRow + 5, cell.Address.ColumnNumber).GetString(), DateNext)); } phonesForFirst.AddCall(new FullCall(phoneNumber, link, Regex.Replace(CellStage.GetString(), @"[\d()]", String.Empty).Trim(), curDate, outgoing.Success, page.Cell(corrRow, cell.Address.ColumnNumber).GetString(), Manager, page.Cell(corrRow + 5, cell.Address.ColumnNumber).GetString(), DateNext)); } } CellStage = CellStage.CellBelow(); } } cell = cell.CellRight(); } phones.CleanSuccess(ref processedCalls); } } }
private void ColumnValidator(TemplateRulesPriorityPatient template, IXLCell columndata, IXLTableRow row) { // Si el tipo es DATETIME if (template.Type == Configuration.GetValueConf(Constants.DATE_TIME_TYPE)) { // Se valida si la fecha del examen esta desactualizada if (template.ValidateOutdated && !columndata.GetString().Trim().Equals("")) { if (template.UnknowValue != null && template.NotApply != null && !template.UnknowValue.Equals("") && !template.NotApply.Equals("")) { // Se compara si el valor es el de default si es no se realiza validaciones if (Convert.ToDateTime(columndata.GetString().Trim()).Equals(Convert.ToDateTime(template.UnknowValue)) || Convert.ToDateTime(columndata.GetString().Trim()).Equals(Convert.ToDateTime(template.NotApply))) { return; } } try { DateTime dateValidation = DateTime.Now.AddMonths(template.MonthOutdated * -1); if (columndata.GetDateTime() < dateValidation) { var val = columndata.GetDateTime() < DateTime.Now ? DateTime.Now - columndata.GetDateTime() : columndata.GetDateTime() - DateTime.Now; string result = $"Vigencia minima: {template.MonthOutdated} Total: {(int)Math.Truncate((val.TotalDays) / 30)}"; // Inserta en variables desactualizadas y no realiza mas validaciones insertOutdated(template.Name, columndata.GetString(), result, row); return; } } catch (Exception ex) { IOUtilities.WriteExceptionLog(ex, Configuration.GetClassName <PriorityPatient>()); } } } // Existe un valor desconocido para la columna tipo fecha if ((template.UnknowValue != null && !template.UnknowValue.Equals("")) || (template.NotApply != null && !template.NotApply.Equals(""))) { // Se compara si el valor es el de default si es no se realiza validaciones if (Convert.ToDateTime(columndata.GetString().Trim()).Equals(Convert.ToDateTime(template.UnknowValue)) || Convert.ToDateTime(columndata.GetString().Trim()).Equals(Convert.ToDateTime(template.NotApply))) { return; } } // Existen un valores permitidos para la columna tipo numero if (template.AllowValues != null && !template.AllowValues.Equals("")) { int dataTmp; if (int.TryParse(columndata.GetString(), out dataTmp)) { if (template.AllowValues.Split(',').Where(val => Convert.ToInt32(val) == dataTmp).Count() != 0) { return; } } } validateRangeEq(template, row, columndata.GetString(), template.Type); validateRangeDif(template, row, columndata.GetString(), template.Type); validateRangeMin(template, row, columndata.GetString()); validateRangeHigher(template, row, columndata.GetString()); }