/// <summary>
/// Set style of the cell for export.
/// Assume the cell belongs to current column.
/// </summary>
/// <param name="cell">The cell to be set.</param>
/// <param name="value">The cell value object.</param>
/// <param name="isHeader">If <c>true</c>, use HeaderFormat; otherwise use DataFormat.</param>
/// <param name="defaultFormats">The default formats dictionary.</param>
/// <param name="helper">The helper object.</param>
public void SetCellStyle(ICell cell, object value, bool isHeader, Dictionary <Type, string> defaultFormats, MapHelper helper)
{
if (cell == null)
{
throw new ArgumentNullException(nameof(cell));
}
if (isHeader && !_headerStyleCached)
{
_headerStyle = helper.GetCellStyle(cell, null, HeaderFormat);
if (_headerStyle == null && HeaderValue != null)
{
_headerStyle = helper.GetDefaultStyle(cell.Sheet.Workbook, HeaderValue, defaultFormats);
}
_headerStyleCached = true;
}
else if (!isHeader && !_dataStyleCached)
{
_dataStyle = helper.GetCellStyle(cell, Attribute.CustomFormat, DataFormat);
if (_dataStyle == null && value != null)
{
_dataStyle = helper.GetDefaultStyle(cell.Sheet.Workbook, value, defaultFormats);
}
_dataStyleCached = true;
}
cell.CellStyle = isHeader ? _headerStyle : _dataStyle;
}
private void Put <T>(ISheet sheet, IEnumerable <T> objects, bool overwrite, int rowOffset = 0, bool shiftDownExistingRows = false)
{
var sheetName = sheet.SheetName;
var firstRow = sheet.GetRow(sheet.FirstRowNum);
var objectArray = objects as T[] ?? objects.ToArray();
var type = MapHelper.GetConcreteType(objectArray);
var columns = GetTrackedColumns(sheetName, type) ??
GetColumns(firstRow ?? PopulateFirstRow(sheet, null, type), type);
firstRow = sheet.GetRow(sheet.FirstRowNum) ?? PopulateFirstRow(sheet, columns, type);
var rowIndex = overwrite
? HasHeader ? sheet.FirstRowNum + 1 : sheet.FirstRowNum
: sheet.GetRow(sheet.LastRowNum) != null ? sheet.LastRowNum + 1 : sheet.LastRowNum;
rowIndex += rowOffset;
ICellStyle baseRowCellStyle = null;
if (shiftDownExistingRows)
{
var cell = sheet.GetRow(rowIndex)?.GetCell(0);
if (cell != null)
{
baseRowCellStyle = Workbook.CreateCellStyle();
baseRowCellStyle.CloneStyleFrom(cell.CellStyle);
}
sheet.ShiftRows(rowIndex, sheet.LastRowNum, objectArray.Length, true, false);
}
MapHelper.EnsureDefaultFormats(columns, TypeFormats);
foreach (var o in objectArray)
{
var row = sheet.GetRow(rowIndex);
if (overwrite && !shiftDownExistingRows && row != null)
{
sheet.RemoveRow(row);
row = sheet.CreateRow(rowIndex);
}
row = row ?? sheet.CreateRow(rowIndex);
foreach (var column in columns)
{
var pi = column.Attribute.Property;
var value = pi?.GetValue(o);
var cell = row.GetCell(column.Attribute.Index, MissingCellPolicy.CREATE_NULL_AS_BLANK);
column.CurrentValue = value;
if (column.Attribute.TryPut == null || column.Attribute.TryPut(column, o))
{
SetCell(cell, column.CurrentValue, column, setStyle: overwrite);
if (baseRowCellStyle != null)
{
var cellStyle = Workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(baseRowCellStyle);
cellStyle.DataFormat = cell.CellStyle.DataFormat;
cell.CellStyle = cellStyle;
}
}
}
rowIndex++;
}
// Remove not used rows if any.
while (overwrite && !shiftDownExistingRows && rowIndex <= sheet.LastRowNum)
{
var row = sheet.GetRow(rowIndex);
if (row != null)
{
sheet.RemoveRow(row);
}
rowIndex++;
}
// Injects custom action for headers.
if (overwrite && HasHeader && _headerAction != null)
{
firstRow?.Cells.ForEach(c => _headerAction(c));
}
}
private static void LoadRowData(IEnumerable <ColumnInfo> columns, IRow row, object target, IRowInfo rowInfo)
{
var errorIndex = -1;
string errorMessage = null;
void ColumnFailed(IColumnInfo column, string message)
{
if (errorIndex >= 0)
{
return; // Ensures the first error will not be overwritten.
}
if (column.Attribute.IgnoreErrors == true)
{
return;
}
errorIndex = column.Attribute.Index;
errorMessage = message;
}
foreach (var column in columns)
{
var index = column.Attribute.Index;
if (index < 0)
{
continue;
}
try
{
var cell = row.GetCell(index);
var propertyType = column.Attribute.PropertyUnderlyingType ?? column.Attribute.Property?.PropertyType;
if (!MapHelper.TryGetCellValue(cell, propertyType, out object valueObj))
{
ColumnFailed(column, "CellType is not supported yet!");
continue;
}
valueObj = column.RefreshAndGetValue(valueObj);
if (column.Attribute.TryTake != null)
{
if (!column.Attribute.TryTake(column, target))
{
ColumnFailed(column, "Returned failure by custom cell resolver!");
}
}
else if (propertyType != null)
{
// Change types between IConvertible objects, such as double, float, int and etc.
if (MapHelper.TryConvertType(valueObj, column, out object result))
{
column.Attribute.Property.SetValue(target, result);
}
else
{
ColumnFailed(column, "Cannot convert value to the property type!");
}
//var value = Convert.ChangeType(valueObj, column.Attribute.PropertyUnderlyingType ?? propertyType);
}
}
catch (Exception e)
{
ColumnFailed(column, e.Message);
}
}
rowInfo.ErrorColumnIndex = errorIndex;
rowInfo.ErrorMessage = errorMessage;
}
private IEnumerable <RowInfo <T> > Take <T>(ISheet sheet, int maxErrorRows, Func <T> objectInitializer = null) where T : class
{
if (sheet == null || sheet.PhysicalNumberOfRows < 1)
{
yield break;
}
var firstRowIndex = sheet.FirstRowNum;
var firstRow = sheet.GetRow(firstRowIndex);
var targetType = typeof(T);
if (targetType == typeof(object)) // Dynamic type.
{
targetType = GetDynamicType(sheet);
MapHelper.LoadDynamicAttributes(Attributes, DynamicAttributes, targetType);
}
// Scan object attributes.
MapHelper.LoadAttributes(Attributes, targetType);
// Read the first row to get column information.
var columns = GetColumns(firstRow, targetType);
// Detect column format based on the first non-null cell.
Helper.LoadDataFormats(sheet, HasHeader ? firstRowIndex + 1 : firstRowIndex, columns, TypeFormats);
if (TrackObjects)
{
Objects[sheet.SheetName] = new Dictionary <int, object>();
}
// Loop rows in file. Generate one target object for each row.
var errorCount = 0;
foreach (IRow row in sheet)
{
if (maxErrorRows > 0 && errorCount >= maxErrorRows)
{
break;
}
if (HasHeader && row.RowNum == firstRowIndex)
{
continue;
}
var obj = objectInitializer == null?Activator.CreateInstance(targetType) : objectInitializer();
var rowInfo = new RowInfo <T>(row.RowNum, obj as T, -1, string.Empty);
LoadRowData(columns, row, obj, rowInfo);
if (rowInfo.ErrorColumnIndex >= 0)
{
errorCount++;
rowInfo.Value = default(T);
}
if (TrackObjects)
{
Objects[sheet.SheetName][row.RowNum] = rowInfo.Value;
}
yield return(rowInfo);
}
}
private IRow PopulateFirstRow(ISheet sheet, List <ColumnInfo> columns, Type type)
{
var row = sheet.CreateRow(sheet.FirstRowNum);
// Use existing column populate the first row.
if (columns != null)
{
foreach (var column in columns)
{
var cell = row.CreateCell(column.Attribute.Index);
if (!HasHeader)
{
continue;
}
SetCell(cell, column.Attribute.Name ?? column.HeaderValue, column, true);
}
return(row);
}
// If no column cached, populate the first row with attributes and object properties.
MapHelper.LoadAttributes(Attributes, type);
var attributes = Attributes.Where(p => p.Value.Property != null && p.Value.Property.ReflectedType == type);
var properties = new List <PropertyInfo>(type.GetProperties(MapHelper.BindingFlag));
// Firstly populate for those have Attribute specified.
foreach (var pair in attributes)
{
var pi = pair.Key;
var attribute = pair.Value;
if (pair.Value.Index < 0)
{
continue;
}
var cell = row.CreateCell(attribute.Index);
if (HasHeader)
{
cell.SetCellValue(attribute.Name ?? pi.Name);
}
properties.Remove(pair.Key); // Remove populated property.
}
var index = 0;
// Then populate for those do not have Attribute specified.
foreach (var pi in properties)
{
var attribute = Attributes.ContainsKey(pi) ? Attributes[pi] : null;
if (attribute?.Ignored == true)
{
continue;
}
while (row.GetCell(index) != null)
{
index++;
}
var cell = row.CreateCell(index);
if (HasHeader)
{
cell.SetCellValue(attribute?.Name ?? pi.Name);
}
else
{
new ColumnAttribute {
Index = index, Property = pi
}.MergeTo(Attributes);
}
index++;
}
return(row);
}
private void Put <T>(ISheet sheet, IEnumerable <T> objects, bool overwrite)
{
var sheetName = sheet.SheetName;
var firstRowIndex = GetFirstRowIndex(sheet);
var firstRow = sheet.GetRow(firstRowIndex);
var objectArray = objects as T[] ?? objects.ToArray();
var type = MapHelper.GetConcreteType(objectArray);
var columns = GetTrackedColumns(sheetName, type) ??
GetColumns(firstRow ?? PopulateFirstRow(sheet, null, type), type);
firstRow = sheet.GetRow(firstRowIndex) ?? PopulateFirstRow(sheet, columns, type);
var rowIndex = overwrite
? HasHeader ? firstRowIndex + 1 : firstRowIndex
: sheet.GetRow(sheet.LastRowNum) != null ? sheet.LastRowNum + 1 : sheet.LastRowNum;
MapHelper.EnsureDefaultFormats(columns, TypeFormats);
foreach (var o in objectArray)
{
var row = sheet.GetRow(rowIndex);
if (overwrite && row != null)
{
sheet.RemoveRow(row);
row = sheet.CreateRow(rowIndex);
}
row = row ?? sheet.CreateRow(rowIndex);
foreach (var column in columns)
{
var pi = column.Attribute.Property;
var value = pi?.GetValue(o);
var cell = row.GetCell(column.Attribute.Index, MissingCellPolicy.CREATE_NULL_AS_BLANK);
column.CurrentValue = value;
if (column.Attribute.TryPut == null || column.Attribute.TryPut(column, o))
{
SetCell(cell, column.CurrentValue, column, setStyle: overwrite);
}
}
rowIndex++;
}
// Remove not used rows if any.
while (overwrite && rowIndex <= sheet.LastRowNum)
{
var row = sheet.GetRow(rowIndex);
if (row != null)
{
sheet.RemoveRow(row);
}
rowIndex++;
}
// Injects custom action for headers.
if (overwrite && HasHeader && _headerAction != null)
{
firstRow?.Cells.ForEach(c => _headerAction(c));
}
}