/// <summary>
/// Inserts the cell in worksheet.
/// </summary>
/// <param name="sheetData">The sheet data.</param>
/// <param name="columnName">Name of the column.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <returns></returns>
protected static SpreadSheet.Cell InsertCellInWorksheet(SpreadSheet.SheetData sheetData, string columnName, uint rowIndex)
{
string cellReference = columnName + rowIndex;
SpreadSheet.Row row;
if (sheetData.Elements <SpreadSheet.Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <SpreadSheet.Row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new SpreadSheet.Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
}
// If column doesn't exist, insert one.
if (row.Elements <SpreadSheet.Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return(row.Elements <SpreadSheet.Cell>().Where(c => c.CellReference.Value == cellReference).First());
}
else
{
SpreadSheet.Cell refCell = null;
foreach (SpreadSheet.Cell cell in row.Elements <SpreadSheet.Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
SpreadSheet.Cell newCell = new SpreadSheet.Cell()
{
CellReference = cellReference
};
row.InsertBefore(newCell, refCell);
return(newCell);
}
}
/// <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;
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;
}
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);
}
/// <summary>
/// Updates a SpreadsheetDocument with new tabular data.
/// </summary>
/// <param name="ssdoc">
/// The excel document to update.</param>
/// <param name="json">
/// String in JSON format representing the tabular data for updating the Chart's cached data points.
/// The JSON object must contain a "fields" attribute as an array containing the field/column names.
/// The JSON object must contain a "rows" attribute as an array of arrays representing the rows and their values, with values matching the same order and cardinality of the field names.
/// This is the same data as the underlying Excel spreadsheet contents.</param>
/// <param name="sheetName">
/// The name of the Excel worksheet where the chart data originates from.
/// Used for updating the chart's cell references.</param>
/// <returns>
/// Returns the updated SpreadsheetDocument</returns>
public static SpreadsheetDocument Update(this SpreadsheetDocument ssdoc, string json, string sheetName)
{
if ((json == null) || (json == String.Empty))
{
json = "{\"fields\": [ \"No Results\" ], \"rows\": [[ \"No Results\" ]]}";
}
//Splunk JSON data is a series of objects consisting of multiple key(column)/value(row) pairs in the result attribute.
dynamic input = JsonConvert.DeserializeObject <dynamic>(json);
if (input["rows"].Count == 0)
{
json = "{\"fields\": [ \"No Results\" ], \"rows\": [[ \"No Results\" ]]}";
input = JsonConvert.DeserializeObject <dynamic>(json);
}
ss.Sheet sheet = ssdoc.WorkbookPart.Workbook.Descendants <ss.Sheet>().Where(s => s.Name.ToString() == sheetName).FirstOrDefault();
if (sheet == null)
{
sheet = ssdoc.WorkbookPart.Workbook.Descendants <ss.Sheet>().FirstOrDefault();
}
WorksheetPart worksheet = (WorksheetPart)ssdoc.WorkbookPart.GetPartById(sheet.Id);
ss.SheetData data = worksheet.Worksheet.GetFirstChild <ss.SheetData>();
//Remove all the rows after our column headers row. We'll replace them with new rows as the table is populated from the Splunk search results.
ss.Row firstRow = data.Elements <ss.Row>().First();
while (firstRow.NextSibling <ss.Row>() != null)
{
firstRow.NextSibling <ss.Row>().Remove();
}
ss.Row newHeader = new ss.Row();
newHeader.DyDescent = 0.25;
newHeader.RowIndex = 1;
var columnNames = input["fields"];
char startingHeaderColumn = 'A';
foreach (var column in columnNames)
{
string cellRef = startingHeaderColumn.ToString() + 1;
ss.Cell newCell = new ss.Cell();
ss.CellValue cv = new ss.CellValue(column.ToString());
newCell.CellReference = cellRef;
newCell.DataType = ss.CellValues.String;
newCell.Append(cv);
newHeader.Append(newCell);
startingHeaderColumn++;
}
data.InsertAfter(newHeader, data.Elements <ss.Row>().Last());
data.RemoveChild(firstRow);
for (int i = 0; i < input["rows"].Count; i++)
{
char startingColumn = 'A';
int startingColumnVal = 1;
int endingColumnVal = 0;
//Set the Excel row index (Excel index starts at 1, not zero and row 1 has headers so add 2 to the for index)
uint rowIndex = Convert.ToUInt32(i) + 2;
ss.Row newRow = new ss.Row();
newRow.DyDescent = 0.25;
newRow.RowIndex = rowIndex;
Debug.WriteLine(String.Format("Writing Excel Row {0}", i + 1));
var row = input["rows"][i];
foreach (var cell in row)
{
string cellRef = startingColumn.ToString() + rowIndex;
ss.Cell newCell = new ss.Cell();
ss.CellValue cv = new ss.CellValue(cell.ToString());
newCell.CellReference = cellRef;
if (startingColumn == 'A')
{
newCell.DataType = ss.CellValues.String;
}
newCell.Append(cv);
newRow.Append(newCell);
startingColumn++;
endingColumnVal++;
}
int numberOfCellsInRow = newRow.Descendants <ss.Cell>().Count();
ListValue <StringValue> spans = new ListValue <StringValue>();
spans.Items.Add(string.Format("{0}:{1}", startingColumnVal, endingColumnVal));
newRow.Spans = spans;
data.InsertAfter(newRow, data.Elements <ss.Row>().Last());
}
// Update Table Reference
Debug.WriteLine("Updating Table");
var table = worksheet.TableDefinitionParts.First().Table;
table.Reference = string.Format("A1:{0}{1}", GetExcelColumnName(input["fields"].Count), input["rows"].Count + 1);
table.TableColumns.RemoveAllChildren();
for (int i = 0; i < columnNames.Count; i++)
{
var newColumn = new ss.TableColumn();
newColumn.Id = new UInt32Value((uint)i + 1);
newColumn.Name = new StringValue(columnNames[i].ToString());
table.TableColumns.Append(newColumn);
}
return(ssdoc);
}
