Problems formatting String with leading Zero

Topics: User Forum
Jul 10, 2008 at 10:01 AM
Hello,

i have a String with leading Zeros eg '0043123213423'
I format the cell with
$this->worksheet->setCellValueExplicitByColumnAndRow($this->spalte,$this->zeile,$cell_content,PHPExcel_Cell_DataType::TYPE_STRING);
but in Excel the String shows up without leading Zeros.
There are no other Operations on this Cell, except for Styles (borders,colors,etc).

Using
$this->nformat->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
or $this->nformat->setFormatCode("General");
dont work too.

$this->nformat->setFormatCode("Standard");
works but gives me a Error when opening the Excel.

Any Ideas ?
regards,
gw

Ps: i'm using PHPExcel/Writer/Excel5.php because i'm not allows to produce Excel 2007 Files....
Sep 5, 2008 at 11:23 AM
Hello,

I have exactly the same problem, i would like to put a string like '0001' in a cell, but the leading zeros are lost.

Is there a way to do this ?!?!

Thank you.

ps: I'm also using Excel5.php
Developer
Sep 5, 2008 at 1:18 PM
To get leading zeros you proceed the same way as you do in MS Office Excel.
  1. Type the value in the cell, e.g 53
  2. Apply custom number format 0000 (example: http://img237.imageshack.us/img237/988/formatkk5.png)
Note that in MS Office Excel it does not help to type e.g. 0053. As soon as you press enter, the leading zeros are cut off.

In PHPExcel, the above corresponds to this:

$cell = $ws->getCell('A1');
$cell->setValue('53');
//$cell->setValue(53); // this would work too
$ws->getStyle('A1')->getNumberFormat()->setFormatCode('0000');
// displayed in cell as: 0053

I think one should not try to change data type to string if the values are numbers from a semantic viewpoint.

As a side remark, number formatting is really confusing in MS Office Excel because here one has labeled one number format "Text". But it does not change data type. Internally, data type stays as a number. It just shifts the number to the left.
Sep 5, 2008 at 2:00 PM
Thanks koyama, it works.
Sep 9, 2008 at 11:01 PM
Thanks for your reply koyama.   However, what if the data value is meant to be stored as a string value so to begin with it's something like "001234" and it needs to be displayed that way in excel.  I usually do this by formatting the column first as number->Text then it works correctly from there on.  After that, when a value is entered into the cell, a green triangle appears in the upper-left hand corner of the cell.  How can i achieve that rather than the method you suggested above because I won't know what the length of the data is to begin with as well as the format (i.e. 00000 or 0000-000)?  Any help would be appreciated.  Thank you.
Developer
Sep 10, 2008 at 12:29 AM
@mouaxiong: In your case string data type may be what you need. Discussion can continue here for your specific case
http://www.codeplex.com/PHPExcel/Thread/View.aspx?ThreadId=35306