PHPExcel getting errors reading decimal number values

Question

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.

Excel sample data: http://dl.dropbox.com/u/25322282/data.xlsx
Log from looping through the data and printing out what it's reading where you can see that prices are read only past row 66. http://dl.dropbox.com/u/25322282/log.htm

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>';

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, Jun 21 '12 at 15:25
Log is posted in the 2nd link, you can see it all there and compare with the actual data. — bikey77, Jun 21 '12 at 16:27
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, Jun 21 '12 at 16:43
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, Jun 21 '12 at 16:51
Can you please try replacing your loop with the loop I've shown in my answer below, including the var_dump() statement; and posting that log. When I run it from the workbook you've posted, I don't see NULL values in column E — Mark Baker, Jun 21 '12 at 16:55
That var_dump() just doesn't tie in with the workbook you've uploaded at all: cell A5 contains "CH05140" in the workbook you uploaded, and yet your log is showing cell A5 as containing "CH05155"... I know PHPExcel is flawed in many ways, but it isn't that imperfect. Can you please verify that the file you're loading is the one you've posted — Mark Baker, Jun 21 '12 at 17:04
Sorry, my wrong. I've been testing so many different things while we're talking that have lead to false info. This is the correct log http://dl.dropbox.com/u/25322282/vardump.htm and the problem is with col G (price) in which many cells are read as strings instead of floats. I apologize for the hassle. — bikey77, Jun 21 '12 at 17:08
OK, you've got me completely flummoxed... if I run the code against your workbook, I get 100% correct results, that bear no resemblence to the var_dump() you've posted... I just can't understand it at all. I'm at a complete loss, and can't even begin to explain it — Mark Baker, Jun 21 '12 at 17:15
I don't know where the confusion is, I'm pretty sure that the last information is correct. However, if I have lead you to confusion, I'm sorry, it's all quite confusing to me, too. — bikey77, Jun 21 '12 at 17:52
The confusion lies that in five years developing PHPExcel and debugging problems I've never encountered an issue like this, where I run the same code against the same file as yourself and get such completely different results — Mark Baker, Jun 21 '12 at 19:11

score 0 · Answer 1 · answered Jun 21 '12 at 15:16

0

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.

answered Jun 21 '12 at 15:16

fineTuneFork

683
7
21

I'm not very sure how to do the cast part, could you provide me with a code sample? – bikey77 Jun 21 '12 at 15:20
Here you go, just use this function & you are all set to go! http://php.net/manual/en/function.floatval.php – fineTuneFork Jun 21 '12 at 15:23
I suppose I could also use number_format but my intention is to find out why the data isn't beeing fetched as expected. Thanks anyway! – bikey77 Jun 21 '12 at 15:25
1

Probably because the string value is something like: "=1.23 * B2", ie a formula rather than a value.... OP needs the result of the calculation – Mark Baker Jun 21 '12 at 15:27
check this post out - http://stackoverflow.com/questions/4584963/how-to-automatically-read-in-calculated-values-with-phpexcel I guess something wrong is being calculate by the function getCalculateValue . Take a look at the first commend of the top-vote answer. Hope this helps. Use `getValue()` instead. – fineTuneFork Jun 21 '12 at 15:31
could very well be what @MarkBaker pointed out. So, wrong thing is being **calculated** – fineTuneFork Jun 21 '12 at 15:32
I already read that question and that's why I've also used getCalculateValue. You suggest I don't use it? – bikey77 Jun 21 '12 at 15:33
Yup, in your case not using it will be the best thing. – fineTuneFork Jun 21 '12 at 15:38
2

@fineTuneFork - I disagree, using getValue() instead of getCalculatedValue() for the price column will be less than helpful... I'd rather know what's failing and fix it than get a formula string back and trying to cast it to a float (which will always give a 0.0 result) – Mark Baker Jun 21 '12 at 15:46
@fineTuneFork - Could you please justify your opinion? Maybe there's something I'm missing? – bikey77 Jun 21 '12 at 15:59
@MarkBaker - Could you please explain what you are suggesting in your 1st comment? – bikey77 Jun 21 '12 at 16:02
Your workbook contains a formula in column 6 ('G')... if you use getValue() to retrieve those cells, you'll get the formula back as a string, not the calculated result of that formula. You need to use getCalculatedValue() if you want the result. – Mark Baker Jun 21 '12 at 16:18
@bikey77 - I know you are, my comments were addressed to fineTuneFork whose advice to use getCellValue() and then cast it to a float, which I consider erroneous – Mark Baker Jun 21 '12 at 16:45
@MarkBaker Oh yes, that would be a big problem if the excel sheet actually contains 'formulas' instead of just 123,456 format of numbers. Bikey77 , if you are using calculated formulas then as mark pointed out getValue would certainly give 0.0 as the formula string is given back. – fineTuneFork Jun 21 '12 at 17:05
Note also that GetSQLValueString($val[0], "int")); is not a good idea when $val[0] is a string like "CH5150" – Mark Baker Jun 21 '12 at 17:07

score 0 · Answer 2 · answered Jun 21 '12 at 16:20

$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

PHPExcel getting errors reading decimal number values

2 Answers2