getCalculatedValue on formulas with name references

Topics: Developer Forum
Apr 15, 2010 at 6:02 PM
Edited Apr 15, 2010 at 6:05 PM

Hi there.

I couldn't getCalculatedValue on a cell wich had a formula that was referencing cells by names.

The question is: if ...

A1 has the rangeName = operand1

A2 has the rangeName = operand2

and A3 is '=operand1+operand2

then will the calculator return the calucalted value for that A3 cell?

I couldn't.

I think because the calculator doesn't read the correct value for a cell referenced by name instead of coordinates.

 

So I am using this function for outputting debug info:

 

    /*
     * Dumps the cell value, calculated value and processing
     * stack for a PHPExcel cell.
     *
     * @param PHPExcel_Worksheet $sheet
     * @param string $cell   e.g. 'A1', 'C3', etc.
     */
    function debugPHPExcelCell( $sheet, $cell )
	{
        //$sheet = $objPHPExcel->getActiveSheet();
        PHPExcel_Calculation::getInstance()->writeDebugLog = true;

        $cellValue = $sheet->getCell($cell)->getValue();
        echo '<b>'.$cell.' Value is </b>'.$cellValue."<br />\n";

        echo '<b>Parser Stack :-</b><pre>';
        $tokens = PHPExcel_Calculation::getInstance()->parseFormula($cellValue);
        print_r($tokens);
        echo '</pre>';

        $cellValue = $sheet->getCell($cell)->getCalculatedValue();
        echo '<b>'.$cell.' Calculated Value is </b>'.$cellValue."<br />\n";

        echo '<h3>Evaluation Log:</h3><pre>';
        print_r(PHPExcel_Calculation::getInstance()->debugLog);
        echo '</pre>';
    }

 

The script that reproduce the problem is like:

 

require_once( 'Excel/PHPExcel.php') ;
require_once( 'Excel/PHPExcel/IOFactory.php') ;
require_once( 'Excel/PHPExcel/NamedRange.php') ;
require_once( 'Excel/PHPExcel/Calculation.php') ;


// read the xls file input
$objPHPExcel = PHPExcel_IOFactory::load( "sum_cell_name.xls" );
// read the coordinates of the named cell
$namedRange1 = $objPHPExcel->getNamedRange( 'operand1' )  ;
$range1 = $namedRange1->getRange() ;

// read the coordinates of the named cell
$namedRange2 = $objPHPExcel->getNamedRange( 'operand2' )  ;
$range2 = $namedRange2->getRange() ;

// read the coordinates of the named cell: TOTAL
$namedRange3 = $objPHPExcel->getNamedRange( 'total' )  ;
$range3 = $namedRange3->getRange() ;



// retrieve the worksheet
$objPHPExcel->setActiveSheetIndex(0)  ;
$worksheet = $objPHPExcel->getActiveSheet();

// debug
debugPHPExcelCell(    $worksheet , $range3 )   ;

 

The autput will be:

Parser Stack :-

Array
(
[0] => Array
(
[type] => Cell Reference
[value] => operand1
[reference] => OPERAND1
)

[1] => Array
(
[type] => Cell Reference
[value] => operand2
[reference] => OPERAND2
)

[2] => Array
(
[type] => Binary Operator
[value] => +
[reference] =>
)

)

And this is the output of the log:

    [0] => C6 -> Evaluating Cell operand1 in current worksheet
[1] => C6 -> Evaluation Result for cell operand1 is a null value
[2] => C6 -> Evaluating Cell operand2 in current worksheet
[3] => C6 -> Evaluation Result for cell operand2 is a null value
[4] => C6 -> Evaluating +
[5] => C6 -> Evaluation Result is an integer number with a value of 0



I am using PHPExcel v.1.7.1
php 5.3.2
apache 2.2.15
windows XP SP3

 

Coordinator
Apr 15, 2010 at 10:01 PM

You're using version 1.7.2 or below, and the calculation engine in those versions interprets a series of letters followed by a numeric (e.g. OPERAND1 or OPERAND2) as a standard cell address, column OPERAND, row 1 or 2. This was identified as an error, and has been fixed in the latest SVN code, available currently through the daily downloads, and that will be part of the 1.7.3 release.

Alternatively, use a different name for your named range that doesn't fall foul of this error, such as OPERAND_1

 

Thanks for the detailed breakdown. What you did is exactly my first step when trying to identify a problem in the calculation engine, and it makes diagnosing problems a whole lot easier.

Apr 21, 2010 at 2:19 PM

Really thanks a lot.