/// <summary>
/// Get the template for an item given the TemplateSource and Global or spreadsheet values
/// </summary>
/// <returns>Sitecore TemplateItem</returns>
private SCItems.Item getParent(XlsMapping mappingData)
{
if (mappingData.ParentIDSource == Enumerations.ParentSource.Global)
{
return(this._parent);
}
else
{
string parentFieldVal = mappingData.Source.DataSource.Rows[this._currentItemIndex][mappingData.ParentColumn].ToString();
var referenceMatch = this._referenceRegEx.Match(parentFieldVal);
Guid parentGuid = Guid.Empty;
if (referenceMatch.Success)
{
var captures = referenceMatch.Groups.Cast <Group>().Skip(1).Where(g => g.Success);
string sheetName = captures.ElementAt(0).Value;
// convert to 0 index row
int row = int.Parse(captures.ElementAt(1).Value) - 1;
XlsMappingSource parentSource = this.MappingSources.FirstOrDefault(ms => ms.Name == sheetName);
if (parentSource != null)
{
parentFieldVal = parentSource.DataSource.Rows[row]["SitecoreID"].ToString();
}
else
{
parentFieldVal = "";
}
}
parentGuid = Guid.Parse(parentFieldVal);
return(this.GetItem(parentGuid));
}
}
/// <summary>
/// Remove a Mapping from ImportMappings using name of Source in ImportSources
/// </summary>
/// <param name="sourceName">Name of MappingSource for Mapping</param>
public void RemoveMapping(string sourceName)
{
XlsMapping mapping = this.ImportMappings.FirstOrDefault(m => m.Source.Name == sourceName);
if (mapping != null)
{
this.removeMapping(mapping);
}
}
/// <summary>
/// Remove a Mapping from ImportMappings using index of Source in ImportSources
/// </summary>
/// <param name="sourceIndex">Index of MappingSource for Mapping</param>
public void RemoveMapping(int sourceIndex)
{
XlsMapping mapping = this.ImportMappings.FirstOrDefault(m => m.Source.WorksheetIndex == sourceIndex);
if (mapping != null)
{
this.removeMapping(mapping);
}
}
/// <summary>
/// Adds a new Mapping from ImportSources to the ImportMappings using ImportSource index
/// </summary>
/// <param name="sourceIndex">Index of MappingSource to add according to ExcelWorksheet number</param>
/// <param name="firstRowLabels">Specify whether first row of Source is label row</param>
/// <returns>New Mapping</returns>
public XlsMapping AddMapping(int sourceIndex, bool firstRowLabels = false)
{
XlsMappingSource source = this.MappingSources.FirstOrDefault(ms => ms.WorksheetIndex == sourceIndex);
if (source == null)
{
throw new NullReferenceException(string.Format("No MappingSource found with specified index of {0}.", sourceIndex));
}
XlsMapping mapping = new XlsMapping(source, firstRowLabels);
this.addMapping(mapping);
return(mapping);
}
/// <summary>
/// Add a new mapping to the ImportMappings
/// </summary>
/// <param name="mapping">Mapping to add</param>
/// <param name="skipInvalid">Skip over mappings that don't relate to valid sources. If not skipped, invalid mappings will throw error.</param>
public void AddMapping(XlsMapping mapping, bool skipInvalid = false)
{
if (mapping != null && mapping.Source != null)
{
var source = this.MappingSources.FirstOrDefault(ms => ms.Name == mapping.Source.Name);
if (source != null)
{
mapping.Source.DataSource = source.DataSource;
this.addMapping(mapping);
}
else if (!skipInvalid)
{
throw new ArgumentException("A mapping relating to a valid MappingSource must be provided.", "mapping.Source.Name: " + mapping.Source.Name);
}
}
else if (!skipInvalid)
{
throw new ArgumentException("A mapping relating to a valid MappingSource must be provided.", "mapping.Source.Name: " + mapping.Source.Name);
}
}
/// <summary>
/// Validate required values for Import Job
/// </summary>
/// <returns>True if validation succeeds</returns>
private bool validateData(XlsMapping mappingData)
{
var errorList = new List <string>();
if (mappingData.Source.DataSource.Rows.Count == 0)
{
errorList.Add("Data source has no data. Verify supplied spreadsheet has valid data.");
}
if (mappingData.NameColumn < 0 || mappingData.TemplateID == Guid.Empty || (mappingData.ParentIDSource == Enumerations.ParentSource.Item && mappingData.ParentColumn < 0))
{
System.Text.StringBuilder argError = new System.Text.StringBuilder("MappingData must supply at least Name, ");
if (mappingData.ParentIDSource == Enumerations.ParentSource.Global)
{
argError.Append("Parent, and Template mappings.");
}
else
{
argError.Append("and Template mappings.");
}
errorList.Add(argError.ToString());
}
if (mappingData.ParentIDSource == Enumerations.ParentSource.Global && mappingData.GlobalParentID == Guid.Empty)
{
errorList.Add("A valid GlobalParentID must be specified when TemplateMapSource is specified as Global");
}
// check errors and issue validation return
if (errorList.Any())
{
foreach (var error in errorList)
{
LogStatus(error);
}
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// Adds a new Mapping from ImportSources to the ImportMappings using ImportSource name
/// </summary>
/// <param name="sourceName">Name of MappingSource to add according to ExcelWorksheet name</param>
/// <param name="firstRowLabels">Specify whether first row of Source is label row</param>
/// <returns>New Mapping</returns>
public XlsMapping AddMapping(string sourceName, bool firstRowLabels = false)
{
XlsMappingSource mapSource = this.MappingSources.FirstOrDefault(ms => ms.Name == sourceName);
if (mapSource == null)
{
StringBuilder sb = new StringBuilder();
if (string.IsNullOrWhiteSpace(sourceName))
{
sb.Append("SourceName cannot be empty.");
}
else
{
sb.AppendFormat("Could not find MappingSource with name \"{0}\". Verify a MappingSource with the supplied name exists.", sourceName);
}
throw new ArgumentException(sb.ToString(), "sourceName");
}
XlsMapping mapping = new XlsMapping(mapSource, firstRowLabels);
this.addMapping(mapping);
return(mapping);
}
/// <summary>
/// Import data from supplied mapping
/// </summary>
/// <param name="currentMapping">Mapping to import from</param>
private void importData(XlsMapping currentMapping)
{
SetParent(currentMapping);
var template = (SCItems.TemplateItem) this.GetItem(currentMapping.TemplateID);
var ds = currentMapping.Source.DataSource;
this._currentItemIndex = (currentMapping.IsFirstRowLabel ? 1 : 0);
for (; _currentItemIndex < ds.Rows.Count; this._currentItemIndex++)
{
if (checkContinue())
{
try
{
// run process
var row = ds.Rows[this._currentItemIndex];
var name = row[currentMapping.NameColumn].ToString();
if (currentMapping.CleanNames)
{
name = SCItems.ItemUtil.ProposeValidItemName(name);
}
var parent = this.getParent(currentMapping);
if (!string.IsNullOrWhiteSpace(name) && parent != null)
{
var newItem = template.CreateItemFrom(name, parent);
row["SitecoreID"] = newItem.ID;
var fields = newItem.GetEditableFields();
using (new SC.SecurityModel.SecurityDisabler())
{
using (new SCItems.EditContext(newItem))
{
foreach (var mapping in currentMapping.FieldMappings)
{
try
{
var field = fields.FirstOrDefault(f => f.Name.ToLower() == mapping.FieldName.ToLower());
if (field != null)
{
var fieldValue = row[mapping.Column].ToString();
if (!string.IsNullOrWhiteSpace(fieldValue))
{
field.SetValueByType(fieldValue);
}
}
}
catch (Exception ex)
{
LogStatus($"[Field: {mapping.FieldName}] {ex.Message}");
}
}
}
}
}
else
{
LogStatus("Name and Parent cannot be blank.");
}
// report progress
this.CurrentPercent = ((this._currentItemIndex + 1) * 100) / currentMapping.Source.DataSource.Rows.Count;
if (this.IsAsync)
{
this.BgWorker.ReportProgress(this.CurrentPercent, currentMapping.Source.Name);
}
}
catch (Exception ex)
{
LogStatus(ex.Message);
}
}
else
{
break;
}
}
}
/// <summary>
/// Method to simplify removeMapping
/// </summary>
/// <param name="mapping">Mapping to remove</param>
private void removeMapping(XlsMapping mapping)
{
this.ImportMappings.Remove(mapping);
}
/// <summary>
/// Method to simplify addMapping
/// </summary>
/// <param name="mapping">Mapping to add</param>
private void addMapping(XlsMapping mapping)
{
this.ImportMappings.Add(mapping);
}
