/// <summary>
/// Returns the index of a shared string.
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="stringItem">String to search for</param>
/// <returns>Index of a shared string. -1 if not found</returns>
public static int IndexOfSharedString(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string stringItem)
{
DocumentFormat.OpenXml.Spreadsheet.SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
bool found = false;
int index = 0;
foreach (DocumentFormat.OpenXml.Spreadsheet.SharedStringItem sharedString in sharedStringTable.Elements <DocumentFormat.OpenXml.Spreadsheet.SharedStringItem>())
{
if (sharedString.InnerText == stringItem)
{
found = true;
break;
}
index++;
}
return(found ? index : -1);
}
/// <summary>
/// Add a single string to shared strings table.
/// Shared string table is created if it doesn't exist.
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="stringItem">string to add</param>
/// <param name="save">Save the shared string table</param>
/// <returns></returns>
public static bool AddSharedString(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string stringItem, bool save = true)
{
DocumentFormat.OpenXml.Spreadsheet.SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
if (0 == sharedStringTable.Where(item => item.InnerText == stringItem).Count())
{
sharedStringTable.AppendChild(
new DocumentFormat.OpenXml.Spreadsheet.SharedStringItem(
new DocumentFormat.OpenXml.Spreadsheet.Text(stringItem)));
// Save the changes
if (save)
{
sharedStringTable.Save();
}
}
return(true);
}
static Dictionary <string, string> ReadSharedStringTable(Package.SpreadsheetDocument importDocument)
{
var result = new Dictionary <string, string>();
var importSharedStringTable = new Excel.SharedStringTable();
if (importDocument.WorkbookPart.SharedStringTablePart != null)
{
importSharedStringTable.Load(importDocument.WorkbookPart.SharedStringTablePart);
}
int index = 0;
foreach (var item in importSharedStringTable.Elements <Excel.SharedStringItem>())
{
result.Add(index.ToString(), item.Text != null ? item.Text.Text : null);
index++;
}
return(result);
}
public dynamic CreateJsonFromCSV(string filePath)
{
int recordNumber = 1;
Resource resource = new Resource();
List <dynamic> ResourcesList = new List <dynamic>();
List <dynamic> organizationsList = new List <dynamic>();
List <dynamic> articlesList = new List <dynamic>();
List <dynamic> organizationReviewsList = new List <dynamic>();
List <dynamic> Resources = new List <dynamic>();
List <string> sheetNames = new List <string>()
{
Constants.ArticleSheetName, Constants.ArticleSectionSheetName, Constants.VideoSheetName, Constants.AdditionalReadingSheetName, Constants.FormSheetName, Constants.OrganizationSheetName, Constants.OrganizationReviewSheetName, Constants.RelatedLinkSheetName
};
try
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(filePath, true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
Spreadsheet.Sheets sheets = workbookPart.Workbook.GetFirstChild <Spreadsheet.Sheets>();
foreach (Spreadsheet.Sheet sheet in sheets)
{
if (sheet.Name.HasValue && sheetNames.Find(a => a == sheet.Name.Value) != null)
{
Spreadsheet.Worksheet worksheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
Spreadsheet.SheetData sheetData = worksheet.Elements <Spreadsheet.SheetData>().First();
Spreadsheet.SharedStringTable sharedStringTable = spreadsheetDocument.WorkbookPart.SharedStringTablePart.SharedStringTable;
Dictionary <string, string> keyValuePairs = new Dictionary <string, string>();
string cellValue;
int counter = 0;
bool isValidated = false;
ClearVariableData();
topicTagIds = new List <TopicTag>();
locations = new List <Shared.Models.Location>();
string resourceIdCell = string.Empty;
string resourceType = GetResourceType(sheet.Name.Value);
foreach (Spreadsheet.Row row in sheetData.Elements <Spreadsheet.Row>())
{
if (counter == 1)
{
var resourceIdColumn = from a in keyValuePairs where a.Key == "Id" select a.Value.First().ToString();
if (resourceIdColumn.Count() > 0)
{
resourceIdCell = resourceIdColumn.First();
}
}
foreach (Spreadsheet.Cell cell in row.Elements <Spreadsheet.Cell>())
{
cellValue = cell.InnerText;
if (string.IsNullOrEmpty(cellValue))
{
if (!string.IsNullOrEmpty(resourceIdCell) && cell.CellReference == string.Concat(resourceIdCell + row.RowIndex))
{
cell.CellValue = new CellValue(Guid.NewGuid().ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.String);
workbookPart.Workbook.Save();
}
}
else if (!string.IsNullOrEmpty(cellValue))
{
string cellActualValue = string.Empty;
if (cell.DataType != null && cell.DataType == Spreadsheet.CellValues.SharedString)
{
cellActualValue = sharedStringTable.ElementAt(Int32.Parse(cellValue, CultureInfo.InvariantCulture)).InnerText;
}
else
{
cellActualValue = cellValue;
}
if (counter == 0)
{
keyValuePairs.Add(cellActualValue, cell.CellReference);
}
else
{
var headerValues = from a in keyValuePairs select a.Key;
if (!isValidated)
{
if (!ValidateHeader(headerValues.ToArray <string>(), recordNumber, resourceType))
{
break;
}
else
{
isValidated = true;
}
}
IEnumerable <string> keyValue = null;
if (cell.CellReference.Value.Length == 2)
{
keyValue = from a in keyValuePairs where a.Value.Take(1).First() == cell.CellReference.Value.Take(1).First() select a.Key;
}
else if (cell.CellReference.Value.Length == 3)
{
keyValue = from a in keyValuePairs where a.Value.Take(2).First() == cell.CellReference.Value.Take(2).First() select a.Key;
}
else if (cell.CellReference.Value.Length == 4)
{
keyValue = from a in keyValuePairs where a.Value.Take(3).First() == cell.CellReference.Value.Take(3).First() select a.Key;
}
if (keyValue.Count() > 0)
{
UpdateFormData(keyValue, cellActualValue, resourceType);
}
}
}
}
if (counter > 0)
{
InsertTopics topic = new InsertTopics();
locations = topic.GetLocations(state, county, city, zipcode);
if (resourceType == Constants.FormResourceType)
{
Form form = new Form()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
form.Validate();
ResourcesList.Add(form);
ClearVariableData();
}
if (resourceType == Constants.OrganizationResourceType)
{
Organization organization = new Organization()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
ResourceCategory = resourceCategory,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
Address = address,
Telephone = telephone,
Overview = overview,
Specialties = specialties,
EligibilityInformation = eligibilityInformation,
Qualifications = qualifications,
BusinessHours = businessHours,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
organization.Validate();
organizationsList.Add(organization);
ClearVariableData();
}
if (resourceType == Constants.OrganizationReview)
{
orgNameList.Add(organizationName);
orgFullNameList.Add(reviewerFullName);
orgTitleList.Add(reviewerTitle);
orgReviewTextList.Add(reviewText);
orgReviewerImageList.Add(reviewerImage);
ClearVariableData();
}
if (resourceType == Constants.ArticleResourceType)
{
Article article = new Article()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
Description = description,
ResourceType = resourceType,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
Overview = overview,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
article.Validate();
articlesList.Add(article);
ClearVariableData();
}
if (resourceType == Constants.ArticleContent)
{
articleNameList.Add(articleName);
headlineList.Add(headline);
contentList.Add(content);
ClearVariableData();
}
if (resourceType == Constants.VideoResourceType)
{
Video video = new Video()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
ResourceCategory = resourceCategory,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
Overview = overview,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
video.Validate();
ResourcesList.Add(video);
ClearVariableData();
}
if (resourceType == Constants.AdditionalReadingResourceType)
{
AdditionalReading additionalReading = new AdditionalReading()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
additionalReading.Validate();
ResourcesList.Add(additionalReading);
ClearVariableData();
}
if (resourceType == Constants.RelatedLinkResourceType)
{
RelatedLink relatedLink = new RelatedLink()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
relatedLink.Validate();
ResourcesList.Add(relatedLink);
ClearVariableData();
}
}
counter++;
recordNumber++;
}
}
}
}
foreach (var resourceList in organizationsList)
{
List <OrganizationReviewer> organizationReviewer = new List <OrganizationReviewer>();
OrganizationReviewer orgReviewer = new OrganizationReviewer();
for (int iterator = 0; iterator < orgNameList.Count; iterator++)
{
var na = orgNameList[iterator];
if (resourceList.Name == orgNameList[iterator])
{
orgReviewer = new OrganizationReviewer
{
ReviewerFullName = orgFullNameList[iterator],
ReviewerTitle = orgTitleList[iterator],
ReviewText = orgReviewTextList[iterator],
ReviewerImage = orgReviewerImageList[iterator]
};
organizationReviewer.Add(orgReviewer);
}
}
var serializedResult = JsonConvert.SerializeObject(organizationReviewer);
var orgReviewData = JsonConvert.DeserializeObject(serializedResult);
resourceList.Reviewer = organizationReviewer;
ResourcesList.Add(resourceList);
}
foreach (var articleList in articlesList)
{
List <ArticleContent> articleContentList = new List <ArticleContent>();
ArticleContent articleContents = new ArticleContent();
for (int iterator = 0; iterator < articleNameList.Count; iterator++)
{
var na = articleNameList[iterator];
if (articleList.Name == articleNameList[iterator])
{
articleContents = new ArticleContent
{
Headline = headlineList[iterator],
Content = contentList[iterator],
};
articleContentList.Add(articleContents);
}
}
var serializedResult = JsonConvert.SerializeObject(articleContentList);
var articleContentData = JsonConvert.DeserializeObject(serializedResult);
articleList.Contents = articleContentList;
ResourcesList.Add(articleList);
}
}
catch (Exception ex)
{
InsertTopics.ErrorLogging(ex, recordNumber);
Resources = null;
}
Resources = ResourcesList;
return(Resources);
}
public dynamic CreateJsonFromCSV(string filePath)
{
int recordNumber = 1;
Shared.Models.Topic topic = new Shared.Models.Topic();
List<dynamic> topicsList = new List<dynamic>();
List<dynamic> topics = new List<dynamic>();
try
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(filePath, true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
Spreadsheet.SharedStringTable sharedStringTable = spreadsheetDocument.WorkbookPart.SharedStringTablePart.SharedStringTable;
Spreadsheet.SheetData sheetData = worksheetPart.Worksheet.Elements<Spreadsheet.SheetData>().First();
Dictionary<string, string> keyValuePairs = new Dictionary<string, string>();
string cellValue;
int counter = 0;
bool isValidated = false;
foreach (Spreadsheet.Row row in sheetData.Elements<Spreadsheet.Row>())
{
dynamic id = null; string name = string.Empty; string keywords = string.Empty; string organizationalUnit = string.Empty;
string state = string.Empty; string county = string.Empty; string city = string.Empty; string zipcode = string.Empty;
string overview = string.Empty; string icon = string.Empty;
List<ParentTopicId> parentTopicIds = new List<ParentTopicId>();
List<Shared.Models.Location> locations = new List<Shared.Models.Location>();
string topicIdCell = string.Empty;
if (counter > 0)
{
var topicIdColumn = from a in keyValuePairs where a.Key == "Topic_ID*" select a.Value.First().ToString();
if (topicIdColumn.Count() > 0)
{
topicIdCell = topicIdColumn.First();
}
}
foreach (Spreadsheet.Cell cell in row.Elements<Spreadsheet.Cell>())
{
cellValue = cell.InnerText;
if (string.IsNullOrEmpty(cellValue))
{
if (!string.IsNullOrEmpty(topicIdCell) && cell.CellReference == string.Concat(topicIdCell + row.RowIndex))
{
cell.CellValue = new CellValue(Guid.NewGuid().ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.String);
workbookPart.Workbook.Save();
}
}
else if (!string.IsNullOrEmpty(cellValue))
{
string cellActualValue = string.Empty;
if (cell.DataType == Spreadsheet.CellValues.SharedString)
{
cellActualValue = sharedStringTable.ElementAt(Int32.Parse(cellValue,CultureInfo.InvariantCulture)).InnerText;
}
else
{
cellActualValue = cellValue;
}
if (counter == 0)
{
keyValuePairs.Add(cellActualValue, cell.CellReference);
}
else
{
var headerValues = from a in keyValuePairs select a.Key;
if (!isValidated)
{
if (!ValidateTopicHeader(headerValues.ToArray<string>(), recordNumber))
{
break;
}
else
{
isValidated = true;
}
}
IEnumerable<string> keyValue = null;
if (cell.CellReference.Value.Length == 2)
{
keyValue = from a in keyValuePairs where a.Value.Take(1).First() == cell.CellReference.Value.Take(1).First() select a.Key;
}
else if (cell.CellReference.Value.Length == 3)
{
keyValue = from a in keyValuePairs where a.Value.Take(2).First() == cell.CellReference.Value.Take(2).First() select a.Key;
}
else if (cell.CellReference.Value.Length == 4)
{
keyValue = from a in keyValuePairs where a.Value.Take(3).First() == cell.CellReference.Value.Take(3).First() select a.Key;
}
if (keyValue.Count() > 0)
{
string val = keyValue.First();
if (val.EndsWith("Topic_ID*", StringComparison.CurrentCultureIgnoreCase))
{
id = cellActualValue.Trim();
}
else if (val.EndsWith("Topic_Name*", StringComparison.CurrentCultureIgnoreCase))
{
name = cellActualValue.Trim();
}
else if (val.EndsWith("Parent_Topic*", StringComparison.CurrentCultureIgnoreCase))
{
string parentId = cellActualValue;
parentTopicIds = GetParentId(parentId);
}
else if (val.EndsWith("Keywords*", StringComparison.CurrentCultureIgnoreCase))
{
keywords = FormatData(cellActualValue);
}
else if (val.EndsWith("Organizational_Unit*", StringComparison.CurrentCultureIgnoreCase))
{
organizationalUnit = FormatData(cellActualValue);
}
else if (val.EndsWith("Location_State*", StringComparison.CurrentCultureIgnoreCase))
{
state = FormatData(cellActualValue);
}
else if (val.EndsWith("Location_County", StringComparison.CurrentCultureIgnoreCase))
{
county = FormatData(cellActualValue);
}
else if (val.EndsWith("Location_City", StringComparison.CurrentCultureIgnoreCase))
{
city = FormatData(cellActualValue);
}
else if (val.EndsWith("Location_Zip", StringComparison.CurrentCultureIgnoreCase))
{
zipcode = FormatData(cellActualValue);
}
else if (val.EndsWith("Overview*", StringComparison.CurrentCultureIgnoreCase))
{
overview = FormatData(cellActualValue);
}
else if (val.EndsWith("Icon", StringComparison.CurrentCultureIgnoreCase))
{
icon = cellActualValue;
}
}
}
}
}
if (counter > 0)
{
locations = GetLocations(state, county, city, zipcode);
topic = new Shared.Models.Topic()
{
Id = (string.IsNullOrEmpty(id)|| string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
Overview = overview,
ParentTopicId = parentTopicIds.Count > 0 ? parentTopicIds : null,
ResourceType = "Topics",
Keywords = keywords,
OrganizationalUnit = organizationalUnit,
Location = locations,
Icon = icon,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
topic.Validate();
topicsList.Add(topic);
}
counter++;
recordNumber++;
}
topics = topicsList;
}
}
catch (Exception ex)
{
ErrorLogging(ex, recordNumber);
topics = null;
}
return topics;
}