/// <summary>
/// Generates a cell style for date formatting.
/// </summary>
/// <param name="book">excel sheet.</param>
/// <param name="options">excel sheet options.</param>
/// <returns>cell style.</returns>
private ICellStyle createCellStyleForDateTime(IWorkbook book, ExcelSpreadSheetOptions options)
{
var style = book.CreateCellStyle();
style.DataFormat = book.CreateDataFormat().GetFormat(options.DataTimeCellFormat.Format);
return(style);
}
/// <summary>
/// Export to a specified spreadsheet.
/// </summary>
/// <typeparam name="T">POCO with no internal List, Array or Class</typeparam>
/// <param name="exportCollections">export collections</param>
/// <param name="options">excel options.</param>
/// <exception cref="ArgumentNullException"></exception>
/// <exception cref="IOException"></exception>
public void Export <T>(IList <T> exportCollections, ISpreadSheetOptions options = null)
{
if (exportCollections == null)
{
throw new ArgumentNullException(nameof(exportCollections));
}
if (!exportCollections.Any())
{
return;
}
if (File.Exists(filePath))
{
throw new IOException($"{filePath} already exists.");
}
if (options == null)
{
options = new ExcelSpreadSheetOptions();
}
ExcelSpreadSheetOptions excelOpt = null;
if (options != null)
{
if (!(options is ExcelSpreadSheetOptions))
{
throw new ArgumentException($"The only Options that this class can receive are {nameof(ExcelSpreadSheetOptions)}.");
}
excelOpt = options as ExcelSpreadSheetOptions;
}
IWorkbook book;
var extension = Path.GetExtension(filePath);
if (extension == HSSF_EXTENSION)
{
book = new HSSFWorkbook();
}
else if (extension == XSSF_EXTENSION)
{
book = new XSSFWorkbook();
}
else
{
throw new ApplicationException($@"Invalid extension has been specified. Valid extensions are either '{HSSF_EXTENSION}' or '{XSSF_EXTENSION}'.");
}
if (TypeUtil.IsPrimitive(typeof(T)))
{
PrimitiveTypeExport(book, exportCollections, excelOpt);
}
else
{
ClassTypeExport(book, exportCollections, excelOpt);
}
using (var fs = new FileStream(filePath, FileMode.Create))
{
book.Write(fs);
}
}
/// <summary>
/// Outputs class type.
/// </summary>
/// <typeparam name="T">any class types.</typeparam>
/// <param name="book">excel sheet.</param>
/// <param name="exportCollections">export any collections.</param>
/// <param name="options">excel sheet options.</param>
protected virtual void ClassTypeExport <T>(IWorkbook book, IList <T> exportCollections, ExcelSpreadSheetOptions options)
{
var rowIndex = 0;
var columnIndex = 0;
var gType = typeof(T);
if (options != null)
{
rowIndex = (int)options.StartRowIndex;
columnIndex = (int)options.StartColumnIndex;
}
var sheet = book.CreateSheet(settings.SheetName);
var style = createCellStyleForDateTime(book, options);
var hColIndex = columnIndex;
if (ReadHeaderInfo.Property == settings.ReadHeaderInfo || ReadHeaderInfo.PropertyAndField == settings.ReadHeaderInfo)
{
foreach (var prop in gType.GetProperties())
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
var cell = row.GetCell(hColIndex) ?? row.CreateCell(hColIndex);
cell.SetCellValue(prop.Name);
hColIndex++;
}
}
if (ReadHeaderInfo.Field == settings.ReadHeaderInfo || ReadHeaderInfo.PropertyAndField == settings.ReadHeaderInfo)
{
foreach (var field in gType.GetFields())
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
var cell = row.GetCell(hColIndex) ?? row.CreateCell(hColIndex);
cell.SetCellValue(field.Name);
hColIndex++;
}
}
rowIndex = rowIndex + 1;
foreach (var value in exportCollections)
{
var colIndex = columnIndex;
if (ReadHeaderInfo.Property == settings.ReadHeaderInfo || ReadHeaderInfo.PropertyAndField == settings.ReadHeaderInfo)
{
foreach (var prop in gType.GetProperties())
{
setCellValue(sheet: sheet, style: style, rowIndex: rowIndex, columnIndex: colIndex, prop.GetValue(value));
colIndex++;
}
}
if (ReadHeaderInfo.Field == settings.ReadHeaderInfo || ReadHeaderInfo.PropertyAndField == settings.ReadHeaderInfo)
{
foreach (var field in gType.GetFields())
{
setCellValue(sheet: sheet, style: style, rowIndex: rowIndex, columnIndex: colIndex, field.GetValue(value));
colIndex++;
}
}
rowIndex++;
}
}
/// <summary>
/// Outputs primitive type.
/// </summary>
/// <typeparam name="T">any primitive types.</typeparam>
/// <param name="book">excel sheet.</param>
/// <param name="exportCollections">export any collections.</param>
/// <param name="options">excel sheet options.</param>
protected virtual void PrimitiveTypeExport <T>(IWorkbook book, IList <T> exportCollections, ExcelSpreadSheetOptions options)
{
var rowIndex = 0;
var columnIndex = 0;
if (options != null)
{
rowIndex = (int)options.StartRowIndex;
columnIndex = (int)options.StartColumnIndex;
}
var sheet = book.CreateSheet(settings.SheetName);
var style = createCellStyleForDateTime(book, options);
foreach (var value in exportCollections)
{
setCellValue(sheet: sheet, style: style, rowIndex: rowIndex, columnIndex: columnIndex, value);
rowIndex++;
}
}
