CODEWITHSUNDEEP
phpphpexcel
Bogdan
Bogdan

Reputation: 1901

How to get phpexcel to keep leading 0s in phone numbers?

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);

Upvotes: 13

Views: 37206

Answers (6)

sk001
sk001

Reputation: 571

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.

Upvotes: 1

Alex
Alex

Reputation: 51

For me this did the trick

// Set the value explicitly as a string
$objPHPExcel->getActiveSheet()->setCellValueExplicit('A1', '0029', PHPExcel_Cell_DataType::TYPE_STRING);

Upvotes: 4

Juan M
Juan M

Reputation: 4503

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

Upvotes: 2

Tomasz
Tomasz

Reputation: 151

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

Upvotes: 0

Allan Sun
Allan Sun

Reputation: 129

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);

Upvotes: 10

Mark Baker
Mark Baker

Reputation: 212412

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.

Upvotes: 25

Related Questions