/// <summary>Exports any data list to a XLSX Excel stream.</summary>
/// <typeparam name="T">Type of the data in the list</typeparam>
/// <param name="data">The data to export</param>
public MemoryStream ExportToExcel <T>(List <T> data, ExcelColumnInfos infos)
{
try
{
var workbook = new Workbook();
var worksheet = workbook.Worksheets[0];
// Validation of data
EnsureValidData(data);
// Importing the array of names to 1st row and first column vertically
worksheet.Cells.ImportCustomObjects(data, 0, 0, new ImportTableOptions());
worksheet.ListObjects.Add(0, 0, data.Count, typeof(T).GetProperties().Length - 1, true);
worksheet.ListObjects[0].TableStyleType = TableStyleType.TableStyleLight9;
ApplyFormatting(infos, worksheet);
var stream = new MemoryStream();
workbook.Save(stream, new OoxmlSaveOptions(SaveFormat.Xlsx));
return(stream);
}
catch (Exception ex)
{
Log.Error(ex, "Unexpected problem exporting data to excel");
throw;
}
}
private void ApplyFormatting(ExcelColumnInfos infos, Worksheet worksheet)
{
try
{
if (infos != null && infos.Any())
{
var table = worksheet.ListObjects.FirstOrDefault();
foreach (var columnInfo in infos)
{
// Find column
var col = table?.ListColumns.Find(c => c.Name.Equals(columnInfo.ColumnName, StringComparison.InvariantCultureIgnoreCase));
if (col != null)
{
FormatColumn(worksheet, columnInfo, col);
}
}
}
}
catch (Exception ex)
{
Log.Error(ex, "Unexpected error while formatting Excel export");
}
}
