protected bool ParseTimeStamp(IXLRow row, IXLCell timeStampCell, out long?value) { value = null; if (!string.IsNullOrWhiteSpace(timeStampCell.GetString())) { try { value = long.Parse(timeStampCell.RichText.Text, CultureInfo.InvariantCulture); } catch (FormatException exception) { Logger.Error($"Unable to parse '{timeStampCell.GetString()}' in row {row.RowNumber()} as Int64", exception); return(false); } catch (ArgumentException exception) { Logger.Error($"Unable to parse '{timeStampCell.GetString()}' in row {row.RowNumber()} as Int64", exception); return(false); } catch (OverflowException exception) { Logger.Error($"Unable to parse '{timeStampCell.GetString()}' in row {row.RowNumber()} as Int64", exception); return(false); } } return(true); }
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 TemplateCell(int row, int column, IXLCell xlCell) : this() { XLCell = xlCell; Row = row; Column = column; if (xlCell != null) { Value = xlCell.Value; Style = xlCell.Style; if (xlCell.HasFormula) { Formula = xlCell.FormulaA1; CellType = TemplateCellType.Formula; } else { CellType = TemplateCellType.Value; var strVal = xlCell.GetString(); if (strVal.StartsWith("&=")) { CellType = TemplateCellType.Formula; Formula = strVal.Substring(2); } if (strVal.Contains("{{")) { IsCalculated = true; } } } }
/// <summary> /// Reads a row of values from the current position and moves down a row /// </summary> /// <returns></returns> public List <string> ReadRow() { IXLCell position = current; var values = new List <string>(); string value = current.GetString(); while (!string.IsNullOrEmpty(value)) { values.Add(value); current = current.CellRight(); value = current.GetString(); } current = position.CellBelow(); return(values); }
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); }
private string GetStringValueFromCell(IXLCell cell) { if (cell.IsMerged()) { return(cell.MergedRange().Cell(1, 1).GetString()); } return(cell.GetString()); }
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); }
public void ValueSetToNull() { string expected = String.Empty; IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLCell cell = ws.Cell(1, 1); cell.Value = new DateTime(2000, 1, 2); cell.Value = null; Assert.AreEqual(expected, cell.GetString()); Assert.AreEqual(expected, cell.Value); cell.Value = new DateTime(2000, 1, 2); cell.SetValue(null as string); Assert.AreEqual(expected, cell.GetString()); Assert.AreEqual(expected, cell.Value); }
private void MatchCallInfoListName(IXLCell cell, ref CallInfoList listInfo, ref CallInfoSymptom symptom, ref CallInfoTime time) { try { switch (cell.WorksheetColumn().FirstCell().GetString().Trim().ToLower()) { case "contactperson": listInfo.ContactPerson.Add(HttpUtility.UrlEncode(cell.GetString())); break; case "location": listInfo.Location.Add(HttpUtility.UrlEncode(cell.GetString())); break; case "company": listInfo.Company.Add(HttpUtility.UrlEncode(cell.GetString())); break; case "requesttype": symptom.RequestType = HttpUtility.UrlEncode(cell.GetString()); break; case "symptom": symptom.Symptom = HttpUtility.UrlEncode(cell.GetString()); break; case "scheduletime": time.ScheduleTime = HttpUtility.UrlEncode(cell.GetString()); break; case "servetime1": time.ServeTime1 = HttpUtility.UrlEncode(cell.GetString()); break; case "servetime2": time.ServeTime2 = HttpUtility.UrlEncode(cell.GetString()); break; case "servicedescription": symptom.ServiceDescription = HttpUtility.UrlEncode(cell.GetString()); break; } } catch (Exception) { throw; } }
public static string GetStringOrDefault(this IXLCell cell) { try { return(cell.HasFormula ? cell.CachedValue.ToString() : cell.GetString()); } catch (Exception) { return(cell.CachedValue.ToString()); } }
public static bool GetYesNo(this IXLCell cell) { var value = cell.GetString(); switch (value) { case "Y": return(true); case "N": return(false); } throw new Exception(); }
public void ValueSetToNull() { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLCell cell = ws.Cell(1, 1); cell.Value = new DateTime(2000, 1, 2); cell.Value = null; string actual = cell.GetString(); string expected = String.Empty; Assert.AreEqual(expected, actual); }
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 static FuelInfo GetFuelInfo(this IXLCell cell) { var value = cell.GetString(); switch (value.First()) { case 'D': { switch (value) { case "DU": return(new FuelInfo { FuelType = FuelType.Diesel }); default: throw new Exception(); } } case 'G': { switch (value) { case "G": return(new FuelInfo { FuelType = FuelType.Gasoline, FuelGrade = FuelGrade.Regular }); case "GM": return(new FuelInfo { FuelType = FuelType.Gasoline, FuelGrade = FuelGrade.MidGradeRecommended }); case "GP": return(new FuelInfo { FuelType = FuelType.Gasoline, FuelGrade = FuelGrade.PremiumRecommended }); case "GPR": return(new FuelInfo { FuelType = FuelType.Gasoline, FuelGrade = FuelGrade.PremiumRequired }); default: throw new Exception(); } } } throw new Exception(); }
public static bool IsMarkedCell(IXLCell cell, MarkerOptions markerOptions) { if (cell.DataType == XLDataType.Text && !cell.HasFormula) { var stringCellValue = cell.GetString().Trim(); if (stringCellValue.Length < (markerOptions.Prefix.Length + markerOptions.Suffix.Length)) { return(false); } var isPrefixMatch = stringCellValue.Substring(0, markerOptions.Prefix.Length) == markerOptions.Prefix; var isSuffixMatch = stringCellValue.Substring(stringCellValue.Length - markerOptions.Suffix.Length, markerOptions.Suffix.Length) == markerOptions.Suffix; if (isPrefixMatch && isSuffixMatch) { return(true); } } return(false); }
public void AddTextTest1() { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLCell cell = ws.Cell(1, 1); IXLRichText richString = cell.RichText; string text = "Hello"; richString.AddText(text).SetBold().SetFontColor(XLColor.Red); Assert.AreEqual(cell.GetString(), text); Assert.AreEqual(cell.RichText.First().Bold, true); Assert.AreEqual(cell.RichText.First().FontColor, XLColor.Red); Assert.AreEqual(1, richString.Count); richString.AddText("World"); Assert.AreEqual(richString.First().Text, text, "Item in collection is not the same as the one returned"); }
public static DrivetrainType GetDrivetrainType(this IXLCell cell) { var value = cell.GetString(); switch (value) { case "A": return(DrivetrainType.AllWheelDrive); case "F": return(DrivetrainType.FrontWheelDrive); case "R": return(DrivetrainType.RearWheelDrive); case "4": return(DrivetrainType.FourWheelDrive); case "P": return(DrivetrainType.PartTimeFourWheelDrive); } throw new Exception(); }
public void AccessRichTextTest1() { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLCell cell = ws.Cell(1, 1); cell.RichText.AddText("12"); cell.DataType = XLCellValues.Number; Assert.AreEqual(12.0, cell.GetDouble()); IXLRichText richText = cell.RichText; Assert.AreEqual("12", richText.ToString()); richText.AddText("34"); Assert.AreEqual("1234", cell.GetString()); Assert.AreEqual(XLCellValues.Number, cell.DataType); Assert.AreEqual(1234.0, cell.GetDouble()); }
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)); } } }
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 string GetString() { return(_cell.GetString()); }
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 static string ExtractMarkerValue(IXLCell cell, MarkerOptions markerOptions) { var stringCellValue = cell.GetString().Trim(); return(stringCellValue.Substring(markerOptions.Prefix.Length, stringCellValue.Length - (markerOptions.Prefix.Length + markerOptions.Suffix.Length))); }
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 <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); }
public static TEnum GetEnum <TEnum>(this IXLCell cell) where TEnum : struct, Enum { var value = cell.GetString(); return(Enum.Parse <TEnum>(value)); }
/// <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); }
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); } } }
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); } } } } }