Calculations Not Working

Topics: Developer Forum
Aug 25, 2009 at 11:58 PM

I've looked around and can't find anyone else having this problem so I'm hoping it's something simple that I'm doing wrong.  Here is my code to load a basic spreadsheet, check a calculated value, modify a field referenced in the calculation and then check the field again.  The result is no change.  Here is my code:

        $objPHPExcel = PHPExcel_IOFactory::load(APPLICATION_PATH . '/../data/pricesheets/test.xlsx');
        $objPHPExcel->setActiveSheetIndex(0);

        $this->view->infoMessage .= 'C2 (expecting 15):'.$objPHPExcel->getSheetByName('Test')->getCell('C2')->getCalculatedValue().'<br />';
        $objPHPExcel->getSheetByName('Test')->setCellValue('D2',3);
        $this->view->infoMessage .= 'C2 (expecting 13):'.$objPHPExcel->getSheetByName('Test')->getCell('C2')->getCalculatedValue().'<br />';

$this->view->infoMessage is my output stream.  

And here is the contents of the spreadsheet.  Very basic with a formula that adds 3 other cells together.  The calculated column contains a formula adding/multiplying the 3 following cells.

 

  Expected Calculated       Basic Addition 15 15 5 5 5 Basic Multiplication 125 125 5 5 5









Developer
Aug 26, 2009 at 12:24 AM

This is probably a caching problem within the calculation engine. Can you try to add this somewhere at the beginning of your script?

require_once 'PHPExcel/Calculation.php';
PHPExcel_Calculation::getInstance()->setCalculationCacheEnabled(false);

I'm not into the details of the calculation engine. Let's wait and see if MarkBaker comes across this post and explains further.

 

Aug 26, 2009 at 12:44 AM

It worked.   THANK YOU!

Aug 26, 2009 at 7:51 AM

OK, the next step is to evaluate formulas that include references to cells in other worksheets, but the result always seems to be empty/blank.  Is this not possible, or do I need to take an additional step?  

I've confirmed that referenced cell calculates properly within the context of it's own sheet. 

Coordinator
Aug 26, 2009 at 8:42 AM
Edited Aug 26, 2009 at 8:47 AM

PHPExcel's calculation engine shouldn't have any problems using cell references in a different sheet. A formula such as ='Sheet 1'!C1*5 is perfectly valid. However, there was a bug reported in the regular expression used to extract the cell references from a formula that could misread the reference if there were several sheet references in the formula (e.g. =MAX('Sheet 1'!C1,'Sheet 2'!C1)). That has been fixed in the latest SVN code

For diagnostic purposes, it might be useful to do something like:

PHPExcel_Calculation::getInstance()->writeDebugLog = true;
$cell = 'A1';
$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>'; 

This will display the parser stack in addition to a log of every step when evaluating the formula

The result should look something like:

A1 Value is =MAX('Sheet 1'!C1,'Sheet 2'!C1)
Parser Stack :-

Array
(
    [0] => Array
        (
            [type] => Cell Reference
            [value] => 'Sheet 1'!C1
        )

    [1] => Array
        (
            [type] => Cell Reference
            [value] => 'Sheet 2'!C1
        )

    [2] => Array
        (
            [type] => Operand Count for Function MAX()
            [value] => 2
        )

    [3] => Array
        (
            [type] => Function
            [value] => MAX(
        )

)

A1 Calculated Value is 10

Evaluation Log:

Array
(
    [0] => A1 -> Evaluating Cell C1 in worksheet Sheet 1
    [1] => A1 -> Evaluation Result for cell C1 in worksheet Sheet 1 is an integer number with a value of 5
    [2] => A1 -> Evaluating Cell C1 in worksheet Sheet 2
    [3] => A1 -> Evaluation Result for cell C1 in worksheet Sheet 2 is an integer number with a value of 10
    [4] => A1 -> Evaluating Function MAX() with 2 arguments
    [5] => A1 -> Evaluating MAX( 5, 10 )
    [6] => A1 -> Evaluation Result is an integer number with a value of 10
)

The 1.7.0 release code (without the bugfix for issue 10453) would instead look something like:

A1 Value is =MAX('Sheet 1'!C1,'Sheet 2'!C1)
Parser Stack :-

Array
(
    [0] => 'Sheet 1'!C1,'Sheet 2'!C1
    [1] => 1
    [2] => MAX(
)

A1 Calculated Value is 0

Evaluation Log:

Array
(
    [0] => A1 -> Evaluating Cell C1 in worksheet 'Sheet 1'!C1,'Sheet 2'
    [1] => A1 -> Evaluation Result for cell C1 in worksheet 'Sheet 1'!C1,'Sheet 2' is a matrix with a value of {  }
    [2] => A1 -> Evaluating Function MAX() with 1 argument
    [3] => A1 -> Evaluating MAX( {  } )
    [4] => A1 -> Evaluation Result is an integer number with a value of 0
)
Aug 27, 2009 at 8:44 PM

THANKS.  That helped a lot.  

The dump was miles long, probably because I have input/output sheet and then a separate sheet for the calcs that all reference back and forth for the calculations.

I've gone back and put everything on a single worksheet and everything works fine....and the dump is manageable.  I'll probably stick with that if I can.