Custom PHPExcel cell format only shown after double-click

Question

I encountered a weird problem setting a custom date format with PHPExcel: I'm writing an sql formatted date to a cell and setting it's format with setFormatCode to 'd/m/y'. When I open it in Excel 2007, it shows the orignial date format until I double click the cell and then it's updated to my desired format. Is there a way to have it refreshing itself without me helping?

$sheet->setCellValueByColumnAndRow($column, $row, '2010-07-16');
$sheet->getStyleByColumnAndRow($column, $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH);

Answer 1

Unless you're using PHPExcel's "Advanced Value Binder", then $sheet->setCellValueByColumnAndRow($column, $row, '2010-07-16'); will be storing the value as a string, not as a date, so the date format you're setting in the next line is meaningless when applied to a string until you read the resultant file in Excel and force a refresh... Excel itself then fixes your error.

To ensure that the value is correctly stored in the first place, you need to store it as a date/timestamp/number rather than a string, then set the format mask to ensure that it is treated as a date/timestamp rather than a numeric value.

Either convert your string to a PHP date using strtotime(), then use PHPExcel's built in date conversion methods:

$PHPDateValue = strtotime('2010-07-16');
$ExcelDateValue = PHPExcel_Shared_Date::PHPToExcel($PHPDateValue);
$sheet->setCellValueByColumnAndRow($column, $row, $ExcelDateValue); 
$sheet->getStyleByColumnAndRow($column, $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH); 

or use the built-in method to convert a date-formatted string to an Excel datetime value directly

$dateString = '2010-07-16';
$ExcelDateValue = PHPExcel_Shared_Date::stringToExcel($dateString);
$sheet->setCellValueByColumnAndRow($column, $row, $ExcelDateValue); 
$sheet->getStyleByColumnAndRow($column, $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH); 

or use the Calculation Engines function library's DATEVALUE() function:

$dateString = '2010-07-16';
$ExcelDateValue = PHPExcel_Calculation_Functions::DATEVALUE($dateString);
$sheet->setCellValueByColumnAndRow($column, $row, $ExcelDateValue); 
$sheet->getStyleByColumnAndRow($column, $row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH); 

or, option 4, is to use PHPExcel's "Advanced Value Binder"

To enable this feature, execute the following static call

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

before you instantiate your workbook object, or load it from file

Then PHPExcel will identify that your value is a date, and handle the conversion to an Excel date/timestamp and format it automatically

$dateString = '2010-07-16';
$sheet->setCellValueByColumnAndRow($column, $row, $dateString); 

Answer 2

I would like to add an option 5...

If you want to use this with SetValueExplicit you would do it this way:

\\convert bDate to dDate 
$dDate = PHPExcel_Shared_Date::PHPToExcel(strtotime($bDate));

\\must use type numeric
$worksheet->getCell($col_row)->setValueExplicit($dDate,PHPExcel_Cell_DataType::TYPE_NUMERIC);
\\use FORMAT_DATE of your choice i am using "YYYY-MM-DD"
$worksheet->getStyle($col_row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);

Please note that the Date Formats in the documentation and online appear to be out of date. For a full list of all the different types of FORMAT_DATE_XYZ just look in the PHPExcel\Style\Number_Format.php file in your copy of the library.

It's probably easiest to just use the Advanced Value Binder but if you can't or don't want to then hopefully this information has been useful to you.