private void ParseValueItem(ValuesBunch valueItems, string type, IXLCell currentValueCell, ref int lastRowNum) { while (currentValueCell.IsEmpty() == false) { if (DateTime.TryParse(currentValueCell.CachedValue.ToString(), out var d)) { return; } if (decimal.TryParse(currentValueCell.CachedValue?.ToString(), out var num)) { try { var value = new ValueItem(num, new StatEnumItem(type)); valueItems.Add(value); } catch { continue; } } lastRowNum = Math.Max(lastRowNum, currentValueCell.Address.RowNumber); currentValueCell = currentValueCell.CellBelow(); } }
public void IsEmpty2() { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLCell cell = ws.Cell(1, 1); bool actual = cell.IsEmpty(true); bool expected = true; Assert.AreEqual(expected, actual); }
public void IsEmpty6() { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLCell cell = ws.Cell(1, 1); cell.Value = "X"; bool actual = cell.IsEmpty(); bool expected = false; Assert.AreEqual(expected, actual); }
public void IsEmpty5() { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLCell cell = ws.Cell(1, 1); cell.Style.Fill.BackgroundColor = XLColor.Red; bool actual = cell.IsEmpty(true); bool expected = false; Assert.AreEqual(expected, actual); }
public void IsEmpty4() { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLCell cell = ws.Cell(1, 1); cell.Style.Fill.BackgroundColor = XLColor.Red; bool actual = cell.IsEmpty(XLCellsUsedOptions.AllContents); bool expected = true; Assert.AreEqual(expected, actual); }
private int ParseValueItems(ValuesBunch valueItems, IXLCell currentTypeCell) { int lastRowNum = currentTypeCell.Address.RowNumber; while (currentTypeCell.IsEmpty() == false) { var type = currentTypeCell.CachedValue.ToString(); if (DataValidation.IsNameValid(type)) { ParseValueItem(valueItems, type, currentTypeCell.CellBelow(), ref lastRowNum); } currentTypeCell = currentTypeCell.CellRight(); } return(lastRowNum); }
private static float PrendiDatoSingolaCella(IXLCell contenutoCella) { if (!contenutoCella.IsEmpty()) { if (contenutoCella.DataType == XLDataType.Text) { if (String.Compare(contenutoCella.GetString().Trim(), "assenti") == 0) { return(0f); } else { var datoCella = float.Parse(contenutoCella.GetString().Trim()); return(datoCella); } } else if (contenutoCella.DataType == XLDataType.Number) { var datoCella = float.Parse(contenutoCella.GetString().Trim()); return(datoCella); } } return(0f); }
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); } } }
/// <summary> /// Return true if it have at least 1 DTOValidacionArchivo object instance. Q: Have any format error? YES(TRUE) or NO(FALSE) /// </summary> /// <param name="template">Template format object</param> /// <param name="cell">IXL Cell objet</param> /// <returns>True for any new instance DTOValidacionArchivo. If DTOValidacionArchivo is NULL then return false</returns> private bool Validator_Cell(TemplateFormatCAC template, IXLCell cell) { bool flag = false; try { DTOValidacionArchivo validation = null; #region Validacion si es null if (cell.IsEmpty() == true && template.Nullable == false) { validation = new DTOValidacionArchivo() { FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)), Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_EMPTY_OR_NULL, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Name), Valor = cell.GetString(), Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}", Fila = $"{cell.WorksheetRow().RowNumber()}", Columna = $"{cell.WorksheetColumn().ColumnLetter()}" }; Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_EMPTY_OR_NULL", validation.ToString(), cell.Worksheet.Name)); validator_result.Add(validation); } #endregion #region Si no es null or empty else if (cell.IsEmpty() == false) { bool hasFormula = cell.HasFormula; if (hasFormula == true) { #region Si tiene formula validation = new DTOValidacionArchivo() { FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)), Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_HAVE_FORMULA, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Name), Valor = cell.GetString(), Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}", Fila = $"{cell.WorksheetRow().RowNumber()}", Columna = $"{cell.WorksheetColumn().ColumnLetter()}" }; Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_HAVE_FORMULA", validation.ToString(), cell.Worksheet.Name)); validator_result.Add(validation); #endregion Si tiene formula } else { #region Validacion del tipo de dato var cell_datatype = cell.DataType.ToString(); if (template.Type.Contains(cell_datatype) == false) { validation = new DTOValidacionArchivo() { FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)), Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_NOT_VALID_TYPE, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Type, cell_datatype, cell.Value, template.Name), Valor = cell.GetString(), Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}", Fila = $"{cell.WorksheetRow().RowNumber()}", Columna = $"{cell.WorksheetColumn().ColumnLetter()}" }; Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_NOT_VALID_TYPE", validation.ToString(), cell.Worksheet.Name)); validator_result.Add(validation); } #endregion #region Validacion si el valor está contenido en la lista predeterminado y si es texto if (template.SelectList != null && template.SelectList.Count > 0) { bool isContained = false; try { isContained = template.SelectList.Where(m => m.Value == cell.GetValue <string>()).Count() > 0 ? true : false; } catch (Exception) { isContained = false; } if (isContained == false) { validation = new DTOValidacionArchivo() { FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)), Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_DO_NOT_LIST_VALUE, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), cell.Value, template.Name), Valor = cell.GetString(), Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}", Fila = $"{cell.WorksheetRow().RowNumber()}", Columna = $"{cell.WorksheetColumn().ColumnLetter()}" }; Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_DO_NOT_LIST_VALUE", validation.ToString(), cell.Worksheet.Name)); validator_result.Add(validation); } } #endregion #region Si es fecha y obtener el valor segun el tipo de dato var cell_type = cell.Value.GetType(); if (typeof(DateTime).ToString().Contains(cell_type.ToString()) == true || template.Type.Contains(typeof(DateTime).Name)) { bool regexResult = false; try { DateTime cell_datetime; if (cell.TryGetValue <DateTime>(out cell_datetime) == true) { string datetime_to_comparer = cell_datetime.ToString(template.Format); string regex = @"^\d{4}-((0\d)|(1[012]))-(([012]\d)|3[01])$"; regexResult = Regex.Match(datetime_to_comparer, regex).Success; } else { regexResult = false; } } catch (Exception) { regexResult = false; } if (regexResult == false) { validation = new DTOValidacionArchivo() { FechaCreacion = DateTime.Now.ToString(Configuration.GetValueConf(Constants.DateFormat)), Descripcion = string.Format(Resource_DefaultMessage.ERROR_CELL_NOT_FORMAT, cell.WorksheetColumn().ColumnLetter(), cell.WorksheetRow().RowNumber(), template.Format, template.Name), Valor = cell.GetString(), Celda = $"{cell.WorksheetColumn().ColumnLetter()}{cell.WorksheetRow().RowNumber()}", Fila = $"{cell.WorksheetRow().RowNumber()}", Columna = $"{cell.WorksheetColumn().ColumnLetter()}" }; Auditor.SaveLog(string.Format(Resource_DefaultMessage.CONTROL_VALUE, nameof(FileProcessBP.Validator_Cell), validation.Columna, validation.Valor, "ERROR_CELL_NOT_FORMAT", validation.ToString(), cell.Worksheet.Name)); validator_result.Add(validation); } } #endregion } } #endregion flag = validation == null ? false : true; } catch (Exception ex) { validator_result.Add(ExceptionWriter(ex)); flag = true; throw ex; } template = null; cell = null; return(flag); }