How to properly write values with leading zeroes with its unknown length

Topics: User Forum
Mar 11, 2015 at 9:30 AM
Edited Mar 11, 2015 at 10:11 AM
I've created custom value binder, match them, and write using
$cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
But still recieve notifications in Excel about numbers written as text. I'd apply some custom number format for it (e.g. ###### or something), but values can contain spaces in different places, and the numbers length is not known(its range approximately 8-15)....

Writer: Excel2007
Coordinator
Mar 11, 2015 at 10:03 AM
If you explicitly set a numeric value as a string like that in PHPExcel, then you will get that message..... if you enter a numeric value in MS Excel itself with leading zeroes by typing it as '123 (with the leading quote) then you'll get exactly the same message about numbers written as text from MS Excel..... it's what MS Excel does

The better option is to insert the value as a number, and set a number format mask, but without knowing the specifics of what you're trying to do, the type of values you're working with and the type of masking that you need for each, then it's very difficult to advise
Mar 11, 2015 at 10:19 AM
Edited Mar 11, 2015 at 10:33 AM
I'm working with values like:
0406440452
017649106486
040 28803190
I can't create mutual format for them...Seems like matching these values by separate regexes and applying specific format is only way to do it. What would you say?

P.S.: placing apostrophe is okay but I didn't figured the way how to do it without showing it in Excel...


UPDATE1

I've implemented possible solution:
  1. I divided my values on 2 types: only numbers and numbers with spaces. Made appropriate regexes for them.
  2. For only numbers I do:
                $valueLength = strlen($value);

                $numberFormat = '0';

                for($i = 1; $i <= $valueLength;$i++) {
                    $numberFormat = $numberFormat . '#';
                }

                $cell->getStyle()->getNumberFormat()->setFormatCode($numberFormat);
                $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
                $cell->getStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
As a result I recieve format like 0#########, depending from numbers amount
For values with spaces I can make similar logic as in 2(but add space in format).

What do you think about all of this, is it good idea at all?