public IList <House> GetHouses(string path) { XLWorkbook wb = new XLWorkbook(path); IXLWorksheet sheet = wb.Worksheets.First(); IXLRange RangeSheet = sheet.RangeUsed(); int rightBorder = RangeSheet.LastColumn().ColumnNumber(); //установим правую границу данных int downBorder = RangeSheet.LastRow().RowNumber(); //установим нижнюю границу данных List <IXLCell> Xlhouses = sheet.Cells().Where(c => c.GetValue <string>().Contains("Дом")).ToList(); //получим ячейки, в которых номера домов List <House> houses = new List <House>(); foreach (var Xlhouse in Xlhouses) { House house = new House(); house.Name = Xlhouse.GetValue <string>(); house.Flats = new List <Flat>(); IXLCell cell = Xlhouse.CellBelow(); while (!(cell.GetValue <string>() == "" && cell.CellBelow().GetValue <string>() == "" && cell.CellRight().GetValue <string>() == "" && cell.CellRight().CellBelow().GetValue <string>() == "" ) && cell.WorksheetColumn().ColumnNumber() <= rightBorder ) //рассматриваем, есть ли значение в ячейке под названием дома, а также в ближайших от нее соседей { IXLCell cellBellowHouse = cell; while (!(cell.GetValue <string>() == "" && cell.CellBelow().GetValue <string>() == "" ) && cell.WorksheetRow().RowNumber() <= downBorder ) //смотрим есть ли в текущей ячейке значение или той, которая находится снизу { if (cell.GetValue <string>().Contains("№")) { Flat flat = new Flat(); flat.Number = cell.GetValue <string>().Substring(1); flat.Price = cell.CellBelow().GetValue <string>(); house.Flats.Add(flat); } cell = cell.CellBelow(); } cell = cellBellowHouse.CellRight(); //после того, как закончили с колонкой, переходим в следующую колонку //самую верхнюю под номером дома } houses.Add(house); } return(houses); //throw new NotImplementedException(); }
/// <summary> /// Reads the current cell as the specified type and moves to the right /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public T ReadValue <T>() { T value = current.GetValue <T>(); current = current.CellRight(); return(value); }
/// <summary> /// 獲取cell所在列的第一個cell的值,和列名進行match and fill JobRequestInfo的屬性值 /// </summary> /// <param name="cell"></param> /// <param name="job"></param> private void FillJobRequestInfo(IXLCell cell, ref JobRequest job) { try { switch (cell.WorksheetColumn().FirstCell().GetValue <string>().Trim()) { case "ContactPerson": job.ContactPerson = cell.GetValue <string>(); break; case "Location": job.Location = cell.GetValue <string>(); break; case "Company": job.Company = cell.GetValue <string>(); break; case "RequestType": job.RequestType = cell.GetValue <string>(); break; case "Symptom": job.Symptom = cell.GetValue <string>(); break; case "ScheduleTime": job.ScheduleTime = cell.GetValue <string>(); break; case "ServeTime1": job.ServeTime1 = cell.GetValue <string>(); break; case "ServeTime2": job.ServeTime2 = cell.GetValue <string>(); break; case "ServiceDescription": job.ServiceDescription = cell.GetValue <string>(); break; } } catch (Exception) { throw; } }
public static dynamic GetValue(this IXLCell cell, Type dataType) { if (dataType.Equals(typeof(string))) { return(cell.GetValue <string>()); } else if (dataType.Equals(typeof(int))) { return(cell.GetValue <int>()); } else if (dataType.Equals(typeof(uint))) { return(cell.GetValue <uint>()); } else if (dataType.Equals(typeof(DateTime))) { return(DateTime.FromOADate(cell.GetDouble())); } else if (dataType.Equals(typeof(short))) { return(cell.GetValue <short>()); } else if (dataType.Equals(typeof(ushort))) { return(cell.GetValue <ushort>()); } else if (dataType.Equals(typeof(float))) { return(cell.GetValue <float>()); } else if (dataType.Equals(typeof(double))) { return(cell.GetValue <double>()); } else if (dataType.Equals(typeof(long))) { return(cell.GetValue <long>()); } else if (dataType.Equals(typeof(ulong))) { return(cell.GetValue <ulong>()); } else { throw new TypeNotSupportedException(); } }
public void Cell3() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue(1).AddToNamed("Result"); IXLCell cell = wb.Cell("Sheet1!Result"); Assert.IsNotNull(cell); Assert.AreEqual(1, cell.GetValue <Int32>()); }
internal void ParseDataTypeRange() { IXLWorksheet dataTypeSheet = Workbook.Worksheets.Worksheet("DataTypes"); IXLRows rows = dataTypeSheet.RowsUsed(); bool firstRow = true; foreach (IXLRow row in rows) { if (firstRow) { firstRow = false; continue; } IXLCell typeNameCell = row.Cell("A"); IXLCell nameSpaceCell = row.Cell("B"); IXLCell idCell = row.Cell("C"); string key = typeNameCell.GetString(); if (string.IsNullOrWhiteSpace(key)) { Logger.Error($"Empty type name in row {row.RowNumber()}"); continue; } byte nsByte; try { nsByte = nameSpaceCell.GetValue <byte>(); } catch (FormatException exception) { Logger.Error($"Unable to parse Namespace of DataType {key} | {nameSpaceCell.GetString()} | {idCell.GetString()}", exception); continue; } byte idByte; try { idByte = idCell.GetValue <byte>(); } catch (FormatException exception) { Logger.Error($"Unable to parse ID of DataType {key} | {nameSpaceCell.GetString()} | {idCell.GetString()}", exception); continue; } NodeID dataTypeID = new NodeID { Namespace = nsByte, Value = idByte }; if (CommonConfig.KnownDataTypes.ContainsKey(key)) { Logger.Warn($"DataType with name '{key}' already exists. Value will be overwritten from row {row.RowNumber()}"); } CommonConfig.KnownDataTypes[key] = dataTypeID; } }
public static decimal HandlePercentage(this IXLCell @this) { try { var res = @this.GetValue <decimal>() * 100; return(res); } catch { return(-999999); } }
public static decimal HandleDecimal(this IXLCell @this) { try { var res = @this.GetValue <decimal>(); return(res); } catch { return(-999999); } }
public override bool IsValid(IXLCell cell, string columName) { var result = true; var match = Regex.Match(cell.GetValue <string>(), Pattern); if (!match.Success) { ErrorMessage = $"El valor \"{cell.Value}\" es inválido (error en celda [{cell.Address.ColumnLetter}{cell.Address.RowNumber}])"; result = match.Success; } return(result); }
protected bool ParseQuality(IXLRow row, IXLCell qualityCell, out ushort?value) { value = null; if (!string.IsNullOrWhiteSpace(qualityCell.GetString())) { try { value = qualityCell.GetValue <ushort>(); } catch (FormatException exception) { Logger.Error($"Unable to parse '{qualityCell.GetString()}' in row {row.RowNumber()} as UInt16", exception); return(false); } } return(true); }
protected bool ParseOrcat(IXLRow row, IXLCell orcatCell, out byte?value) { value = null; if (!string.IsNullOrWhiteSpace(orcatCell.GetString())) { try { value = orcatCell.GetValue <byte>(); } catch (FormatException exception) { Logger.Error($"Unable to parse '{orcatCell.GetString()}' in row {row.RowNumber()} as byte", exception); return(false); } } return(true); }
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)); } } }
/// <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); }
internal CommonConfig ReadCommon() { if (Workbook == null) { return(null); } IXLWorksheet commonSheet = Workbook.Worksheets.Worksheet("Common"); if (commonSheet == null) { Logger.Error("Unable to find \"Common\" sheet."); return(null); } IXLRows rows = commonSheet.RowsUsed(); CommonConfig.MetaConfig.ConfigurationVersion = new ConfigurationVersion(); DateTime now = DateTime.UtcNow; TimeSpan time = now - ConfigurationVersion.Base; uint defaultConfigVersion = (uint)time.TotalSeconds; foreach (IXLRow row in rows) { IXLCell cell = row.Cell("A"); IXLCell valueCell = row.Cell("B"); Logger.Debug($"Cell value: {cell.Value}"); switch (cell.Value) { case "Publisher ID": CommonConfig.PublisherID = valueCell.GetString(); break; case "DataSetWriterId": ushort id; try { id = valueCell.GetValue <ushort>(); } catch (FormatException exception) { Logger.Error($"Unable to parse DataSetWriterID: '{valueCell.GetString()}'"); Logger.Debug("FormatException:", exception); break; } CommonConfig.DataSetWriterID = id; break; case "MetaData - Name": CommonConfig.MetaConfig.MetaDataName = valueCell.GetString(); break; case "MetaData - Description": CommonConfig.MetaConfig.MetaDataDescription = valueCell.GetString(); break; case "ConfigurationVersion - Major": uint major; try { major = valueCell.GetValue <uint>(); } catch (FormatException exception) { Logger.Info($"Unable to parse ConfigurationVersion - Major: '{valueCell.GetString()}', using default value {defaultConfigVersion}"); major = defaultConfigVersion; Logger.Debug("FormatException:", exception); } CommonConfig.MetaConfig.ConfigurationVersion.Major = major; break; case "ConfigurationVersion - Minor": uint minor; try { minor = valueCell.GetValue <uint>(); } catch (FormatException exception) { Logger.Info($"Unable to parse ConfigurationVersion - Minor: '{valueCell.GetString()}', using default value {defaultConfigVersion}"); minor = defaultConfigVersion; Logger.Debug("FormatException:", exception); } CommonConfig.MetaConfig.ConfigurationVersion.Minor = minor; break; } } return(CommonConfig); }
internal void ParseEnumSheet() { IXLWorksheet dataTypeSheet = Workbook.Worksheets.Worksheet("Enums"); IXLRows rows = dataTypeSheet.RowsUsed(); bool firstRow = true; foreach (IXLRow row in rows) { if (firstRow) { firstRow = false; continue; } IXLCell dataTypeCell = row.Cell("A"); IXLCell qualifiedNameCell = row.Cell("B"); //IXLCell enumDescriptionDataTypeCell = row.Cell("C"); IXLCell valueCell = row.Cell("C"); IXLCell displayNameCell = row.Cell("D"); IXLCell descriptionCell = row.Cell("E"); IXLCell valueNameCell = row.Cell("F"); EnumEntry entry = new EnumEntry(); if (!TryGetNodeID(dataTypeCell.GetString(), out NodeID tempID, CommonConfig)) { continue; } entry.DataType = tempID; entry.QualifiedName = qualifiedNameCell.GetString(); int intValue; try { intValue = valueCell.GetValue <int>(); } catch (FormatException exception) { Logger.Error($"Unable to parse integer value for enum '{valueCell.GetString()}' in row {row.RowNumber()}", exception); continue; } entry.Value = intValue; entry.DisplayName = displayNameCell.GetString(); entry.Description = descriptionCell.GetString(); entry.ValueName = valueNameCell.GetString(); EnumDescription enumDescription; if (CommonConfig.EnumDescriptions.ContainsKey(entry.DataType)) { enumDescription = CommonConfig.EnumDescriptions[entry.DataType]; } else { enumDescription = new EnumDescription { Name = new QualifiedName(entry.QualifiedName), DataTypeID = entry.DataType, Fields = new List <EnumField>() }; CommonConfig.EnumDescriptions.Add(entry.DataType, enumDescription); } EnumField enumField = new EnumField { Value = entry.Value, Name = new String(entry.ValueName), Description = new LocalizedText(), DisplayName = new LocalizedText() }; if (!string.IsNullOrEmpty(entry.DisplayName)) { enumField.DisplayName.Locale = new String("en-US"); enumField.DisplayName.Text = new String(entry.DisplayName); } if (!string.IsNullOrEmpty(entry.Description)) { enumField.Description.Locale = new String("en-US"); enumField.Description.Text = new String(entry.Description); } // TODO: Add check for duplicates enumDescription.Fields.Add(enumField); } }
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 TValue GetValue <TValue>() { return(_cell.GetValue <TValue>()); }
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 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 List <KeyEntry> Read(string Name) { List <KeyEntry> keyEntries = new List <KeyEntry>(); if (CommonConfig == null || Workbook == null) { return(keyEntries); } IXLWorksheet keySheet = Workbook.Worksheets.Worksheet(Name); IXLRows rows = keySheet.RowsUsed(); bool firstRow = true; foreach (IXLRow row in rows) { if (firstRow) { firstRow = false; continue; } KeyEntry entry = new KeyEntry(); // Index IXLCell indexCell = row.Cell("A"); if (string.IsNullOrWhiteSpace(indexCell.GetString())) { Logger.Error($"Empty index cell in row {row.RowNumber()}"); continue; } ushort index = ushort.MinValue; try { index = indexCell.GetValue <ushort>(); } catch (FormatException e) { Logger.Error($"Unable to parse index '{indexCell.GetString()}' as int in row {row.RowNumber()}", e); continue; } entry.Index = index; // Name IXLCell nameCell = row.Cell("B"); if (string.IsNullOrWhiteSpace(nameCell.GetString())) { Logger.Error($"Name is empty in row {row.RowNumber()}"); continue; } entry.Name = nameCell.GetString(); // Description IXLCell descriptionCell = row.Cell("C"); if (string.IsNullOrWhiteSpace(descriptionCell.GetString())) { Logger.Info($"Description is empty in row {row.RowNumber()}"); } entry.Description = descriptionCell.GetString(); // DataType IXLCell dataTypeCell = row.Cell("D"); if (ReadConfiguration.TryGetNodeID(dataTypeCell.GetString(), out NodeID tempID, CommonConfig)) { entry.DataType = tempID; }
public List <DeltaEntry> Read(string Name, bool isGrouped = false) { List <DeltaEntry> deltaEntries = new List <DeltaEntry>(); if (CommonConfig == null || Workbook == null) { return(deltaEntries); } IXLWorksheet keySheet = Workbook.Worksheets.Worksheet(Name); IXLRows rows = keySheet.RowsUsed(); bool firstRow = true; foreach (IXLRow row in rows) { if (firstRow) { firstRow = false; continue; } DeltaEntry entry = new DeltaEntry(); // Index IXLCell indexCell = row.Cell("A"); if (string.IsNullOrWhiteSpace(indexCell.GetString())) { Logger.Error($"Empty index cell in row {row.RowNumber()}"); continue; } ushort index = ushort.MinValue; try { index = indexCell.GetValue <ushort>(); } catch (FormatException e) { Logger.Error($"Unable to parse index '{indexCell.GetString()}' as ushort in row {row.RowNumber()}", e); continue; } entry.Index = index; //Grouped Delta Entry if (isGrouped) { entry.DataType = KeyEntries.First() .DataType; } else { // Map DataType from KeyList if (KeyEntries == null || KeyEntries.Count == 0) { Logger.Error("Cannot parse value of DeltaEntry, because KeyEntries are not available."); continue; } if (KeyEntries.Count < entry.Index) { Logger.Error($"Cannot parse value of DeltaEntry, because index [{entry.Index}] of Delta Entry is not available in KeyEntries List (count is {KeyEntries.Count})." ); continue; } KeyEntry keyEntry = KeyEntries[entry.Index]; entry.DataType = keyEntry.DataType; } // Orcat IXLCell orcatCell = row.Cell("B"); if (ParseOrcat(row, orcatCell, out byte?orcatValue) && orcatValue.HasValue) { entry.Orcat = orcatValue.Value; } // Quality IXLCell qualityCell = row.Cell("C"); if (ParseQuality(row, qualityCell, out ushort?qualityValue) && qualityValue.HasValue) { entry.Quality = qualityValue.Value; } // Timestamp IXLCell timeStampCell = row.Cell("D"); if (ParseTimeStamp(row, timeStampCell, out long?timeStampValue) && timeStampValue.HasValue) { entry.TimeStamp = timeStampValue.Value; } // Value IXLCell valueCell = row.Cell("E"); IXLCell value2Cell = row.Cell("F"); IEntry baseEntry = entry; if (!TryParse(valueCell, value2Cell, ref baseEntry)) { continue; } //Name if (isGrouped) { IXLCell cellName = row.Cell("F"); if (!string.IsNullOrWhiteSpace(cellName.GetString())) { try { entry.Name = cellName.GetValue <string>(); } catch (FormatException exception) { Logger.Error($"Unable to parse '{qualityCell.GetString()}' in row {row.RowNumber()} as UInt16", exception); } } } deltaEntries.Add(entry); } return(deltaEntries); }
internal bool TryParse(IXLCell cell, IXLCell cell2, ref IEntry entry) { string stringValue = cell.GetString(); string stringValue2 = cell2.GetString(); if (string.IsNullOrWhiteSpace(stringValue)) { return(false); } if (entry.DataType == SPSValue.PreDefinedNodeID || entry.DataType == SPSEvent.PreDefinedNodeID) { bool value; try { value = cell.GetValue <bool>(); } catch (FormatException exception) { Logger.Error($"Unable to parse '{stringValue}' in row {cell.WorksheetRow().RowNumber()} as bool.", exception); return(false); } entry.Value = value; return(true); } if (entry.DataType == DPSValue.PreDefinedNodeID || entry.DataType == DPSEvent.PreDefinedNodeID) { byte value; try { value = cell.GetValue <byte>(); } catch (FormatException exception) { Logger.Error($"Unable to parse '{stringValue}' in row {cell.WorksheetRow().RowNumber()} as byte.", exception); return(false); } entry.Value = value; return(true); } if (entry.DataType == MeasuredValue.PreDefinedNodeID || entry.DataType == MeasuredValueEvent.PreDefinedNodeID) { float value; try { value = cell.GetValue <float>(); } catch (FormatException exception) { Logger.Error($"Unable to parse '{stringValue}' in row {cell.WorksheetRow().RowNumber()} as float.", exception); return(false); } entry.Value = value; return(true); } if (entry.DataType == StepPosValue.PreDefinedNodeID || entry.DataType == StepPosEvent.PreDefinedNodeID) { int value; bool value2; try { value = cell.GetValue <int>(); value2 = cell2.GetValue <bool>(); } catch (FormatException exception) { Logger.Error($"Unable to parse '{stringValue}' and '{stringValue2}' in row {cell.WorksheetRow().RowNumber()} as float and bool.", exception); return(false); } entry.Value = value; entry.Value2 = value2; return(true); } if (entry.DataType == CounterValue.PreDefinedNodeID) { long value; float value2; try { value = cell.GetValue <int>(); value2 = cell2.GetValue <float>(); } catch (FormatException exception) { Logger.Error($"Unable to parse '{stringValue}' and '{stringValue2}' in row {cell.WorksheetRow().RowNumber()} as int and float.", exception); return(false); } entry.Value = value; entry.Value2 = value2; return(true); } if (entry.DataType == ComplexMeasuredValue.PreDefinedNodeID) { float value; float value2; try { value = cell.GetValue <float>(); value2 = cell2.GetValue <float>(); } catch (FormatException exception) { Logger.Error($"Unable to parse '{stringValue}' and '{stringValue2}' in row {cell.WorksheetRow().RowNumber()} as float and float.", exception); return(false); } entry.Value = value; entry.Value2 = value2; return(true); } if (entry.DataType == StringEvent.PreDefinedNodeID) { string value; try { value = cell.GetValue <string>(); } catch (FormatException exception) { Logger.Error($"Unable to parse '{stringValue}' in row {cell.WorksheetRow().RowNumber()} as string.", exception); return(false); } entry.Value = value; return(true); } if (ProcessValueFactory.GetNodeIDType(entry.DataType) == NodeIDType.GroupDataTypeTimeSeries) { float value; try { value = cell.GetValue <float>(); } catch (FormatException exception) { Logger.Error($"Unable to parse '{stringValue}' in row {cell.WorksheetRow().RowNumber()} as string.", exception); return(false); } entry.Value = value; return(true); } // For everything else we assume it's an Integer or an Enum int enumValue; try { enumValue = cell.GetValue <int>(); } catch (FormatException exception) { Logger.Error($"Unable to parse '{stringValue}' in row {cell.WorksheetRow().RowNumber()} as int.", exception); return(false); } entry.Value = enumValue; return(true); }