Esempio n. 1
0
 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
                 //	Greater than
                 case '<':
                     //	Less than
                 //	Less than
                 case '>=':
                     //	Greater than or Equal to
                 //	Greater than or Equal to
                 case '<=':
                     //	Less than or Equal to
                 //	Less than or Equal to
                 case '=':
                     //	Equality
                 //	Equality
                 case '<>':
                     //	Inequality
                     $this->_executeBinaryComparisonOperation($cellID, $operand1, $operand2, $token, $stack);
                     break;
                     //	Binary Operators
                 //	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;
 }
Esempio n. 2
0
 private function _parseFormula($formula, PHPExcel_Cell $pCell = null)
 {
     if (($formula = self::_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/', '', $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*\\))/i', 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;
 }