CODEWITHSUNDEEP
phpphpexcel
Tjirp
Tjirp

Reputation: 2455

Problem reading numbers from excel with PHPExcel

I am trying to read a number from an excelsheet using PHPExcel.

The code I have that reads the data:

$objReader = PHPExcel_IOFactory::createReaderForFile($upload_file);
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($upload_file);
$objWorksheet = $objPHPExcel->getActiveSheet();
$data = array();

$i = 0;

foreach ($objWorksheet->getRowIterator() as $row) {
    if ($i < 1) {
    $i++; 
    continue; //Skip heading titles
}
$cellIterator = $row->getCellIterator();        
$cellIterator->setIterateOnlyExistingCells(false);      
foreach ($cellIterator as $cell) {          
    $data [$i] [] = $cell->getValue();          
}       
$i ++;

The code works. I read the Excel and all the data is nicely saved in the $data array. Problem is in Column A I got a number ( 4078500264822 ). Which it returns as "4078500264820" (see the last 0). No matter how I read it. if I print the entire Row i see the value is wrong. when I use functions such as "getCalculatedValue" the result is the same.

I think it has something to do with how the field is saved in excel. I have it saved as type "Number". Where as on another row. I have the same number saved. this time as "text" (excel throws a notice about this). PHPExcel can read that number. I have a 40 record sheet. where 26 cells get their last number replaced with a 0. You can find a truncated file (only 2 rows) here --> http://dl.dropbox.com/u/1458083/excel.xlsx

Upvotes: 3

Views: 12420

Answers (4)

T.O.
T.O.

Reputation: 130

Or maybe just try ini_set("precision", "15"); as default value is 12. Worked for me.

Upvotes: 10

Tjirp
Tjirp

Reputation: 2455

Thank you mark and crishoj for your support. Unfortunatly it did not solve the problem ;( I can not give anyone here the correct solution, since my data apperently was corrupted even before the Excel load function was called.

I solved the problem by forcing my customer to use CSV. it was a very minor change in the code (aka. 1 line, PHPExcel == awesome!) and now Im recieving everything as a string (and thus, it works!)

Again, thank you for all the help.

Upvotes: -3

Mark Baker
Mark Baker

Reputation: 212402

If the value is stored as a string in Excel, then it will be stored as a string when it is loaded into PHPExcel (assuming that you're using the Default Value Binder... the Advanced Value Binder would certainly cause you problems as its purpose is to handle conversion to a more appropriate data type). If it's stored as a number in Excel, then precision is lost even before it is loaded into PHPExcel.

What you could do though, is set your own Value Binder (extending the Default Value Binder) with a bindValue() method that tests the column reference of the cell that's being read (to see if it's column A), and forces PHPExcel to store that value as a string (even if a numeric has been read from the Excel file).

EDIT

This binder should do what I've suggested, forcing PHPExcel to load all values in column A as strings.

class SpecialValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder
{
    public function bindValue(PHPExcel_Cell $cell, $value = null)
    {
        if ($cell->getColumn() == 'A') {
            $value = PHPExcel_Shared_String::SanitizeUTF8($value);
            $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
            return true;
        }

        // Not bound yet? Use parent...
        return parent::bindValue($cell, $value);
    }
}


/**  Tell PHPExcel that we want to use our Special Value Binder  **/
PHPExcel_Cell::setValueBinder( new SpecialValueBinder() );

Ensure that you define the binder, and tell PHPExcel to use it before executing the load()

EDIT 2

As crishoj has done, I've run your code on (using 32-bit PHP on Windows). A var_dump() returns the value 4078500264822 as a float, which is what I'd expect (the value is too large to be stored as a 32-bit Integer), and echoing it displays the same, correct value with no loss of accuracy.

Upvotes: 2

crishoj
crishoj

Reputation: 5917

What platform are you on?

On my Mac, using the (64 bit) PHP interpreter from MacPorts with PHPExcel 1.7.6, I seem to get the correct values:

array (
  1 => 
  array (
    0 => 4078500264822,
    1 => '02648-32.000.00',
    2 => 'Wand-slangenbox automatic',
    3 => '20m slang, rolt zichzelf gelijkmatig op na gebruik. Geïntegreerde draaggreep voor 
comfortabel transport, inclusief montagemateriaal en wand-/vloerhouder.',
    4 => 17400,
    5 => 119.95,
    6 => '',
    7 => 12,
  ),
  2 => 
  array (
    0 => '4078500888707',
    1 => '08887-20.000.00',
    2 => 'Accu gras-buxusschaar set',
    3 => 'Eenvoudig gras en buxus in vorm knippen zonder storende kabels. De messen kunnen worden verwisseld zonder gereedschap, het is bijzonder gemakkelijk, snel en veilig. Laat tevens op tijd zien wanneer de accu weer moet worden opgeladen. ',
    4 => 16340,
    5 => 69.95,
    6 => 79.95,
    7 => 12,
  ),
)

What is the output when you run:

$ php -r 'echo PHP_INT_MAX;'

If it's 2147483647, then try a 64 bit interpreter. Or file a request with PHPExcel to fix the casting.

Upvotes: 2

Related Questions