Using ExcelDataReader with AutoMapper to create lists of strongly-typed data

I recently needed to import some data from an Excel spreadsheet in my C# app and wanted to avoid the Office interop assemblies and OleDbReaders if at all possible. I found the excellent ExcelDataReader, which is a pure-.NET class for reading .xls and .xlsx files.

The problem is that this library returns DataSets representing the Excel files, with one DataTable per sheet. This is an understandable approach since the data being imported can be so varied, but I wanted to use lists of strongly-typed objects instead. (LinqToExcel seems to be designed around doing this all as part of one library, but its use of OleDbReader and its hard dependency on log4net to log a single exception in its Dispose method turned me off).

I started going down the road of “rolling my own” methods to create objects from the DataSets, but realized there had to be a better way. After a bit more digging around, I found AutoMapper, which is designed to take the gruntwork out of all type of object-to-object conversions. It can operate on IDataReaders, which are a quick DataTable.CreateDataReader() away. Perfect!

Looking a bit at the examples though, I found that AutoMapper is built around static methods, in which Mapper.CreateMap configures the mapping between two objects, and Mapper.Map performs the mapping conversion. That wasn’t going to work too well for me, because the types of data I needed could vary greatly at runtime, and I’d even need to pull multiple objects of the same type from each row (the data being imported had been flattened somewhat).

StackOverflow helped me solve that problem — the static methods are just wrappers around singletons internally, but you can create your own configuration and MappingEngine objects instead.

Finally, all the pieces were falling into place. I decided to write a quick helper class to build the necessary MappingEngine objects easily.

Because I wanted to map properties at runtime, I was interested in a few different Map methods, which can take a lambda expression representing the property to set, along with either a field name, callback, or both. If a field name is given, the property is set from that field; if a callback is given, it will receive a ExcelMapReaderState object and should return the desired value. (The state object will contain the “current” value if a field name was also given).

Because ExcelDataReader maps all numeric types to double, I wanted the ability to convert these back to int, long or decimal if desired. I also wanted the ability to map a field to an enum, in which case it will call TryParse with the field value (with whitespace removed).

It would be rather straightforward to use attributes instead to perform the mapping, but that isn’t something I really needed (and I’m guessing AutoMapper is already better at that type of thing, but I’m still unfamiliar with its operation).

Find the ExcelDataMapper code here on Gist.

Example usage:

    /// <summary>
    /// This is the class we want to map Excel data to. Value types should be set nullable, since empty
    /// Excel cells will be set as null.
    /// </summary>
    public class PurchaseItem
        public string ItemName { get; set; }
        public AccountTypeEnum? AccountType { get; set; }
        public decimal? Price { get; set; }
        public DateTime? ImportDate { get; set; }

    public void ContrivedExample()
        var mapper = new ExcelRowMapper<PurchaseItem>();
        // get value from Excel "Item" field
        mapper.Map(item => item.ItemName, "Item");

        // parse this field to get enum value
        mapper.Map(item => item.AccountType, "Account Type");

        // get price from "Price" field but post-process it too
        mapper.Map(item => item.Price, "Price", state => (decimal)state.CurrentValue < 0 ? 0 : state.CurrentValue);

        // or ignore the incoming data entirely
        mapper.Map(item => item.ImportDate, state => DateTime.Today);
Posted in General

Leave a Reply

Your email address will not be published. Required fields are marked *


Search The Social Media Firehose or U.S. Census Records online

Posts Calendar

October 2014
« Jun   Dec »