EPPlus date cell data type not working

Question

I have code that takes IEnumerable and generates an Excel document from it. Objects in IEnumerable have a date field, and I want them to be formatted as dates in Excel. However, when you look at it in Excel, the dates do not seem to be of the date data type until you double-click on the cell and then press enter. When you do this, the values ​​move to the right, and the table filters work correctly. If you do not double-click and enter a thing, the value remains justified, and the table filters treat it as text instead of a date. I need Excel to treat it as a date out of the box.

Here is 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();
}

, ws.Cells[currentrow, i + 1].Style.Numberformat.Format = "m/d/yyyy"; , , , , . Excel " ", , , . " " " a z".

Answer 1

ToString(),

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

, DataFormat. , "".

"if", , , excel, , dt?