Coordinator
Aug 26, 2009 at 9:42 AM
Edited Aug 26, 2009 at 9: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
)
