C# generic Excel exporter using reflection

Question

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").

Here is my code:

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

How I consume ExcelExporter class:

ExcelExporter excelExporter = new ExcelExporter();

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

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.

Here is a test scenario Excel output:

Any help is appreciated!

Answer 1

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

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

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