private void AutoFit(MSOpenXML.Worksheet worksheet)
{
MSOpenXML.MergeCells mergeCells = worksheet.Elements <MSOpenXML.MergeCells>().Count() > 0 ? mergeCells = worksheet.Elements <MSOpenXML.MergeCells>().First() : null;
List <MergeCell> cells = mergeCells != null?mergeCells.Elements <MergeCell>().ToList() : new List <MergeCell>();
Dictionary <string, int> d = new Dictionary <string, int>();
foreach (var row in worksheet.Descendants <Row>())
{
foreach (var cell in row.Elements <Cell>())
{
if (cell.CellValue == null)
{
continue;
}
if (cells.Exists(x => Contains(x.Reference.Value, cell.CellReference.Value)))
{
continue;
}
int s = cell.CellValue.Text.Length;
if (cell.StyleIndex != null)
{
if (cell.StyleIndex.Value == 1)
{
s = 10;
}
if (cell.StyleIndex.Value == 3)
{
s = decimal.Parse(cell.CellValue.Text, _en_us_ci.NumberFormat).ToString("n2").Length;
}
}
string c = GetColumnName(cell.CellReference);
if (d.ContainsKey(c))
{
d[c] = Math.Max(d[c], s);
}
else
{
d[c] = s;
}
}
}
Columns columns = new Columns();
foreach (var item in d)
{
columns.Append(CreateColumnData(GetColumnIndex(item.Key) + 1, GetColumnIndex(item.Key) + 1, item.Value * 1.2));
}
worksheet.InsertBefore(columns, worksheet.Elements <MSOpenXML.SheetData>().First());
}
static void ReadSheet(Spreadsheet document, Package.SpreadsheetDocument importDocument, Excel.Sheet importSheet,
Dictionary <string, string> sharedStringTable, Dictionary <int, CellFormat> cellFormats)
{
Sheet sheet = document.Sheets.Sheet(importSheet.Name);
var importWorksheet = new Excel.Worksheet();
importWorksheet.Load((Package.WorksheetPart)importDocument.WorkbookPart.GetPartById(importSheet.Id));
var sheetData = (Excel.SheetData)importWorksheet.Elements <Excel.SheetData>().First();
foreach (var importColumn in sheetData.Elements <Excel.Column>())
{
ReadColumn();
}
foreach (var importRow in sheetData.Elements <Excel.Row>())
{
ReadRow(sheet, importRow, sharedStringTable, cellFormats);
}
}
//This function is created by Apt to append header cell with corresponding column
private static void AppendHeaderCell(string cellReference, string cellStringValue, Row excelRow, uint columnIndex, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet)
{
DocumentFormat.OpenXml.Spreadsheet.Columns columns;
DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null;
// Add a new Excel Cell to our Row
Cell cell = new Cell()
{
CellReference = cellReference, DataType = CellValues.String
};
CellValue cellValue = new CellValue();
cellValue.Text = cellStringValue;
cell.Append(cellValue);
excelRow.Append(cell);
columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
// Check if the column collection exists
if (columns == null)
{
columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0);
}
// Check if the column exists
if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0)
{
// Find the previous existing column in the columns
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault();
if (previousColumn != null)
{
break;
}
}
columns.InsertAfter(
new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = columnIndex,
Max = columnIndex,
CustomWidth = true,
Width = 9
}, previousColumn);
}
}
/// <summary>
/// Sets the column width
/// </summary>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="width">Width to set</param>
/// <returns>True if succesful</returns>
public static bool SetColumnWidth(DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, int columnIndex, int width)
{
DocumentFormat.OpenXml.Spreadsheet.Columns columns;
DocumentFormat.OpenXml.Spreadsheet.Column column;
// Get the column collection exists
columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
if (columns == null)
{
return(false);
}
// Get the column
column = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).FirstOrDefault();
if (column == null)
{
return(false);
}
column.Width = width;
column.CustomWidth = true;
worksheet.Save();
return(true);
}
/// <summary>
/// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="rowIndex">Index of the row</param>
/// <param name="valueType">Type of the value</param>
/// <param name="value">The actual value</param>
/// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param>
/// <param name="save">Save the worksheet?</param>
/// <returns>True if succesful</returns>
private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint?styleIndex, bool save = true)
{
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>();
DocumentFormat.OpenXml.Spreadsheet.Row row;
DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null;
DocumentFormat.OpenXml.Spreadsheet.Cell cell;
DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null;
DocumentFormat.OpenXml.Spreadsheet.Columns columns;
DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null;
string cellAddress = ExcelProc.ColumnNameFromIndex(columnIndex) + rowIndex;
// Check if the row exists, create if necessary
if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First();
}
else
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowIndex
};
//sheetData.Append(row);
for (uint counter = rowIndex - 1; counter > 0; counter--)
{
previousRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault();
if (previousRow != null)
{
break;
}
}
sheetData.InsertAfter(row, previousRow);
}
// Check if the cell exists, create if necessary
if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0)
{
cell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First();
}
else
{
// Find the previous existing cell in the row
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousCell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == ExcelProc.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault();
if (previousCell != null)
{
break;
}
}
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = cellAddress
};
row.InsertAfter(cell, previousCell);
}
// Check if the column collection exists
columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
if (columns == null)
{
columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0);
}
// Check if the column exists
if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0)
{
// Find the previous existing column in the columns
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault();
if (previousColumn != null)
{
break;
}
}
columns.InsertAfter(
new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = columnIndex,
Max = columnIndex,
CustomWidth = true,
Width = 9
}, previousColumn);
}
// Add the value
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value);
if (styleIndex != null)
{
cell.StyleIndex = styleIndex;
}
if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date)
{
cell.DataType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType);
}
if (save)
{
worksheet.Save();
}
return(true);
}
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);
}
private void MergeRange(MSOpenXML.Worksheet worksheet, string cell1Name, string cell2Name)
{
// Open the document for editing.
if (worksheet == null || string.IsNullOrEmpty(cell1Name) || string.IsNullOrEmpty(cell2Name))
{
return;
}
if (cell1Name == cell2Name)
{
return;
}
// Verify if the specified cells exist, and if they do not exist, create them.
CreateCellIfNotExist(worksheet, cell1Name);
CreateCellIfNotExist(worksheet, cell2Name);
MSOpenXML.MergeCells mergeCells;
if (worksheet.Elements <MSOpenXML.MergeCells>().Count() > 0)
{
mergeCells = worksheet.Elements <MSOpenXML.MergeCells>().First();
}
else
{
mergeCells = new MSOpenXML.MergeCells();
// Insert a MergeCells object into the specified position.
if (worksheet.Elements <MSOpenXML.CustomSheetView>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.CustomSheetView>().First());
}
else if (worksheet.Elements <MSOpenXML.DataConsolidate>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.DataConsolidate>().First());
}
else if (worksheet.Elements <MSOpenXML.SortState>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.SortState>().First());
}
else if (worksheet.Elements <MSOpenXML.AutoFilter>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.AutoFilter>().First());
}
else if (worksheet.Elements <MSOpenXML.Scenarios>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.Scenarios>().First());
}
else if (worksheet.Elements <MSOpenXML.ProtectedRanges>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.ProtectedRanges>().First());
}
else if (worksheet.Elements <MSOpenXML.SheetProtection>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.SheetProtection>().First());
}
else if (worksheet.Elements <MSOpenXML.SheetCalculationProperties>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.SheetCalculationProperties>().First());
}
else
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.SheetData>().First());
}
}
// Create the merged cell and append it to the MergeCells collection.
MSOpenXML.MergeCell mergeCell = new MSOpenXML.MergeCell()
{
Reference = cell1Name + ":" + cell2Name
};
mergeCells.Append(mergeCell);
}