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?

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:

Answer 2

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

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/

Answer 4

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