/// <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; }