ToCollection

ToCollection and ToCollectionWithMappings were introduced in EPPlus 6.1. This method allows you to export data from a spreadsheet into an IEnumerable<T> where T is a class.

ToCollection<T>

You can use this method to export data from a spreadshet range into an enumerable of class instances. In this example we will use the class below:

public class Person
{
    public string FirstName { get; set; }

    public string LastName { get; set; }

    public int Height { get; set; }

    public DateTime BirthDate { get; set; }
}

Here is how to use the ToCollection method to export instances from a range to an IEnumerable<Person>:

IEnumerable<Person> exportedPersons = worksheet.Cells["A1:D4"].ToCollection<Person>();

In this case it is assumed that:

• The exported range has headers (the first row in the range) that EPPlus can map to the property names of the class.
• The value of each cell is of a data type that can be converted to the corresponding property in the class used.

ToCollectionOptions

You can configure how this method behaves if you set the properties on this class that can be supplied as an argument to ToCollection - either via a lambda or as a class instance.

IEnumerable<Person> exportedPersons = worksheet.Cells["A1:D4"].ToCollection<Person>(options => {
   
   // 0-based index of the Header row in the range, if applicable. This is an int? and a null value means there is no header row.
   options.HeaderRow = 0;

   // 0-based index of the data start row in the range. This is an int? and null value means the data rows starts direcly after the header row.
   options.DataStartRow = 1;

   // How conversion failures should be handled when mapping properties.
   options.ConversionFailureStrategy = ConversionFailureStrategy.Exception; // Throws an `EPPlusDataTypeConvertionException` if the conversion fails
   options.ConversionFailureStrategy = ConversionFailureStrategy.SetDefaultValue; // Set the default value for the property's datatype if cerversion fails.
});

ToCollectionWithMappings<T>

Use this method if you need to define all or some of the mappings yourself. The first argument of this function is a Func<ToCollectionRow, T>. We are using the same Person class in the below example:

// The code in the lambda below will run once for each exported row in the range. It must return
// an instance of the class supplied as the generic type argument to the function.
var exportedPersons2 = ws.Cells["A1:D4"].ToCollectionWithMappings<ToCollectionSamplePerson>(
    row => 
    {
        // this runs once per row in the range

        // Create an instance of the exported class
        var person = new ToCollectionSamplePerson();

        // If some of the cells can be automapped, start by automapping the row data to the class
        row.Automap(person);

        // Note that you can only use column names as below
        // if options.HeaderRow is set to the 0-based row index
        // of the header row.
        person.FirstName = row.GetValue<string>("FirstName");

        // get value by the 0-based column index
        person.Height = row.GetValue<int>(2);
                        
        // return the class instance
        return person;
    }, 
    options => options.HeaderRow = 0);

Usage with ExcelTables

The ToCollection and ToCollectionWithMappings methods are also available on the ExcelTable class. So if you have a table you can use this method like this:

var myTable = worksheet.Tables["MyTable"];
var exportedPersons4 = myTable.ToCollection<ToCollectionSamplePerson>();

See also

More sample code for these methods is available in our sample projects, see sample 33 for NET Core and .NET Framework.