TYPE_NUMERIC

Topics: User Forum
Dec 20, 2008 at 7:41 AM
I have a formatting issue with Excel with some ID's. For instance, "207375933158" will display as "2.07376E+11" - it does it for all of the IDs within the 20737* range, but not others (like "44533114719" will display just fine). I've tried casting the number as an int with PHP (" $ID = (integer)207375933158);") although, some of you may know that this will only work on numbers below 2147483648 - then it casts it as "float" - which will not work.

In PHPExcel, I tried the following:
$objPHPExcel->getActiveSheet()->getCell('H' . $cellnum)->setValueExplicit($ID, PHPExcel_Cell_DataType::TYPE_NUMERIC);

No go, does the same thing. Oddly enough, when I manually format the cell column as "Number" then it formats to how it should. Am I using the wrong TYPE?

Otherwise, I'm out of ideas...maybe I just have to tell the executives "you'll just have to manually set the field every time you open a fresh document..." - which sucks, because one of the big reasons I tried this system out was because of the ability to define the data type. I really hope someone has at least a few ideas to try...if not the solution.

Thank you!
Coordinator
Dec 20, 2008 at 9:25 AM
Edited Dec 20, 2008 at 9:32 AM
So 12-digit numbers (or more, I'd assume) such as 207375933158 are showing in scientific notation, while 11-digit numbers lile 44533114719 display OK.

If the problem is in an Excel file that you've written, then look at number masking the values.
$excelObject->getActiveSheet()->getStyle($cellID)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
or whatever Excel number format masking is appropriate

If it's in the PHP, then I'd take a look at your php.ini file, and check the value of precision possibly increasing it if it is set to 12
Note that you can modify this value within your own code.

Developer
Dec 20, 2008 at 9:47 AM
Yeah, it is probably the number format that is the problem. As markbaker says, you may need to use

$excelObject->getActiveSheet()->getStyle($cellID)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);

This corresponds to the number format ”Number” in Excel.

I just noticed that PHPExcel_Style_NumberFormat::FORMAT_NUMBER is not working for Excel5 writer, but ok for Excel2007 writer. Not sure why, but I will check up on this.

Developer
Dec 21, 2008 at 9:22 AM
Problem related to PHPExcel_Writer_Excel5 is fixed per work item #8596:
http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=8596

Download latest source at next check out:
http://www.codeplex.com/PHPExcel/SourceControl/ListDownloadableCommits.aspx

Dec 22, 2008 at 5:51 PM
'FORMAT_NUMBER' did it! Thanks a lot...that's such a relief! :)

A+
Jan 20, 2009 at 5:38 PM
I have a similiar problem, I have the format set as CURRENCY_USD, however the numbers like 200000 show up in scientific notation instead of the currency format that I have set. Any ideas why? BTW I am using the latest source code
Developer
Jan 20, 2009 at 6:14 PM
@jay102482: Can you try this?

$excel = new PHPExcel();
$sheet = $excel->getActiveSheet();

$sheet->getCell('A1')->setValue(200000);
$sheet->getStyle('A1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD);

$writer = new PHPExcel_Writer_Excel2007($excel);
$writer->save('test.xlsx');


Result looks ok:
http://img519.imageshack.us/img519/2315/dollarzi8.png

Feb 11, 2009 at 2:38 PM

I am having a problem, just svn'd the latest, but have this code:

 $objPHPExcel->getActiveSheet()->setCellValue("A{$row}",$chargeback->AccountNumber);
$objPHPExcel->getActiveSheet()->getStyle("A{$row}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);

In the spreadsheet, the last digit is being replaced with a zero.  I am finally pushing this into an Excel5 spreadsheet.

Developer
Feb 11, 2009 at 2:55 PM
@niterain: How many digits are there? Can you try the following:
Create the spreadsheet in MS Office Excel as you would like it to look. Then do a plain read/write using PHPExcel_Reader_Excel5 / PHPExcel_Writer_Excel5.
Check if output looks like input.

Feb 11, 2009 at 3:04 PM
One is about 16 characters and the other is 23 characters long.
Developer
Feb 11, 2009 at 3:30 PM
This may be the problem. There is a limit how large numbers you can store in Excel. Notice that redcore's example had only about 12 digits. Approximately 15 digits is the limit.
http://en.allexperts.com/q/Excel-1059/Excel-rounding-large-numbers.htm

Your only option is to store as text. But if you are not using those numbers in any formulas then it should be ok.

$sheet->getCell('B2')->setValueExplicit('9999988888777776666655555', PHPExcel_Cell_DataType::TYPE_STRING);

Dec 2, 2013 at 1:07 PM
Hi,

Those who are having problem with lengthy integer values which convert in xls files as scientific notation the below code solves the problem. What you need to do is place these lines in your while loop . After formatting cells data will write into cell.

while (){// Begins
$objPHPExcel->getActiveSheet()->getStyle('B'.$s)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
$objPHPExcel->getActiveSheet()->getStyle('D'.$s)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);

$objPHPExcel->getActiveSheet()->setCellValue('B'.$s, $csvrow['LARGEINTEGER']);
}

Simple . Happy PHPEXCELING :)