Number format when reading file

Topics: Developer Forum, User Forum
Jul 7, 2009 at 6:26 PM
Edited Jul 7, 2009 at 6:28 PM
I'm using PHPExcel for the very first time. It's very nice so far. Great work!
I'm using it to read spreadsheets, and I'm having trouble with number formatting. I'm using the Worksheet->toArray() function and some of the numbers in the spreadsheet are being formatted like '5.783,00'. I am in the United States and PHP is not recognizing these as numbers, but as strings, which is causing problems. Is there a way I can specify the number format (like 5,783.00)? I've seen some references for doing that for spreadsheets being created, but this one is just being read, not written. Any help or pointers would be very appreciated.
My code looks pretty much like this:

include 'PHPExcel/IOFactory.php';
try 
{
	$spreadsheet = PHPExcel_IOFactory::load($_FILES['upload']['tmp_name']);
}
catch (Exception $e)
{
	// ...
}

$numberOfSheets = $spreadsheet->getSheetCount();
for($i = 0; $i < $numberOfSheets; $i++)
{
	$sheet = $spreadsheet->getSheet($i);	
	$sheetArray = $sheet->toArray();
	print_r($sheetArray);
}

 

 

Jul 7, 2009 at 9:24 PM

I've reworked my code to use

$sheet->getCellByColumnAndRow($col, $row)->getValue()

and

$sheet->getCell()->getValue()

which return plain numbers, not strings.

 

I'd still like to know if there's a way to make toArray() work, as it might be useful for other tasks. Thanks.

Developer
Jul 8, 2009 at 4:06 AM

>> PHP is not recognizing these as numbers, but as strings

Well, '5.783,00' or '5,783.00' won't make any difference. Neither string will be recognized as the number 5783 in PHP. There must not be any thousands separator at all.

>> some of the numbers in the spreadsheet are being formatted like '5.783,00'.

If your locale is set to English, then this is a bug. It should be returning '5,783.00' in English locale. Can you try with latest source code as this may have been fixed:
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Jul 8, 2009 at 6:41 PM

The latest source code has taken care of it. Thanks!