Excel time format using Epplus

Question

I have an excel document which I try to import to my system. (.net core 2.2 and EPPlus v4.5.3.1)

The excel data like 09:57:32 and Custom Cell format [hh]:mm:ss

private TimeSpan? GetRequiredTimeFromRowOrNull(ExcelWorksheet worksheet, int row, int column, string columnName, StringBuilder exceptionMessage)
    {
        worksheet.Cells[row, column].Style.Numberformat.Format = "hh:mm:ss";
        var cellValue = TimeSpan.Parse(worksheet.Cells[row, column].Value.ToString());

        if (cellValue.ToString() != null && !string.IsNullOrWhiteSpace(cellValue.ToString()))
        {
            return cellValue;
        }

        exceptionMessage.Append(GetLocalizedExceptionMessagePart(columnName));
        return null;
    }

"worksheet.Cells[row, column].Value" comes 0.414953703703704

and also

"worksheet.Cells[row, column].Text" comes 09:12:32

How can I get exact value?

Answer 1

Excel stores dates and time in decimal values. The whole-number part is the day and the decimal part is the time.

So, to get into a C# DateTime, use the OLE Automation converter:

try
{
    var val = (double)worksheet.Cells[row, column].Value;
    var dt = DateTime.FromOADate(val);
    var cellValue = dt.TimeOfDay;
}
catch (Exception)
{
    //log conversion error
}

Will want to put a Try.Catch around the cast JIC the cell does not have a number in it.