无法使用PHP中的Spreadsheet_Excel_Reader从电子表格中读取时间值

Question

I am trying to import an excel file into my database in PHP. The file contains a column which has time values in the format H:MM. When I try to read this column, I get garbage values like 0.416666666667.

My code goes here :

    $data = new Spreadsheet_Excel_Reader();
    $data->setOutputEncoding('CP1252');             
    $data->setUTFEncoder('mb');
    $data->read($filePath);
    echo $data->sheets[0]['cells'][$i][$j];

Am I missing something? The value in the column is 10:00 but all I get is 0.416666666667.

Please help.

Answer 1

The value in the MS Excel cell is a serialized timestamp, it's formatted as a human readable date/time by the format mask, which SER isn't particularly good at handling

$time =  0.416666666667 * 86400;
$hours = round($time / 3600);
$minutes = round($time / 60) - ($hours * 60);
$seconds = round($time) - ($hours * 3600) - ($minutes * 60);

echo sprintf('%02d:%02d:%02d', $hours, $minutes, $seconds);