Ignoring properties when calling LoadFromCollection in EPPlus

c# entity-framework-6 epplus excel

Question

I'm attempting to use the following code to create an Excel file:

public static Stream GenerateFileFromClass<T>(IEnumerable<T> collection, int startrow, int startcolumn, byte[]templateResource)
        {
using (Stream template = new MemoryStream(templateResource))//this is an excel file I am using for a base/template
    {
        using (var tmpl = new ExcelPackage(template))
        {
            ExcelWorkbook wb = tmpl.Workbook;
            if (wb != null)
            {
                if (wb.Worksheets.Count > 0)
                {
                    ExcelWorksheet ws = wb.Worksheets.First();
                    ws.Cells[startrow, startcolumn].LoadFromCollection<T>(collection, false);
                }
                return new MemoryStream(tmpl.GetAsByteArray());
            }
            else
            {
                throw new ArgumentException("Unable to load template WorkBook");
            }
        }
    }
}

However, this works a treat. In order for my class collection to align with my design, I want to omit a few of the attributes. I am aware of theLoadFromCollection will create columns in the Excel file based on the class's public properties, but since I'm loading the class using Entity Framework, if I designate a field as private, EF gets upset, probably because the Key is one of the values I don't want to display.

I've made an effort to indicate the attributes I don't wish to use.[XmlIgnore] but in vain. Is there any other method to do this than importing the whole collection into a dataset and then removing the columns from it? or by casting to a base class that lacks the attributes I require?

1
11
1/7/2016 11:33:29 PM

Popular Answer

Yes, EPPlus offers a surplus of the.LoadFromCollection<T>() approach using aMemberInfo[] the characteristics you want to add as a parameter.

This provides us all the information we need to disregard any properties with a certain property.

If we wish to disregard properties containing this particular custom property, for instance:

public class EpplusIgnore : Attribute { }

then we may create a little extension method to locate all of them initially.MemberInfo without the objects for the characteristics[EpplusIgnore] then to return the outcome of the proper overload of the attribute.LoadFromCollection in the EPPlus dll procedure.

Possibly like this:

public static class Extensions
{
    public static ExcelRangeBase LoadFromCollectionFiltered<T>(this ExcelRangeBase @this, IEnumerable<T> collection) where T:class
    {
        MemberInfo[] membersToInclude = typeof(T)
            .GetProperties(BindingFlags.Instance | BindingFlags.Public)
            .Where(p=>!Attribute.IsDefined(p,typeof(EpplusIgnore)))
            .ToArray();

        return @this.LoadFromCollection<T>(collection, false, 
            OfficeOpenXml.Table.TableStyles.None, 
            BindingFlags.Instance | BindingFlags.Public, 
            membersToInclude);
    }

}

Using it in this manner, for instance, will disregard the.Key while exporting a propertyPerson gathering to Excel:

public class Person
{
    [EpplusIgnore]
    public int Key { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        var demoData = new List<Person> { new Person { Key = 1, Age = 40, Name = "Fred" }, new Person { Key = 2, Name = "Eve", Age = 21 } };

        FileInfo fInfo = new FileInfo(@"C:\Temp\Book1.xlsx");
        using (var excel = new ExcelPackage())
        {
            var ws = excel.Workbook.Worksheets.Add("People");
            ws.Cells[1, 1].LoadFromCollectionFiltered(demoData);

            excel.SaveAs(fInfo);
        }
    }
}

giving the results we'd anticipate:

enter image description here

20
1/7/2016 10:40:19 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow