PHPExcel getting errors reading decimal number values

0
votes

I'm using PHPExcel to update an eshop's items prices and stock, passing the data to a mysql query and updating the DB. I'm having a problem with some of the cells containing numbers with the format of 123,45 (using comma as a decimal separator). Although all cells contain the same format, only some are being read correctly. Please have a look for yourselves and let me know what I'm missing.

And finally, my code:

    $objPHPExcel = PHPExcel_IOFactory::load($path);
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);

    $highestRow = $objWorksheet->getHighestRow();
    $highestColumn = $objWorksheet->getHighestColumn();
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $nrColumns = ord($highestColumn) - 64;

    $rowsadded=0;
    $begin_row=2; // 1st line of data in excel file
    for ($row = $begin_row; $row <= $highestRow; ++ $row) {
        $val=array();
        for ($col=0; $col < $highestColumnIndex; $col++) {
            $cell = $objWorksheet->getCellByColumnAndRow($col, $row);
            $val[] = $cell->getCalculatedValue();
        }

        if (($val[0]<>'') && ($val[6]>0)) { //check that row contains data before inserting
            $rowsadded++;
            $sql = sprintf("update productsizes set price=%s, stock=%s where auxcode=%s",
                   GetSQLValueString($val[6], "float"),
                   GetSQLValueString($val[4], "int"),
                   GetSQLValueString($val[0], "text"));
            $result = mysql_query($sql) or die(mysql_error());
        } //end of checking that row contains data before inserting

    } echo '<h2>'.$rowsadded. ' rows updated successfully</h2>';
2
phpmysqlphpexcel
Can you please clarify exactly what your log is showing, because it's not obvious from the code compared with the workbook that you've posted. It might also be useful to var_dump you $val array to show that actual datatypes retrieved from the getCalculatedValue() call - Mark Baker
Log is posted in the 2nd link, you can see it all there and compare with the actual data. - bikey77
I looked at the log, I'm more interested in where in the code it's being generated. Every test I've done using your code, and using my own has returned exactly what I expect to see when looking at the workbook itself (without any problems) but I can't see where you're generating the log in your code, so I can't see at what point your values are being changed from what PHPExcel is returning. - Mark Baker
I generated the log with some test code and erased it, only so I could see what phpexcel is reading. I did the var dupm as you suggested and I'm getting many null values from col E, even when the cell has a number in it. I dont know where to look for the problem. - bikey77
btw, thank you for all your help, i very appreciate it. - bikey77

2 Answers

0
votes

I haven't worked with PHPExcel but why don't you change this $val[] = $cell->getCalculatedValue(); to get the string value then replace , with a . & the cast the variable as an integer and store it in another variable / array ? I bet this will work.

0
votes
$rowsadded=0;
$begin_row=2; // 1st line of data in excel file
for ($row = $begin_row; $row <= $highestRow; ++ $row) {
    $val = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row,NULL,TRUE,FALSE,TRUE);
    var_dump($val);
    $val = $val[$row];

    if (($val['A']<>'') && ($val['G']>0)) { //check that row contains data before inserting
        $rowsadded++;
        // other code goes in here
    } //end of checking that row contains data before inserting

} echo '<h2>'.$rowsadded. ' rows updated successfully</h2>';

The var_dump() will show you the value and datatype of each row as you load it. Use this for debugging purposes