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

Just found the problem: PHPExcel translates to UTC time inside: PHPExcel/Shared/Date.php

The function PHPToExcel change temporarily the timezone to UTC, and then go back to the default timezone.

The solution is simple but requires to change that file. Simply copy paste the PHPToExcel function, name it PHPToExcelWithoutUTC (or any name you want) and comment the following lines:

//$saveTimeZone = date_default_timezone_get();
//date_default_timezone_set('UTC');

//date_default_timezone_set($saveTimeZone);

And it works.

EDIT: It seems it's intended to always use UTC time by default. I'm not deleting this thread because it might be useful for those who want the user timezone.

Answer 3

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.

Answer 4

You may look up to the source,

ExcelToPHP() and PHPToExcel()

https://github.com/PHPOffice/PHPExcel/blob/1.8/Classes/PHPExcel/Shared/Date.php#L115

also provide parameters so that you may convert the timezone from UTC to yours

    /**
 *    Convert a date from Excel to PHP
 *
 *    @param        integer        $dateValue            Excel date/time value
 *    @param        boolean        $adjustToTimezone    Flag indicating whether $dateValue should be treated as
 *                                                    a UST timestamp, or adjusted to UST
 *    @param        string         $timezone            The timezone for finding the adjustment from UST
 *    @return       integer        PHP serialized date/time
 */
public static function ExcelToPHP($dateValue = 0, $adjustToTimezone = false, $timezone = null)