CODEWITHSUNDEEP
phpphpexcel
Psyche
Psyche

Reputation: 8773

Set correct reading format for a cell with PHPExcel

I'm using PHPExcel to read an Excel 2007 document. In this document there's a column with "Time" formatting and it displays data correctly in Excel. When I try to parse this column I get some weird values. Any idea how can I fix this?

Thank you.

Upvotes: 2

Views: 5495

Answers (3)

Automate Everything
Automate Everything

Reputation: 126

The value for Time as it is stored in Excel is actually the fraction of the day, so 0.2 is 24 hours (or 1440 minutes or 86400 seconds) times 0.2. You can calculate the time of day based on that information, and then calculating from the beginning of the day. It makes it a little more usable than a formatted time, but a lot less readable.

Upvotes: 2

Mark Baker
Mark Baker

Reputation: 212402

Weird values?!? You mean you get a number instead of a human-readable date string.... it always helps to describe things accurately where possible, and weird isn't really an accurate description.

If you simply get the value from the cell, you'll be reading a raw timestamp value (MS Excel holds date and time values as a timestamp; like a Unix timestamp value in PHP, except that Excel's timestamp is the number of days since 1st January 1900 (or 1st January 1904, depending on which calendar it is configured to use).

MS Excel uses number format masking to display this timestamp as a human-readable date/time string.

You can use the getFormattedValue() method rather than simple getValue() to retrieve this as a formatted date/time (getFormattedValue() applies any number format mask for the cell to the value). Not ethat if you've loaded the file with readDataOnly set to TRUE, then the number format masks aren't loaded, so PHPExcel cannot identify whetehr a cell contains a date or not.

Or, as James has suggested in his answer, you can convert the raw timestamp to a formatted value manually by applying number formatting with your own format mask

A third alternative is that you can use PHPExcxel's built-in date handling functions to convert this Excel timestamp value to a PHP/unix timestamp or to a PHP DateTime object (PHPExcel_Shared_Date::ExcelToPHP() and PHPExcel_Shared_Date::ExcelToPHPObject() respectively), that you can then display using PHP's own date handling functions

Upvotes: 2

james_tookey
james_tookey

Reputation: 905

When parsing, you need to specify the format of the column. e.g.

$my_cell = $objWorksheet->getCellByColumnAndRow(1, 1);
$my_cell_value = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'hh:mm:ss');
print $my_cell_value;

Upvotes: 9

Related Questions