public static MemoryStream ObjectListToExcelWorksheet <T>(List <T> modelList, GridSpec <T> spec, string sheetName)
{
var xmlStream = SpreadsheetReader.Create();
using (var spreadsheetDocument = SpreadsheetDocument.Open(xmlStream, true))
{
SetSheetName(sheetName, spreadsheetDocument);
var worksheetPart = GetWorksheetPartByName(spreadsheetDocument, sheetName);
var modelObjects = modelList.Select(c => c);
uint rowIndex = 1;
var sharedStringDictionary = new SharedStringTableDictionary();
AddExcelRow(spec.ColumnNames, worksheetPart, sharedStringDictionary, rowIndex);
foreach (var modelObject in modelObjects)
{
rowIndex++;
ToExcelRow(modelObject, spec, worksheetPart, sharedStringDictionary, rowIndex);
}
// Update the real string table with values
var sharedStringTablePart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
sharedStringDictionary.AppendToSharedStringTable(sharedStringTablePart);
sharedStringTablePart.SharedStringTable.Save();
// Save to the memory stream
Save(spreadsheetDocument);
spreadsheetDocument.Close();
}
return(xmlStream);
}
private static void AddCell(WorksheetPart worksheetPart, SharedStringTableDictionary sharedStringTablePart, string cellValue, int columnIndex, uint rowIndex)
{
var index = sharedStringTablePart.AddOrLookupSharedString(cellValue);
var cell = InsertCellInWorksheet(GetCellReference(columnIndex), rowIndex, worksheetPart);
cell.CellValue = new CellValue(index.ToString(CultureInfo.InvariantCulture));
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
private static void AddExcelRow(IEnumerable <string> values, WorksheetPart worksheetPart, SharedStringTableDictionary sharedStringTablePart, uint rowIndex)
{
var headers = values.ToArray();
for (var i = 0; i < headers.Count(); i++)
{
AddCell(worksheetPart, sharedStringTablePart, headers[i], i, rowIndex);
}
}
private static void ToExcelRow <T>(T thingToRead, IEnumerable <ColumnSpec <T> > gridSpec, WorksheetPart worksheetPart, SharedStringTableDictionary sharedStringTablePart, uint rowIndex)
{
var propertyValues = gridSpec.Select((columnSpec, index) => new { CellValue = columnSpec.CalculateStringValue(thingToRead), ColumnIndex = index }).ToList();
propertyValues.ForEach(p => AddCell(worksheetPart, sharedStringTablePart, p.CellValue, p.ColumnIndex, rowIndex));
}
