Skip to content

ToCollection

ToCollection and ToCollectionWithMappings were introduced in EPPlus 6.1. These methods allows you to export data from a spreadsheet into an IEnumerable<T> where T is a class. If you have used the LoadFromCollection method of EPPlus previously this is kind of a reversed version of that method

ToCollection<T>

You can use this method to export data from a spreadsheet 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; }
}

Public Class Person
    Public Property FirstName As String

    Public Property LastName As String

    Public Property Height As Integer

    Public Property BirthDate As DateTime
End Class

Here is how to use the ToCollection method to export instances from a range to an IEnumerable<Person>, where each instance corresponds to a row of the range:

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

Dim exportedPersons As IEnumerable(Of Person) = worksheet.Cells("A1:D4").ToCollection(Of 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. It does so either by looking for an exact match, a match after having removed whitespace from the column header or by using attributes on the exported 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 directly 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 conversion fails.
});

Dim exportedPersons As IEnumerable(Of Person) = worksheet.Cells("A1:D4").ToCollection(Of Person)(
    Sub(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 conversion fails.
    End Sub)

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 exportedPersons = ws.Cells["A1:D4"].ToCollectionWithMappings<Person>(
    row => 
    {
        // this runs once per row in the range

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

        // 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);

' 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.
Dim exportedPersons = ws.Cells("A1:D4").ToCollectionWithMappings(Of Person)(
    Function(row)
        ' this runs once per row in the range

        ' Create an instance of the exported class
        Dim person As New Person()

        ' 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(Of String)("FirstName")

        ' get value by the 0-based column index
        person.Height = row.GetValue(Of Integer)(2)

        ' return the class instance
        Return person
    End Function,
    Sub(options) options.HeaderRow = 0)

ToCollectionRow

This class represents one row in the exported range - see the lambda parameter row in the example above.

Member Returns Description
index [int] object Returns a cell value from the current row by its column index.
index [string] object Returns a cell value from the current row by its column name. Note that headers must be specified with options.HeaderRow or options.SetCustomHeaders, if not an ArgumentException will be thrown.
GetValue<T>(int) an instance of T Returns a cell value from the current row by its column index.
GetValue<T>(string) an instance of T Returns a cell value from the current row by its column name. Note that headers must be specified with options.HeaderRow or options.SetCustomHeaders, if not an ArgumentException will be thrown.
GetText(int) string Returns formatted value of the cell at the column index within the row of the range.
GetText(string) string Returns formatted value of the cell at the column index within the row of the range. Note that headers must be specified with options.HeaderRow or options.SetCustomHeaders, if not an ArgumentException will be thrown.
Automap<T>(T item) void Maps all properties on the instance of T to values matching the column header with the property name or attibutes without white spaces.
DataIsTransposed bool Transpose data on export

Custom headers

Via the options SetCustomHeaders function you can create an alias for each column that you can use when getting the cell values from the row. The list of custom headers (see example below) must be specified in column order.

var exportedPersons = sheet.Cells["A2:E3"].ToCollectionWithMappings(row =>
{
   return new Person
   {
      FirstName = row.GetValue<string>("Fn"),
      LastName = row.GetValue<string>("Ln"),
      Height = row.GetValue<int>("H"),
      BirthDate = row.GetValue<DateTime>("Bd")
   };
}, options => options.SetCustomHeaders("Fn", "Ln", "H", "Bd"));

Dim exportedPersons = sheet.Cells("A2:E3").ToCollectionWithMappings(Of Person)(
    Function(row)
        Return New Person With {
            .FirstName = row.GetValue(Of String)("Fn"),
            .LastName = row.GetValue(Of String)("Ln"),
            .Height = row.GetValue(Of Integer)("H"),
            .BirthDate = row.GetValue(Of DateTime)("Bd")
        }
    End Function,
    Sub(options) options.SetCustomHeaders("Fn", "Ln", "H", "Bd"))

Usage with attributes

You can use attributes to map a readable header to the properties of the class. These attributes works the same way as in the LoadFromCollection method. These attributes are (in priorityorder from how EPPlus is looking for them):

  1. OfficeOpenXml.Attributes.EPPlusTableColumnAttribute
  2. System.ComponentModel.DescriptionAttribute
  3. System.ComponentModel.DisplayNameAttribute

Here is how these attributes can be used on the Person class in the example above:

public class Person
{
    [DisplayName("The persons first name")]
    public string FirstName { get; set; }

    [Description("The persons last name")]
    public string LastName { get; set; }

    [EpplusTableColumn(Header ="Height of the person")]
    public int Height { get; set; }

    public DateTime BirthDate { get; set; }
}

Public Class Person
    <DisplayName("The persons first name")>
    Public Property FirstName As String

    <Description("The persons last name")>
    Public Property LastName As String

    <EpplusTableColumn(Header:="Height of the person")>
    Public Property Height As Integer

    Public Property BirthDate As DateTime
End Class

Note that whilst LoadFromCollection supports a few more attributes, these are the only ones that ToCollection and ToCollectionWithMappings currently supports. If you need to map columns to a complex property on the exported type, use the ToCollectionWithMappings method.

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 exportedPersons = myTable.ToCollection<ToCollectionSamplePerson>();

Dim myTable = worksheet.Tables("MyTable")
Dim exportedPersons = myTable.ToCollection(Of ToCollectionSamplePerson)()

See also