PHPExcel gets wrong timezone even after setting date_default_timezone_set

Question

I'm using http://phpexcel.codeplex.com in one of my project, and I have come into an issue. I want to write the time() value inside a cell, which I am doing with :

function writeTimeLine($objActiveSheet, &$lineNumber, $timeStart, $timeEnd, $duration, $category, $client, $date, $comment)
{
    $objActiveSheet->setCellValue('A'.$lineNumber, PHPExcel_Shared_Date::PHPToExcel( $timeStart ));
    $objActiveSheet->getStyle('A'.$lineNumber)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
}

The $objActiveSheet refers to $objPHPExcel->getActiveSheet()

And the result in Excel is:

20:27:39

When in real on the computer I'm testing it it's 16:27:39

So it's an issue with the timezone (I'm living in Eastern America which is -4). However, I'm including PHPExcel files after setting the default timezone with

date_default_timezone_set('America/New_York');

And even with an echo of the time() I do see the correct hour (16:27:39).

Is it a bug of PHPExcel or am I doing something wrong here?

Thanks for your help.

Answer 1

Instead of changing the PHPExcel lib, you better add the time difference:

$timeStart + date('Z', $timeStart)

For example like that:

date_default_timezone_set('America/New_York');
function writeTimeLine($objActiveSheet, &$lineNumber, $timeStart, $timeEnd, $duration, $category, $client, $date, $comment)
{
    $objActiveSheet->setCellValue('A'.$lineNumber, PHPExcel_Shared_Date::PHPToExcel( $timeStart + date('Z', $timeStart) ));
    $objActiveSheet->getStyle('A'.$lineNumber)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
}

Answer 2

Granted rather old, but wanted to supply another solution.

If you're not wanting to edit the library source files, since PHPExcel_Shared_Date::PHPToExcel (1.7.8+) accepts either an integer timestamp or DateTime object, you can easily just convert the integer timestamp to a DateTime object.

Then PHPToExcel will use your DateTime object's timezone when calling $dateValue->format().

The DateTime timezone is only calculated on construct/modify, not on format.

PHP 5 <= 5.2

$timeStart = new DateTime(date("c", $timeStart));
PHPExcel_Shared_Date::PHPToExcel($timeStart);

PHP 5 >= 5.3

$date = new DateTime();
PHPExcel_Shared_Date::PHPToExcel($date->setTimestamp($timeStart));

Note

The DateTime construct uses the UTC TimeZone when using '@' . $timestamp.

setTimestamp uses the environments current timezone.

However setTimestamp was not made available until 5.3

When developing turnkey solutions, It's generally best practice to try and avoid anything that changes or attempts to determine the environment in which it is deployed. A good example I often have to fix in other applications is date('Y-m-d', PHP_INT_MAX);. Which will work fine on 32-bit system, but not 64-bit (except Windows). Producing unexpected results between different environments.