public List <ExcelRange> Read(ExcelWorkbook workbook, ExcelImporterConfiguration configuration)
{
var result = new List <ExcelRange>();
var xmlSheets = workbook.GetSheets();
foreach (var sheet in xmlSheets)
{
if (sheet.Worksheet.Visibility == ClosedXML.Excel.XLWorksheetVisibility.Visible)
{
var colTotal = sheet.Worksheet.ColumnsUsed().Count();
var rowTotal = sheet.Worksheet.LastRowUsed().RowNumber();
for (int i = 0; i < rowTotal - configuration.RowStart + 1; i++)
{
var row = i + configuration.RowStart;
var xlRange = sheet.GetRange(row, 1, rowTotal, colTotal);
if (xlRange.HasData(row))
{
xlRange.IndexInSheet = row;
xlRange.SheetName = sheet.Worksheet.Name;
result.Add(xlRange);
}
}
}
}
return(result);
}
public object Parse(IContent currentContent, object excelData, ExcelImporterConfiguration configuration)
{
var urlResolver = ServiceLocator.Current.GetInstance <IUrlResolver>();
if (excelData == null)
{
return(null);
}
var link = excelData.ToString();
var content = urlResolver.Route(new UrlBuilder(link));
if (content != null)
{
return(content.ContentLink);
}
return(null);
}
public object Parse(IContent currentContent, object excelData, ExcelImporterConfiguration configuration)
{
if (excelData == null)
{
return(null);
}
int id;
if (int.TryParse(excelData + "", out id))
{
var contentRef = new ContentReference(id);
if (!ContentReference.IsNullOrEmpty(contentRef))
{
return(contentRef);
}
}
return(null);
}
public object Parse(IContent currentContent, object excelData, ExcelImporterConfiguration configuration)
{
if (excelData == null)
{
return(null);
}
var strData = excelData + "";
if (string.IsNullOrWhiteSpace(strData))
{
return(null);
}
if (strData.StartsWith("<p"))
{
return(strData);
}
strData = strData.Replace(System.Environment.NewLine, "<br />");
strData = strData.Replace("\n", "<br />");
return("<p>" + strData + "</p>");
}
public object Parse(IContent currentContent, object excelData, ExcelImporterConfiguration configuration)
{
var result = new List <ContentReference>();
var urlResolver = ServiceLocator.Current.GetInstance <IUrlResolver>();
if (excelData == null)
{
return(null);
}
var links = excelData.ToString().Split(
new[] { "\r\n", "\r", "\n" },
StringSplitOptions.None
);
foreach (var link in links)
{
var content = urlResolver.Route(new UrlBuilder(link));
if (content != null)
{
result.Add(content.ContentLink);
}
}
return(result);
}
public ExcelImporterTranslationResult()
{
Configuration = new ExcelImporterConfiguration();
}
/// <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);
}
public virtual RowImportResult DoInsert(ExcelRange row, int rowNumber, IContent content, ExcelImporterConfiguration configuration)
{
return(null);
}
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);
}
