EPPlus Date Cell Datatype Not Working

Question

I have some code that accepts an IEnumerable and generates an Excel document from it. The objects in the IEnumerable have a date field, and I want those to be formatted as dates in Excel. However, when you look at it in Excel, the dates don't seem to be of the "date" data type until you double click in the cell, then press enter. When you do that, the values moves to right justified, and table filters work correctly. If you don't do the double click and enter thing, the value is left justified and table filters treat it as text instead of a date. I need Excel to treat it as a date out of the box.

Here's my code.

/// <summary>
/// Converts any IEnumerable to an Excel Document. Objects appear in the order declared in the class.
/// </summary>
/// <typeparam name="T">Any object.</typeparam>
/// <param name="objects">List of objects to generate document from.</param>
/// <returns>Byte array representing a .xlsx file.</returns>
public static byte[] ToExcelDocument<T>(this IEnumerable<T> objects)
{
    int currentrow = 1;
    Type type = typeof(T);
    List<PropertyInfo> propertyinfos = type.GetProperties().ToList();
    int numcolumns = propertyinfos.Count;
    ExcelPackage pck = new ExcelPackage();
    ExcelWorksheet ws = pck.Workbook.Worksheets.Add(type.Name + "(s)");

    for (int i = 0; i < numcolumns; i++)
    {
        ws.Cells[currentrow, i + 1].Value = propertyinfos[i].Name;
    }
    currentrow++;
    foreach (object o in objects)
    {
        for (int i = 0; i < propertyinfos.Count; i++)
        {
            if (o.GetType().GetProperty(propertyinfos[i].Name).PropertyType == typeof(DateTime))
            {
                ws.Cells[currentrow, i + 1].Style.Numberformat.Format = "m/d/yyyy";
                DateTime dt = (DateTime)(o.GetType().GetProperty(propertyinfos[i].Name).GetValue(o, null));
            }
            else if (o.GetType().GetProperty(propertyinfos[i].Name).PropertyType == typeof(DateTime?))
            {
                DateTime? dt = (DateTime?)(o.GetType().GetProperty(propertyinfos[i].Name).GetValue(o, null));
                if (dt.HasValue)
                {
                    ws.Cells[currentrow, i + 1].Style.Numberformat.Format = "m/d/yyyy";
                    ws.Cells[currentrow, i + 1].Value = dt.Value.ToString("MM/dd/yyyy");
                }
            }
            else
            {
                ws.Cells[currentrow, i + 1].Value = o.GetType().GetProperty(propertyinfos[i].Name).GetValue(o, null);
            }
        }
        currentrow++;
    }

    ExcelAddressBase eab = new ExcelAddressBase(1, 1, currentrow - 1, numcolumns);
    ws.Tables.Add(eab, "MyTable");
    ws.Tables["MyTable"].TableStyle = OfficeOpenXml.Table.TableStyles.Medium15;
    ws.Cells.AutoFitColumns();
    MemoryStream ms = new MemoryStream();
    pck.SaveAs(ms);
    return ms.ToArray();
}

I would think that ws.Cells[currentrow, i + 1].Style.Numberformat.Format = "m/d/yyyy"; would be enough to accomplish what I want, but it doesn't appear to be working. When you right click the cell in Excel and go to Format Cells, it shows that date is selected, yet it doesn't seem to work for table filters. Instead of "sort oldest to newest" I get "sort from a to z".

Answer 1

Dont use a ToString(),

ws.Cells[currentrow, i + 1].Value = dt.Value; // dont do this -> .ToString("MM/dd/yyyy");

It cannot determine what the DataFormat is then. I had a similiar issue with decimal numbers, i was trying to "preformat" them.

Also in your first 'if', where you check if the value is a date, shouldnt the returned value be put into the excel sheet, instead of into a new variable called dt?