Search code examples
phptimezonephpexcel

PHPExcel gets wrong timezone even after setting date_default_timezone_set

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.

Solution

  • 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.