Ignoring properties when calling LoadFromCollection in EPPlus

Question

I am trying to generate an Excel file using the following code:

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");
            }
        }
    }
}

This works like a treat, however.. I want to ignore a couple of the properties in my class collection, so it matches up with my template. I know that the LoadFromCollection will generate columns in the Excel file based on the public properties of the class, but as I am loading the class using Entity Framework, if I mark the field as private, then EF complains - mostly because one of the fields I don't want to show is the Key.

I tried to mark the properties I don't want using [XmlIgnore] to no avail. Is there any way to do this, short of loading the whole collection into a dataset or some such and trimming the columns out of that? Or casting to a base class without the properties I don't need?

Amias

Could you not use something like AutoMapper or Custom mapping to another POCO which only the desired properties and then pass that new object collection to epplus, instead of the EF collection – Hematite

Answer 1

Yes, EPPlus provides an overload of the .LoadFromCollection<T>() method with a MemberInfo[] parameter for the properties you wish to include.

This gives us all we need to ignore any properties with a certain attribute.

For example, if we want to ignore properties with this custom attribute:

public class EpplusIgnore : Attribute { }

then we can write a little extension method to first find all MemberInfo objects for the properties without the [EpplusIgnore] attribute then to return the result of the correct overload of the .LoadFromCollection method in the EPPlus dll.

Something like this:

public static class Extensions
{
    public static ExcelRangeBase LoadFromCollectionFiltered<T>(this ExcelRangeBase @this, IEnumerable<T> collection, bool printHeaders = true) 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, printHeaders, 
            OfficeOpenXml.Table.TableStyles.None, 
            BindingFlags.Instance | BindingFlags.Public, 
            membersToInclude);
    }
    
}

So, for example, using it like this will ignore the .Key property when exporting a Person collection 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 output we'd expect:

Influent

It works for me. But I created a method LoadFromCollectionFiltered<T>(this ExcelRangeBase @this, IEnumerable<T> collection, bool printHeader) in order to decide I want to print the header or not later. – Vicar

This is a fantastic suggestion, but unfortunately it doesn't work for me. Using the standard LoadFromCollection for an IEnumerable<T>, it works perfectly, just with the extra columns. When I use the filtered method you've provided here, I receive an error about declaring types: Supplied properties in parameter Properties must be of the same type as T – Canakin

Solved my own problem; my class was inheriting from another class, and Epplus isn't checking for subtypes or inheritance when it checks the declaring type. Therefore, I just had to add another where clause to ensure it didn't include the parent class in the MemberInfo array. Like: .Where(p => p.DeclaringType != typeof(ParentObject)) – Canakin

Answer 2

Thanks Stewart_R, based on your work i made a new one that receives property names:

public static ExcelRangeBase LoadFromCollection<T>(this ExcelRangeBase @this, 
    IEnumerable<T> collection, string[] propertyNames, bool printHeaders) where T:class
{
    MemberInfo[] membersToInclude = typeof(T)
            .GetProperties(BindingFlags.Instance | BindingFlags.Public)
            .Where(p=>propertyNames.Contains(p.Name))
            .ToArray();

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

Mishmash

Answer 3

As of version 5.5 of EPPlus, the EpplusIgnore attribute is baked into the library. https://github.com/EPPlusSoftware/EPPlus/pull/258

We can use it like below.

using System;
using OfficeOpenXml.Attributes;

namespace ExportToExcel.Services.ExportViewModels
{
   
    [EpplusTable]
    public class StudentExportViewModel
    {
        
        // [EpplusTableColumn]
        [EpplusIgnore]
        public string Id { get; set; }

        ...

    }
}

Note that, either the EpplusTable or EpplusTableColumn attribute is required.

Here is a link to the related test cases https://github.com/EPPlusSoftware/EPPlus/blob/develop/src/EPPlusTest/LoadFunctions/LoadFromCollectionAttributesTests.cs

Recently, I had to use EPPlus in a project and I've documented the set-up in this blog post https://www.kajanm.com/blog/exporting-data-to-excel-in-c-sharp/

Cutinize

Answer 4

Below is a short version of Stewart_R's answer. If someone is using a Generic method to create excel then go ahead with the below approach.

 public static class EPPlusHelper
    {
        public static byte[] GetExportToExcelByteArray<T>(IEnumerable<T> data)
        {
            var memoryStream = new MemoryStream();
            ExcelPackage.LicenseContext = LicenseContext.Commercial;

            using (var excelPackage = new ExcelPackage(memoryStream))
            {
                //To get all members without having EpplusIgnore attribute added
                MemberInfo[] membersToInclude = typeof(T)
                .GetProperties(BindingFlags.Instance | BindingFlags.Public)
                .Where(p => !Attribute.IsDefined(p, typeof(EpplusIgnore)))
                .ToArray();

                var worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
                worksheet.Cells["A1"].LoadFromCollection(data, true, TableStyles.None, BindingFlags.Instance | BindingFlags.Public,
                membersToInclude);
                worksheet.Cells["A1:AN1"].Style.Font.Bold = true;
                worksheet.DefaultColWidth = 20;

                return excelPackage.GetAsByteArray();
            }
        }
    }

Then call this method like

public ActionResult ExportToExcel()
        {
            var result= // Here pass your data (list)
            byte[] fileResult = EPPlusHelper.GetExportToExcelByteArray(result);

            return File(fileResult, "application/vnd.ms-excel", "FileName.xlsx");
        }

Imbed