Use Display Name attribute for column header in exported Excel data

Question

I am trying to use a DisplayName attribute to modify the column headers in data exported to an Excel file. I want "My Name" but get "MyName". The data is data returned from a query, converted to a List, then to a DataTable, then exported to Excel.
What else can I try?

There is some discussion here saying that functionality for this was being added.

Using .NET Framework 4

    public class MyExcelViewModel
    {
            private string myName;
            //[System.ComponentModel.DisplayName("Mine Name")] // no effect
            //[System.ComponentModel.DataAnnotations("My Name")] // doesn't compile
            //[System.ComponentModel.DisplayNameAttribute("My Name")] // no effect
            [Display(Name = "My Name")] // no effect
            public string MyName { get; set; }
    }

    public class MyController : Controller
        // initialize exportList
        IList<MyExcelViewModel> exportList = new List<MyExcelViewModel>(fromQueryList.Count);
        foreach (var value in fromQueryList)
        {
            exportList.Add(new MyExcelViewModel());
        }

        DataTable dataTable = ConvertListToDataTable(exportList); // convert to DataTable
        Export_EPPlus(dataTable); // export

        private void Export_EPPlus(DataTable dataTable)
        {
                 using (ExcelPackage package = new ExcelPackage())
                 {
                     ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(" Results");

                     // Load the datatable into the sheet, starting from cell A1.
                     worksheet.Cells["A1"].LoadFromDataTable(dataTable, true);

                     worksheet.View.FreezePanes(2, 1); // freeze first row & column
                     worksheet.Row(1).Style.Font.Bold = true;

                     worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns(); // autofit column width

                     for (int rowNumber = worksheet.Dimension.Start.Row; rowNumber <= worksheet.Dimension.End.Row; rowNumber++)
                     {
                         int rowIndex = rowNumber % 2; // even => 0, odd => 1
                         ExcelRow excelRow = worksheet.Row(rowNumber);
                         ExcelFill excelFillRow = excelRow.Style.Fill;
                         excelFillRow.PatternType = ExcelFillStyle.Solid;
                         switch (rowIndex)
                         {
                             case 0:
                                 excelFillRow.BackgroundColor.SetColor(System.Drawing.Color.White);
                                 break;
                             case 1:
                                 excelFillRow.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
                                 break;
                         }
                     }

                     try
                     {
                         // Write to client
                         Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                         Response.AddHeader("content-disposition", "attachment;  filename="Results-EPPlus.xlsx");
                         Response.BinaryWrite(package.GetAsByteArray());

                     }
                     catch (Exception ex)
                     {
                         System.Diagnostics.Trace.WriteLine("MyController.Export_EPPlus(): exception: " + ex);
                     }
                 }
             }

        public static DataTable ConvertListToDataTable<T>(this IList<T> data)
        {
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                table.Columns.Add(prop.Name, prop.PropertyType);
            }
            object[] values = new object[props.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }
                table.Rows.Add(values);
            }
            return table;
        }
}

Answer 1

The DisplayName attribute thing only works in 4.0, not with 3.1 unfortunately. The only way to do it in version 3.1 is to manually set the Caption of each DataColumn in the DataTable record, as seen here.

I wrote a blog post on that and other new 4.0 features in practice that can be found here.

As proof, here is the relevant code from EPPlus 3.1 that prints the column headers in the LoadFromDataTable method:

if (PrintHeaders)
{
  foreach (DataColumn dataColumn in (InternalDataCollectionBase) Table.Columns)
    this._worksheet.Cell(row, num++).Value = (object) dataColumn.Caption;
  ++row;
  num = this._fromCol;
}