CODEWITHSUNDEEP
phpphpexcel
Dan
Dan

Reputation: 387

Correct format for strings / numbers beginning with zero?

I'm trying to use PHP to create a file containing a list of phone numbers. It's working OK however if the phone number begins with zero, the digit is dropped from the Excel file.

Does anyone know how to set the formatting correctly so that it remains in place?

Upvotes: 30

Views: 66851

Answers (6)

Himal Majumder
Himal Majumder

Reputation: 21

In My case, I'm using 

->setCellValue('A3', ' '0000123456789000' ');

And it's work perfectly.

Upvotes: 0

Nishad Aliyar
Nishad Aliyar

Reputation: 77

Use \t with the value like "$phone\t"

Upvotes: 1

keepthepeach
keepthepeach

Reputation: 1621

If for some reason the answers above don't work, you can simply try to wrap your data in quotes, something like this: 

setCellValue('A1, '"' . $phone . '" ')

But your value will be surrounded by quotes in your file as well.

Upvotes: 1

Tomasz
Tomasz

Reputation: 151

This is an old question but I was recently struggling with this issue and I thought it may help someone in the future if I post some additional info here:

Whilst the above answers are correct, the formatting gets lost when you remove a column or row that is located before the formatted cell.

The solution that seems to be resistand to that is:

$cellRichText = new \PHPExcel_RichText($worksheet->getCell($cell));                        
$cellRichText->createText($cellValue);

Upvotes: 1

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

Upvotes: 69

Sjoerd
Sjoerd

Reputation: 75598

Set the type to string explicitly:

$type = PHPExcel_Cell_DataType::TYPE_STRING;
$sheet->getCellByColumnAndRow($column, $rowno)->setValueExplicit($value, $type);

Upvotes: 16

Related Questions