ExcelDataMapper as described at http://tobiasly.com/2014/10/31/using-exceldatareader-with-automapper-to-create-lists-of-strongly-typed-data

ExcelDataReader.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using AutoMapper;
using AutoMapper.Mappers;

namespace IronToby.Vandelay
{
    public class ExcelRowMapper<TResult> : IExcelRowMapper
    {
        private readonly List<ExcelRowMapItem> _mapItems = new List<ExcelRowMapItem>();
        
        /// <summary>
        /// Returns an <see cref="AutoMapper.MappingEngine"/> with the configuration as defined by previous
        /// Map calls.
        /// </summary>
        public MappingEngine GetMappingEngine()
        {
            var config = new ConfigurationStore(new TypeMapFactory(), MapperRegistry.Mappers);

            var map = config.CreateMap<IDataReader, TResult>();

            foreach (var item in _mapItems)
            {
                map.ForMember(item.PropertyExpression, opt => opt.MapFrom(reader => GetValue(reader, item)));
            }

            return new MappingEngine(config);
        }

        private object GetValue(IDataReader reader, ExcelRowMapItem map)
        {
            var callback = map.ValueCallback;

            object excelValue;
            var value = GetValueFromField(reader, map, out excelValue);

            if (callback != null)
                value = callback(new ExcelMapReaderState(reader, value, map.FieldName, excelValue));

            return map.PropertyType.IsInstanceOfType(value) ? value : null;
        }

        private object GetValueFromField(IDataReader reader, ExcelRowMapItem map, out object excelValue)
        {
            excelValue = null;

            if (map.FieldName == null)
                return null;

            var index = reader.GetOrdinal(map.FieldName);

            if (reader.IsDBNull(index))
                return null;

            var value = excelValue = reader.GetValue(index);

            Type enumType;

            if (value is double?)
            {
                return GetNumericValueFromDouble(value, map);
            }

            if (value is string && (enumType = GetEnumType(map.PropertyType)) != null)
            {
                return GetEnumValueFromString(value, enumType);
            }

            return value;
        }

        private static Type GetEnumType(Type type)
        {
            if (type.IsEnum)
                return type;

            var underlying = Nullable.GetUnderlyingType(type);

            if (underlying != null && underlying.IsEnum)
                return underlying;

            return null;
        }

        /// <summary>
        /// ExcelDataReader stores all numeric values as doubles, so to avoid tripping up
        /// AutoMapper we perform a more specific conversion here
        /// </summary>
        private static object GetNumericValueFromDouble(object value, ExcelRowMapItem map)
        {
            if (typeof(long?).IsAssignableFrom(map.PropertyType))
                return Convert.ToInt64((double)value);

            if (typeof(int?).IsAssignableFrom(map.PropertyType))
                return Convert.ToInt32((double)value);

            if (typeof(decimal?).IsAssignableFrom(map.PropertyType))
                return Convert.ToDecimal((double)value);

            return value;
        }

        private static object GetEnumValueFromString(object value, Type enumType)
        {
            var valueName = ((string)value).Replace(" ", "").Trim();

            try
            {
                return Enum.Parse(enumType, valueName, true);
            }
            catch (ArgumentException)
            {
                return null;
            }
        }

        public void Map(Expression<Func<TResult, object>> property, string fieldName)
        {
            Map(property, fieldName, null);
        }

        public void Map(Expression<Func<TResult, object>> property, Func<ExcelMapReaderState, object> callback)
        {
            Map(property, null, callback);
        }

        public void Map(Expression<Func<TResult, object>> property, string fieldName,
            Func<ExcelMapReaderState, object> callback)
        {
            var item = new ExcelRowMapItem
            {
                PropertyExpression = property,
                PropertyType = GetPropertyType(property),
                FieldName = fieldName,
                ValueCallback = callback,
            };

            _mapItems.Add(item);
        }

        private static Type GetPropertyType(LambdaExpression expression)
        {
            var unary = expression.Body as UnaryExpression;

            // if a unary expression, an implicit Convert to object was created so look at its operand
            var accessor = unary == null ?
                expression.Body as MemberExpression :
                unary.Operand as MemberExpression;

            var propertyInfo = accessor == null ? null : (accessor.Member as PropertyInfo);

            if (propertyInfo == null)
                throw new InvalidOperationException(string.Format(
                    "Member accessor does not select a single property from type '{0}': {1}", typeof(TResult), expression));

            return propertyInfo.PropertyType;
        }

        private class ExcelRowMapItem
        {
            public Expression<Func<TResult, object>> PropertyExpression { get; set; }
            public Type PropertyType { get; set; }
            public string FieldName { get; set; }
            public Func<ExcelMapReaderState, object> ValueCallback { get; set; }
        }
    }

    /// <summary>
    /// Non-generic interface for getting the MappingEngine from an ExcelRowMapper without knowing
    /// its result type.
    /// </summary>
    public interface IExcelRowMapper
    {
        MappingEngine GetMappingEngine();
    }

    /// <summary>
    /// The state of the Excel mapper and DataReader; used in callbacks to perform custom mapping.
    /// </summary>
    public class ExcelMapReaderState
    {
        internal ExcelMapReaderState(IDataReader reader, object value, string fieldName,
            object excelValue)
        {
            DataReader = reader;
            CurrentValue = value;
            FieldName = fieldName;
            ExcelValue = excelValue;
        }

        /// <summary>
        /// The data reader being used to read the Excel-based DataTable.
        /// </summary>
        public IDataReader DataReader { get; private set; }

        /// <summary>
        /// The value as translated by AutoMapper.
        /// </summary>
        public object CurrentValue { get; private set; }

        /// <summary>
        /// The name of the Excel field from which this value was retrieved.
        /// </summary>
        public string FieldName { get; private set; }

        /// <summary>
        /// The original value of the field as read from Excel.
        /// </summary>
        public object ExcelValue { get; private set; }
    }
}

Toby, how would you write the Automapper 4.2+ for the following:

public MappingEngine GetMappingEngine()
{
var config = new ConfigurationStore(new TypeMapFactory(), MapperRegistry.Mappers);
var map = config.CreateMap<IDataReader, TResult>();
foreach (var item in _mapItems)
{
map.ForMember(item.PropertyExpression, opt => opt.MapFrom(reader => GetValue(reader, item)));
}
return new MappingEngine(config);
}
Jack

@AgileQ use MapperConfiguration as follows

        var config = new MapperConfiguration(cfg =>
        {
            var map = cfg.CreateMap<IDataReader, TResult>();
            foreach (var item in _mapItems)
            {
                map.ForMember(item.PropertyExpression, opt => opt.MapFrom(reader => GetValue(reader, item)));
            }
        });

        return new MappingEngine(config, config.CreateMapper());

Can you please describe, how to call this mapping ?
A snippet to assign datasource please.

@nimiaj you use GetMappingEngine() that returns IMappingEngine which you'll use to call .Map() for your source and destination.

Line 54 var index = reader.GetOrdinal(map.FieldName); throws method not implemented exception. What is your work-around for that ?

@cemremengu i don't get such an error. I would make sure your source object is using the correct IDataReader implementation. I'm using DbDataReader.

I am not able to get this code working. "MappingEngine" cannot be resolved by IDE. I installed AutoMapper using NuGet and added the class shown above. Do I miss some basics here?

MappingEngine is no longer available, Therfore it cannot be used.
However an example for how to use the code listed above with an actual excel sheet would be very nice. I don't know how to use the code shown here..