EPPlus : date column returning int rather than the actual displayed text value

Question

I am using EPPlus to read excel data. I need to display the excel cells as-is in the grid to the users.

while reading date column EPPlus is giving the OADate int value for the date.

Is there a way I can read the value as string, which users normally see when they open the excel. i.e. the value after applying the excel formats.

There does not seem to be any function on the CELL class which provides valueAsString or valueAsDisplayed, etc...

Answer 1

If the dates are stored in excel as doubles (or a whole number if there is no time component) with formatting applied, which would be the "correct" way, you have recreate the date in code and reapply the date format applied in Excel. The only wrinkle is excel's formatting is slightly different then .net especially when it comes to case sensitivity so you should put a check in there to make sure MMM is not mmm (which would give you minutes in .net). So something like this works:

[TestMethod]
public void OADate_Test()
{
    //http://stackoverflow.com/questions/28046069/epplus-date-column-returning-int-rather-than-the-actual-displayed-text-value
    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();


    using (var package = new ExcelPackage(existingFile))
    {
        //.NET is case sensive so MMM must be capital but Excel might not be
        const string FORMATDATE = "dd-MMM-yyyy";
        var mydate = new DateTime(2015, 1, 1);
        var datedouble = mydate.ToOADate();

        var worksheet = package.Workbook.Worksheets.Add("Newsheet");

        worksheet.Cells["A1"].Value = "As Date";

        worksheet.Cells["A2"].Value = datedouble;
        worksheet.Cells["A2"].Style.Numberformat.Format = FORMATDATE;

        package.Save();
    }

    using (var package = new ExcelPackage(existingFile))
    {
        var worksheet = package.Workbook.Worksheets["Newsheet"];

        worksheet.Cells["B1"].Value = "As String";

        var datedouble = (double) worksheet.Cells["A2"].Value;
        worksheet.Cells["B2"].Value = DateTime.FromOADate(datedouble).ToString(worksheet.Cells["A2"].Style.Numberformat.Format);

        package.Save();

    }
}