Aug 30, 2012 at 9:26 AM
Edited Aug 30, 2012 at 9:26 AM
I'm having problems when converting dates stored as a Unix timestamp into Excel dates for inserting into a spreadsheet - from April onwards they are all off by one hour (e.g. 01/06/2012 00:00 becomes 31/05/2012 23:00).
I've hardcoded the timestamp here, but in normal use it comes from a INT UNSIGNED column in a MySQL database:
$spreadsheet = new PHPExcel();
$spreadsheet->setActiveSheetIndex(0);$worksheet = $spreadsheet->getActiveSheet();
$php_date = 1338505200;
print date('d/m/Y', $php_date) . "\n";
$excel_date = PHPExcel_Shared_Date::PHPToExcel($php_date);
print $excel_date . "\n";
$worksheet->SetCellValueByColumnAndRow(0, 1, $excel_date);$worksheet->getStyleByColumnAndRow(0, 1)->getNumberFormat()->setFormatCode('dd/mm/yyyy');
$writer = new PHPExcel_Writer_Excel2007($spreadsheet);$writer->save('date-test.xlsx');
When I execute this code, it prints:
And A1 contains the value: 31/05/2012 23:00:00 (displayed as 31/05/2012).
I suspect the problem is due to the change in timezone (we go to BST/GMT + 1 during the summer), but it seems to only affect Excel dates, not PHP ones. Is there any way to fix this? I'm using the latest stable version of PHPExcel (1.7.7).