public override RowImportResult DoUpdate(ExcelRange rowData, int rowNumber, IContent content, ExcelImporterConfiguration configuration) { RowImportResult result = new RowImportResult(); result.IsSuccess = true; result.Message = $"Update data from excel successfully. Sheet {rowData.SheetName} - row {rowData.IndexInSheet}. Content: {content.ContentLink} - {content.Name}"; try { var contentData = content as ContentData; var contentToModify = (PageData)contentData.CreateWritableClone(); var columnMappings = configuration.ColumnMappings; foreach (var columnMapping in columnMappings) { var colNumber = columnMapping.ExcelColumnNumber; var parser = columnMapping.Parser; var excelValue = rowData.Range.Cell(1, colNumber).GetValue <object>(); if ((excelValue == null || string.IsNullOrEmpty(excelValue + "")) && columnMapping.UseDefaultValue) { excelValue = columnMapping.DefaultValue; } var propertyIndicator = columnMapping.PropertyIndicator; _propertyIndicatorHelper.SaveProperty(contentToModify, columnMapping, excelValue); //ReloadContent(contentToModify); } var defaultMappings = configuration.DefaultValueMappings; foreach (var defaultMapping in defaultMappings) { var parser = defaultMapping.Parser; var defaultValue = defaultMapping.DefaultValue; var propertyIndicator = defaultMapping.PropertyIndicator; _propertyIndicatorHelper.SaveProperty(contentToModify, defaultMapping, defaultValue); //ReloadContent(contentToModify); } result.Content = contentToModify; } catch (Exception ex) { _logger.Error("Update data from excel error: ", ex); result.IsSuccess = false; result.Message = $"Update data from excel error: {ex.Message}. Issue at sheet {rowData.SheetName} - row {rowData.IndexInSheet}. Content Id: {content.ContentLink}"; } return(result); }
/// <summary> /// Step by step import. /// 1. Read the excel file with IExcelReader /// 2. Validate all rows wih IExcelValidators /// 3. Get target contents based on content roots and target paget type /// 4. Loop through all rows, find matching content using IRowToContentIndicator, update / insert content /// </summary> /// <param name="workbook"></param> /// <param name="importerConfiguration"></param> /// <returns></returns> public virtual ExcelImportResult DoImport(ExcelWorkbook workbook, ExcelImporterConfiguration importerConfiguration) { ExcelImportResult importResult = new ExcelImportResult(); List <IContent> updatedContents = new List <IContent>(); try { importResult.IsSuccess = true; var excelReader = importerConfiguration.ExcelReader; var rawExcelData = excelReader.Read(workbook, importerConfiguration); importResult.ExcelRowCount = rawExcelData.Count; var validationResults = ValidateData(rawExcelData, importerConfiguration.ExcelValidators); if (validationResults.Any()) { importResult.IsSuccess = false; foreach (var validationResult in validationResults) { importResult.AddMessage(validationResult.Message); } return(importResult); } var targetPageType = importerConfiguration.TargetPageType; var targetContents = GetTargetContents(importerConfiguration.ContentRoots, targetPageType); var rowToContentIndicator = importerConfiguration.RowIndicators; for (int i = 0; i < rawExcelData.Count; i++) { RowImportResult rowImportResult = null; var row = rawExcelData[i]; var rowNumber = importerConfiguration.RowStart + i; IContent contentToModify = null; var decision = rowToContentIndicator.FindContent(row, rowNumber, targetContents, out contentToModify); if (contentToModify == null) { decision = RowToContentFinderResult.ShouldIgnore; } switch (decision) { case RowToContentFinderResult.ShouldIgnore: importResult.AddMessage($"Sheet: {rawExcelData[i].SheetName} - Row {rawExcelData[i].IndexInSheet} ignored. No matching content found"); importResult.NoOfContentIgnored++; continue; case RowToContentFinderResult.ShouldUpdate: rowImportResult = DoUpdate(row, rowNumber, contentToModify, importerConfiguration); break; case RowToContentFinderResult.ShouldInsert: rowImportResult = DoInsert(row, rowNumber, contentToModify, importerConfiguration); break; default: break; } if (rowImportResult != null) { importResult.AddMessage(rowImportResult.Message); if (rowImportResult.IsSuccess) { switch (decision) { case RowToContentFinderResult.ShouldUpdate: updatedContents.Add(rowImportResult.Content); importResult.NoOfContentUpdated++; break; case RowToContentFinderResult.ShouldInsert: importResult.NoOfContentInserted++; break; default: continue; } } else { importResult.NoOfContentErrors++; } } } AfterImport?.Invoke(this, new AfterImportEventArgs() { UpdatedContents = updatedContents, Configuration = importerConfiguration }); } catch (Exception ex) { importResult.IsSuccess = false; importResult.Message.Add(ex.Message); _logger.Error("Office Excel Import Error", ex); } return(importResult); }