/// <summary>
/// Iterated through spreadsheet and imports items to Global navigation list.
/// </summary>
/// <returns></returns>
private static ImportResult ImportSubmissionItemsFromSpreadSheet(FileInfo file, ClientContext ctx)
{
_logs = new List <LogItem>();
using (var package = new ExcelPackage(file))
{
if (!file.Name.EndsWith(".xlsx"))
{
return(ImportResult.IncorrectFileFormat);
}
var ws = package.Workbook.Worksheets[1];
if (ws == null)
{
return(ImportResult.IncorrectFileFormat);
}
if (!IsSpreadsheetValid(ws))
{
return(ImportResult.IncorrectFileFormat);
}
var dt = GetDataTableFromSpreadsheet(file);
if (dt == null || dt.Rows.Count == 0)
{
return(ImportResult.None);
}
Log($"Start Time: {DateTime.Now}");
_logs.Add(new LogItem(LogCategory.RowCount, dt.Rows.Count));
TaxonomySession taxonomySession = TaxonomySession.GetTaxonomySession(ctx);
ctx.Load(taxonomySession, ts => ts.TermStores);
ctx.ExecuteQuery();
var termStore = taxonomySession.GetDefaultSiteCollectionTermStore();
/*ctx.Load(termStore,
* store => store.Groups.Include(group => group.TermSets));
* ctx.ExecuteQuery();
*
* ctx.Load(termStore.Groups);
* ctx.ExecuteQuery();*/
TermGroup termGroup = termStore.GetSiteCollectionGroup(ctx.Site, false);
ctx.Load(termGroup);
ctx.Load(termGroup.TermSets);
ctx.ExecuteQuery();
foreach (var termSet in termGroup.TermSets)
{
var allTerms = termSet.GetAllTerms();
ctx.Load(allTerms);
ctx.ExecuteQuery();
termsetDictionary.Add(termSet.Name, allTerms);
}
var lists = WorksheetToListMap.GroupBy(x => x.ListName);
foreach (var list in lists)
{
var listName = list.Key;
if (string.IsNullOrEmpty(listName))
{
continue;
}
var columnNames = WorksheetToListMap.Where(x => x.ListName == listName).Select(x => x.ListColumnName);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i <= 1)
{
continue;
}
var row = dt.Rows[i];
try
{
ImportRow(ctx, termGroup, listName, row, columnNames.ToArray(), i, ref _logs);
}
catch (Exception ex)
{
//if (Debugger.IsAttached) Debugger.Break();
_logs.Add(new LogItem(LogCategory.Exceptions, ex.Message));
}
}
}
}
_logs.Add(new LogItem(LogCategory.DoneMessage, "Done"));
WriteToLog(_logs);
Log($"End Time: {DateTime.Now}");
return(ImportResult.None);
}
private static void ImportRow(ClientContext ctx, TermGroup termGroup, string listName, DataRow row, string[] columnNames, int rowIndex, ref List <LogItem> logs)
{
var web = ctx.Web;
ctx.Load(web);
ctx.ExecuteQuery();
ctx.Load(web.Lists);
ctx.ExecuteQuery();
var list = web.Lists.FirstOrDefault(r => Regex.IsMatch(r.Title, listName, RegexOptions.IgnoreCase | RegexOptions.Compiled));
ctx.Load(list);
var fields = list.Fields;
ctx.Load(fields);
ctx.ExecuteQuery();
var data = new List <DataFieldItem>();
foreach (var name in columnNames)
{
if (WorksheetToListMap.FirstOrDefault(x => x.ListName == listName && x.ListColumnName == name).Ignore)
{
continue;
}
var field = fields.FirstOrDefault(x => x.InternalName == name);
var mappedProperty = WorksheetToListMap.FirstOrDefault(x => x.ListName == listName && x.ListColumnName == name);
var value = row[mappedProperty.ColumnCode].ToString().Trim();
if (string.IsNullOrEmpty(value))
{
continue;
}
switch (field.TypeAsString)
{
case "TaxonomyFieldTypeMulti":
{
// log if term isnt found
var excelTerms = value.Split(new char[] { ',', ';' });
var f = field as TaxonomyField;
var terms = new List <string>();
for (int i = 0; i < excelTerms.Length; i++)
{
if (string.IsNullOrEmpty(excelTerms[i]))
{
continue;
}
Term term = null;
try
{
term = GetTermByLabel(termGroup, excelTerms[i], mappedProperty.TermSetName);
}
catch
{
if (!logs.Exists(x => x.Category == LogCategory.MissingTerm && x.LogValue.ToString() == excelTerms[i].ToString()))
{
logs.Add(new LogItem(LogCategory.MissingTerm, value, name, rowIndex));
}
}
/*
* if (term == null)
* {
* if (!logs.Exists(x => x.Category == LogCategory.MissingTerm && x.LogValue.ToString() == value.ToString()))
* logs.Add(new LogItem(LogCategory.MissingTerm, value, name, rowIndex));
* continue;
* }
*/
if (term != null)
{
terms.Add($"-1;#{excelTerms[i]}|{term.Id}");
}
}
var termString = string.Join(";#", terms.ToArray());
if (!string.IsNullOrEmpty(termString))
{
var o = new DataFieldItem {
Name = name,
SPField = field,
FieldValue = termString
};
data.Add(o);
}
}
break;
case "TaxonomyFieldType":
{
// log if term isnt found
var f = field as TaxonomyField;
//var term = GetTermByLabel(ctx, value, mappedProperty.TermGroup, mappedProperty.TermSetName);
var term = GetTermByLabel(termGroup, value, mappedProperty.TermSetName);
if (term == null)
{
if (!logs.Exists(x => x.Category == LogCategory.MissingTerm && x.LogValue.ToString() == value.ToString()))
{
logs.Add(new LogItem(LogCategory.MissingTerm, value, name, rowIndex));
}
break;
}
TaxonomyFieldValue taxValue = new TaxonomyFieldValue();
taxValue.TermGuid = term.Id.ToString();
taxValue.Label = term.Name;
taxValue.WssId = -1;
data.Add(new DataFieldItem
{
Name = name,
SPField = field,
FieldValue = term
});
}
break;
case "User":
var user = ctx.Web.SiteUsers.GetByEmail(value);
ctx.Load(user);
try { ctx.ExecuteQuery(); }
catch
{
if (!logs.Exists(x => x.Category == LogCategory.UserNotFound && x.LogValue.ToString() == value.ToString()))
{
logs.Add(new LogItem(LogCategory.UserNotFound, value, name, rowIndex));
}
continue;
}
var userValue = new FieldUserValue();
userValue.LookupId = user.Id;
data.Add(new DataFieldItem
{
Name = name,
SPField = field,
FieldValue = userValue
});
break;
case "DateTime":
DateTime d;
if (DateTime.TryParse(value, out d))
{
data.Add(new DataFieldItem
{
Name = name,
SPField = field,
FieldValue = d.ToShortDateString()
});
}
else
{
continue;
}
break;
case "Boolean":
data.Add(new DataFieldItem
{
Name = name,
SPField = field,
FieldValue = (value == "Yes" ? true : false)
});
break;
default:
data.Add(new DataFieldItem
{
Name = name,
SPField = field,
FieldValue = value
});
break;
}
}
var itemCreateInfo = new ListItemCreationInformation();
var newItem = list.AddItem(itemCreateInfo);
foreach (var d in data)
{
var field = d.SPField;
switch (field.TypeAsString)
{
case "TaxonomyFieldTypeMulti":
{
var f = field as TaxonomyField;
f.SetFieldValueByValueCollection(newItem, new TaxonomyFieldValueCollection(ctx, d.FieldValue.ToString(), f));
}
break;
case "TaxonomyFieldType":
{
var f = field as TaxonomyField;
f.SetFieldValueByValue(newItem, d.FieldValue as TaxonomyFieldValue);
}
break;
case "User":
newItem[field.InternalName] = d.FieldValue;
break;
case "DateTime":
newItem[field.InternalName] = d.FieldValue;
break;
case "Boolean":
newItem[field.InternalName] = d.FieldValue;
break;
default:
newItem[field.InternalName] = d.FieldValue;
break;
}
}
newItem["CEN_ClinicalReviewPhase"] = "Complete";
newItem["CEN_ProgressStatus"] = "Final";
newItem.Update();
try { ctx.ExecuteQuery(); }
catch (Exception ex)
{
//if (Debugger.IsAttached) Debugger.Break();
logs.Add(new LogItem(LogCategory.Exceptions, newItem.Id, newItem["Title"].ToString(), rowIndex));
}
}
