Simple POCO Mapper for EPPlus

• Codeberg repository

Introduction

For a recent project of mine, I needed to be able to read Excel files and I came across an excellent open source library called EPPlus. Unfortunately, I couldn't find any generic methods to cast the rows or columns to objects so I decided to create these myself.

Using the Code

The main logic for using the code is encapsulated in the class ExcelWorksheetExtensions.

Here, we have two public methods for reading one specific mapping or get all mappings returned in a list.

public static TItem GetRecord<TItem>(this ExcelWorksheet sheet, 
                                     int rowOrColumn, ExcelMap<TItem> map = null)

public static List<TItem> GetRecords<TItem>(this ExcelWorksheet sheet, 
                                                 ExcelMap<TItem> map = null)

Both have an optional parameter ExcelMap<TItem>. When no map is provided, we try to create one automatically based on the values of the first row or by reading the ExcelMapper and ExcelMap attributes of TItem.

The ExcelMap<TItem> class holds the mapping of the Excel rows or columns to the properties of TItem. It specifies a Header property which can be overridden in a derived class and has a default value of 1. The MappingDirection property tells the mapper if we should map a row or a column to an object. By default, it's a row. It also holds a Mapping property of type Dictionary<int, PropertyInfo> which holds the actual mapping.

If you would like to supply your own mapping, all you need to do is derive from ExcelMap<TItem> and fill the Mapping dictionary.

Another way of indicating the relation between the Excel rows or columns and the properties of TItem is by the use of attributes. To indicate that the code needs to create a mapping object for you based on attributes, you need to decorate the TItem class with the attribute ExcelMapper. You can provide an optional value to the Header and the MappingDirection property, for example, [ExcelMapper(MappingDirection = ExcelMappingDirectionType.Vertical, Header = 0)] means that there is no header in the Excel sheet and the objects are mapped vertically.

The link between a column and a property for horizontal mapping can be set by decorating the property with the ExcelMap attribute and supplying the column index. For example, [ExcelMap(Column = 1)] above a property would mean that this property is mapped to the first column of the Excel sheet. Likewise, [ExcelMap(Row = 1)] means that a property is mapped to the first row of the Excel sheet if the direction is specified as vertical.

A third method is letting the code create a mapping for you based on the values of the header row or column. These will then be stripped of spaces and compared to a list of properties of TItem. Casing in this case will be ignored.

If the code creates a mapping for you, it will call the GetMap function.

private static ExcelMap<TItem> GetMap<TItem>(ExcelWorksheet sheet)
    where TItem : class
{
    var method = typeof(ExcelMap<TItem>).GetMethod("CreateMap", 
                 BindingFlags.Static | BindingFlags.NonPublic);
    if (method == null)
    {
        throw new ArgumentNullException(nameof(method), 
              $"Method CreateMap not found on type {typeof(ExcelMap<TItem>)}");
    }
    method = method.MakeGenericMethod(typeof(ExcelMap<TItem>));

    var map = method.Invoke(null, new object[] { sheet }) as ExcelMap<TItem>;
    if (map == null)
    {
        throw new ArgumentNullException(nameof(map), 
              $"Map {typeof(ExcelMap<TItem>)} could not be created");
    }
    return map;
}

This method will look for the CreateMap function of ExcelMap<TItem> by means of reflection. If the method is found, we define the return type by calling MakeGenericMethod. Next, we invoke the method and supply the Excel sheet as a parameter.

protected static TMap CreateMap<TMap>(ExcelWorksheet sheet) where TMap : ExcelMap<TItem>

The first thing we do is create an instance of TMap. Then, we look for the ExcelMapper attribute on TItem. If found, fill the mapping dictionary with the appropriate values.

var map = Activator.CreateInstance<TMap>();

var type = typeof(TItem);

// Check if we map by attributes or by column header name
var mapper = type.GetCustomAttribute<ExcelMapperAttribute>();
if (mapper != null)
{
    // Map by attribute
    map.MappingDirection = mapper.MappingDirection;
    map.Header = mapper.Header;

    type.GetProperties()
        .Select(x => new { Property = x, 
                Attribute = x.GetCustomAttribute<ExcelMapAttribute>() })
        .Where(x => x.Attribute != null)
        .ToList()
        .ForEach(prop =>
        {
            var key = map.MappingDirection == ExcelMappingDirectionType.Horizontal
                ? prop.Attribute.Column
                : prop.Attribute.Row;
            map.Mapping.Add(key, prop.Property);
        });
}

If we can't find the ExcelMapper attribute or the Mapping property is empty, we try to create the mapping automatically based on the values of the MappingDirection and Header properties. By default, this will be Horizontal with Header value 1, which results in the code trying to map the columns of row 1 with the properties of the supplied object. Likewise, a MappingDirection with the value Vertical and a Header property with value 2, for example, will try to map the values in the second column of the Excel sheet with the properties of the object.

if (!map.Mapping.Any())
{
    // Map by column / row header name
    var props = type.GetProperties().ToList();
    
    // Determine end dimension for the header
    var endDimension = map.MappingDirection == ExcelMappingDirectionType.Horizontal
        ? sheet.Dimension.End.Column
        : sheet.Dimension.End.Row;
    for (var rowOrColumn = 1; rowOrColumn <= endDimension; rowOrColumn++)
    {
        var parameter = map.MappingDirection == ExcelMappingDirectionType.Horizontal
            ? sheet.GetValue<string>(map.Header, rowOrColumn)
            : sheet.GetValue<string>(rowOrColumn, map.Header);
        if (string.IsNullOrWhiteSpace(parameter))
        {
            var message = map.MappingDirection == ExcelMappingDirectionType.Horizontal
                ? $"Column {rowOrColumn} has no parameter name"
                : $"Row {rowOrColumn} has no parameter name";
            throw new ArgumentNullException(nameof(parameter), message);
        }

        // Remove spaces
        parameter = parameter.Replace(" ", string.Empty).Trim();

        // Map to property
        var prop = props.FirstOrDefault(x => 
                   StringComparer.OrdinalIgnoreCase.Equals(x.Name, parameter));
        if (prop == null)
        {
            throw new ArgumentNullException(nameof(parameter), 
                      $"No property {parameter} found on type {typeof(TItem)}");
        }
        map.Mapping.Add(rowOrColumn, prop);
    }
}

So we have the mapping object now, either supplied directly or created by the code, what we need to do next is create the TItem object and fill it with actual values. In the GetRecords<TItem> method, we call GetItem<TItem> and supply the mapping.

private static TItem GetItem<TItem>(ExcelWorksheet sheet, 
                                    int rowOrColumn, ExcelMap<TItem> map)
    where TItem : class
{
    var item = Activator.CreateInstance<TItem>();
    foreach (var mapping in map.Mapping)
    {
        if ((map.MappingDirection == ExcelMappingDirectionType.Horizontal && 
             mapping.Key > sheet.Dimension.End.Column) ||
            (map.MappingDirection == ExcelMappingDirectionType.Vertical && 
             mapping.Key > sheet.Dimension.End.Row))
        {
            throw new ArgumentOutOfRangeException(nameof(rowOrColumn),
                $"Key {mapping.Key} is outside of the sheet dimension 
                       using direction {map.MappingDirection}");
        }
        var value = (map.MappingDirection == ExcelMappingDirectionType.Horizontal)
            ? sheet.GetValue(rowOrColumn, mapping.Key)
            : sheet.GetValue(mapping.Key, rowOrColumn);
        if (value != null)
        {
            // Test nullable
            var type = mapping.Value.PropertyType.IsValueType
                ? Nullable.GetUnderlyingType(mapping.Value.PropertyType) ?? 
                  mapping.Value.PropertyType
                : mapping.Value.PropertyType;
            var convertedValue = (type == typeof(string))
                ? value.ToString().Trim()
                : Convert.ChangeType(value, type);
            mapping.Value.SetValue(item, convertedValue);
        }
        else
        {
            // Explicitly set null values to prevent properties 
            // being initialized with their default values
            mapping.Value.SetValue(item, null);
        }
    }
    return item;
}

We first create the requested object using Activator.CreateInstance. Next, we loop through the mappings and check if the supplied column or row is within the dimensions of the Excel sheet. We then read the value from the sheet. To determine the correct type for the property in case of a value type, we check for nullable by calling Nullable.GetUnderlyingType. We then fill the property of TItem with the value of the Excel column.

The end result using the six different mapping methods is shown below:

History

• 27^th August, 2017: Initial release
• 14^th February, 2018: Added support for vertical mapping
• 18^th November, 2023: Rebased on EPPlusFree, now available as nuget: AO.EPPlusFree