11 Vote

PHPExcel: Leading Zeros

Question by WebHorn | Last update on 2024-01-12 | Created on 2016-12-20

I am using the library PHPExcel for creating and offering an Excel file created with PHP for download.

It is working quite well, but I would like to write the months from 01 till 12 into my table. Although I am passing the values "01", "02" and so on, there are appearing only "1" and "2" in the corresponding cells when opening the file in Excel without any zero in front.

What can I do to make Excel show those leading zeros?

ReplyPositiveNegative
1Best Answer1 Vote

When Excel is detecting a "01" within a cell, Excel interprets this content as a number and it only displays the integer 1 instead of the string "01".

In order to change this behavior, there are generally two possibilities: The first way is to format the cell no longer as number but as string instead. You can do that like the following:

$php->getActiveSheet()
    ->setCellValueExplicit('A1', 
                           '01', 
                           PHPExcel_Cell_DataType::TYPE_STRING);

If it is important for you that the number is further processed as a number by Excel, you can also alternatively change the number formatting of the corresponding cell. In the following example, first, we set the cell A1 to 1, afterwards we are adjusting its formatting:

$php->getActiveSheet()->setCellValue('A1', 1);
$php->getActiveSheet()->getStyle('A1')
                      ->getNumberFormat()
                      ->setFormatCode('00');

In your case, you have to specify "00". If the numbers are longer or if you want more zeros to be displayed, you have to pass "000", "0000" or accordingly just your desired number of digits.
Last update on 2024-01-12 | Created on 2016-12-21

ReplyPositive Negative
Reply

Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.