Struggeling with number format

Topics: Developer Forum, User Forum
Apr 21, 2011 at 7:53 AM
Edited Apr 21, 2011 at 1:39 PM

Hi,

I hope somebody can help me out here. I want to read an excel 2003 file with serial number in it. Whatever i try these numbers are read in the format 9.35035001415E+14 instead of 935035001414631. Here is my code:

        $objReader = PHPExcel_IOFactory::createReaderForFile($file);
       
        $objPHPExcel = $objReader->load($file);
        $objWorksheet = $objPHPExcel->getActiveSheet();

        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();

        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $range="A1:D$highestRow";
        $objWorksheet->getStyle($range)->getNumberFormat()->setFormatCode("0000");

            for ($row = 1; $row <= $highestRow; ++$row) {
              for ($col = 0; $col <= $highestColumnIndex; ++$col) {
                echo $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
              }
            }


When I show the format code it seems to be set correct to '0000'.

Whatever approach I use (advancedvalue binder, or setdatareadonly,...) the numbers are shown as 9.35035001415E+14. Please help...

Thanks

Mic

Apr 21, 2011 at 6:31 PM

Try setting the format code to just '0'.

Apr 21, 2011 at 9:16 PM
Edited Apr 21, 2011 at 9:17 PM

Scott,

thanks for your comment. The thing is: Setting the format code does not seem to have any effect. As I wrote before: When I read it back it is shown as set before but there is no effect on the cells/contents. Am i missing something here?

Cheers

Mic

Coordinator
Apr 21, 2011 at 10:11 PM
Edited Apr 21, 2011 at 10:12 PM

The numbers are read correctly, probably as a floating point value (because they exceed the max for a 32-bit integer); and it all comes down to how you're displaying them. Using echo, will simply display that floating point number as PHP will always display a floating point number: if it exceeds a certain size (based on the value of the php.ini precision setting) it will display it in scientific format. You can use standard PHP functions like sprintf() or number_format() to force PHP to convert the value to a formatted string for display, or you can do this within PHPExcel using the cell's getFormattedValue() method rather than getValue() to return the value as a formatted string, as per the number format mask. Note that accuracy may be lost purely because this is a floating point number.

Apr 21, 2011 at 10:24 PM

Mark,

thank you so much. That did the trick! After hours spending with this problem I finally have it done.

 

Thanks again

Mic

Aug 30, 2011 at 6:02 PM

Hi,

I have the same problem with number format, if I use  getFormattedValue() method rather than getValue()  , i get this error:

 

Fatal error: Call to a member function getParent() on a non-object in /...../Classes/PHPExcel/Cell.php on line 205

 

 

thanks for help