13

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);
Bogdan
  • 1,869
  • 6
  • 24
  • 53

6 Answers6

25

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.

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • Is there anyway to specify the data type when exporting the data _without_ explictly providing the cell name (e.g. A3)? – crmpicco Jan 07 '13 at 10:35
  • @crmpicco - Don't understand.... if you're setting a cell value, without specifying a cell name, how is PHPExcel supposed to know what cell value you want to set? – Mark Baker Jan 07 '13 at 10:45
  • Apologies if this is slightly off-topic, but I am looking for a PHP solution that will allow me to export data from an array that includes elements such as `0078`. MS Excel converts this value to `78` which is invalid for the application that I wish to use it in, so I am looking for a 3rd party solution to alleviate this problem. PHPExcel has came up in my searches, but looking at the documentation it would seem I can't export the data and sate that it is a string and not an integer? Unless I have to specify the cell name - is that correct? – crmpicco Jan 07 '13 at 10:50
  • PHPExcel uses a "value binder" to control how data is stored in a cell. The default behaviour is to identify the datatype based on PHP's is_numeric(), and treat it as a number, converting as apprpriate; but you can write your own binder to define specific behaviours. You could also set a number format mask to display the numeric with leading zeroes, and that can be set for a range of cells, or even a whole worksheet – Mark Baker Jan 07 '13 at 10:56
9

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);
Allan Sun
  • 119
  • 2
  • 6
4

For me this did the trick

// Set the value explicitly as a string
$objPHPExcel->getActiveSheet()->setCellValueExplicit('A1', '0029', PHPExcel_Cell_DataType::TYPE_STRING);
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
Alex
  • 51
  • 1
1

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:

http://www.ozgrid.com/Excel/CustomFormats.htm

Juan M
  • 4,063
  • 4
  • 19
  • 28
1

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.

sk001
  • 561
  • 6
  • 27
0

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

Community
  • 1
  • 1
Tomasz
  • 151
  • 5