LoadFromCollection

This method provides an easy way to load data from IEnumerables of .NET classes into a spreadsheet.

Basic usage

We can start with the following simple class...

public class MyClass
{
    public string Id { get; set; }
    public string Name { get; set; }
    public int Number { get; set; }
}

...and a a few instances of it in a List.

var items = new List<MyClass>()
{
    new MyClass(){ Id = "123", Name = "Item 1", Number = 3},
    new MyClass(){ Id = "456", Name = "Item 2", Number = 6}
};

Now let's create a workbook with a worksheet and add this data into it using the LoadFromCollection method:

using (var pck = new ExcelPackage())
{
    var sheet = pck.Workbook.Worksheets.Add("sheet");
    sheet.Cells["C1"].LoadFromCollection(items);
}

This will load data into the worksheet, starting at cell C1. C1 will get the value "123", D1 will get the value "Item 1", E1 will get the value 3.

Print headers

LoadFromCollection has several method signatures and argument number 2 specifies if we should have a row with headers above the data. In this case the headers will have the same values as the names of the class properties, i.e. "Id", "Name" and "Number".

sheet.Cells["C1"].LoadFromCollection(items, true);

...alternatively (from version 5.2.1 and higher):

sheet.Cells["C1"].LoadFromCollection(items, c => c.PrintHeaders = true);

The headers ("Id", "Name" and "Number") will be written in the range C1:E1 and the data will be written from D1.

Table style

The third argument - TableStyle - gives you the possibility to style the range as a table.

sheet.Cells["C1"].LoadFromCollection(items, true, TableStyles.Dark1);

...alternatively (from version 5.2.1 and higher):

sheet.Cells["C1"].LoadFromCollection(items, c => {
    c.PrintHeaders = true;
    c.TableStyle = TableStyles.Dark1;
});

The TableStyles enum gives you over 60 different table styles to pick from!

Headers

The default behaviour of the LoadFromCollection method is to replace underscores with a space - "My_property" will be "My property". But there are a few options to configure this.

Attributes

If you decorate the members of your class with the System.ComponentModel.DescriptionAttribute the value of this attribute will be used instead of the name of the property. If this attribute is not present EPPlus will look for the System.ComponentModel.DisplayNameAttribute. If none of this is present it will use the default behaviour (see above).

public class MyClass
{
    [DisplayName("The id")]
    public string Id { get; set; }
    [Description("The name")]
    public string Name { get; set; }
    public int Number { get; set; }
}

HeaderParsingType

Available from version 5.2.1 and higher

The HeaderParsingType argument is available on the following method signature:

sheet.Cells["C1"].LoadFromCollection(items, c => {
    c.PrintHeaders = true;
    c.HeaderParsingType = HeaderParsingTypes.CamelCaseToSpace;;
});

If you set HeaderParsingType to HeaderParsingTypes.Preserve the header will be exactly like the member name. If you set it to HeaderParsingTypes.CamelCaseToSpace it will put a space before each capital letter in the property name ("MyProperty" to "My Property").

Filtering members

The default behaviour is to import all members of a class, but you can specify which members to import with the Members argument. See the following example:

var t = typeof(MyClass);
sheet.Cells["C1"].LoadFromCollection(items, true, TableStyles.Dark1, LoadFromCollectionParams.DefaultBindingFlags, 
    new MemberInfo[] 
    { 
        t.GetProperty("Id"), 
        t.GetProperty("Name")
    });

...alternatively:

var t = typeof(MyClass);
sheet.Cells["C1"].LoadFromCollection(items, c => {
    c.PrintHeaders = true;
    c.Members = new MemberInfo[]
        {
            t.GetProperty("Id"), 
            t.GetProperty("Name")
        }
});