Correct format for strings / numbers beginning with zero?

Question

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?

score 69 · Accepted Answer · answered Jun 16 '10 at 14:50

69

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

answered Jun 16 '10 at 14:50

Mark Baker

209,507
32
346
385

2

Just as a note to future readers, the second option doesn't seem to work, it just sets it as a number. But the first option works great, thanks. – Pippa Rose Smith Aug 26 '12 at 15:02
Both options work for me for xlsx and xls files, haven't tested other writers – Mark Baker Nov 26 '12 at 16:20

score 16 · Answer 2 · answered Jun 16 '10 at 14:46

16

Set the type to string explicitly:

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

answered Jun 16 '10 at 14:46

Sjoerd

74,049
16
131
175

score 1 · Answer 3 · answered Jan 22 '14 at 16:05

1

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

answered Jan 22 '14 at 16:05

Tomasz

151
5

I think format is not lost but kept in the original cell position, i.e I set the format in the column E, remove column B, column E is now column D, but the format remains in column E. I'm just dealing with this issue, because I need to remove columns after setting the format. – morgar Mar 22 '20 at 02:49

score 1 · Answer 4 · answered Apr 21 '15 at 05:05

1

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.

answered Apr 21 '15 at 05:05

keepthepeach

1,621
2
20
27

score 1 · Answer 5 · edited Aug 22 '16 at 12:39

1

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

edited Aug 22 '16 at 12:39

Paul Roub

36,322
27
84
93

answered Aug 22 '16 at 12:29

Nishad Aliyar

77
1
3

Great solution. – Kalaiyarasan Sep 08 '16 at 11:38
This will add a tab in the value field. Not great if you want to copy it without spaces/tabs and hassle. – CodeBrauer Oct 24 '16 at 08:49
It is not a clean solution but in my case works fine. Thanks :) – eloirobe Apr 25 '18 at 12:46

score 0 · Answer 6 · edited Feb 24 '20 at 11:26

0

In My case, I'm using

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

And it's work perfectly.

edited Feb 24 '20 at 11:26

Dorian Turba

3,260
3
23
67

answered Feb 24 '20 at 10:31

Himal Majumder

21
1
3

Correct format for strings / numbers beginning with zero?

6 Answers6

Linked

Related