Set format of cells produced by LoadFromCollection (epplus) using data annotation

Question

I am using eeplus to create an excel spreadsheet, like this

using (var pck = new ExcelPackage())
{
  var ws = pck.Workbook.Worksheets.Add("Customers");
  ws.Cells["A1"].LoadFromCollection(customers, PrintHeaders: true);

  var ms = new System.IO.MemoryStream();
  pck.SaveAs(ms);

  ms.WriteTo(Response.OutputStream);
}

The customer class has properties like

[DisplayName("Customer creation date")]
public DateTime Created { get; set; }

DisplayName seems to get honored, so the topmost line will read Customer creation date but the cell contents show up as 43257,41667.

What I would really like to have is cells that has the format 2018-04-05.

Can I do that will data annotations? I tried both

[DisplayName("Customer creation date")]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}")]
public DateTime Created { get; set; }

and

[DisplayName("Customer creation date")]
[DataType(DataType.Date)]
public DateTime Created { get; set; }

but the cell contents remains the same.

score 5 · Accepted Answer · answered Jun 05 '18 at 10:12

No, EPPlus doesnot format your data according to data annotations. It formats date as integers, so you should specify the column you wish to format as

ws.Column(colPosition+1).Style.Number.Format="yyyy-mm-dd";

You can find details here: https://github.com/JanKallman/EPPlus/wiki/Formatting-and-styling

https://codereview.stackexchange.com/questions/139569/ensuring-specific-columns-in-an-excelworksheet-format-as-shortdate

score 0 · Answer 2 · answered Feb 27 '21 at 20:01

EPPlus always changed column name while updating into excel based upon DisplayName Attribute else if there is no DisplayName Attribute is set, then it will Find "_" (underscore) character & replace it with " " (Space) Character in the column name, Due to which we cannot easily find PropertyInfo with help of column name to format the column as per our need.

Here is the simple & quickest solution to format column based upon indexing the PropertyInfo

PropertyInfo[] props = typeof(T).GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
for (int i = 0; i < props.Length; i++)
{
    Type t = props[i].PropertyType;
    if (t == typeof(DateTime) || t == typeof(DateTime?))
        ws.Column(i + 1).Style.Numberformat.Format = "dd-MMM-yyyy HH:mm:ss";
    else if (t == typeof(TimeSpan) || t == typeof(TimeSpan?))
        ws.Column(i + 1).Style.Numberformat.Format = "HH:mm:ss";
}

I have another solution if you need to format columns based upon column names.

void ApplyDateTimeFormatting<T>(ExcelWorksheet ws, IEnumerable<T> data)
{
    if (data.Count() == 0) 
        return;
    Type type = data.First().GetType();
    for (int c = 1; c <= toColumns; c++)
    {
        string column = ws.Cells[1, c].Text;
        var t = type.GetPropertyWithDisplayName<T>(column).PropertyType;
        if (t == typeof(DateTime) || t == typeof(DateTime?))
            ws.Column(c).Style.Numberformat.Format = "dd-MMM-yyyy HH:mm:ss";
        else if (t == typeof(TimeSpan) || t == typeof(TimeSpan?))                
            ws.Column(c).Style.Numberformat.Format = "HH:mm:ss";
    }
}

PropertyInfo GetPropertyFromDisplayName(Type type, string DisplayName)
{
    MemberInfo[] members = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (var member in members)
    {
        DisplayNameAttribute displayNameAttribute = member
            .GetCustomAttributes(typeof(DisplayNameAttribute), inherit: false)
            .FirstOrDefault() as DisplayNameAttribute;
        string text = ((displayNameAttribute == null) ? member.Name.Replace('_', ' ') :
            displayNameAttribute.DisplayName);
        if (text == DisplayName)
            return type.GetProperty(member.Name);
    }
    return null;
}

Rafael Parenza · Answer 3 · 2022-05-30T18:35:33.833

I solved it as follows, so I just load the model and change as per my model if it is int or datetime

var li = typeof(Model).GetProperties().ToArray();
using (var package = new ExcelPackage(stream))
{
var workSheet = package.Workbook.Worksheets.Add("Sheet1");
var i = 0;
foreach (var c in li)
{
  i++;
  if(c.PropertyType.Name == typeof(DateTime).Name || c.PropertyType.Name == typeof(DateTime?).Name)
                            workSheet.Column(i).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern; ;
    
 if (c.PropertyType.Name == typeof(int).Name || c.PropertyType.Name == typeof(int?).Name)
                            workSheet.Column(i).Style.Numberformat.Format = "0";                    
}

}

Set format of cells produced by LoadFromCollection (epplus) using data annotation

3 Answers3