/// <summary>
/// Creates an exported sheet of a specific configuration
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="exportConfiguration"></param>
/// <param name="columns"></param>
/// <returns></returns>
private SheetData CreateExportSheet <T>(SpreadsheetConfiguration <T> exportConfiguration, out Columns columns) where T : class
{
//Build out our sheet information
var data = new SheetData();
UInt32 currentRow = 1;
if (exportConfiguration.RenderTitle)
{
var row = new Row {
RowIndex = 1
};
var headerCell = new Cell
{
CellReference = $"A{currentRow}",
CellValue = new CellValue(exportConfiguration.DocumentTitle),
DataType = CellValues.String,
StyleIndex = (int)FontStyleIndex.Header
};
row.Append(headerCell);
data.Append(row);
//Increment row
currentRow++;
}
if (exportConfiguration.RenderSubTitle)
{
var row = new Row {
RowIndex = currentRow
};
var headerCell = new Cell
{
CellReference = $"A{currentRow}",
CellValue = new CellValue(exportConfiguration.DocumentSubTitle),
DataType = CellValues.String,
StyleIndex = (int)FontStyleIndex.SubHeader
};
row.Append(headerCell);
data.Append(row);
//Increment row
currentRow++;
}
//Run data headers
var headerProperties = TypeDescriptor.GetProperties(typeof(T));
var headerRow = new Row {
RowIndex = currentRow
};
var customFormats = new Dictionary <string, UInt32Value>();
foreach (PropertyDescriptor prop in headerProperties)
{
var headerCell = new Cell
{
CellValue = new CellValue(prop.DisplayName),
DataType = CellValues.String,
StyleIndex = (int)FontStyleIndex.DataHeader
};
headerRow.Append(headerCell);
//Handle formats
if (prop.Attributes.Count <= 0)
{
continue;
}
foreach (var attribute in prop.Attributes)
{
if (!(attribute is SpreadsheetColumnFormatAttribute detail))
{
continue;
}
switch (detail.Format.ToLowerInvariant())
{
case "d":
customFormats.Add(prop.DisplayName, (int)FontStyleIndex.NormalDate);
break;
case "c":
customFormats.Add(prop.DisplayName, (int)FontStyleIndex.NormalCurrency);
break;
}
break;
}
}
data.Append(headerRow);
currentRow++;
//Run the data
foreach (var item in exportConfiguration.ExportData)
{
var dataRow = new Row {
RowIndex = currentRow
};
foreach (PropertyDescriptor prop in headerProperties)
{
var itemValue = prop.GetValue(item);
var dataCell = new Cell
{
CellValue = new CellValue(itemValue?.ToString()),
DataType = CellValues.String
};
if (customFormats.ContainsKey(prop.DisplayName))
{
dataCell.StyleIndex = customFormats[prop.DisplayName];
if (dataCell.StyleIndex == 4)
{
dataCell.DataType = CellValues.Number;
dataCell.CellValue = new CellValue(decimal.Parse(itemValue?.ToString()));
}
else if (dataCell.StyleIndex == 5) //Date
{
dataCell.CellValue = new CellValue(DateTime.Parse(itemValue.ToString()).ToShortDateString());
}
}
dataRow.Append(dataCell);
}
data.Append(dataRow);
currentRow++;
}
//Auto-size
columns = AutoSize(data);
return(data);
}
/// <summary>
/// Creates a single worksheet document using the provided configuration information
/// </summary>
/// <typeparam name="T">The object type for exporting</typeparam>
/// <param name="exportConfiguration">The loaded configuration</param>
/// <returns>A completed MS Excel file</returns>
public byte[] CreateSingleSheetSpreadsheet <T>(SpreadsheetConfiguration <T> exportConfiguration) where T : class
{
//Validate input
if (string.IsNullOrWhiteSpace(exportConfiguration.WorksheetName))
{
throw new ArgumentException("Worksheet name must be supplied", nameof(exportConfiguration.WorksheetName));
}
if (exportConfiguration.ExportData == null)
{
throw new ArgumentException("Export data must be specified", nameof(exportConfiguration.ExportData));
}
if (exportConfiguration.RenderTitle && string.IsNullOrEmpty(exportConfiguration.DocumentTitle))
{
throw new ArgumentException("Document Title is required when 'Render Title' is true",
nameof(exportConfiguration.DocumentTitle));
}
if (exportConfiguration.RenderSubTitle && string.IsNullOrEmpty(exportConfiguration.DocumentSubTitle))
{
throw new ArgumentException("Document Sub Title is required when 'Render Sub Title' is true",
nameof(exportConfiguration.DocumentSubTitle));
}
//Setup a memory stream to hold the generated file
using (var documentStream = new MemoryStream())
{
//Create the document & overall workbook
var spreadsheetDocument = SpreadsheetDocument.Create(documentStream, SpreadsheetDocumentType.Workbook);
var workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
//Setup our styles
var stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = CreateStylesheet();
stylesPart.Stylesheet.Save();
var data = CreateExportSheet(exportConfiguration, out var columns);
//Add a worksheet to our document
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
worksheetPart.Worksheet.Append(columns);
worksheetPart.Worksheet.Append(data);
//Add the sheet to the workbook
var sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
var sheet = new Sheet
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = exportConfiguration.WorksheetName
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
spreadsheetDocument.Close();
//Return the bytearray
documentStream.Seek(0, SeekOrigin.Begin);
return(documentStream.ToArray());
}
}
