**`ToCollection` and `ToCollectionWithMappings` were introduced in EPPlus 6.1. These methods allows you to export data from a spreadsheet into an `IEnumerable` 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\ 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: ```csharp public class Person { public string FirstName { get; set; } public string LastName { get; set; } public int Height { get; set; } public DateTime BirthDate { get; set; } } ``` ```vbnet 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`, where each instance corresponds to a row of the range: ```csharp IEnumerable exportedPersons = worksheet.Cells["A1:D4"].ToCollection(); ``` ```vbnet 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](<#usage-with-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. ```csharp IEnumerable exportedPersons = worksheet.Cells["A1:D4"].ToCollection(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. }); ``` ```vbnet 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\ Use this method if you need to define all or some of the mappings yourself. The first argument of this function is a `Func`. We are using the same `Person` class in the below example: ```csharp // 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( 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("FirstName"); // get value by the 0-based column index person.Height = row.GetValue(2); // return the class instance return person; }, options => options.HeaderRow = 0); ``` ```vbnet ' 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](<#custom-headers>), if not an `ArgumentException` will be thrown.* | | `GetValue(int)` | an instance of T | Returns a cell value from the current row by its column index. | | `GetValue(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`](<#custom-headers>), 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](<#custom-headers>), if not an `ArgumentException` will be thrown.* | | `Automap(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. ```csharp var exportedPersons = sheet.Cells["A2:E3"].ToCollectionWithMappings(row => { return new Person { FirstName = row.GetValue("Fn"), LastName = row.GetValue("Ln"), Height = row.GetValue("H"), BirthDate = row.GetValue("Bd") }; }, options => options.SetCustomHeaders("Fn", "Ln", "H", "Bd")); ``` ```vbnet 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: ```csharp 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; } } ``` ```vbnet 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 ``` *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`](<#tocollectionwithmappingst>) 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: ```csharp var myTable = worksheet.Tables["MyTable"]; var exportedPersons = myTable.ToCollection(); ``` ```vbnet Dim myTable = worksheet.Tables("MyTable") Dim exportedPersons = myTable.ToCollection(Of ToCollectionSamplePerson)() ``` ## See also - [Sample 2.2-C\#]() and [Sample 2.2-VB]() - [Export data]()