Formatting using a number format affects the way a number is displayed, not the way it is stored.
You'll have to store the numbers explicitly as strings, so you can't use fromArray().
Use setCellValueExplicit() or setCellValueExplicitByColumnAndRow() instead, passing a $pDataType argument of PHPExcel_Cell_DataType::TYPE_STRING.
EDIT
Note that you can also set styles for a range of cells, so there's no need to add the overhead of the for loop:
$range = 'A'.$row.':'.$latestBLColumn.$row;
$objPHPExcel->getActiveSheet()
->getStyle($range)
->getNumberFormat()
->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );
EDIT #2 Using a cell binder
Create a customised cell value binder:
class PHPExcel_Cell_MyValueBinder extends PHPExcel_Cell_DefaultValueBinder
implements PHPExcel_Cell_IValueBinder
{
public function bindValue(PHPExcel_Cell $cell, $value = null)
{
// sanitize UTF-8 strings
if (is_string($value)) {
$value = PHPExcel_Shared_String::SanitizeUTF8($value);
}
// Implement your own override logic
if (is_string($value) && $value[0] == '0') {
$cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
return true;
}
// Not bound yet? Use default value parent...
return parent::bindValue($cell, $value);
}
}
To avoid any problems with the autoloader, create this in the /Classes/PHPExcel/Cell directory. Otherwise, give the class your own non-PHPExcel name, and ensure that it's loaded independently.
Then, before using your fromArray() call, tell PHPExcel to use your value binder instead of the default binder:
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_MyValueBinder() );
The value store in PHPExcel is an Excel serialized datetime value (a float, representing the number of days since 1/1/1900 or 1/1/9004 depending on whether Windows or Mac Calendar was used). Changing the format is simply changing the way this value is displayed.
You will either need to know in advance whether a cell contains a date, or test it using the PHPExcel_Shared_Date::isDateTime()
method, and convert it appropriately. You can then either retrieve it using getFormattedValue()
to return the value as a formatted string; or use the built-in conversion functions to convert it to a unix timestamp (PHPExcel_Shared_Date::ExcelToPHP()
) or a PHP DateTime object (PHPExcel_Shared_Date::ExcelToPHPObject()
) that you can then format using the standard PHP date formatting functions before using it in your MySQL INSERT/UPDATE statements.
Best Answer
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:
or use the built-in method to convert a date-formatted string to an Excel datetime value directly
or use the Calculation Engines function library's DATEVALUE() function:
or, option 4, is to use PHPExcel's "Advanced Value Binder"
To enable this feature, execute the following static call
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