+global\PHPExcel_Calculation::_parseFormula()

Complexity

Conditions	113
Paths	> 20000

Size

Total Lines	368
Code Lines	232

Duplication

Lines	37
Ratio	10.05 %

Importance

Changes

0

<?php
	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->getParent() : NULL;

		//	Binary Operators
		//	These operators always work on two values
		//	Array key is the operator, the value indicates whether this is a left or right associative operator
		$operatorAssociativity	= array('^' => 0,															//	Exponentiation
										'*' => 0, '/' => 0, 												//	Multiplication and Division
										'+' => 0, '-' => 0,													//	Addition and Subtraction
										'&' => 0,															//	Concatenation
										'|' => 0, ':' => 0,													//	Intersect and Range
										'>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0		//	Comparison
								 	  );
		//	Comparison (Boolean) Operators
		//	These operators work on two values, but always return a boolean result
		$comparisonOperators	= array('>' => true, '<' => true, '=' => true, '>=' => true, '<=' => true, '<>' => true);

		//	Operator Precedence
		//	This list includes all valid operators, whether binary (including boolean) or unary (such as %)
		//	Array key is the operator, the value is its precedence
		$operatorPrecedence	= array(':' => 8,																//	Range
									'|' => 7,																//	Intersect
									'~' => 6,																//	Negation
									'%' => 5,																//	Percentage
									'^' => 4,																//	Exponentiation
									'*' => 3, '/' => 3, 													//	Multiplication and Division
									'+' => 2, '-' => 2,														//	Addition and Subtraction
									'&' => 1,																//	Concatenation
									'>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0			//	Comparison
								   );

		$regexpMatchString = '/^('.self::CALCULATION_REGEXP_FUNCTION.
							   '|'.self::CALCULATION_REGEXP_NUMBER.
							   '|'.self::CALCULATION_REGEXP_STRING.
							   '|'.self::CALCULATION_REGEXP_OPENBRACE.
							   '|'.self::CALCULATION_REGEXP_CELLREF.
							   '|'.self::CALCULATION_REGEXP_NAMEDRANGE.
							   '|'.self::CALCULATION_REGEXP_ERROR.
							 ')/si';

		//	Start with initialisation
		$index = 0;
		$stack = new PHPExcel_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 <b>'.substr($formula, $index).'</b><br />';
			$opCharacter = $formula{$index};	//	Get the first character of the value at the current index position
//			echo 'Initial character of expression block is '.$opCharacter.'<br />';
			if ((isset($comparisonOperators[$opCharacter])) && (strlen($formula) > $index) && (isset($comparisonOperators[$formula{$index+1}]))) {
				$opCharacter .= $formula{++$index};
//				echo 'Initial character of expression block is comparison operator '.$opCharacter.'<br />';
			}

			//	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').'<br />';
//			var_dump($match);

			if ($opCharacter == '-' && !$expectingOperator) {				//	Is it a negation instead of a minus?
//				echo 'Element is a Negation operator<br />';
				$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<br />';
				$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<br />';
				++$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?

Using logical operators such as or instead of || is generally not recommended.

//				echo 'Element with value '.$opCharacter.' is an Operator<br />';
				while($stack->count() > 0 &&
					($o2 = $stack->last()) &&
					isset(self::$_operators[$o2['value']]) &&
					@($operatorAssociativity[$opCharacter] ? $operatorPrecedence[$opCharacter] < $operatorPrecedence[$o2['value']] : $operatorPrecedence[$opCharacter] <= $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<br />';
				$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.'<br />';
					$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<br />';
//					} elseif ($argumentCount == 1) {
//						echo 'With 1 argument<br />';
//					} else {
//						echo 'With '.$argumentCount.' arguments<br />';
//					}
					$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.'<br />';
						$expectedArgumentCount = self::$_controlFunctions[$functionName]['argumentCount'];
						$functionCall = self::$_controlFunctions[$functionName]['functionCall'];
					} elseif (isset(self::$_PHPExcelFunctions[$functionName])) {
//						echo 'PHPExcel function '.$functionName.'<br />';
						$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).'<br />';
							if ($argumentCount > abs($expectedArgumentCount)) {
								$argumentCountError = true;
								$expectedArgumentCountString = 'no more than '.abs($expectedArgumentCount);
							}
						} else {
//							echo '$expectedArgumentCount is numeric '.$expectedArgumentCount.'<br />';
							if ($argumentCount != $expectedArgumentCount) {
								$argumentCountError = true;
								$expectedArgumentCountString = $expectedArgumentCount;
							}
						}
					} elseif ($expectedArgumentCount != '*') {
						$isOperandOrFunction = preg_match('/(\d*)([-+,])(\d*)/',$expectedArgumentCount,$argMatch);
//						print_r($argMatch);
//						echo '<br />';
						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");

The variable $expectedArgumentCountString does not seem to be defined for all execution paths leading up to this point.

					}
				}
				++$index;

			} elseif ($opCharacter == ',') {			//	Is this the separator for function arguments?
//				echo 'Element is a Function argument separator<br />';
				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 = (float) $val;
						} else {
//							echo 'Casting '.$val.' to integer<br />';
							$val = (integer) $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']]) &&
						@($operatorAssociativity[$opCharacter] ? $operatorPrecedence[$opCharacter] < $operatorPrecedence[$o2['value']] : $operatorPrecedence[$opCharacter] <= $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($opCharacter) && $opCharacter['value'] == '(') || ($opCharacter === '('))

The variable $opCharacter does not seem to be defined for all execution paths leading up to this point.

				return $this->_raiseFormulaError("Formula Error: Expecting ')'");	// if there are any opening braces on the stack, then braces were unbalanced
			$output[] = $op;
		}
		return $output;
	}	//	function _parseFormula()