Here's the code i'm using right now to set the cell values. It works alright if the number has separators like . or / but when there's no separator it gets saved as int and the leading 0 is stripped
$sheet->setCellValue($letter[$j].$row_nr,$entity['Phone'], PHPExcel_Cell_DataType::TYPE_STRING);
Either:
// Set the value explicitly as a string
$objPHPExcel->getActiveSheet()->setCellValueExplicit('A1', '0029', PHPExcel_Cell_DataType::TYPE_STRING);
or
// Set the value as a number formatted with leading zeroes
$objPHPExcel->getActiveSheet()->setCellValue('A3', 29);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getNumberFormat()->setFormatCode('0000');
Note that in the first case I'm calling the setCellValueExplicit() method, not the setCellValue() method. In your code, passing PHPExcel_Cell_DataType::TYPE_STRING to setCellValue() has no meaning, and the argument is simply ignored.
The easiest solution is to use setCellValueExplicitByColumnAndRow($pColumn = 0, $pRow = 1, $pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING),
$PHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow($columnPointer, $rowPointer, $value);
I came across this thread when looking for a solution and there is my other answer that may be helpfull for someone in case or column/row deletion that causes cell formatting to get lost...
Just came across an alternative solution and I thought I'd post it here. This does not require the use of libraries, just formatting the required .xls cells when creating the html table to be exported.
<td style="mso-number-format:'@';">your number w/leading zeroes here</td>
I hope someone finds it useful. Below is a complete reference with formatting codes:
For me this did the trick
// Set the value explicitly as a string
$objPHPExcel->getActiveSheet()->setCellValueExplicit('A1', '0029', PHPExcel_Cell_DataType::TYPE_STRING);
Whenever someone proceeds like I did, this can help :
$inputFileName = 'file.xls';
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
$objWorkSheet = $objPHPExcel->getActiveSheet();
$objWorkSheet->getCell('A1')->setValueExplicit('0029', PHPExcel_Cell_DataType::TYPE_STRING);
As inspired by this answer. I hope this help somebody.