private void AddCellValidators <T>(IExcelToEnumerableOptions <T> options)
{
if (options.Validations == null)
{
return;
}
foreach (var item in options.Validations)
{
var cellSetter = _cellSettersDictionaryForRead.Values.FirstOrDefault(x =>
x.ColumnName?.ToLowerInvariant() == item.Key.ToLowerInvariant());
if (cellSetter == null)
{
continue;
}
if (cellSetter.Validators == null)
{
cellSetter.Validators = new List <ExcelCellValidator>();
}
foreach (var validator in item.Value)
{
cellSetter.Validators.Add(validator);
}
}
}
private bool CheckForMissingFields <T>(IExcelToEnumerableOptions <T> options, List <string> fieldsPresentInRow,
int rowCount, IList <Exception> exceptionsList)
{
var diff = options.LoweredRequiredColumns.Except(fieldsPresentInRow);
var success = !diff.Any();
if (!success)
{
var firstException = CreateExceptionForMissingField(diff.First(), rowCount);
switch (options.ExceptionHandlingBehaviour)
{
case ExceptionHandlingBehaviour.ThrowOnFirstException:
throw firstException;
default:
exceptionsList.Add(firstException);
foreach (var exception in diff.Skip(1).Select(x => CreateExceptionForMissingField(x, rowCount)))
{
exceptionsList.Add(exception);
}
break;
}
}
return(success);
}
public RowMapper GetRowMapper <T>(IExcelToEnumerableOptions <T> options)
{
var hashCode = options.GetHashCode();
return(_rowMapperDictionary.ContainsKey(hashCode)
? _rowMapperDictionary[hashCode]
: null);
}
internal IEnumerable <T> MapExcelToEnumerable(string filePath,
IExcelToEnumerableContext excelToEnumerableContext,
IExcelToEnumerableOptions <T> options)
{
var workbook = new ExcelReader(filePath);
var list = MapWorkbookToEnumerable(workbook, excelToEnumerableContext, options);
return(list);
}
public static void Ignore <T>(string propertyName, IExcelToEnumerableOptions <T> options)
{
if (options.UnmappedProperties == null)
{
options.UnmappedProperties = new List <string>();
}
options.UnmappedProperties.Add(propertyName);
}
internal static void MapsToColumnNumber <T>(int i, string propertyName, IExcelToEnumerableOptions <T> options)
{
if (i < 1)
{
throw new ExcelToEnumerableConfigException(
$"Unable to map '{propertyName}' to column {i}. MapsToColumnNumber expects a 1-based column number");
}
options.CustomHeaderNumbers[propertyName] = i - 1;
}
private RowMapper CreateRowMapperFromPropertyDescriptorCollection <T>(
IExcelToEnumerableOptions <T> options)
{
var type = typeof(T);
var constructor = new RowMapper
{
Setters = GetSetters(type, options),
};
return(constructor);
}
private void GetPropertySetterDictionary <T>(IExcelToEnumerableOptions <T> options,
string[] normalisedHeaderArray)
{
if (normalisedHeaderArray == null)
{
_cellSettersDictionaryForRead = GetPropertySetterDictionaryByColumnNumber(options.CustomHeaderNumbers, Setters);
}
else
{
ValidateColumnNames(GetNormalisedColumnNames(Setters), normalisedHeaderArray, options.UnmappedProperties, options.OptionalColumns, options.IgnoreColumnsWithoutMatchingProperties);
GetPropertySetterDictionaryByColumnName(normalisedHeaderArray);
}
}
private Func <object, object> GetPropertyMapping <T>(IExcelToEnumerableOptions <T> options,
string propertyName, Type typeOrEnumerableType)
{
if (options.CustomMappings.ContainsKey(propertyName))
{
return(options.CustomMappings[propertyName]);
}
if (DefaultTypeMappers.Dictionary.ContainsKey(typeOrEnumerableType))
{
return(DefaultTypeMappers.Dictionary[typeOrEnumerableType]);
}
return(typeOrEnumerableType.GetTypeWithoutNullable().IsEnum ? DefaultTypeMappers.CreateEnumTypeMapper(typeOrEnumerableType) : null);
}
public RowMapper SetRowMapper <T>(IExcelToEnumerableOptions <T> options)
{
var hashCode = options.GetHashCode();
if (_rowMapperDictionary.ContainsKey(hashCode))
{
return(_rowMapperDictionary[hashCode]);
}
var rowMapper = CreateRowMapperFromPropertyDescriptorCollection(options);
_rowMapperDictionary.TryAdd(hashCode, rowMapper);
return(rowMapper);
}
private IEnumerable <PropertySetter> GetSetters <T>(Type type, IExcelToEnumerableOptions <T> options)
{
var propertyDescriptorCollection = TypeDescriptor.GetProperties(type);
var propertyDescriptors = propertyDescriptorCollection.Cast <PropertyDescriptor>().ToArray();
if (options.UnmappedProperties != null)
{
propertyDescriptors = propertyDescriptors.Where(x => !options.UnmappedProperties.Contains(x.Name)).ToArray();
}
return(propertyDescriptors.Select(x =>
GetSettersForProperty(type.GetProperty(x.Name), Array.IndexOf(propertyDescriptors.ToArray(), x), options))
.SelectMany(y => y).ToArray());
}
private IEnumerable <PropertySetter> GetSettersForProperty <T>(PropertyInfo propertyInfo, int index,
IExcelToEnumerableOptions <T> options)
{
if (propertyInfo.PropertyType != typeof(string) &&
typeof(IEnumerable).IsAssignableFrom(propertyInfo.PropertyType))
{
if (options.CollectionConfigurations == null ||
!options.CollectionConfigurations.ContainsKey(propertyInfo.Name))
{
return(new PropertySetter[0]);
}
return(GetSettersForEnumerable(propertyInfo, index, options));
}
var setter = GetterSetterHelpers.GetSetter(propertyInfo);
var getter = options.UniqueProperties != null && options.UniqueProperties.Contains(propertyInfo.Name)
? GetterSetterHelpers.GetGetter(propertyInfo)
: null;
var fromCellSetter = new PropertySetter
{
Getter = getter,
Setter = setter,
Type = propertyInfo.PropertyType,
ColumnName =
options.CustomHeaderNames != null && options.CustomHeaderNames.ContainsKey(propertyInfo.Name)
? options.CustomHeaderNames[propertyInfo.Name]
: propertyInfo.Name.ToLowerInvariant(),
PropertyName = propertyInfo.Name,
PropertyMapping = GetPropertyMapping(options, propertyInfo.Name, propertyInfo.PropertyType),
RelaxedNumberMatching = options.RelaxedNumberMatching && propertyInfo.PropertyType.IsNumeric()
};
if (propertyInfo.Name == options.RowNumberProperty)
{
fromCellSetter.ColumnName = null;
}
if (options.NotNullProperties.Contains(propertyInfo.Name))
{
fromCellSetter.Validators = new List <ExcelCellValidator> {
ExcelCellValidatorFactory.CreateRequired()
};
}
return(new[] { fromCellSetter });
}
private IDictionary <int, string> ReadHeader(IExcelToEnumerableOptions <T> options, SheetReader worksheet)
{
IDictionary <int, string> header = null;
if (options.UseHeaderNames || _options.OnReadingHeaderRowAction != null)
{
header = GetHeaderRow(worksheet);
_options.OnReadingHeaderRowAction?.Invoke(header);
}
while (worksheet.CurrentRowNumber < options.StartRow)
{
worksheet.ReadNext();
}
return(header);
}
private IEnumerable <PropertySetter> GetSettersForEnumerable <T>(PropertyInfo propertyInfo, int index,
IExcelToEnumerableOptions <T> options)
{
var collectionsConfig = options.CollectionConfigurations[propertyInfo.Name];
var isDictionary = typeof(IDictionary).IsAssignableFrom(propertyInfo.PropertyType);
var enumerableType = propertyInfo.PropertyType.GenericTypeArguments[0];
var fromCellSetters = collectionsConfig.ColumnNames.Select(x => new PropertySetter
{
ColumnName = x.ToLowerInvariant(),
PropertyName = propertyInfo.Name,
Setter = isDictionary
? GetterSetterHelpers.GetDictionaryAdder(propertyInfo, x)
: GetterSetterHelpers.GetAdder(propertyInfo),
Type = enumerableType,
PropertyMapping = GetPropertyMapping(options, propertyInfo.Name, enumerableType)
});
return(fromCellSetters);
}
private IEnumerable <T> MapWorkbookToEnumerable(ExcelReader workbook,
IExcelToEnumerableContext excelToEnumerableContext,
IExcelToEnumerableOptions <T> options)
{
_options = options;
var worksheet = _options.WorksheetNumber.HasValue
? workbook[_options.WorksheetNumber.Value]
: workbook[_options.WorksheetName];
while (!worksheet.CurrentRowNumber.HasValue || worksheet.CurrentRowNumber < _options.HeaderRow)
{
worksheet.ReadNext();
}
_exceptionList =
options.ExceptionHandlingBehaviour == ExceptionHandlingBehaviour.ThrowOnFirstException
? null
: new List <Exception>();
var rowMapper = excelToEnumerableContext.GetRowMapper(_options) ??
excelToEnumerableContext.SetRowMapper(_options);
var headerArray = ReadHeader(options, worksheet);
var headerHandler = new HeaderHandler(rowMapper.Setters);
var cellSettersDictionaryForRead = headerHandler.GetPropertySetterDictionary(headerArray, options);
var list = MainLoop(worksheet, rowMapper, options, cellSettersDictionaryForRead);
if (_options.UniqueProperties != null)
{
list = CheckUniqueFields(rowMapper, list);
}
HandleAggregatedExceptions();
return(list);
}
private void HandleException <T>(
object cellValue,
string propertyName,
int rowNumber,
int columnNumber,
IExcelToEnumerableOptions <T> options,
IList <Exception> exceptionsList,
Exception innerException,
ExcelToEnumerableValidationCode?validationCode,
string validationMessage = null)
{
var exception = new ExcelToEnumerableCellException(_currentObject, rowNumber, columnNumber, cellValue,
propertyName, RowValuesByColumRef, validationMessage, innerException, validationCode);
switch (options.ExceptionHandlingBehaviour)
{
case ExceptionHandlingBehaviour.ThrowOnFirstException:
throw exception;
default:
exceptionsList.Add(exception);
break;
}
}
internal IEnumerable <T> MapExcelToEnumerable(Stream excelStream,
ExcelToEnumerableContext excelToEnumerableContext, IExcelToEnumerableOptions <T> options)
{
var workbook = new ExcelReader(excelStream);
var list = MapWorkbookToEnumerable(workbook, excelToEnumerableContext, options);
excelStream.Dispose();
return(list);
}
private IEnumerable <T> MainLoop(SheetReader worksheet, RowMapper rowMapper,
IExcelToEnumerableOptions <T> options, IDictionary <int, PropertySetter> cellSettersDictionaryForRead)
{
rowMapper.SetCellSettersDictionaryForRead(cellSettersDictionaryForRead);
var list = new List <T>();
do
{
if (_options.EndRow < 0 && worksheet.WorksheetDimension == null)
{
throw new NotImplementedException("ExcelToEnumerable is currently unable to handle a negative EndingWithRow value for a worksheet that does not have a WorksheetDimension xml element.");
}
if (_options.EndRow < 0 && worksheet.WorksheetDimension != null)
{
if (worksheet.CurrentRowNumber > worksheet.WorksheetDimension.BottomRight.Row + _options.EndRow)
{
break;
}
}
if (_options.EndRow >= 0 && worksheet.CurrentRowNumber > _options.EndRow)
{
break;
}
rowMapper.ReadRowValues(worksheet);
// ReSharper disable once PossibleInvalidOperationException
var rowNumber = worksheet.CurrentRowNumber.Value;
if (rowNumber <= options.HeaderRow)
{
continue;
}
switch (options.BlankRowBehaviour)
{
case BlankRowBehaviour.CreateEntity:
var newEntities = HandleCreateEntityBehaviour(worksheet, rowMapper);
list.AddRange(newEntities);
break;
case BlankRowBehaviour.Ignore:
var item = HandleIgnoreBehaviour(rowMapper, rowNumber);
if (item != null)
{
list.Add(item);
}
break;
case BlankRowBehaviour.StopReading:
// ReSharper disable once IdentifierTypo
var continu =
HandleStopReadingOrThrowExceptionBehaviour(worksheet, list, rowMapper);
if (!continu)
{
return(list);
}
break;
case BlankRowBehaviour.ThrowException:
var addedRow =
HandleStopReadingOrThrowExceptionBehaviour(worksheet, list, rowMapper);
if (!addedRow)
{
throw new ExcelToEnumerableRowException(
null,
$"Blank row found at row: '{rowNumber}'", rowNumber,
rowMapper.RowValuesByColumRef);
}
break;
}
rowMapper.Clear();
} while (worksheet.ReadNext());
return(list);
}
public void CreateMapper <T>(IExcelToEnumerableOptions <T> options)
{
var fromRowConstructor = CreateRowMapperFromPropertyDescriptorCollection(options);
_rowMapperDictionary.TryAdd(options.GetHashCode(), fromRowConstructor);
}
public static void NotNullProperties <T>(string propertyName, IExcelToEnumerableOptions <T> options)
{
options.NotNullProperties.Add(propertyName);
}
public static void Unique <T>(string propertyName, IExcelToEnumerableOptions <T> options)
{
options.UniqueProperties.Add(propertyName);
}
internal static void RequiredColumn <T>(bool isRequired, string propertyName, IExcelToEnumerableOptions <T> options)
{
if (isRequired)
{
options.ExplicitlyRequiredColumns.Add(propertyName);
}
else
{
options.ExplicitlyRequiredColumns.Remove(propertyName);
}
}
public bool AddPropertiesFromRowValues <T>(object obj, int rowCount, IExcelToEnumerableOptions <T> options,
IList <Exception> exceptionsList)
{
_currentObject = obj;
var success = true;
List <string> fieldsPresentInThisRow = (options.NotNullProperties != null && options.NotNullProperties.Any())
? new List <string>()
: null;
foreach (var item in RowValues)
{
var cellValue = item.Value;
var propertySetter = _cellSettersDictionaryForRead[item.Key];
var isRequiredAlreadyAdded = false;
var invalidCast = false;
try
{
cellValue = propertySetter.PropertyMapping != null
? propertySetter.PropertyMapping(cellValue)
: ConvertType(cellValue, propertySetter.Type, propertySetter.RelaxedNumberMatching);
propertySetter.Setter(obj, cellValue);
}
catch (Exception e)
{
if (propertySetter.PropertyMapping == null && !(e is InvalidCastException))
{
throw;
}
HandleException(cellValue, propertySetter.ColumnName, rowCount, item.Key, options,
exceptionsList, e, null, "Value is invalid");
success = false;
invalidCast = true;
}
if (propertySetter.Validators != null && !invalidCast)
{
foreach (var validator in propertySetter.Validators)
{
if (!validator.Validator(cellValue))
{
HandleException(cellValue, propertySetter.ColumnName, rowCount, item.Key, options,
exceptionsList, null,
validator.ExcelToEnumerableValidationCode,
validator.Message);
if (validator.ExcelToEnumerableValidationCode == ExcelToEnumerableValidationCode.Required)
{
isRequiredAlreadyAdded = true;
}
success = false;
break;
}
}
}
if (fieldsPresentInThisRow != null && !isRequiredAlreadyAdded)
{
fieldsPresentInThisRow.Add(propertySetter.ColumnName);
}
}
if (fieldsPresentInThisRow != null)
{
var result = CheckForMissingFields(options, fieldsPresentInThisRow, rowCount, exceptionsList);
if (!result)
{
success = false;
}
}
if (options.RowNumberProperty != null)
{
var setter = Setters.First(x => x.PropertyName == options.RowNumberProperty);
setter.Setter(obj, rowCount);
}
return(success);
}
public IDictionary <int, PropertySetter> GetPropertySetterDictionary <T>(IDictionary <int, string> headers, IExcelToEnumerableOptions <T> options)
{
var normalisedHeaderArray = headers?.Values.Select(x => x?.ToNormalisedVariableName()).ToArray();
GetPropertySetterDictionary(options, normalisedHeaderArray);
AddCellValidators(options);
return(_cellSettersDictionaryForRead);
}
internal static void MapFromColumns <T>(IEnumerable <string> columnNames, string propertyName, IExcelToEnumerableOptions <T> options)
{
if (options.CollectionConfigurations == null)
{
options.CollectionConfigurations = new Dictionary <string, ExcelToEnumerableCollectionConfiguration>();
}
var configuration = new ExcelToEnumerableCollectionConfiguration
{
PropertyName = propertyName,
ColumnNames = columnNames
};
options.CollectionConfigurations.Add(propertyName, configuration);
}
public static void MapsToColumnNamed <T>(string columnName, string propertyName, IExcelToEnumerableOptions <T> options)
{
options.CustomHeaderNames[propertyName] = columnName;
}
public static void ShouldBeGreaterThan <T>(double minValue, string propertyName, IExcelToEnumerableOptions <T> options)
{
if (!options.Validations.ContainsKey(propertyName))
{
options.Validations[propertyName] = new List <ExcelCellValidator>();
}
options.Validations[propertyName].Add(ExcelCellValidatorFactory.CreateGreaterThan(minValue));
}
public static void MapsToRowNumber <T>(string propertyName, IExcelToEnumerableOptions <T> options)
{
options.RowNumberProperty = propertyName;
}
