Skip to content

ToCollection

Jump to bottom
AdrianEPPlus edited this page Jun 20, 2024 · 24 revisions

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 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>, where each instance corresponds to a row of the range:

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

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 wiill 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 wiill 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 wiill 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 false

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

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; }
}

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

See also

EPPlus Software AB - https://epplussoftware.com

Features - Licence overview - Developers

Home

EPPlus wiki

Versions

Worksheet & Ranges

Styling

Import/Export data

Formulas and filters

Charts & Drawing objects

Tables & Pivot Tables

VBA & Protection

Branching strategy and CI

Roadmap

License FAQ

Using our develop nuget feed

Clone this wiki locally