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...

score 3 · Accepted Answer · answered Jan 20 '15 at 15:30

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();

    }
}

something like this would have been best : `worksheet.Cells["A2"].Value.ToString( worksheet.Cells["A2"].Style.Numberformat.Format)` I mean something similar to this — harishr, Jan 20 '15 at 18:17
@HarishR Do you mean on that second to last line (the one before the save)? I believe that would generate a design time error. Value is an object type so it cant apply a format string without casting first. — Ernie S, Jan 20 '15 at 19:53
i know it would generate error, what I saying is, something like that would have been great :) and is missing from the EP-Plus library, really not able to understand why the framework is missing something basic like give-formatted-value. anyway thanks for the answer — harishr, Jan 21 '15 at 04:39
the problem in my case is, the excel string value (supposed to be date) is not always guranted to be a valid date, so I might need more validation checking around it — harishr, Jan 21 '15 at 04:40
@HarishR I see. Ya, that would be nice to see, like may things. But I suppose there is only so much they can do - generating excel files without excel is pretty hard. For validation, yes, definitely need some here, maybe a try-catch around the casting. Good luck with it! :) — Ernie S, Jan 21 '15 at 11:16

Eddy Jawed · Answer 2 · 2015-11-24T13:03:42.267

I also found a less complex method. If you change the 'NumberFormatLocal' instead of 'NumberFormat' property to "dd/mm/yyyy" they will import as dates instead of numbers.

So

Sub ChangeExcelColumnFormat()

    Dim ExcelApp As Excel.Application
    Dim ExcelWB As Excel.Workbook
    Dim ExcelWS As Excel.Worksheet
    Dim formatRange As Excel.Range

    Dim strFile As String = "C:\Test.xlsx"
    Dim strSheetname As String = "Sheet1"


    ExcelApp = New Excel.Application
    ExcelWB = ExcelApp.Workbooks.Open(strFile)

        strColSelect = "A:A"
        strFormat = "dd/mm/yyyy"

        formatRange = ExcelWS.Range(strColSelect)
        formatRange.NumberFormatLocal = strFormat

    ExcelWB.Save()
    ExcelWB.Close()
    ExcelApp.Quit()

    ExcelWS = Nothing
    ExcelWB = Nothing
    ExcelApp = Nothing

End Sub

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

2 Answers2