private function PHPExcel_Calculation::_processTokenStack in Loft Data Grids 6.2

Same name and namespace in other branches

1. 7.2 vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation.php \PHPExcel_Calculation::_processTokenStack()

1 call to PHPExcel_Calculation::_processTokenStack()

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 3098

Class

PHPExcel_Calculation

PHPExcel_Calculation (Multiton)

Code

private function _processTokenStack($tokens, $cellID = NULL, PHPExcel_Cell $pCell = NULL) {
  if ($tokens == FALSE) {
    return FALSE;
  }

  //	If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent cell collection),
  //		so we store the parent cell collection so that we can re-attach it when necessary
  $pCellWorksheet = $pCell !== NULL ? $pCell
    ->getWorksheet() : NULL;
  $pCellParent = $pCell !== NULL ? $pCell
    ->getParent() : null;
  $stack = new PHPExcel_Calculation_Token_Stack();

  //	Loop through each token in turn
  foreach ($tokens as $tokenData) {

    //			print_r($tokenData);
    //			echo '<br />';
    $token = $tokenData['value'];

    //			echo '<b>Token is '.$token.'</b><br />';
    // if the token is a binary operator, pop the top two values off the stack, do the operation, and push the result back on the stack
    if (isset(self::$_binaryOperators[$token])) {

      //				echo 'Token is a binary operator<br />';
      //	We must have two operands, error if we don't
      if (($operand2Data = $stack
        ->pop()) === NULL) {
        return $this
          ->_raiseFormulaError('Internal error - Operand value missing from stack');
      }
      if (($operand1Data = $stack
        ->pop()) === NULL) {
        return $this
          ->_raiseFormulaError('Internal error - Operand value missing from stack');
      }
      $operand1 = self::_dataTestReference($operand1Data);
      $operand2 = self::_dataTestReference($operand2Data);

      //	Log what we're doing
      if ($token == ':') {
        $this->_debugLog
          ->writeDebugLog('Evaluating Range ', $this
          ->_showValue($operand1Data['reference']), ' ', $token, ' ', $this
          ->_showValue($operand2Data['reference']));
      }
      else {
        $this->_debugLog
          ->writeDebugLog('Evaluating ', $this
          ->_showValue($operand1), ' ', $token, ' ', $this
          ->_showValue($operand2));
      }

      //	Process the operation in the appropriate manner
      switch ($token) {

        //	Comparison (Boolean) Operators
        case '>':

        //	Greater than
        case '<':

        //	Less than
        case '>=':

        //	Greater than or Equal to
        case '<=':

        //	Less than or Equal to
        case '=':

        //	Equality
        case '<>':

          //	Inequality
          $this
            ->_executeBinaryComparisonOperation($cellID, $operand1, $operand2, $token, $stack);
          break;

        //	Binary Operators
        case ':':

          //	Range
          $sheet1 = $sheet2 = '';
          if (strpos($operand1Data['reference'], '!') !== FALSE) {
            list($sheet1, $operand1Data['reference']) = explode('!', $operand1Data['reference']);
          }
          else {
            $sheet1 = $pCellParent !== NULL ? $pCellWorksheet
              ->getTitle() : '';
          }
          if (strpos($operand2Data['reference'], '!') !== FALSE) {
            list($sheet2, $operand2Data['reference']) = explode('!', $operand2Data['reference']);
          }
          else {
            $sheet2 = $sheet1;
          }
          if ($sheet1 == $sheet2) {
            if ($operand1Data['reference'] === NULL) {
              if (trim($operand1Data['value']) != '' && is_numeric($operand1Data['value'])) {
                $operand1Data['reference'] = $pCell
                  ->getColumn() . $operand1Data['value'];
              }
              elseif (trim($operand1Data['reference']) == '') {
                $operand1Data['reference'] = $pCell
                  ->getCoordinate();
              }
              else {
                $operand1Data['reference'] = $operand1Data['value'] . $pCell
                  ->getRow();
              }
            }
            if ($operand2Data['reference'] === NULL) {
              if (trim($operand2Data['value']) != '' && is_numeric($operand2Data['value'])) {
                $operand2Data['reference'] = $pCell
                  ->getColumn() . $operand2Data['value'];
              }
              elseif (trim($operand2Data['reference']) == '') {
                $operand2Data['reference'] = $pCell
                  ->getCoordinate();
              }
              else {
                $operand2Data['reference'] = $operand2Data['value'] . $pCell
                  ->getRow();
              }
            }
            $oData = array_merge(explode(':', $operand1Data['reference']), explode(':', $operand2Data['reference']));
            $oCol = $oRow = array();
            foreach ($oData as $oDatum) {
              $oCR = PHPExcel_Cell::coordinateFromString($oDatum);
              $oCol[] = PHPExcel_Cell::columnIndexFromString($oCR[0]) - 1;
              $oRow[] = $oCR[1];
            }
            $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)) . min($oRow) . ':' . PHPExcel_Cell::stringFromColumnIndex(max($oCol)) . max($oRow);
            if ($pCellParent !== NULL) {
              $cellValue = $this
                ->extractCellRange($cellRef, $this->_workbook
                ->getSheetByName($sheet1), FALSE);
            }
            else {
              return $this
                ->_raiseFormulaError('Unable to access Cell Reference');
            }
            $stack
              ->push('Cell Reference', $cellValue, $cellRef);
          }
          else {
            $stack
              ->push('Error', PHPExcel_Calculation_Functions::REF(), NULL);
          }
          break;
        case '+':

          //	Addition
          $this
            ->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'plusEquals', $stack);
          break;
        case '-':

          //	Subtraction
          $this
            ->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'minusEquals', $stack);
          break;
        case '*':

          //	Multiplication
          $this
            ->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'arrayTimesEquals', $stack);
          break;
        case '/':

          //	Division
          $this
            ->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'arrayRightDivide', $stack);
          break;
        case '^':

          //	Exponential
          $this
            ->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'power', $stack);
          break;
        case '&':

          //	Concatenation
          //	If either of the operands is a matrix, we need to treat them both as matrices
          //		(converting the other operand to a matrix if need be); then perform the required
          //		matrix operation
          if (is_bool($operand1)) {
            $operand1 = $operand1 ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE'];
          }
          if (is_bool($operand2)) {
            $operand2 = $operand2 ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE'];
          }
          if (is_array($operand1) || is_array($operand2)) {

            //	Ensure that both operands are arrays/matrices
            self::_checkMatrixOperands($operand1, $operand2, 2);
            try {

              //	Convert operand 1 from a PHP array to a matrix
              $matrix = new PHPExcel_Shared_JAMA_Matrix($operand1);

              //	Perform the required operation against the operand 1 matrix, passing in operand 2
              $matrixResult = $matrix
                ->concat($operand2);
              $result = $matrixResult
                ->getArray();
            } catch (PHPExcel_Exception $ex) {
              $this->_debugLog
                ->writeDebugLog('JAMA Matrix Exception: ', $ex
                ->getMessage());
              $result = '#VALUE!';
            }
          }
          else {
            $result = '"' . str_replace('""', '"', self::_unwrapResult($operand1, '"') . self::_unwrapResult($operand2, '"')) . '"';
          }
          $this->_debugLog
            ->writeDebugLog('Evaluation Result is ', $this
            ->_showTypeDetails($result));
          $stack
            ->push('Value', $result);
          break;
        case '|':

          //	Intersect
          $rowIntersect = array_intersect_key($operand1, $operand2);
          $cellIntersect = $oCol = $oRow = array();
          foreach (array_keys($rowIntersect) as $row) {
            $oRow[] = $row;
            foreach ($rowIntersect[$row] as $col => $data) {
              $oCol[] = PHPExcel_Cell::columnIndexFromString($col) - 1;
              $cellIntersect[$row] = array_intersect_key($operand1[$row], $operand2[$row]);
            }
          }
          $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)) . min($oRow) . ':' . PHPExcel_Cell::stringFromColumnIndex(max($oCol)) . max($oRow);
          $this->_debugLog
            ->writeDebugLog('Evaluation Result is ', $this
            ->_showTypeDetails($cellIntersect));
          $stack
            ->push('Value', $cellIntersect, $cellRef);
          break;
      }

      // if the token is a unary operator, pop one value off the stack, do the operation, and push it back on
    }
    elseif ($token === '~' || $token === '%') {

      //				echo 'Token is a unary operator<br />';
      if (($arg = $stack
        ->pop()) === NULL) {
        return $this
          ->_raiseFormulaError('Internal error - Operand value missing from stack');
      }
      $arg = $arg['value'];
      if ($token === '~') {

        //					echo 'Token is a negation operator<br />';
        $this->_debugLog
          ->writeDebugLog('Evaluating Negation of ', $this
          ->_showValue($arg));
        $multiplier = -1;
      }
      else {

        //					echo 'Token is a percentile operator<br />';
        $this->_debugLog
          ->writeDebugLog('Evaluating Percentile of ', $this
          ->_showValue($arg));
        $multiplier = 0.01;
      }
      if (is_array($arg)) {
        self::_checkMatrixOperands($arg, $multiplier, 2);
        try {
          $matrix1 = new PHPExcel_Shared_JAMA_Matrix($arg);
          $matrixResult = $matrix1
            ->arrayTimesEquals($multiplier);
          $result = $matrixResult
            ->getArray();
        } catch (PHPExcel_Exception $ex) {
          $this->_debugLog
            ->writeDebugLog('JAMA Matrix Exception: ', $ex
            ->getMessage());
          $result = '#VALUE!';
        }
        $this->_debugLog
          ->writeDebugLog('Evaluation Result is ', $this
          ->_showTypeDetails($result));
        $stack
          ->push('Value', $result);
      }
      else {
        $this
          ->_executeNumericBinaryOperation($cellID, $multiplier, $arg, '*', 'arrayTimesEquals', $stack);
      }
    }
    elseif (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $token, $matches)) {
      $cellRef = NULL;

      //				echo 'Element '.$token.' is a Cell reference<br />';
      if (isset($matches[8])) {

        //					echo 'Reference is a Range of cells<br />';
        if ($pCell === NULL) {

          //						We can't access the range, so return a REF error
          $cellValue = PHPExcel_Calculation_Functions::REF();
        }
        else {
          $cellRef = $matches[6] . $matches[7] . ':' . $matches[9] . $matches[10];
          if ($matches[2] > '') {
            $matches[2] = trim($matches[2], "\"'");
            if (strpos($matches[2], '[') !== FALSE || strpos($matches[2], ']') !== FALSE) {

              //	It's a Reference to an external workbook (not currently supported)
              return $this
                ->_raiseFormulaError('Unable to access External Workbook');
            }
            $matches[2] = trim($matches[2], "\"'");

            //							echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />';
            $this->_debugLog
              ->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in worksheet ', $matches[2]);
            if ($pCellParent !== NULL) {
              $cellValue = $this
                ->extractCellRange($cellRef, $this->_workbook
                ->getSheetByName($matches[2]), FALSE);
            }
            else {
              return $this
                ->_raiseFormulaError('Unable to access Cell Reference');
            }
            $this->_debugLog
              ->writeDebugLog('Evaluation Result for cells ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this
              ->_showTypeDetails($cellValue));

            //							$cellRef = $matches[2].'!'.$cellRef;
          }
          else {

            //							echo '$cellRef='.$cellRef.' in current worksheet<br />';
            $this->_debugLog
              ->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in current worksheet');
            if ($pCellParent !== NULL) {
              $cellValue = $this
                ->extractCellRange($cellRef, $pCellWorksheet, FALSE);
            }
            else {
              return $this
                ->_raiseFormulaError('Unable to access Cell Reference');
            }
            $this->_debugLog
              ->writeDebugLog('Evaluation Result for cells ', $cellRef, ' is ', $this
              ->_showTypeDetails($cellValue));
          }
        }
      }
      else {

        //					echo 'Reference is a single Cell<br />';
        if ($pCell === NULL) {

          //						We can't access the cell, so return a REF error
          $cellValue = PHPExcel_Calculation_Functions::REF();
        }
        else {
          $cellRef = $matches[6] . $matches[7];
          if ($matches[2] > '') {
            $matches[2] = trim($matches[2], "\"'");
            if (strpos($matches[2], '[') !== FALSE || strpos($matches[2], ']') !== FALSE) {

              //	It's a Reference to an external workbook (not currently supported)
              return $this
                ->_raiseFormulaError('Unable to access External Workbook');
            }

            //							echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />';
            $this->_debugLog
              ->writeDebugLog('Evaluating Cell ', $cellRef, ' in worksheet ', $matches[2]);
            if ($pCellParent !== NULL) {
              $cellSheet = $this->_workbook
                ->getSheetByName($matches[2]);
              if ($cellSheet && $cellSheet
                ->cellExists($cellRef)) {
                $cellValue = $this
                  ->extractCellRange($cellRef, $this->_workbook
                  ->getSheetByName($matches[2]), FALSE);
                $pCell
                  ->attach($pCellParent);
              }
              else {
                $cellValue = NULL;
              }
            }
            else {
              return $this
                ->_raiseFormulaError('Unable to access Cell Reference');
            }
            $this->_debugLog
              ->writeDebugLog('Evaluation Result for cell ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this
              ->_showTypeDetails($cellValue));

            //							$cellRef = $matches[2].'!'.$cellRef;
          }
          else {

            //							echo '$cellRef='.$cellRef.' in current worksheet<br />';
            $this->_debugLog
              ->writeDebugLog('Evaluating Cell ', $cellRef, ' in current worksheet');
            if ($pCellParent
              ->isDataSet($cellRef)) {
              $cellValue = $this
                ->extractCellRange($cellRef, $pCellWorksheet, FALSE);
              $pCell
                ->attach($pCellParent);
            }
            else {
              $cellValue = NULL;
            }
            $this->_debugLog
              ->writeDebugLog('Evaluation Result for cell ', $cellRef, ' is ', $this
              ->_showTypeDetails($cellValue));
          }
        }
      }
      $stack
        ->push('Value', $cellValue, $cellRef);

      // if the token is a function, pop arguments off the stack, hand them to the function, and push the result back on
    }
    elseif (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $token, $matches)) {

      //				echo 'Token is a function<br />';
      $functionName = $matches[1];
      $argCount = $stack
        ->pop();
      $argCount = $argCount['value'];
      if ($functionName != 'MKMATRIX') {
        $this->_debugLog
          ->writeDebugLog('Evaluating Function ', self::_localeFunc($functionName), '() with ', $argCount == 0 ? 'no' : $argCount, ' argument', $argCount == 1 ? '' : 's');
      }
      if (isset(self::$_PHPExcelFunctions[$functionName]) || isset(self::$_controlFunctions[$functionName])) {

        // function
        if (isset(self::$_PHPExcelFunctions[$functionName])) {
          $functionCall = self::$_PHPExcelFunctions[$functionName]['functionCall'];
          $passByReference = isset(self::$_PHPExcelFunctions[$functionName]['passByReference']);
          $passCellReference = isset(self::$_PHPExcelFunctions[$functionName]['passCellReference']);
        }
        elseif (isset(self::$_controlFunctions[$functionName])) {
          $functionCall = self::$_controlFunctions[$functionName]['functionCall'];
          $passByReference = isset(self::$_controlFunctions[$functionName]['passByReference']);
          $passCellReference = isset(self::$_controlFunctions[$functionName]['passCellReference']);
        }

        // get the arguments for this function
        //					echo 'Function '.$functionName.' expects '.$argCount.' arguments<br />';
        $args = $argArrayVals = array();
        for ($i = 0; $i < $argCount; ++$i) {
          $arg = $stack
            ->pop();
          $a = $argCount - $i - 1;
          if ($passByReference && isset(self::$_PHPExcelFunctions[$functionName]['passByReference'][$a]) && self::$_PHPExcelFunctions[$functionName]['passByReference'][$a]) {
            if ($arg['reference'] === NULL) {
              $args[] = $cellID;
              if ($functionName != 'MKMATRIX') {
                $argArrayVals[] = $this
                  ->_showValue($cellID);
              }
            }
            else {
              $args[] = $arg['reference'];
              if ($functionName != 'MKMATRIX') {
                $argArrayVals[] = $this
                  ->_showValue($arg['reference']);
              }
            }
          }
          else {
            $args[] = self::_unwrapResult($arg['value']);
            if ($functionName != 'MKMATRIX') {
              $argArrayVals[] = $this
                ->_showValue($arg['value']);
            }
          }
        }

        //	Reverse the order of the arguments
        krsort($args);
        if ($passByReference && $argCount == 0) {
          $args[] = $cellID;
          $argArrayVals[] = $this
            ->_showValue($cellID);
        }

        //					echo 'Arguments are: ';
        //					print_r($args);
        //					echo '<br />';
        if ($functionName != 'MKMATRIX') {
          if ($this->_debugLog
            ->getWriteDebugLog()) {
            krsort($argArrayVals);
            $this->_debugLog
              ->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', implode(self::$_localeArgumentSeparator . ' ', PHPExcel_Calculation_Functions::flattenArray($argArrayVals)), ' )');
          }
        }

        //	Process each argument in turn, building the return value as an array
        //					if (($argCount == 1) && (is_array($args[1])) && ($functionName != 'MKMATRIX')) {
        //						$operand1 = $args[1];
        //						$this->_debugLog->writeDebugLog('Argument is a matrix: ', $this->_showValue($operand1));
        //						$result = array();
        //						$row = 0;
        //						foreach($operand1 as $args) {
        //							if (is_array($args)) {
        //								foreach($args as $arg) {
        //									$this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', $this->_showValue($arg), ' )');
        //									$r = call_user_func_array($functionCall,$arg);
        //									$this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($r));
        //									$result[$row][] = $r;
        //								}
        //								++$row;
        //							} else {
        //								$this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', $this->_showValue($args), ' )');
        //								$r = call_user_func_array($functionCall,$args);
        //								$this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($r));
        //								$result[] = $r;
        //							}
        //						}
        //					} else {
        //	Process the argument with the appropriate function call
        if ($passCellReference) {
          $args[] = $pCell;
        }
        if (strpos($functionCall, '::') !== FALSE) {
          $result = call_user_func_array(explode('::', $functionCall), $args);
        }
        else {
          foreach ($args as &$arg) {
            $arg = PHPExcel_Calculation_Functions::flattenSingleValue($arg);
          }
          unset($arg);
          $result = call_user_func_array($functionCall, $args);
        }

        //					}
        if ($functionName != 'MKMATRIX') {
          $this->_debugLog
            ->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this
            ->_showTypeDetails($result));
        }
        $stack
          ->push('Value', self::_wrapResult($result));
      }
    }
    else {

      // if the token is a number, boolean, string or an Excel error, push it onto the stack
      if (isset(self::$_ExcelConstants[strtoupper($token)])) {
        $excelConstant = strtoupper($token);

        //					echo 'Token is a PHPExcel constant: '.$excelConstant.'<br />';
        $stack
          ->push('Constant Value', self::$_ExcelConstants[$excelConstant]);
        $this->_debugLog
          ->writeDebugLog('Evaluating Constant ', $excelConstant, ' as ', $this
          ->_showTypeDetails(self::$_ExcelConstants[$excelConstant]));
      }
      elseif (is_numeric($token) || $token === NULL || is_bool($token) || $token == '' || $token[0] == '"' || $token[0] == '#') {

        //					echo 'Token is a number, boolean, string, null or an Excel error<br />';
        $stack
          ->push('Value', $token);

        // if the token is a named range, push the named range name onto the stack
      }
      elseif (preg_match('/^' . self::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $token, $matches)) {

        //					echo 'Token is a named range<br />';
        $namedRange = $matches[6];

        //					echo 'Named Range is '.$namedRange.'<br />';
        $this->_debugLog
          ->writeDebugLog('Evaluating Named Range ', $namedRange);
        $cellValue = $this
          ->extractNamedRange($namedRange, NULL !== $pCell ? $pCellWorksheet : NULL, FALSE);
        $pCell
          ->attach($pCellParent);
        $this->_debugLog
          ->writeDebugLog('Evaluation Result for named range ', $namedRange, ' is ', $this
          ->_showTypeDetails($cellValue));
        $stack
          ->push('Named Range', $cellValue, $namedRange);
      }
      else {
        return $this
          ->_raiseFormulaError("undefined variable '{$token}'");
      }
    }
  }

  // when we're out of tokens, the stack should have a single element, the final result
  if ($stack
    ->count() != 1) {
    return $this
      ->_raiseFormulaError("internal error");
  }
  $output = $stack
    ->pop();
  $output = $output['value'];

  //		if ((is_array($output)) && (self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY)) {
  //			return array_shift(PHPExcel_Calculation_Functions::flattenArray($output));
  //		}
  return $output;
}