Set columns header text

Jan 8, 2013 at 6:36 AM

I use next entity for employee data:

public class Employee
{
    [Display(Name = "Employee Id")]
    public int EMPLOYEE_ID { set; get; }
        
    [Display(Name = "Nam")]
    public string USERNAME { set; get; }
}

Next code works fine:

ws.Cells["A1"].LoadFromCollection<Employee>(EmpList, true);

I have a one problem - headers of columns. I don't want to see "EMPLOYEE_ID", a want to see "Employee Id".

Do I have a way to set custom text for headers?

Jan 9, 2013 at 5:52 AM

I wrote a extension method. In my environment it works fine

public static ExcelRangeBase LoadFromCollectionWithHeaders<T>(this ExcelRange excelRange, IEnumerable<T> list)
{
    excelRange.LoadFromCollection<T>(list, true);

    int Row = 1;
    int ColumnsCount = excelRange.Worksheet.Cells.Count() / list.Count();
    for (int Column = 1; Column <= ColumnsCount; Column++)
    {
        string IncorrectHeader = (((OfficeOpenXml.ExcelRangeBase)(excelRange.Worksheet.Cells[Row, Column]))).Text;

        PropertyInfo[] Properties = typeof(T).GetProperties();
        foreach (PropertyInfo Property in Properties)
        {
            if (IncorrectHeader == Property.Name.Replace('_', ' '))
            {
                object[] DisplayAttributes = Property.GetCustomAttributes(typeof(DisplayAttribute), true);

                if (DisplayAttributes.Length == 1)
                {
                    (((OfficeOpenXml.ExcelRangeBase)(excelRange.Worksheet.Cells[Row, Column]))).Value = ((DisplayAttribute)(DisplayAttributes[0])).Name;
                            break;
                }
                else
                {
                    continue;
                }
            }
        }
    }

    return excelRange;
}

Apr 14, 2014 at 4:08 PM
I recently forked and the pull request was recently approved for support of the DisplayNameAttribute, so you should be able to grab latest source and compile yourself for the following code to work.
public class Employee
{
    [DisplayName("Employee Id")]
    public int EMPLOYEE_ID { set; get; }
        
    [DisplayName("Name")]
    public string USERNAME { set; get; }
}
Dec 18, 2014 at 2:31 PM
Edited Dec 19, 2014 at 8:15 PM
Now in version 4, e.g.:
worksheet.Cells["A1"].LoadFromCollection(myList, true);
will use DisplayName attribute for column headers.