-
Notifications
You must be signed in to change notification settings - Fork 275
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
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.
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.
});
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);
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 |
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"));
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):
OfficeOpenXml.Attributes.EPPlusTableColumnAttribute
System.ComponentModel.DescriptionAttribute
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.
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>();
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles