CODEWITHSUNDEEP
phpexcel
First Last
First Last

Reputation: 133

PHPExcel getCalculatedValue() causing #REF

I'm using getCalculatedValue() in my PHPExcel to read 34 Excel files. Unfortunately there were some users who put i:\drive\test[month.xls]$C$1 in the cell causing PHPExcel unable to read and store #REF! in the mysql.

I know that getCalculatedValue() will read formula in a cell, but how about link? Somemore, the link found in the Excel files are external link, which I couldn't access.

Although the cell contain links to another worksheet, but on the cell itself displaying some value. The best solution is to copy all and paste value. But this seem not practical to open 34 Excel files for this purpose.

I appreciate someone can enlighten me.

Thanks.

Upvotes: 2

Views: 4549

Answers (3)

zoorz
zoorz

Reputation: 15

@Mark Baker thanks so much for the answer. It's real useful for the link found in the Excel files are external link.addtional for myself.

$cell = $activeSheet->getCellByColumnAndRow($colIndex,$rowIndex)->getValue();
                    if($cell instanceof PHPExcel_RichText)     //richText with color etc 
                        $cell = $cell->__toString();  
                    if(substr($cell,0,1)=='='){ //with fomula
                        try{
                            if($activeSheet->getCellByColumnAndRow($colIndex,$rowIndex)->getCalculatedValue() == '#REF!' || $activeSheet->getCellByColumnAndRow($colIndex,$rowIndex)->getCalculatedValue() == '#VALUE!')
                            {
                                $cell = $activeSheet->getCellByColumnAndRow($colIndex,$rowIndex)->getOldCalculatedValue();
                            }
                                else
                            {
                                $cell= $activeSheet->getCellByColumnAndRow($colIndex,$rowIndex)->getCalculatedValue();
                            }
                        }catch(Exception $e){
                            $cell = $activeSheet->getCellByColumnAndRow($colIndex,$rowIndex)->getOldCalculatedValue();
                        }
}

I hope it will by useful!

Upvotes: 0

First Last
First Last

Reputation: 133

Ok done, but the solution is not that clean and neat. I'm using Try method, which the code can be seen as follows:

try{
if($objPHPExcel->setActiveSheetIndexByName($caRef)->getCell($cells)->getCalculatedValue() == '#REF!' || $objPHPExcel->setActiveSheetIndexByName($caRef)->getCell($cells)->getCalculatedValue() == '#VALUE!')
{
    $value = $objPHPExcel->setActiveSheetIndexByName($caRef)->getCell($cells)->getOldCalculatedValue();
}
    else
{
    $value = $objPHPExcel->setActiveSheetIndexByName($caRef)->getCell($cells)->getCalculatedValue();
}

}

As you can see, I'm not using Catch and Throw, as I really want PHPExcel to grab the value and put it in the MySQl.However, this really working although is not neat and tidy script.

Upvotes: 1

Mark Baker
Mark Baker

Reputation: 212452

The latest SVN code for PHPExcel will throw an exception if it encounters a reference to an external workbook. You can wrap your getCalculatedValue() call in a try/catch block, and if this exception is thrown use getOldCalculatedValue() to retrieve the result from when MS Excel itself last calculated this cell's value.

A #REF! response may be a perfectly valid response from a formula, so it isn't adequate simply to trap for that, which is why the Exception is thrown in this particular circumstance.

Note that this is not guaranteed - it is possible to disable automatic calculation in Excel, or for the referenced external workbooks to have been modified since the workbook you are reading was saved - in both these cases, getOldCalculatedValue() may return an erroneous result, and you have no way of knowing if this is the case... but it's generally pretty accurate. The SVN code modifications were for precisely this reason, when I had the same situation for a client that I was working with back in December; and the code will be included within the 1.7.7 production release of PHPExcel that should be available by the end of this week.

EDIT

try {
    $calculatedValue = $objPHPExcel->getActiveSheet()->getCell('A1')->getCalculatedValue();
} catch (Exception $e) {
    $calculatedValue = $objPHPExcel->getActiveSheet()->getCell('A1')->getOldCalculatedValue();
}

Upvotes: 5

Related Questions