在EPPlus中调用LoadFromCollection时忽略属性 [英] Ignoring properties when calling LoadFromCollection in EPPlus

问题描述

我正在使用以下代码生成一个Excel文件：

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

工作像一个对待，但是..我想忽略我的类集合中的几个属性，所以它匹配我的模板。我知道， LoadFromCollection 将根据类的公共属性在Excel文件中生成列，但是当我使用Entity Framework加载类时，如果我标记字段作为私人的，然后，EF投诉 - 主要是因为我不想显示的一个字段是Key。

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.

我试图标记我没有的属性想要使用 [XmlIgnore] ，没有用。有没有办法做到这一点，把整个集合加载到一个数据集或一些这样的方式，并修剪这些列？或者转换为基类，而不需要我不需要的属性。

I have 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?

推荐答案

是的，EPPlus提供 .LoadFromCollection< T>（）方法与您想要包含的属性的 MemberInfo [] 参数。

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 { }

然后我们可以写一个小的扩展方法，首先找到没有 [EpplusIgnore] 属性的属性的所有 MemberInfo 对象，然后返回结果在EPPlus dll中的 .LoadFromCollection 方法的正确超载。

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.

如下所示：

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

}

所以，例如，使用它当将 Person collection导出到excel时，这将忽略 .Key 属性：

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

给我们输出期望：

这篇关于在EPPlus中调用LoadFromCollection时忽略属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！