Error when resolving formulas referencing other worksheets

Topics: Developer Forum
Apr 25, 2013 at 3:09 AM
Edited Apr 25, 2013 at 3:10 AM
Just for example, I have a sheet named "1" and a sheet named "2". In worksheet "2" there is a cell whose contents are the formula "='1'!F4".
$sheetData = $objPHPExcel->getActiveSheet()->getCell('F4')->getValue();
produces the following output:
string(5) "=1!F4" 
In addition, when I tried to load an entire sheet with
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
it initially gave me a Cyclical check, but then when I threw the following line into my code
PHPExcel_Calculation::getInstance()->cyclicFormulaCount = 1;
It would give me the following error (note, this is referring to worksheet '2' cell A2):
PHP Fatal error:  Uncaught exception 'PHPExcel_Exception' with message '2!A2 -> undefined variable '!'' in /var/www/Classes/PHPExcel/Cell.php:307
Stack trace:
#0 /var/www/Classes/PHPExcel/Worksheet.php(2415): PHPExcel_Cell->getCalculatedValue()
#1 /var/www/Classes/PHPExcel/Worksheet.php(2486): PHPExcel_Worksheet->rangeToArray('A1:AN180', NULL, true, true, true)
#2 /var/www/index.php(29): PHPExcel_Worksheet->toArray(NULL, true, true, true)
#3 {main}
  thrown in /var/www/Classes/PHPExcel/Cell.php on line 307
I'll add that I don't know what passing NULL, true, true, true to toArray does, but this same type of error (or correct functionality that I'm using incorrectly) occurs elsewhere, too.

As it stands, I have standardized spreadsheets so I can manually trace every reference and set the active sheet and cell and get the value, but.... that's a LOT of work when we have hundreds of cells referencing cells in other sheets that reference cells in other sheets that.....

Any help would be appreciated.

Apr 25, 2013 at 6:29 AM
So I've been working around the issue, and just ran into another, much like the 2nd block above. I have the following (I have sheets named 1-days of the month that I pull the same cell from on each):
for( $i=1;$i<=$daysInMonth;$i++ ) {
    echo $objPHPExcel->getActiveSheet()->getCell('D19')->getValue() . "<BR>";
and that returns "=SUM(D15+D18)" For every getValue(). I understand this is normal functionality and I'm just using it to illustrate the actual value of the cells. I then change the code to:
for( $i=1;$i<=$daysInMonth;$i++ ) {
    echo $objPHPExcel->getActiveSheet()->getCell('D19')->getCalculatedValue() . "<BR>";
Which is just swapping out getCalculatedValue() for getValue(). It outputs the first value correctly (because it doesn't reference a different sheet-it's the first of the month), which is 0, and then it errors out giving me:
[Thu Apr 25 01:12:42 2013] [error] [client] PHP Fatal error:  Uncaught exception 'PHPExcel_Exception' with message '2!D19 -> 2!D15 -> 2!D13 -> 2!B13 -> undefined variable '!'' in /var/www/Classes/PHPExcel/Cell.php:307
Stack trace:
#0 /var/www/index.php(42): PHPExcel_Cell->getCalculatedValue()
#1 {main}
  thrown in /var/www/Classes/PHPExcel/Cell.php on line 307
For clarity as to the D19 -> D15 -> D13 -> B13 -> undefined, D19 =SUM(D15+D18). D15 =SUM(D13-D14). D13 =SUM(C13-B13)*A15. C13=53454. B13 ='1'!C13. And that's where it errs out. There's got to be something I'm just overlooking
Apr 25, 2013 at 6:47 AM
Edited Apr 25, 2013 at 6:49 AM
Give your worksheets a name beginning with an alpha character, or modify the formulae so that the worksheet name is quoted (ie ='2'!A1) rather than =1!A1) and it should fix this problem.

And in the meanwhile, I'll look at a complete rewrite of the calculation engine so that it can handle purely numeric worksheet names. Memo to self, must modify regular expressions so that a number can't simply be treated as a number anymore, but requires the overhead of an additional context check.... please don't anybody complain about speed of execution once I've added that extra overhead in performance
Apr 25, 2013 at 7:06 AM
I was JUST able to come to the same conclusion. I found this page: that details the problem. I created my own spreadsheet with values with sheetnames both enclosed in 's and not, with and with spaces, just to make sure that wasn't the issue. Unfortunately, I can't change the sheet names, I'm just processing spreadsheets that are generated in bulk, daily, by an automated system.

Thanks so much for your response, but the formulas are already referencing the cell as shown in bold above with B13 ='1'!C13. Also, I look back at my output above, and see that getValue() has turned the actual value "='1'!F4" into string(5) "=1!F4" (I only include the data type to show it only has 5 characters in the string).

I thought about looking at how getCalculatedValue() works, but your code is so complex, I couldn't even find its definition. I'm going to have to do a recursive grep on the Classes directory.
Apr 25, 2013 at 7:31 AM
So I did me some grepping, and on line 2164 of Calculation.php, there is the line:
$result = self::_unwrapResult($this->_calculateFormulaValue($pCell->getValue(), $pCell->getCoordinate(), $pCell));
If referencing the cell on a different sheet by using the '1'!A1 format WOULD have worked, getting the formula's text with getValue() removed the 's, as shown above..... which just made me wonder if magic_quotes might have something to do with it. I'm burnt out for the night, so I'll mess with that tomorrow, just to rule it out.

I've gotta say, I've been walking through your code and it's beautiful.

I'm sure you already know this, but I tried to trace the issue through the code as far as I could, and essentially, _calculateFormulaValue is sending the value of $pCell to _parseFormula with getValue() which changes ='1'!A1 to =1!A1 (actually, it's 1!A1 by this point, but again, you know this) which then flows down to line 2905 of Calculation.php and hits } elseif (is_numeric($val)) { and it thinks it's a number instead of a sheet name, and then gets the next character which is a ! and it thinks it's a variable or something.