formula parser booleans and named references

Topics: Developer Forum
Apr 23, 2011 at 6:22 PM

The Excel 2007 reader I used miscalculated some cell values I extracted from an Excel file.
I cut the formulas into parts and it seems that the parser doesn't work well with named references (to other sheets) and booleans (if a formula returns TRUE/FALSE)

Am I right?

Coordinator
Apr 23, 2011 at 7:29 PM

Generally speaking, the parser should work perfectly well with named references to other sheets (though it won't work with references to other workbooks), and works perfectly well with booleans as well. Without knowing the specifics of your formula, it's very difficult to diagnose any problem. When debugging formulae, there's a simple function that I use to display the parser stack, and a detailed log of every step within the evaluation.

$sheet = $objPHPExcel->getActiveSheet();
PHPExcel_Calculation::getInstance()->writeDebugLog = true;

function testFormula($sheet,$cell) {
   $formulaValue = $sheet->getCell($cell)->getValue();
   echo '<b>'.$cell.' Value is </b>'.$formulaValue."<br />\n";
   $expectedValue = $sheet->getCell($cell)->getOldCalculatedValue();
   echo '<b>'.$cell.' Expected Value is </b>'.$expectedValue."<br />\n";

   $calculate = false;
   try {
      $tokens = PHPExcel_Calculation::getInstance()->parseFormula($formulaValue,$sheet->getCell($cell));
      echo '<b>Parser Stack :-</b><pre>';
      print_r($tokens);
      echo '</pre>';
      $calculate = true;
   } catch (Exception $e) {
      echo "PARSER ERROR: ".$e->getMessage()."<br />\n";
      echo '<b>Parser Stack :-</b><pre>';
      print_r($tokens);
      echo '</pre>';
   }
   if ($calculate) {
      try {
         $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>';
      } catch (Exception $e) {
         echo "CALCULATION ENGINE ERROR: ".$e->getMessage()."<br />\n";
         echo '<h3>Evaluation Log:</h3><pre>';
         print_r(PHPExcel_Calculation::getInstance()->debugLog);
         echo '</pre>';
      }
   }
}

testFormula($sheet,'A5');

to test the formula in cell A5 of $sheet.

 

Apr 25, 2011 at 7:36 AM
Edited Apr 25, 2011 at 8:14 AM

Thanks. Using this function I can actually pinpoint the error:

First the Reference:
[0] => SF.MCR_G!G13 -> Evaluating Cell D10 in current worksheet
    [1] => SF.MCR_G!G13 -> SF.MCR_G!D10 -> Evaluating Function INDIRECT() with 1 argument
    [2] => SF.MCR_G!G13 -> SF.MCR_G!D10 -> Evaluating INDIRECT( "SF.SCR_G!SCR" )
    [3] => SF.MCR_G!G13 -> SF.MCR_G!D10 -> Evaluation Result for INDIRECT() function call is a #REF! error

In Excel this isn't a #REF! error. It just gets the value from the cell named SCR on sheet SF.SCR_G.

Then the boolean, which is also caused by a reference error:

 [0] => SF.MCR_G!F13 -> Evaluating Cell D13 in current worksheet
    [1] => SF.MCR_G!F13 -> SF.MCR_G!D13 -> Evaluating Cell J100 in current worksheet
    [2] => SF.MCR_G!F13 -> SF.MCR_G!D13 -> SF.MCR_G!J100 -> Evaluating Cell J94 in current worksheet
    [3] => SF.MCR_G!F13 -> SF.MCR_G!D13 -> SF.MCR_G!J100 -> SF.MCR_G!J94 -> Evaluating Cell K94 in current worksheet
    [4] => SF.MCR_G!F13 -> SF.MCR_G!D13 -> SF.MCR_G!J100 -> SF.MCR_G!J94 -> Evaluation Result for cell SF.MCR_G!K94 is a string with a value of D.Dataset!Start
    [5] => SF.MCR_G!F13 -> SF.MCR_G!D13 -> SF.MCR_G!J100 -> SF.MCR_G!J94 -> Evaluating Function INDIRECT() with 1 argument
    [6] => SF.MCR_G!F13 -> SF.MCR_G!D13 -> SF.MCR_G!J100 -> SF.MCR_G!J94 -> Evaluating INDIRECT( D.Dataset!Start )
    [7] => SF.MCR_G!F13 -> SF.MCR_G!D13 -> SF.MCR_G!J100 -> SF.MCR_G!J94 -> Evaluation Result for INDIRECT() function call is a #REF! error
    [8] => SF.MCR_G!F13 -> SF.MCR_G!D13 -> SF.MCR_G!J100 -> SF.MCR_G!J94 -> Evaluating Function ISERROR() with 1 argument
    [9] => SF.MCR_G!F13 -> SF.MCR_G!D13 -> SF.MCR_G!J100 -> SF.MCR_G!J94 -> Evaluating ISERROR( #REF! )

The conclusion is: PHPExcel seems to be unable to evaluate an INDIRECT("SHEET!LOCALNAME") function. 

Coordinator
Apr 26, 2011 at 9:39 PM

I've checked the the code for the INDIRECT() function in PHPExcel, and it's only designed to work with cell references, not with named ranges: the values that you are passing through to INDIRECT() in these cases are Named Ranges. I'm working out the changes necessary in the code to handle a named range.

Coordinator
Apr 26, 2011 at 9:40 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Apr 29, 2011 at 5:42 PM

Fixed in SVN

Sep 14, 2011 at 4:15 PM
Edited Sep 14, 2011 at 4:16 PM

I have a possibly related issue with formulas and booleans.

If I simply set a cell as text "TRUE", the value results in a formula "=TRUE()".

This is how Excel seems to work too (?!), but PHPExcel will not set the cell's format code to text (@) either or seem able to override this in any other direct way.

Even if I load an existing XLS with the target cell set as @, PHPExcel will write the formula =TRUE() and set the format code to "General."

Sep 14, 2011 at 4:35 PM

I just discovered that if I simply...

setCellValueExplicit($cell, $data);
...in such cases, it will work just peachy.