public static ExcelValueParse <double?> GetDoubleFromExcelRow(ExcelHeaderRow headerInfo, IXLRangeRow toProcess, string columnName) { var contentIdColumn = headerInfo.Columns.Single(x => string.Equals(x.ColumnHeader, columnName.TrimNullToEmpty(), StringComparison.CurrentCultureIgnoreCase)); var stringValue = toProcess.Worksheet.Cell(toProcess.RowNumber(), contentIdColumn.ExcelSheetColumn).Value .ToString(); if (string.IsNullOrWhiteSpace(stringValue)) { return new ExcelValueParse <double?> { ParsedValue = null, StringValue = stringValue, ValueParsed = true } } ; if (double.TryParse(stringValue, out var parsedValue)) { return new ExcelValueParse <double?> { ParsedValue = parsedValue, StringValue = stringValue, ValueParsed = true } } ; return(new ExcelValueParse <double?> { ParsedValue = null, StringValue = stringValue, ValueParsed = false }); }
public static ExcelValueParse <string> GetStringFromExcelRow(ExcelHeaderRow headerInfo, IXLRangeRow toProcess, string columnName) { var contentIdColumn = headerInfo.Columns.Single(x => string.Equals(x.ColumnHeader, columnName.TrimNullToEmpty(), StringComparison.CurrentCultureIgnoreCase)); var stringValue = toProcess.Worksheet.Cell(toProcess.RowNumber(), contentIdColumn.ExcelSheetColumn).Value .ToString(); return(new ExcelValueParse <string> { ParsedValue = stringValue, StringValue = stringValue, ValueParsed = true }); }
public static async Task <(bool hasError, string errorNotes, dynamic processContent)> ImportContentFromExcelRow( ExcelHeaderRow headerInfo, IXLRangeRow toProcess) { // ReSharper disable once StringLiteralTypo var contentId = GetGuidFromExcelRow(headerInfo, toProcess, "contentid"); dynamic dbEntry; if (contentId?.ParsedValue == null) { var newContentType = GetStringFromExcelRow(headerInfo, toProcess, "newcontenttype"); if (string.IsNullOrWhiteSpace(newContentType.ParsedValue)) { return(true, "No ContentId or NewContentId Found", null); } dbEntry = NewContentTypeToImportDbType(newContentType.ParsedValue); dbEntry.ContentId = Guid.NewGuid(); } else { var db = await Db.Context(); dbEntry = await db.ContentFromContentId(contentId.ParsedValue.Value); } if (dbEntry == null) { return(true, $"No Db Entry for {contentId.ParsedValue} found", null); } var errors = UpdateContentFromExcelRow(dbEntry, headerInfo, toProcess); return(errors.Count > 0, string.Join(Environment.NewLine, errors), dbEntry); }
public static List <string> UpdateContentFromExcelRow <T>(T toUpdate, ExcelHeaderRow headerInfo, IXLRangeRow toProcess) { // ReSharper disable StringLiteralTypo var skipColumns = new List <string> { "contentid", "id", "contentversion", "lastupdatedon", "originalfilename", "pointdetail" }; // ReSharper restore StringLiteralTypo var properties = typeof(T).GetProperties().ToList(); var propertyNames = properties.Select(x => x.Name.ToLower()).ToList(); var columnNames = headerInfo.Columns.Where(x => !string.IsNullOrWhiteSpace(x.ColumnHeader)) .Select(x => x.ColumnHeader.TrimNullToEmpty().ToLower()).ToList(); var namesToProcess = propertyNames.Intersect(columnNames).Where(x => !skipColumns.Any(x.StartsWith)) .ToList(); var propertiesToUpdate = properties.Where(x => namesToProcess.Contains(x.Name.ToLower())).ToList(); var returnString = new List <string>(); foreach (var loopProperties in propertiesToUpdate) { if (loopProperties.PropertyType == typeof(string)) { var excelResult = GetStringFromExcelRow(headerInfo, toProcess, loopProperties.Name); if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value) { returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}"); } loopProperties.SetValue(toUpdate, excelResult.ParsedValue.TrimNullToEmpty()); } else if (loopProperties.PropertyType == typeof(Guid?)) { var excelResult = GetGuidFromExcelRow(headerInfo, toProcess, loopProperties.Name); if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value) { returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}"); } loopProperties.SetValue(toUpdate, excelResult.ParsedValue); } else if (loopProperties.PropertyType == typeof(Guid)) { var excelResult = GetGuidFromExcelRow(headerInfo, toProcess, loopProperties.Name); if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value || excelResult.ParsedValue == null) { returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}"); } loopProperties.SetValue(toUpdate, excelResult.ParsedValue); } else if (loopProperties.PropertyType == typeof(DateTime?)) { var excelResult = GetDateTimeFromExcelRow(headerInfo, toProcess, loopProperties.Name); if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value) { returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}"); } loopProperties.SetValue(toUpdate, excelResult.ParsedValue); } else if (loopProperties.PropertyType == typeof(DateTime)) { var excelResult = GetDateTimeFromExcelRow(headerInfo, toProcess, loopProperties.Name); if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value || excelResult.ParsedValue == null) { returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}"); } loopProperties.SetValue(toUpdate, excelResult.ParsedValue); } else if (loopProperties.PropertyType == typeof(double?)) { var excelResult = GetDoubleFromExcelRow(headerInfo, toProcess, loopProperties.Name); if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value) { returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}"); } loopProperties.SetValue(toUpdate, excelResult.ParsedValue); } else if (loopProperties.PropertyType == typeof(double)) { var excelResult = GetDoubleFromExcelRow(headerInfo, toProcess, loopProperties.Name); if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value || excelResult.ParsedValue == null) { returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}"); } loopProperties.SetValue(toUpdate, excelResult.ParsedValue); } else if (loopProperties.PropertyType == typeof(int?)) { var excelResult = GetIntFromExcelRow(headerInfo, toProcess, loopProperties.Name); if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value) { returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}"); } loopProperties.SetValue(toUpdate, excelResult.ParsedValue); } else if (loopProperties.PropertyType == typeof(int)) { var excelResult = GetIntFromExcelRow(headerInfo, toProcess, loopProperties.Name); if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value || excelResult.ParsedValue == null) { returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}"); } loopProperties.SetValue(toUpdate, excelResult.ParsedValue); } else if (loopProperties.PropertyType == typeof(bool?)) { var excelResult = GetBoolFromExcelRow(headerInfo, toProcess, loopProperties.Name); if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value) { returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}"); } loopProperties.SetValue(toUpdate, excelResult.ParsedValue); } else if (loopProperties.PropertyType == typeof(bool)) { var excelResult = GetBoolFromExcelRow(headerInfo, toProcess, loopProperties.Name); if (excelResult.ValueParsed == null || !excelResult.ValueParsed.Value || excelResult.ParsedValue == null) { returnString.Add($"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}"); } loopProperties.SetValue(toUpdate, excelResult.ParsedValue); } else { returnString.Add( $"Row {toProcess.RowNumber()} - could not process {loopProperties.Name}, not a recognized type"); } } if (toUpdate is PointContentDto pointDto) { var excelResult = GetPointDetails(headerInfo, toProcess); if (excelResult.Any(x => x.ValueParsed == null) || excelResult.Any(x => !x.ValueParsed.Value) || excelResult.Any(x => x.ParsedValue == null)) { returnString.Add($"Row {toProcess.RowNumber()} - could not process Point Details"); } else { pointDto.PointDetails = excelResult.Select(x => x.ParsedValue).ToList(); pointDto.PointDetails.ForEach(x => x.PointContentId = pointDto.ContentId); } } return(returnString); }
public static async Task <ExcelContentTableImportResults> ImportExcelContentTable(IXLRange toProcess, IProgress <string> progress) { if (toProcess == null || toProcess.Rows().Count() < 2) { return new ExcelContentTableImportResults { HasError = true, ErrorNotes = "Nothing to process", ToUpdate = new List <ExcelImportContentUpdateSuggestion>() } } ; var headerInfo = new ExcelHeaderRow(toProcess.Row(1)); var errorNotes = new List <string>(); var updateList = new List <ExcelImportContentUpdateSuggestion>(); var db = await Db.Context(); var lastRow = toProcess.Rows().Last().RowNumber(); progress?.Report($"{lastRow} to Process"); foreach (var loopRow in toProcess.Rows().Skip(1)) { var importResult = await ImportContentFromExcelRow(headerInfo, loopRow); if (importResult.hasError) { errorNotes.Add($"Excel Row {loopRow.RowNumber()} - {importResult.errorNotes}"); continue; } try { Db.DefaultPropertyCleanup(importResult.processContent); importResult.processContent.Tags = Db.TagListCleanup(importResult.processContent.Tags); } catch { await EventLogContext.TryWriteDiagnosticMessageToLog( $"Excel Row {loopRow.RowNumber()} - Excel Import via dynamics - Tags threw an error on ContentId {importResult.processContent.ContentId ?? "New Entry"} - property probably not present", "Excel Import"); continue; } Guid contentId = importResult.processContent.ContentId; int contentDbId = importResult.processContent.Id; string differenceString; if (contentDbId > 0) { var currentDbEntry = await db.ContentFromContentId(contentId); var compareLogic = new CompareLogic { Config = { MembersToIgnore = new List <string> { "LastUpdatedBy" }, MaxDifferences= 100 } }; ComparisonResult comparisonResult = compareLogic.Compare(currentDbEntry, importResult.processContent); if (comparisonResult.AreEqual) { progress?.Report( $"Excel Row {loopRow.RowNumber()} of {lastRow} - No Changes - Title: {currentDbEntry.Title}"); continue; } var friendlyReport = new UserFriendlyReport(); differenceString = friendlyReport.OutputString(comparisonResult.Differences); importResult.processContent.LastUpdatedOn = DateTime.Now; } else { differenceString = "New Entry"; } GenerationReturn validationResult; switch (importResult.processContent) { case PhotoContent p: validationResult = await PhotoGenerator.Validate(p, UserSettingsSingleton.CurrentSettings().LocalMediaArchivePhotoContentFile(p)); break; case FileContent f: validationResult = await FileGenerator.Validate(f, UserSettingsSingleton.CurrentSettings().LocalMediaArchiveFileContentFile(f)); break; case ImageContent i: validationResult = await ImageGenerator.Validate(i, UserSettingsSingleton.CurrentSettings().LocalMediaArchiveImageContentFile(i)); break; case PointContentDto pc: validationResult = await PointGenerator.Validate(pc); break; case PostContent pc: validationResult = await PostGenerator.Validate(pc); break; case LinkContent l: validationResult = await LinkGenerator.Validate(l); break; case NoteContent n: validationResult = await NoteGenerator.Validate(n); break; default: validationResult = await GenerationReturn.Error("Excel Import - No Content Type Generator found?"); break; } if (validationResult.HasError) { errorNotes.Add($"Excel Row {loopRow.RowNumber()} - {validationResult.GenerationNote}"); progress?.Report($"Excel Row {loopRow.RowNumber()} of {lastRow} - Validation Error."); continue; } updateList.Add(new ExcelImportContentUpdateSuggestion { DifferenceNotes = differenceString, Title = importResult.processContent.Title, ToUpdate = importResult.processContent }); progress?.Report( $"Excel Row {loopRow.RowNumber()} of {lastRow} - Adding To Changed List ({updateList.Count}) - Title: {importResult.processContent.Title}"); } if (!errorNotes.Any()) { var internalContentIdDuplicates = updateList.Select(x => x.ToUpdate).GroupBy(x => x.ContentId) .Where(x => x.Count() > 1).Select(x => x.Key).Cast <Guid>().ToList(); if (internalContentIdDuplicates.Any()) { return new ExcelContentTableImportResults { HasError = true, ErrorNotes = $"Content Ids can only appear once in an update list - {string.Join(", ", internalContentIdDuplicates)}", ToUpdate = updateList } } ; var internalSlugDuplicates = updateList.Select(x => x.ToUpdate).Where(x => !(x is LinkContent)) .GroupBy(x => x.Slug).Where(x => x.Count() > 1).Select(x => x.Key).Cast <string>().ToList(); if (internalSlugDuplicates.Any()) { return new ExcelContentTableImportResults { HasError = true, ErrorNotes = $"This import appears to create duplicate slugs - {string.Join(", ", internalSlugDuplicates)}", ToUpdate = updateList } } ; } return(new ExcelContentTableImportResults { HasError = errorNotes.Any(), ErrorNotes = string.Join(Environment.NewLine, errorNotes), ToUpdate = updateList }); }
public static List <ExcelValueParse <PointDetail> > GetPointDetails(ExcelHeaderRow headerInfo, IXLRangeRow toProcess) { var contentColumns = headerInfo.Columns.Where(x => x.ColumnHeader.StartsWith("PointDetail")); var returnList = new List <ExcelValueParse <PointDetail> >(); foreach (var loopColumns in contentColumns) { var stringValue = toProcess.Worksheet.Cell(toProcess.RowNumber(), loopColumns.ExcelSheetColumn) .GetString(); if (string.IsNullOrWhiteSpace(stringValue)) { continue; } var toAdd = new ExcelValueParse <PointDetail> { StringValue = stringValue }; returnList.Add(toAdd); var splitList = stringValue.RemoveNewLines().TrimNullToEmpty().Split("||") .Select(x => x.TrimNullToEmpty()).ToList(); if (splitList.Count != 3) { toAdd.ParsedValue = null; toAdd.ValueParsed = false; continue; } PointDetail pointDetail; // // Content Id - new or db retrieved PointDetail() // if (splitList[0].Length <= 10 || !splitList[0].StartsWith("ContentId:")) { pointDetail = new PointDetail { ContentId = Guid.NewGuid(), CreatedOn = DateTime.Now }; } else { var contentIdString = splitList[0].Substring(10, splitList[0].Length - 10).TrimNullToEmpty(); if (!Guid.TryParse(contentIdString, out var contentId)) { toAdd.ParsedValue = null; toAdd.ValueParsed = false; continue; } var db = Db.Context().Result; var possiblePoint = db.PointDetails.Single(x => x.ContentId == contentId); //Content Id specified but no db entry - error, exit if (possiblePoint == null) { toAdd.ParsedValue = null; toAdd.ValueParsed = false; continue; } pointDetail = possiblePoint; pointDetail.LastUpdatedOn = DateTime.Now; } // //Get the data type first so it can be used to create a new point if needed // if (splitList[1].Length <= 5 || !splitList[1].StartsWith("Type:")) { toAdd.ParsedValue = null; toAdd.ValueParsed = false; continue; } var dataTypeString = splitList[1].Substring(5, splitList[1].Length - 5).TrimNullToEmpty(); if (!Db.PointDetailDataTypeIsValid(dataTypeString)) { toAdd.ParsedValue = null; toAdd.ValueParsed = false; continue; } pointDetail.DataType = dataTypeString; // // Point Detail Data // if (splitList[2].Length <= 5 || !splitList[2].StartsWith("Data:")) { //Empty Data - error toAdd.ParsedValue = null; toAdd.ValueParsed = false; continue; } try { var jsonString = splitList[2].Substring(5, splitList[2].Length - 5); var detailData = Db.PointDetailDataFromIdentifierAndJson(dataTypeString, jsonString); var validationResult = detailData.Validate(); if (!validationResult.isValid) { toAdd.ParsedValue = null; toAdd.ValueParsed = false; continue; } pointDetail.StructuredDataAsJson = jsonString; } catch { toAdd.ParsedValue = null; toAdd.ValueParsed = false; continue; } toAdd.ParsedValue = pointDetail; toAdd.ValueParsed = true; } return(returnList); }