Get full date and time value from a cell in Excel with NPOI

Question

I am trying to get data from Excel files (the old xls ones) and in particular, one column is formatted as date though it should be formatted as time.

When I read the file with NPOI, I get only the date part of the cell; the time part is fixed to 12PM.

When I open the file in Excel and click the cell, I can see that the time is populated.

For example, in Excel, the cell's "formula value" is:

4/23/2020  3:00:00 PM

And the cell is formatted as:

4/23/2020

When I read the cell with NPOI, I get:

var value = cell.NumericCellValue;// value = 43944.5
var dateValue = DateUtil.GetJavaDate(cell.NumericCellValue);// dateValue = {4/23/2020 12:00:00 PM}
var formattedValue = _formatter.FormatCellValue(cell);// formattedValue = "4/23/20"
var formula = cell.CellFormula;// throws exception "Cannot get a formula value from a numeric formula cell"

I read the code for DateUtil.GetJavaDate and 43944.5 does indeed convert to 4/23/2020 12:00:00 PM which means that NumericCellValue is the value with formatting applied already.

So my question is: how can I get the 4/23/2020 3:00:00 PM "formula value" for that cell using NPOI?

If you have suggestions with other libraries, I'm happy to try them too.

Answer 1

Try using the DateCellValue property of the cell:

if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
{
    DateTime date = cell.DateCellValue;
    Debug.WriteLine(date.ToString("MM/dd/yyyy h:mm:ss tt"));
}