You are here

Home » API reference » Loft Data Grids 7.2 » Calculation.php » PHPExcel_Calculation

private function PHPExcel_Calculation::_parseFormula in Loft Data Grids 7.2

Same name and namespace in other branches
  1. 6.2 vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation.php \PHPExcel_Calculation::_parseFormula()
2 calls to PHPExcel_Calculation::_parseFormula()
PHPExcel_Calculation::parseFormula in vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation.php
* Validate and parse a formula string * *
PHPExcel_Calculation::_calculateFormulaValue in vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation.php
* Parse a cell formula and calculate its value * *

File

vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation.php, line 2740

Class

PHPExcel_Calculation
PHPExcel_Calculation (Multiton)

Code

private function _parseFormula($formula, PHPExcel_Cell $pCell = NULL) {
  if (($formula = $this
    ->_convertMatrixReferences(trim($formula))) === FALSE) {
    return FALSE;
  }

  //	If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent worksheet),
  //		so we store the parent worksheet so that we can re-attach it when necessary
  $pCellParent = $pCell !== NULL ? $pCell
    ->getWorksheet() : NULL;
  $regexpMatchString = '/^(' . self::CALCULATION_REGEXP_FUNCTION . '|' . self::CALCULATION_REGEXP_CELLREF . '|' . self::CALCULATION_REGEXP_NUMBER . '|' . self::CALCULATION_REGEXP_STRING . '|' . self::CALCULATION_REGEXP_OPENBRACE . '|' . self::CALCULATION_REGEXP_NAMEDRANGE . '|' . self::CALCULATION_REGEXP_ERROR . ')/si';

  //	Start with initialisation
  $index = 0;
  $stack = new PHPExcel_Calculation_Token_Stack();
  $output = array();
  $expectingOperator = FALSE;

  //	We use this test in syntax-checking the expression to determine when a
  //		- is a negation or + is a positive operator rather than an operation
  $expectingOperand = FALSE;

  //	We use this test in syntax-checking the expression to determine whether an operand
  //		should be null in a function call
  //	The guts of the lexical parser
  //	Loop through the formula extracting each operator and operand in turn
  while (TRUE) {

    //echo 'Assessing Expression '.substr($formula, $index),PHP_EOL;
    $opCharacter = $formula[$index];

    //	Get the first character of the value at the current index position

    //echo 'Initial character of expression block is '.$opCharacter,PHP_EOL;
    if (isset(self::$_comparisonOperators[$opCharacter]) && strlen($formula) > $index && isset(self::$_comparisonOperators[$formula[$index + 1]])) {
      $opCharacter .= $formula[++$index];

      //echo 'Initial character of expression block is comparison operator '.$opCharacter.PHP_EOL;
    }

    //	Find out if we're currently at the beginning of a number, variable, cell reference, function, parenthesis or operand
    $isOperandOrFunction = preg_match($regexpMatchString, substr($formula, $index), $match);

    //echo '$isOperandOrFunction is '.(($isOperandOrFunction) ? 'True' : 'False').PHP_EOL;

    //var_dump($match);
    if ($opCharacter == '-' && !$expectingOperator) {

      //	Is it a negation instead of a minus?

      //echo 'Element is a Negation operator',PHP_EOL;
      $stack
        ->push('Unary Operator', '~');

      //	Put a negation on the stack
      ++$index;

      //		and drop the negation symbol
    }
    elseif ($opCharacter == '%' && $expectingOperator) {

      //echo 'Element is a Percentage operator',PHP_EOL;
      $stack
        ->push('Unary Operator', '%');

      //	Put a percentage on the stack
      ++$index;
    }
    elseif ($opCharacter == '+' && !$expectingOperator) {

      //	Positive (unary plus rather than binary operator plus) can be discarded?

      //echo 'Element is a Positive number, not Plus operator',PHP_EOL;
      ++$index;

      //	Drop the redundant plus symbol
    }
    elseif (($opCharacter == '~' || $opCharacter == '|') && !$isOperandOrFunction) {

      //	We have to explicitly deny a tilde or pipe, because they are legal
      return $this
        ->_raiseFormulaError("Formula Error: Illegal character '~'");

      //		on the stack but not in the input expression
    }
    elseif ((isset(self::$_operators[$opCharacter]) or $isOperandOrFunction) && $expectingOperator) {

      //	Are we putting an operator on the stack?

      //echo 'Element with value '.$opCharacter.' is an Operator',PHP_EOL;
      while ($stack
        ->count() > 0 && ($o2 = $stack
        ->last()) && isset(self::$_operators[$o2['value']]) && @(self::$_operatorAssociativity[$opCharacter] ? self::$_operatorPrecedence[$opCharacter] < self::$_operatorPrecedence[$o2['value']] : self::$_operatorPrecedence[$opCharacter] <= self::$_operatorPrecedence[$o2['value']])) {
        $output[] = $stack
          ->pop();

        //	Swap operands and higher precedence operators from the stack to the output
      }
      $stack
        ->push('Binary Operator', $opCharacter);

      //	Finally put our current operator onto the stack
      ++$index;
      $expectingOperator = FALSE;
    }
    elseif ($opCharacter == ')' && $expectingOperator) {

      //	Are we expecting to close a parenthesis?

      //echo 'Element is a Closing bracket',PHP_EOL;
      $expectingOperand = FALSE;
      while (($o2 = $stack
        ->pop()) && $o2['value'] != '(') {

        //	Pop off the stack back to the last (
        if ($o2 === NULL) {
          return $this
            ->_raiseFormulaError('Formula Error: Unexpected closing brace ")"');
        }
        else {
          $output[] = $o2;
        }
      }
      $d = $stack
        ->last(2);
      if (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $d['value'], $matches)) {

        //	Did this parenthesis just close a function?
        $functionName = $matches[1];

        //	Get the function name

        //echo 'Closed Function is '.$functionName,PHP_EOL;
        $d = $stack
          ->pop();
        $argumentCount = $d['value'];

        //	See how many arguments there were (argument count is the next value stored on the stack)

        //if ($argumentCount == 0) {

        //	echo 'With no arguments',PHP_EOL;

        //} elseif ($argumentCount == 1) {

        //	echo 'With 1 argument',PHP_EOL;

        //} else {

        //	echo 'With '.$argumentCount.' arguments',PHP_EOL;

        //}
        $output[] = $d;

        //	Dump the argument count on the output
        $output[] = $stack
          ->pop();

        //	Pop the function and push onto the output
        if (isset(self::$_controlFunctions[$functionName])) {

          //echo 'Built-in function '.$functionName,PHP_EOL;
          $expectedArgumentCount = self::$_controlFunctions[$functionName]['argumentCount'];
          $functionCall = self::$_controlFunctions[$functionName]['functionCall'];
        }
        elseif (isset(self::$_PHPExcelFunctions[$functionName])) {

          //echo 'PHPExcel function '.$functionName,PHP_EOL;
          $expectedArgumentCount = self::$_PHPExcelFunctions[$functionName]['argumentCount'];
          $functionCall = self::$_PHPExcelFunctions[$functionName]['functionCall'];
        }
        else {

          // did we somehow push a non-function on the stack? this should never happen
          return $this
            ->_raiseFormulaError("Formula Error: Internal error, non-function on stack");
        }

        //	Check the argument count
        $argumentCountError = FALSE;
        if (is_numeric($expectedArgumentCount)) {
          if ($expectedArgumentCount < 0) {

            //echo '$expectedArgumentCount is between 0 and '.abs($expectedArgumentCount),PHP_EOL;
            if ($argumentCount > abs($expectedArgumentCount)) {
              $argumentCountError = TRUE;
              $expectedArgumentCountString = 'no more than ' . abs($expectedArgumentCount);
            }
          }
          else {

            //echo '$expectedArgumentCount is numeric '.$expectedArgumentCount,PHP_EOL;
            if ($argumentCount != $expectedArgumentCount) {
              $argumentCountError = TRUE;
              $expectedArgumentCountString = $expectedArgumentCount;
            }
          }
        }
        elseif ($expectedArgumentCount != '*') {
          $isOperandOrFunction = preg_match('/(\\d*)([-+,])(\\d*)/', $expectedArgumentCount, $argMatch);

          //print_r($argMatch);

          //echo PHP_EOL;
          switch ($argMatch[2]) {
            case '+':
              if ($argumentCount < $argMatch[1]) {
                $argumentCountError = TRUE;
                $expectedArgumentCountString = $argMatch[1] . ' or more ';
              }
              break;
            case '-':
              if ($argumentCount < $argMatch[1] || $argumentCount > $argMatch[3]) {
                $argumentCountError = TRUE;
                $expectedArgumentCountString = 'between ' . $argMatch[1] . ' and ' . $argMatch[3];
              }
              break;
            case ',':
              if ($argumentCount != $argMatch[1] && $argumentCount != $argMatch[3]) {
                $argumentCountError = TRUE;
                $expectedArgumentCountString = 'either ' . $argMatch[1] . ' or ' . $argMatch[3];
              }
              break;
          }
        }
        if ($argumentCountError) {
          return $this
            ->_raiseFormulaError("Formula Error: Wrong number of arguments for {$functionName}() function: {$argumentCount} given, " . $expectedArgumentCountString . " expected");
        }
      }
      ++$index;
    }
    elseif ($opCharacter == ',') {

      //	Is this the separator for function arguments?

      //echo 'Element is a Function argument separator',PHP_EOL;
      while (($o2 = $stack
        ->pop()) && $o2['value'] != '(') {

        //	Pop off the stack back to the last (
        if ($o2 === NULL) {
          return $this
            ->_raiseFormulaError("Formula Error: Unexpected ,");
        }
        else {
          $output[] = $o2;
        }

        // pop the argument expression stuff and push onto the output
      }

      //	If we've a comma when we're expecting an operand, then what we actually have is a null operand;
      //		so push a null onto the stack
      if ($expectingOperand || !$expectingOperator) {
        $output[] = array(
          'type' => 'NULL Value',
          'value' => self::$_ExcelConstants['NULL'],
          'reference' => NULL,
        );
      }

      // make sure there was a function
      $d = $stack
        ->last(2);
      if (!preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $d['value'], $matches)) {
        return $this
          ->_raiseFormulaError("Formula Error: Unexpected ,");
      }
      $d = $stack
        ->pop();
      $stack
        ->push($d['type'], ++$d['value'], $d['reference']);

      // increment the argument count
      $stack
        ->push('Brace', '(');

      // put the ( back on, we'll need to pop back to it again
      $expectingOperator = FALSE;
      $expectingOperand = TRUE;
      ++$index;
    }
    elseif ($opCharacter == '(' && !$expectingOperator) {

      //				echo 'Element is an Opening Bracket<br />';
      $stack
        ->push('Brace', '(');
      ++$index;
    }
    elseif ($isOperandOrFunction && !$expectingOperator) {

      // do we now have a function/variable/number?
      $expectingOperator = TRUE;
      $expectingOperand = FALSE;
      $val = $match[1];
      $length = strlen($val);

      //				echo 'Element with value '.$val.' is an Operand, Variable, Constant, String, Number, Cell Reference or Function<br />';
      if (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $val, $matches)) {
        $val = preg_replace('/\\s/u', '', $val);

        //					echo 'Element '.$val.' is a Function<br />';
        if (isset(self::$_PHPExcelFunctions[strtoupper($matches[1])]) || isset(self::$_controlFunctions[strtoupper($matches[1])])) {

          // it's a function
          $stack
            ->push('Function', strtoupper($val));
          $ax = preg_match('/^\\s*(\\s*\\))/ui', substr($formula, $index + $length), $amatch);
          if ($ax) {
            $stack
              ->push('Operand Count for Function ' . strtoupper($val) . ')', 0);
            $expectingOperator = TRUE;
          }
          else {
            $stack
              ->push('Operand Count for Function ' . strtoupper($val) . ')', 1);
            $expectingOperator = FALSE;
          }
          $stack
            ->push('Brace', '(');
        }
        else {

          // it's a var w/ implicit multiplication
          $output[] = array(
            'type' => 'Value',
            'value' => $matches[1],
            'reference' => NULL,
          );
        }
      }
      elseif (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $val, $matches)) {

        //					echo 'Element '.$val.' is a Cell reference<br />';
        //	Watch for this case-change when modifying to allow cell references in different worksheets...
        //	Should only be applied to the actual cell column, not the worksheet name
        //	If the last entry on the stack was a : operator, then we have a cell range reference
        $testPrevOp = $stack
          ->last(1);
        if ($testPrevOp['value'] == ':') {

          //	If we have a worksheet reference, then we're playing with a 3D reference
          if ($matches[2] == '') {

            //	Otherwise, we 'inherit' the worksheet reference from the start cell reference
            //	The start of the cell range reference should be the last entry in $output
            $startCellRef = $output[count($output) - 1]['value'];
            preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $startCellRef, $startMatches);
            if ($startMatches[2] > '') {
              $val = $startMatches[2] . '!' . $val;
            }
          }
          else {
            return $this
              ->_raiseFormulaError("3D Range references are not yet supported");
          }
        }
        $output[] = array(
          'type' => 'Cell Reference',
          'value' => $val,
          'reference' => $val,
        );

        //					$expectingOperator = FALSE;
      }
      else {

        // it's a variable, constant, string, number or boolean
        //					echo 'Element is a Variable, Constant, String, Number or Boolean<br />';
        //	If the last entry on the stack was a : operator, then we may have a row or column range reference
        $testPrevOp = $stack
          ->last(1);
        if ($testPrevOp['value'] == ':') {
          $startRowColRef = $output[count($output) - 1]['value'];
          $rangeWS1 = '';
          if (strpos('!', $startRowColRef) !== FALSE) {
            list($rangeWS1, $startRowColRef) = explode('!', $startRowColRef);
          }
          if ($rangeWS1 != '') {
            $rangeWS1 .= '!';
          }
          $rangeWS2 = $rangeWS1;
          if (strpos('!', $val) !== FALSE) {
            list($rangeWS2, $val) = explode('!', $val);
          }
          if ($rangeWS2 != '') {
            $rangeWS2 .= '!';
          }
          if (is_integer($startRowColRef) && ctype_digit($val) && $startRowColRef <= 1048576 && $val <= 1048576) {

            //	Row range
            $endRowColRef = $pCellParent !== NULL ? $pCellParent
              ->getHighestColumn() : 'XFD';

            //	Max 16,384 columns for Excel2007
            $output[count($output) - 1]['value'] = $rangeWS1 . 'A' . $startRowColRef;
            $val = $rangeWS2 . $endRowColRef . $val;
          }
          elseif (ctype_alpha($startRowColRef) && ctype_alpha($val) && strlen($startRowColRef) <= 3 && strlen($val) <= 3) {

            //	Column range
            $endRowColRef = $pCellParent !== NULL ? $pCellParent
              ->getHighestRow() : 1048576;

            //	Max 1,048,576 rows for Excel2007
            $output[count($output) - 1]['value'] = $rangeWS1 . strtoupper($startRowColRef) . '1';
            $val = $rangeWS2 . $val . $endRowColRef;
          }
        }
        $localeConstant = FALSE;
        if ($opCharacter == '"') {

          //						echo 'Element is a String<br />';
          //	UnEscape any quotes within the string
          $val = self::_wrapResult(str_replace('""', '"', self::_unwrapResult($val)));
        }
        elseif (is_numeric($val)) {

          //						echo 'Element is a Number<br />';
          if (strpos($val, '.') !== FALSE || stripos($val, 'e') !== FALSE || $val > PHP_INT_MAX || $val < -PHP_INT_MAX) {

            //							echo 'Casting '.$val.' to float<br />';
            $val = (double) $val;
          }
          else {

            //							echo 'Casting '.$val.' to integer<br />';
            $val = (int) $val;
          }
        }
        elseif (isset(self::$_ExcelConstants[trim(strtoupper($val))])) {
          $excelConstant = trim(strtoupper($val));

          //						echo 'Element '.$excelConstant.' is an Excel Constant<br />';
          $val = self::$_ExcelConstants[$excelConstant];
        }
        elseif (($localeConstant = array_search(trim(strtoupper($val)), self::$_localeBoolean)) !== FALSE) {

          //						echo 'Element '.$localeConstant.' is an Excel Constant<br />';
          $val = self::$_ExcelConstants[$localeConstant];
        }
        $details = array(
          'type' => 'Value',
          'value' => $val,
          'reference' => NULL,
        );
        if ($localeConstant) {
          $details['localeValue'] = $localeConstant;
        }
        $output[] = $details;
      }
      $index += $length;
    }
    elseif ($opCharacter == '$') {

      // absolute row or column range
      ++$index;
    }
    elseif ($opCharacter == ')') {

      // miscellaneous error checking
      if ($expectingOperand) {
        $output[] = array(
          'type' => 'NULL Value',
          'value' => self::$_ExcelConstants['NULL'],
          'reference' => NULL,
        );
        $expectingOperand = FALSE;
        $expectingOperator = TRUE;
      }
      else {
        return $this
          ->_raiseFormulaError("Formula Error: Unexpected ')'");
      }
    }
    elseif (isset(self::$_operators[$opCharacter]) && !$expectingOperator) {
      return $this
        ->_raiseFormulaError("Formula Error: Unexpected operator '{$opCharacter}'");
    }
    else {

      // I don't even want to know what you did to get here
      return $this
        ->_raiseFormulaError("Formula Error: An unexpected error occured");
    }

    //	Test for end of formula string
    if ($index == strlen($formula)) {

      //	Did we end with an operator?.
      //	Only valid for the % unary operator
      if (isset(self::$_operators[$opCharacter]) && $opCharacter != '%') {
        return $this
          ->_raiseFormulaError("Formula Error: Operator '{$opCharacter}' has no operands");
      }
      else {
        break;
      }
    }

    //	Ignore white space
    while ($formula[$index] == "\n" || $formula[$index] == "\r") {
      ++$index;
    }
    if ($formula[$index] == ' ') {
      while ($formula[$index] == ' ') {
        ++$index;
      }

      //	If we're expecting an operator, but only have a space between the previous and next operands (and both are
      //		Cell References) then we have an INTERSECTION operator
      //				echo 'Possible Intersect Operator<br />';
      if ($expectingOperator && preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '.*/Ui', substr($formula, $index), $match) && $output[count($output) - 1]['type'] == 'Cell Reference') {

        //					echo 'Element is an Intersect Operator<br />';
        while ($stack
          ->count() > 0 && ($o2 = $stack
          ->last()) && isset(self::$_operators[$o2['value']]) && @(self::$_operatorAssociativity[$opCharacter] ? self::$_operatorPrecedence[$opCharacter] < self::$_operatorPrecedence[$o2['value']] : self::$_operatorPrecedence[$opCharacter] <= self::$_operatorPrecedence[$o2['value']])) {
          $output[] = $stack
            ->pop();

          //	Swap operands and higher precedence operators from the stack to the output
        }
        $stack
          ->push('Binary Operator', '|');

        //	Put an Intersect Operator on the stack
        $expectingOperator = FALSE;
      }
    }
  }
  while (($op = $stack
    ->pop()) !== NULL) {

    // pop everything off the stack and push onto output
    if (is_array($op) && $op['value'] == '(' || $op === '(') {
      return $this
        ->_raiseFormulaError("Formula Error: Expecting ')'");
    }

    // if there are any opening braces on the stack, then braces were unbalanced
    $output[] = $op;
  }
  return $output;
}