C＃通用Excel导出器使用反射 [英] C# generic Excel exporter using reflection

问题描述

我工作在C＃泛型的Excel出口国。我的观点是把任何类型的集合，并指定其类的属性应使用lambda表达式导出，我已经做到了。我挣扎的问题是，当我在我的课有复杂属性，导出的属性值是Namespace.ClassName（例如MyApp.ViewModels.MyViewModel）。

I'm working on generic Excel exporter in C#. My point is to put a collection of any type and specify which properties of the class should be exported using lambda expressions and I have done that. The problem I'm struggling with is that when I have complex property in my class, the property value exported is "Namespace.ClassName" (e.g. "MyApp.ViewModels.MyViewModel").

下面是我的代码：

Excel的出口类：

Excel exporter class:

    public class ExcelExporter
    {
        public void ExportToExcel<T>(IEnumerable<T> data, params Expression<Func<T, object>>[] columns)
        {
            DataTable dataTable = this.ConvertToDataTable(data, columns);              
            //Export the dataTable object to Excel using some library...
        }

        private DataTable ConvertToDataTable<T>(IEnumerable<T> data, params Expression<Func<T, object>>[] columnsFunc)
        {
            DataTable table = new DataTable();

            foreach (var column in columnsFunc)
            {
                string columnName = ReflectionUtility.GetPropertyDisplayName<T>(column);
                table.Columns.Add(columnName);
            }

            foreach (T obj in data)
            {
                DataRow row = table.NewRow();

                for (int i = 0; i < table.Columns.Count; i++)
                {
                    row[table.Columns[i].ColumnName] = ReflectionUtility.GetPropertyValue<T>(obj, columnsFunc[i]);
                }

                table.Rows.Add(row);
            }

            return table;

        }

ReflectionUtility类 - 提供了获取属性名称和值。
GetPropertyDisplayName方法读取[显示名称]从属性的属性值，并将其设置为在Excel文档中的标题栏（原因是我想喜欢显示'名'姓'属性。）

ReflectionUtility class - provides methods to get property name and value. "GetPropertyDisplayName" method reads the [DisplayName] attribute value from the property and sets it as a header column in the Excel document (the reason is that I'd like property like 'FirstName' to be displayed 'First Name').

public static class ReflectionUtility
    {
        /// <summary>
        /// Returns the display name of a property (set by using [DisplayName] attribute).
        /// If [DisplayName] is not provided, returns the actual property name.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static string GetPropertyDisplayName<T>(Expression<Func<T, object>> expression)
        {
            var memberExpression = expression.Body as MemberExpression;

            if (memberExpression == null)
            {
                memberExpression = ((UnaryExpression)expression.Body).Operand as MemberExpression;
            }

            var property = memberExpression.Member as PropertyInfo;

            if (property != null)
            {
                var displayNameAttribute = property.GetCustomAttribute(typeof(DisplayNameAttribute), false) as DisplayNameAttribute;

                if (displayNameAttribute != null)
                {
                    return displayNameAttribute.DisplayName;
                }
            }

            return memberExpression.Member.Name;
        }

        public static object GetPropertyValue<T>(T obj, Expression<Func<T, object>> expression)
        {
            var memberExpression = expression.Body as MemberExpression;

            if (memberExpression == null)
            {
                memberExpression = ((UnaryExpression)expression.Body).Operand as MemberExpression;
            }

            var property = memberExpression.Member as PropertyInfo;

            if (property != null)
            {
                // Note: If we want to export complex object, the object's value is something like "Namespace.ClassName", which is
                // inappropriate for displaying. So we must specify additionally which property from the complex object should be visualized...

                var value = property.GetValue(obj);

                return value;
            }

            return null;
        }

我如何消费ExcelExporter类：

How I consume ExcelExporter class:

ExcelExporter excelExporter = new ExcelExporter();

    excelExporter.ExportToExcel<MyViewModel>(genericListToExport,
        p => p.StringProperty1,
        p => p.StringProperty2,
        p => p.ComplexProperty.IntProperty1);

如何传递ComplexProperty.IntProperty1并获得它的价值和处理的情况下，当ComplexProperty为null ，所以我不会得到一个NullReferenceException

How can I pass ComplexProperty.IntProperty1 and get the value of it and handle the case when ComplexProperty is null, so I won't get a NullReferenceException.

下面是一个测试场景输出到Excel：

Here is a test scenario Excel output:

任何帮助表示赞赏！

推荐答案

EPPlus 可以一个IEnumerable加载到工作表。这意味着你可以加载 Enumerable.Select 调用的结果将列限制为只有那些你想要的，例如：

EPPlus can load an IEnumerable to a worksheet. This means that you can load the results of an Enumerable.Select call to restrict the columns to only those you want, eg:

var products=allProducts.Where(prod=>prod.CustomerId=14)
                        .Select(new {prod.Name,prod.Category});
sheet.Cells["A1"].LoadFromCollection(products);

您可以使用它，如果你愿意，也可以检查它是如何在的 ExcelRangeBase.LoadFromCollection 。

You can use it if you want, or you can check how it's done in ExcelRangeBase.LoadFromCollection.

展望在代码中，EPPlus查找显示名称和说明属性生成标题文本，回落至前成员名称

Looking at the code, EPPlus looks for the DisplayName and Description attributes to generate the header text, before falling back to the member's name

这篇关于C＃通用Excel导出器使用反射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！