Need string with leading zeros to appear as string and not get formatted to number automatically

Topics: Developer Forum, User Forum
Sep 9, 2008 at 9:17 PM
This feature works if you use the method:


However, when you initially open up the excel file, you'll receive an error but as soon as you save it in excel, it works fine from there on.  Could anyone tell me how to get around the error.  I've read the recent posts and the setFormatCode('0000'); will not work for me because the strings can be any number of length.  I need it to truly function as if you opened up an excel file and formatted a cell using the following:

right-click on a cell-->format cells-->number tab-->Text-->hit OK.  This process truly formats a cell to allow any type of string to be typed in it and does not allow any auto-formatting after you hit enter.  In addition, the green triangle also appears in the upper-left hand corner.  The method I described above allows it to work but gives me an error initially.  Could anyone help me with this one.  Thanks in advance.
Sep 9, 2008 at 10:07 PM
The reason why you are getting the error is because there is no number format code labeled String.

What you want is the "Text" number format from the menu in MS Office Excel:


Quick introduction to number format codes:
Sep 9, 2008 at 10:38 PM
Sorry, I misread your post. I guess you still need the leading zeros. The above will not work.

It turns out that in MS Office Excel 2007 (and probably earlier versions too) whenever you first set number format to be "Text" and then enter a value such as '0200' then MS Office Excel will automatically change data type to 'String' for the cell in order to show the leading zeros. I was not aware of that. PHPExcel does not mimic this special behavior.

To get the same result in PHPExcel, you will have to explicitly set the data type for the cell.

$ws->getCell('A1')->setValueExplicit('0200', PHPExcel_Cell_DataType::TYPE_STRING);

Note that rendering will be the same in MS Office Excel even if you leave out the format code above.

However, string data type may not be the right approach.

Consider this solutions too which uses ordinary numerical data type.

Sep 10, 2008 at 1:57 PM
Thanks for your response koyama.  I would really like to achieve what I'm trying to get at by setting the formatnumber to 'String' programmatically.  I read somewhere that if you take the excel file and save it as an xml file and view the xml, you can see the formatting placed around the data.  As I noted in my first post, I did that for the one which I set the formatnumber to 'String' and then again for the xls file after I re-open it up and resave it as an xml file which works fine after opening it the first time with an error.  Then I compared the two xml files and there's very little difference except for a small section at the very end.  I'm wondering if there's anyway I can get past this because I would like to try and avoid work-arounds because it's not going to truly fix my problem.  Any other suggestion would be awesome.  Thanks for your help so far.
Sep 10, 2008 at 2:49 PM
I may still be a bit confused what you are trying to achieve.

Where did you get the idea to apply setFormatCode('String'), because in fact this is not a valid format code. Therefore you are getting broken xlsx files.

From what I have read so far the correct approach for you is to do like this:
$ws->getCell('A1')->setValueExplicit('0200', PHPExcel_Cell_DataType::TYPE_STRING);

Do not set any number format code.

Can you please try out the above and explain how the resulting xlsx file differs from what you want.
Sep 11, 2008 at 9:10 PM
I'm sorry if I'm still confusing you koyama.  I appreciate all your help so far.  I tried what you indicated above and it seems like it still treats the value "0200" as a numeric value and so the string is converted to 200 in the cell and the "0" is dropped from the front of it. 

The reason I tried "String" was because if you save the file as an xml file after you've formatted the cell to "text" and then type in "0200", the "0" is retained then if you open up the xml file, the formatting indicated for the cell is of type "String".  In other words, the code I provided in my first post, I tried using "String" and it worked but on initial open of the file, it displayed an error but if you save it and reopen it again, the error is gone but most importantly, the text format is kept in place where the green triangle is displayed in the upper-left hand corner of the cell.

I guess I really hoping to achieve this programmatically because I don't want to figure a work around for it.  The reason being is that the data is consisted of global zip codes and so there's really no way of determining the length or a standard mask to test for.  A good example of this is the zip code "02583".  If the format I'm talking about was possible, it would keep the zero in the front.  Please let me know if there's any other way of setting the text format I'm referring to.  Thank you.

Here's my code

        // get worksheet instance
        $ws = $objPHPExcel->getActiveSheet();              
        $ws->getCell('B2')->setValueExplicit('00200', PHPExcel_Cell_DataType::TYPE_STRING);

        echo date('H:i:s') . " Write to Excel5 format\n";
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        // Save file to specified directory
        $objWriter->save($saveDir . "/excelfile.xls");

Sep 12, 2008 at 2:31 PM

I was able to achieve the display of the green triangle in the upper-left hand corner simply by placing a space after the set value which allowed me to keep the leading zeros.  Let me know in the future if there's a more logical way of doing this.  Thank you.
Sep 14, 2008 at 12:03 AM
I suddenly notice that you are using Excel5 writer and not Excel2007 writer which I was testing the scripts with. Indeed there seems to be a bug in Excel5 writer not recognizing data type. A work item has been created:
Once this is resolved you will be able to solve the problem the correct way like I showed you.

I now understand where you got the idea to use String keyword. But please note that this piece of information refers to the cell data type and not the number format. And changing the data type to String is exactly what my suggestion is about. Thank you for your report.
Nov 25, 2008 at 2:36 PM
This is what I did to solve this problem and it seems to work. It retains the leading zeroes of data and changes the column to a text type

$cellBadgeNo = ($col).$row;

// skip and write other columns, then get back to writing numbers to string

$excel->getActiveSheet()->setCellValue($cellBadgeNo, $rec["EmpBadge"]." ");
$excel->getActiveSheet()->setCellValue($cellBadgeNo, "=MID($cellBadgeNo, 1, LEN($cellBadgeNo)-1)");

Feb 23, 2009 at 4:13 PM
i want the same thing i.e. want to apply "Text" format rather than "General" format
not for any single cell but for whole sheet
if i apply it cell by cell the page dies out of timeout
any thing possible here?