Epplus example of `LoadFromCollection(IEnumerable, Boolean, TableStyles, BindingFlags, MemberInfo[])`

Question

When using Epplus ExcelPackage, I typically use a basic LoadFromCollection function to populate a Worksheet, like in this manner:

worksheet.Cells[1, 1].LoadFromCollection(Collection:data, PrintHeaders:true);

On this site, I see the listing of other overloads of that function, but no specific examples. This one looks interesting. Can someone provide a basic example of this function overload in use?

LoadFromCollection<T>(IEnumerable<T> Collection, Boolean PrintHeaders,
      TableStyles TableStyle, BindingFlags memberFlags, MemberInfo[] Members)

I can see by clicking through the API that "TableStyles" is an enum that can be assigned like so:

TableStyles TableStyle = OfficeOpenXml.Table.TableStyles.Medium1;

The memberFlags and Members parameters remain a mystery to me.

score 12 · Accepted Answer · edited May 23 '17 at 11:46

LoadFromCollection basically allow you to use Generics as the source Collection that implements IEnumerable<T> and the function will use the T type from that collection. So, say, a List of thingy objects can be consumed by it. Here is a thread that demonstrates it:

EPPlus - LoadFromCollection - Text converted to number

The collection datalist is

var datalist = new List<TestObject>();

is passed into the function

worksheet.Cells.LoadFromCollection(datalist);

As for the overloads, the longest one (the one you show) takes the MemberInfo's of the T object as the last parameter. If Members is null (via the other overloads) it simply does

Members = type.GetProperties(memberFlags);

where type is

var type = typeof(T);

You can supply your own but it is rarely necessary unless you are doing some very specific. The above memberFlags is the typical Public and Instance property types (again not usually necessary to supply your own).

If you want to get a better idea of this you can checkout the source doe here:

http://epplus.codeplex.com/SourceControl/latest#EPPlus/ExcelRangeBase.cs

RESPONSE TO COMMENTS

Here is a proper example relevant to the question:

public class TestObject
{
    public int Col1 { get; set; }
    public int Col2 { get; set; }
    public string Col3 { get; set; }
    public DateTime Col4 { get; set; }
}

[TestMethod]
public void LoadFromCollection_MemberList_Test()
{
    //https://stackoverflow.com/questions/32587834/epplus-loadfromcollection-text-converted-to-number/32590626#32590626

    var TestObjectList = new List<TestObject>();
    for (var i = 0; i < 10; i++)
        TestObjectList.Add(new TestObject {Col1 = i, Col2 = i*10, Col3 = (i*10) + "E4"});

    //Create a test file
    var fi = new FileInfo(@"c:\temp\LoadFromCollection_MemberList_Test.xlsx");
    if (fi.Exists)
        fi.Delete();

    using (var pck = new ExcelPackage(fi))
    {
        //Do NOT include Col1
        var mi = typeof (TestObject)
            .GetProperties()
            .Where(pi => pi.Name != "Col1")
            .Select(pi => (MemberInfo)pi)
            .ToArray();

        var worksheet = pck.Workbook.Worksheets.Add("Sheet1");
        worksheet.Cells.LoadFromCollection(
            TestObjectList
            , true
            , TableStyles.Dark1
            , BindingFlags.Public| BindingFlags.Instance
            , mi);

        pck.Save();
    }
}

Notice that Col1 is NOT in the output:

This just sunk in "it is rarely necessary unless you are doing some very specific". Out of curiosity, do you have an example of what kind of specific thing could be done? — , Jun 19 '16 at 20:24
@Rubix_Revenge I dont recall every doing it but all it is doing is generating a collection of `MemberInfo` via Reflections off of all Public and Instance type properties. So I could see it being useful if, say, you `T` object has a dozen properties but you would wanted certain ones to be included in your output. You could generate you own array of `MemberInfos` so Epplus would only include those in the output. — Ernie S, Jun 19 '16 at 22:15
@Rubix_Revenge Felt like my answer was a bit lacking in not providing a proper example so see my edit above. — Ernie S, Jun 20 '16 at 14:23
This is brilliant! It's also an under-documented feature of Epplus that can solve many problems. For example, there were many times I wanted to be able to simply skip specific columns from the collection, and this is an easy way to do it, now that I see an example. — , Jun 20 '16 at 14:40
You could use attributes to control which members were included under which scenarios. — DavidCC, Aug 16 '18 at 22:18

Epplus example of `LoadFromCollection(IEnumerable, Boolean, TableStyles, BindingFlags, MemberInfo[])`

1 Answers1

Linked