NPOI DateCell is 1 second off from time in Excel

Question

I have an excel document with times listed as "02:30" ... however, when iterating through them using NPOI, the DateCellValue flips to "02:29:59" ... This first happens on 1/1/2019 (which correctly stores as "02:30") but then on 1/2/2019 it flips to "02:29:59" ... Does anyone know how to have it simply get the value of the cell without trying to do any voodoo to it? It's obviously taking into account perhaps a leap second, or something? It is, however, clear as day in Excel as "02:30", and at my breakpoint with:

[Model].DepartureDttm = row.GetCell(j).DateCellValue [1/2/2019 2:29:59 AM]

Answer 1

You're not the only one that have faced this problem. Here's a good answer.

You can use the DateUtil.GetJavaDate coming with NPOI to resolve this issue . You can create this basic extension method:

public static class Extensions
{
    public static DateTime DateCellValueRounded(this ICell cell)
    {
        return DateUtil.GetJavaDate(cell.NumericCellValue, false, TimeZone.CurrentTimeZone, true);
    }
}

Then use it like this:

DateTime date = row.GetCell(index).DateCellValueRounded;

---

GetJavaDate signature (the last parameter set to true does the job):

public static DateTime GetJavaDate(double date, bool use1904windowing, TimeZone tz, bool roundSeconds);