Named Range not ok in .xls but ok in .xlsx 1.7.2

Topics: Developer Forum
Jan 14, 2012 at 7:39 PM

Bug: #REF! error when trying to calculate the value of a formula with a named reference.  Ok in .XLSX, but not .XLS.

PHP 5.3.8, PHPExcel 1.7.6, Windows XP and CentOS

In this note:

trace from xls

trace from xlsx

php source code to produce

 

I'll attach an Excel file named PHPExcelBugNamedRange.xls and one named PHPExcelBugNamedRange.xlsx.

with XLS not happy:
trace output from testformula()  (a function I found posted here somewhere...)

> php PHPExcel_176_BugNamedRange.php PHPExcelBugNamedRange.xls
PETest test1

<b>A20 Value is </b>=EXP(-mu*(1-rho)*(7/60))<br />
<b>A20 Expected Value is </b>0.3114032239146<br />
<b>Parser Stack :-</b><pre>Array
(
    [0] => Array
        (
            [type] => Value
            [value] => mu
            [reference] =>
        )

    [1] => Array
        (
            [type] => Unary Operator
            [value] => ~
            [reference] =>
        )

    [2] => Array
        (
            [type] => Value
            [value] => 1
            [reference] =>
        )

    [3] => Array
        (
            [type] => Value
            [value] => rho
            [reference] =>
        )

    [4] => Array
        (
            [type] => Binary Operator
            [value] => -
            [reference] =>
        )

    [5] => Array
        (
            [type] => Binary Operator
            [value] => *
            [reference] =>
        )

    [6] => Array
        (
            [type] => Value
            [value] => 7
            [reference] =>
        )

    [7] => Array
        (
            [type] => Value
            [value] => 60
            [reference] =>
        )

    [8] => Array
        (
            [type] => Binary Operator
            [value] => /
            [reference] =>
        )

    [9] => Array
        (
            [type] => Binary Operator
            [value] => *
            [reference] =>
        )

    [10] => Array
        (
            [type] => Operand Count for Function EXP()
            [value] => 1
            [reference] =>
        )

    [11] => Array
        (
            [type] => Function
            [value] => EXP(
            [reference] =>
        )

)
</pre>
Warning: exp() expects parameter 1 to be double, string given in C:\Documents and Settings\Brent\My Documents\data\proje
cts\Chexel 10\PHPExcel\c10\harness\PHPExcel\Calculation.php on line 3343
<b>A20 Calculated Value is </b>0<br />
<h3>Evaluation Log:</h3><pre>Array
(
    [0] => DS11-10ab!A20 -> Evaluating Named Range mu
    [1] => DS11-10ab!A20 -> Evaluation Result for named range mu is a #REF! error
    [2] => DS11-10ab!A20 -> Evaluating Negation of #REF!
    [3] => DS11-10ab!A20 -> Evaluation Result is a #REF! error
    [4] => DS11-10ab!A20 -> Evaluating Named Range rho
    [5] => DS11-10ab!A20 -> Evaluation Result for named range rho is a #REF! error
    [6] => DS11-10ab!A20 -> Evaluating 1 - #REF!
    [7] => DS11-10ab!A20 -> Evaluation Result is a #REF! error
    [8] => DS11-10ab!A20 -> Evaluating #REF! * #REF!
    [9] => DS11-10ab!A20 -> Evaluation Result is a #REF! error
    [10] => DS11-10ab!A20 -> Evaluating 7 / 60
    [11] => DS11-10ab!A20 -> Evaluation Result is a floating point number with a value of 0.11666666666667
    [12] => DS11-10ab!A20 -> Evaluating #REF! * 0.11666666666667
    [13] => DS11-10ab!A20 -> Evaluation Result is a #REF! error
    [14] => DS11-10ab!A20 -> Evaluating Function EXP() with 1 argument
    [15] => DS11-10ab!A20 -> Evaluating EXP( #REF! )
    [16] => DS11-10ab!A20 -> Evaluation Result for EXP() function call is a NULL value
)
</pre>
>



with XLSX happy

<b>A20 Value is </b>=EXP(-mu * (1-rho) * (7/60))<br />
<b>A20 Expected Value is </b>0.31140322391459768<br />
<b>Parser Stack :-</b><pre>Array
(
    [0] => Array
        (
            [type] => Value
            [value] => mu
            [reference] =>
        )

    [1] => Array
        (
            [type] => Unary Operator
            [value] => ~
            [reference] =>
        )

    [2] => Array
        (
            [type] => Value
            [value] => 1
            [reference] =>
        )

    [3] => Array
        (
            [type] => Value
            [value] => rho
            [reference] =>
        )

    [4] => Array
        (
            [type] => Binary Operator
            [value] => -
            [reference] =>
        )

    [5] => Array
        (
            [type] => Binary Operator
            [value] => *
            [reference] =>
        )

    [6] => Array
        (
            [type] => Value
            [value] => 7
            [reference] =>
        )

    [7] => Array
        (
            [type] => Value
            [value] => 60
            [reference] =>
        )

    [8] => Array
        (
            [type] => Binary Operator
            [value] => /
            [reference] =>
        )

    [9] => Array
        (
            [type] => Binary Operator
            [value] => *
            [reference] =>
        )

    [10] => Array
        (
            [type] => Operand Count for Function EXP()
            [value] => 1
            [reference] =>
        )

    [11] => Array
        (
            [type] => Function
            [value] => EXP(
            [reference] =>
        )

)
</pre><b>A20 Calculated Value is </b>0.3114032239146<br />
<h3>Evaluation Log:</h3><pre>Array
(
    [0] => DS11-10ab!A20 -> Evaluating Named Range mu
    [1] => DS11-10ab!A20 -> Evaluation Result for named range A5 is a floating point number with a value of 40
    [2] => DS11-10ab!A20 -> Evaluating Negation of 40
    [3] => DS11-10ab!A20 -> Evaluation Result is a floating point number with a value of -40
    [4] => DS11-10ab!A20 -> Evaluating Named Range rho
    [5] => DS11-10ab!A20 -> DS11-10ab!A6 -> Evaluating Named Range lambda
    [6] => DS11-10ab!A20 -> DS11-10ab!A6 -> Evaluation Result for named range A4 is a floating point number with a value
 of 30
    [7] => DS11-10ab!A20 -> DS11-10ab!A6 -> Evaluating Named Range mu
    [8] => DS11-10ab!A20 -> DS11-10ab!A6 -> Evaluation Result for named range A5 is a floating point number with a value
 of 40
    [9] => DS11-10ab!A20 -> DS11-10ab!A6 -> Evaluating 30 / 40
    [10] => DS11-10ab!A20 -> DS11-10ab!A6 -> Evaluation Result is a floating point number with a value of 0.75
    [11] => DS11-10ab!A20 -> Evaluation Result for named range A6 is a floating point number with a value of 0.75
    [12] => DS11-10ab!A20 -> Evaluating 1 - 0.75
    [13] => DS11-10ab!A20 -> Evaluation Result is a floating point number with a value of 0.25
    [14] => DS11-10ab!A20 -> Evaluating -40 * 0.25
    [15] => DS11-10ab!A20 -> Evaluation Result is a floating point number with a value of -10
    [16] => DS11-10ab!A20 -> Evaluating 7 / 60
    [17] => DS11-10ab!A20 -> Evaluation Result is a floating point number with a value of 0.11666666666667
    [18] => DS11-10ab!A20 -> Evaluating -10 * 0.11666666666667
    [19] => DS11-10ab!A20 -> Evaluation Result is a floating point number with a value of -1.1666666666667
    [20] => DS11-10ab!A20 -> Evaluating Function EXP() with 1 argument
    [21] => DS11-10ab!A20 -> Evaluating EXP( -1.1666666666667 )
    [22] => DS11-10ab!A20 -> Evaluation Result for EXP() function call is a floating point number with a value of 0.3114
032239146
)
</pre>

 

<?php
/**
 * PHPExcel_176_BugNamedRange.php
 *
 * @author Brent Reeves <brent@twinbear.com>
 * tests PHPExcel named range parsing / calculating
 * seems to parse ok
 */
error_reporting(E_ALL);

Class PETest
{
  private $CLASSDIR = '';

  public function test1($aCell, $aSheetName, $aFileName)
  {
    print ("PETest test1");
    require_once ($this->CLASSDIR . 'PHPExcel.php');
    $wkb = $this->readExcelFile($aFileName);
    $wks = $wkb->getSheetByName($aSheetName);
   
    PHPExcel_Calculation::getInstance()->writeDebugLog = true;
   
    $this->testFormula($wks,$aCell);
  }

  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>';
      }
    }
  }
 
  function readExcelFile($file) {
    $px = new PHPExcel();
    try {
      $px = PHPExcel_IOFactory::load($file);
    } catch (Exception $e) {
      print "IOFactory::load had trouble: ". $e->getMessage();
      return NULL;
    }
    PHPExcel_Calculation::getInstance()->clearCalculationCache();
    return $px;
  }
}

$o1 = new PETest();
if (!empty($argc) && $argc > 1) {
  $filename  = $argv[1];
} else {
  $filename  = "PHPExcelBugNamedRange.xls";
}

$o1->test1('A20', "DS11-10ab", $filename)

?>

Coordinator
Jan 15, 2012 at 11:47 AM
Edited Jan 15, 2012 at 12:11 PM

Problem in the Excel5 Reader when the named range sheet reference is wrapped in quotes... PHPExcel assumes sheetname itself includes the quotes.

 

Thanks for the detailed explanation of the bug... the sample workbooks, together with the stack trace and debug information made it easy to replicate and isolate the problem. I've copied this to a Work Item, and uploaded a fix to the Excel5 Reader to the SVN repository.

Coordinator
Jan 15, 2012 at 11:48 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Jan 15, 2012 at 8:42 PM
Awesome - nice work - thank you.
-brent

On Sun, Jan 15, 2012 at 5:48 AM, MarkBaker <notifications@codeplex.com> wrote:

From: MarkBaker

This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.

Read the full discussion online.

To add a post to this discussion, reply to this email (PHPExcel@discussions.codeplex.com)

To start a new discussion for this project, email PHPExcel@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com