private function _parseFormula($formula, PHPExcel_Cell $pCell = NULL)
{
if (($formula = $this->_convertMatrixReferences(trim($formula))) === FALSE) {
return FALSE;
}
// If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent worksheet),
// so we store the parent worksheet so that we can re-attach it when necessary
$pCellParent = $pCell !== NULL ? $pCell->getWorksheet() : NULL;
$regexpMatchString = '/^(' . self::CALCULATION_REGEXP_FUNCTION . '|' . self::CALCULATION_REGEXP_CELLREF . '|' . self::CALCULATION_REGEXP_NUMBER . '|' . self::CALCULATION_REGEXP_STRING . '|' . self::CALCULATION_REGEXP_OPENBRACE . '|' . self::CALCULATION_REGEXP_NAMEDRANGE . '|' . self::CALCULATION_REGEXP_ERROR . ')/si';
// Start with initialisation
$index = 0;
$stack = new PHPExcel_Calculation_Token_Stack();
$output = array();
$expectingOperator = FALSE;
// We use this test in syntax-checking the expression to determine when a
// - is a negation or + is a positive operator rather than an operation
$expectingOperand = FALSE;
// We use this test in syntax-checking the expression to determine whether an operand
// should be null in a function call
// The guts of the lexical parser
// Loop through the formula extracting each operator and operand in turn
while (TRUE) {
//echo 'Assessing Expression '.substr($formula, $index),PHP_EOL;
$opCharacter = $formula[$index];
// Get the first character of the value at the current index position
//echo 'Initial character of expression block is '.$opCharacter,PHP_EOL;
if (isset(self::$_comparisonOperators[$opCharacter]) && strlen($formula) > $index && isset(self::$_comparisonOperators[$formula[$index + 1]])) {
$opCharacter .= $formula[++$index];
//echo 'Initial character of expression block is comparison operator '.$opCharacter.PHP_EOL;
}
// Find out if we're currently at the beginning of a number, variable, cell reference, function, parenthesis or operand
$isOperandOrFunction = preg_match($regexpMatchString, substr($formula, $index), $match);
//echo '$isOperandOrFunction is '.(($isOperandOrFunction) ? 'True' : 'False').PHP_EOL;
//var_dump($match);
if ($opCharacter == '-' && !$expectingOperator) {
// Is it a negation instead of a minus?
//echo 'Element is a Negation operator',PHP_EOL;
$stack->push('Unary Operator', '~');
// Put a negation on the stack
++$index;
// and drop the negation symbol
} elseif ($opCharacter == '%' && $expectingOperator) {
//echo 'Element is a Percentage operator',PHP_EOL;
$stack->push('Unary Operator', '%');
// Put a percentage on the stack
++$index;
} elseif ($opCharacter == '+' && !$expectingOperator) {
// Positive (unary plus rather than binary operator plus) can be discarded?
//echo 'Element is a Positive number, not Plus operator',PHP_EOL;
++$index;
// Drop the redundant plus symbol
} elseif (($opCharacter == '~' || $opCharacter == '|') && !$isOperandOrFunction) {
// We have to explicitly deny a tilde or pipe, because they are legal
return $this->_raiseFormulaError("Formula Error: Illegal character '~'");
// on the stack but not in the input expression
} elseif ((isset(self::$_operators[$opCharacter]) or $isOperandOrFunction) && $expectingOperator) {
// Are we putting an operator on the stack?
//echo 'Element with value '.$opCharacter.' is an Operator',PHP_EOL;
while ($stack->count() > 0 && ($o2 = $stack->last()) && isset(self::$_operators[$o2['value']]) && @(self::$_operatorAssociativity[$opCharacter] ? self::$_operatorPrecedence[$opCharacter] < self::$_operatorPrecedence[$o2['value']] : self::$_operatorPrecedence[$opCharacter] <= self::$_operatorPrecedence[$o2['value']])) {
$output[] = $stack->pop();
// Swap operands and higher precedence operators from the stack to the output
}
$stack->push('Binary Operator', $opCharacter);
// Finally put our current operator onto the stack
++$index;
$expectingOperator = FALSE;
} elseif ($opCharacter == ')' && $expectingOperator) {
// Are we expecting to close a parenthesis?
//echo 'Element is a Closing bracket',PHP_EOL;
$expectingOperand = FALSE;
while (($o2 = $stack->pop()) && $o2['value'] != '(') {
// Pop off the stack back to the last (
if ($o2 === NULL) {
return $this->_raiseFormulaError('Formula Error: Unexpected closing brace ")"');
} else {
$output[] = $o2;
}
}
$d = $stack->last(2);
if (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $d['value'], $matches)) {
// Did this parenthesis just close a function?
$functionName = $matches[1];
// Get the function name
//echo 'Closed Function is '.$functionName,PHP_EOL;
$d = $stack->pop();
$argumentCount = $d['value'];
// See how many arguments there were (argument count is the next value stored on the stack)
//if ($argumentCount == 0) {
// echo 'With no arguments',PHP_EOL;
//} elseif ($argumentCount == 1) {
// echo 'With 1 argument',PHP_EOL;
//} else {
// echo 'With '.$argumentCount.' arguments',PHP_EOL;
//}
$output[] = $d;
// Dump the argument count on the output
$output[] = $stack->pop();
// Pop the function and push onto the output
if (isset(self::$_controlFunctions[$functionName])) {
//echo 'Built-in function '.$functionName,PHP_EOL;
$expectedArgumentCount = self::$_controlFunctions[$functionName]['argumentCount'];
$functionCall = self::$_controlFunctions[$functionName]['functionCall'];
} elseif (isset(self::$_PHPExcelFunctions[$functionName])) {
//echo 'PHPExcel function '.$functionName,PHP_EOL;
$expectedArgumentCount = self::$_PHPExcelFunctions[$functionName]['argumentCount'];
$functionCall = self::$_PHPExcelFunctions[$functionName]['functionCall'];
} else {
// did we somehow push a non-function on the stack? this should never happen
return $this->_raiseFormulaError("Formula Error: Internal error, non-function on stack");
}
// Check the argument count
$argumentCountError = FALSE;
if (is_numeric($expectedArgumentCount)) {
if ($expectedArgumentCount < 0) {
//echo '$expectedArgumentCount is between 0 and '.abs($expectedArgumentCount),PHP_EOL;
if ($argumentCount > abs($expectedArgumentCount)) {
$argumentCountError = TRUE;
$expectedArgumentCountString = 'no more than ' . abs($expectedArgumentCount);
}
} else {
//echo '$expectedArgumentCount is numeric '.$expectedArgumentCount,PHP_EOL;
if ($argumentCount != $expectedArgumentCount) {
$argumentCountError = TRUE;
$expectedArgumentCountString = $expectedArgumentCount;
}
}
} elseif ($expectedArgumentCount != '*') {
$isOperandOrFunction = preg_match('/(\\d*)([-+,])(\\d*)/', $expectedArgumentCount, $argMatch);
//print_r($argMatch);
//echo PHP_EOL;
switch ($argMatch[2]) {
case '+':
if ($argumentCount < $argMatch[1]) {
$argumentCountError = TRUE;
$expectedArgumentCountString = $argMatch[1] . ' or more ';
}
break;
case '-':
if ($argumentCount < $argMatch[1] || $argumentCount > $argMatch[3]) {
$argumentCountError = TRUE;
$expectedArgumentCountString = 'between ' . $argMatch[1] . ' and ' . $argMatch[3];
}
break;
case ',':
if ($argumentCount != $argMatch[1] && $argumentCount != $argMatch[3]) {
$argumentCountError = TRUE;
$expectedArgumentCountString = 'either ' . $argMatch[1] . ' or ' . $argMatch[3];
}
break;
}
}
if ($argumentCountError) {
return $this->_raiseFormulaError("Formula Error: Wrong number of arguments for {$functionName}() function: {$argumentCount} given, " . $expectedArgumentCountString . " expected");
}
}
++$index;
} elseif ($opCharacter == ',') {
// Is this the separator for function arguments?
//echo 'Element is a Function argument separator',PHP_EOL;
while (($o2 = $stack->pop()) && $o2['value'] != '(') {
// Pop off the stack back to the last (
if ($o2 === NULL) {
return $this->_raiseFormulaError("Formula Error: Unexpected ,");
} else {
$output[] = $o2;
}
// pop the argument expression stuff and push onto the output
}
// If we've a comma when we're expecting an operand, then what we actually have is a null operand;
// so push a null onto the stack
if ($expectingOperand || !$expectingOperator) {
$output[] = array('type' => 'NULL Value', 'value' => self::$_ExcelConstants['NULL'], 'reference' => NULL);
}
// make sure there was a function
$d = $stack->last(2);
if (!preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $d['value'], $matches)) {
return $this->_raiseFormulaError("Formula Error: Unexpected ,");
}
$d = $stack->pop();
$stack->push($d['type'], ++$d['value'], $d['reference']);
// increment the argument count
$stack->push('Brace', '(');
// put the ( back on, we'll need to pop back to it again
$expectingOperator = FALSE;
$expectingOperand = TRUE;
++$index;
} elseif ($opCharacter == '(' && !$expectingOperator) {
// echo 'Element is an Opening Bracket<br />';
$stack->push('Brace', '(');
++$index;
} elseif ($isOperandOrFunction && !$expectingOperator) {
// do we now have a function/variable/number?
$expectingOperator = TRUE;
$expectingOperand = FALSE;
$val = $match[1];
$length = strlen($val);
// echo 'Element with value '.$val.' is an Operand, Variable, Constant, String, Number, Cell Reference or Function<br />';
if (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $val, $matches)) {
$val = preg_replace('/\\s/u', '', $val);
// echo 'Element '.$val.' is a Function<br />';
if (isset(self::$_PHPExcelFunctions[strtoupper($matches[1])]) || isset(self::$_controlFunctions[strtoupper($matches[1])])) {
// it's a function
$stack->push('Function', strtoupper($val));
$ax = preg_match('/^\\s*(\\s*\\))/ui', substr($formula, $index + $length), $amatch);
if ($ax) {
$stack->push('Operand Count for Function ' . strtoupper($val) . ')', 0);
$expectingOperator = TRUE;
} else {
$stack->push('Operand Count for Function ' . strtoupper($val) . ')', 1);
$expectingOperator = FALSE;
}
$stack->push('Brace', '(');
} else {
// it's a var w/ implicit multiplication
$output[] = array('type' => 'Value', 'value' => $matches[1], 'reference' => NULL);
}
} elseif (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $val, $matches)) {
// echo 'Element '.$val.' is a Cell reference<br />';
// Watch for this case-change when modifying to allow cell references in different worksheets...
// Should only be applied to the actual cell column, not the worksheet name
// If the last entry on the stack was a : operator, then we have a cell range reference
$testPrevOp = $stack->last(1);
if ($testPrevOp['value'] == ':') {
// If we have a worksheet reference, then we're playing with a 3D reference
if ($matches[2] == '') {
// Otherwise, we 'inherit' the worksheet reference from the start cell reference
// The start of the cell range reference should be the last entry in $output
$startCellRef = $output[count($output) - 1]['value'];
preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $startCellRef, $startMatches);
if ($startMatches[2] > '') {
$val = $startMatches[2] . '!' . $val;
}
} else {
return $this->_raiseFormulaError("3D Range references are not yet supported");
}
}
$output[] = array('type' => 'Cell Reference', 'value' => $val, 'reference' => $val);
// $expectingOperator = FALSE;
} else {
// it's a variable, constant, string, number or boolean
// echo 'Element is a Variable, Constant, String, Number or Boolean<br />';
// If the last entry on the stack was a : operator, then we may have a row or column range reference
$testPrevOp = $stack->last(1);
if ($testPrevOp['value'] == ':') {
$startRowColRef = $output[count($output) - 1]['value'];
$rangeWS1 = '';
if (strpos('!', $startRowColRef) !== FALSE) {
list($rangeWS1, $startRowColRef) = explode('!', $startRowColRef);
}
if ($rangeWS1 != '') {
$rangeWS1 .= '!';
}
$rangeWS2 = $rangeWS1;
if (strpos('!', $val) !== FALSE) {
list($rangeWS2, $val) = explode('!', $val);
}
if ($rangeWS2 != '') {
$rangeWS2 .= '!';
}
if (is_integer($startRowColRef) && ctype_digit($val) && $startRowColRef <= 1048576 && $val <= 1048576) {
// Row range
$endRowColRef = $pCellParent !== NULL ? $pCellParent->getHighestColumn() : 'XFD';
// Max 16,384 columns for Excel2007
$output[count($output) - 1]['value'] = $rangeWS1 . 'A' . $startRowColRef;
$val = $rangeWS2 . $endRowColRef . $val;
} elseif (ctype_alpha($startRowColRef) && ctype_alpha($val) && strlen($startRowColRef) <= 3 && strlen($val) <= 3) {
// Column range
$endRowColRef = $pCellParent !== NULL ? $pCellParent->getHighestRow() : 1048576;
// Max 1,048,576 rows for Excel2007
$output[count($output) - 1]['value'] = $rangeWS1 . strtoupper($startRowColRef) . '1';
$val = $rangeWS2 . $val . $endRowColRef;
}
}
$localeConstant = FALSE;
if ($opCharacter == '"') {
// echo 'Element is a String<br />';
// UnEscape any quotes within the string
$val = self::_wrapResult(str_replace('""', '"', self::_unwrapResult($val)));
} elseif (is_numeric($val)) {
// echo 'Element is a Number<br />';
if (strpos($val, '.') !== FALSE || stripos($val, 'e') !== FALSE || $val > PHP_INT_MAX || $val < -PHP_INT_MAX) {
// echo 'Casting '.$val.' to float<br />';
$val = (double) $val;
} else {
// echo 'Casting '.$val.' to integer<br />';
$val = (int) $val;
}
} elseif (isset(self::$_ExcelConstants[trim(strtoupper($val))])) {
$excelConstant = trim(strtoupper($val));
// echo 'Element '.$excelConstant.' is an Excel Constant<br />';
$val = self::$_ExcelConstants[$excelConstant];
} elseif (($localeConstant = array_search(trim(strtoupper($val)), self::$_localeBoolean)) !== FALSE) {
// echo 'Element '.$localeConstant.' is an Excel Constant<br />';
$val = self::$_ExcelConstants[$localeConstant];
}
$details = array('type' => 'Value', 'value' => $val, 'reference' => NULL);
if ($localeConstant) {
$details['localeValue'] = $localeConstant;
}
$output[] = $details;
}
$index += $length;
} elseif ($opCharacter == '$') {
// absolute row or column range
++$index;
} elseif ($opCharacter == ')') {
// miscellaneous error checking
if ($expectingOperand) {
$output[] = array('type' => 'NULL Value', 'value' => self::$_ExcelConstants['NULL'], 'reference' => NULL);
$expectingOperand = FALSE;
$expectingOperator = TRUE;
} else {
return $this->_raiseFormulaError("Formula Error: Unexpected ')'");
}
} elseif (isset(self::$_operators[$opCharacter]) && !$expectingOperator) {
return $this->_raiseFormulaError("Formula Error: Unexpected operator '{$opCharacter}'");
} else {
// I don't even want to know what you did to get here
return $this->_raiseFormulaError("Formula Error: An unexpected error occured");
}
// Test for end of formula string
if ($index == strlen($formula)) {
// Did we end with an operator?.
// Only valid for the % unary operator
if (isset(self::$_operators[$opCharacter]) && $opCharacter != '%') {
return $this->_raiseFormulaError("Formula Error: Operator '{$opCharacter}' has no operands");
} else {
break;
}
}
// Ignore white space
while ($formula[$index] == "\n" || $formula[$index] == "\r") {
++$index;
}
if ($formula[$index] == ' ') {
while ($formula[$index] == ' ') {
++$index;
}
// If we're expecting an operator, but only have a space between the previous and next operands (and both are
// Cell References) then we have an INTERSECTION operator
// echo 'Possible Intersect Operator<br />';
if ($expectingOperator && preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '.*/Ui', substr($formula, $index), $match) && $output[count($output) - 1]['type'] == 'Cell Reference') {
// echo 'Element is an Intersect Operator<br />';
while ($stack->count() > 0 && ($o2 = $stack->last()) && isset(self::$_operators[$o2['value']]) && @(self::$_operatorAssociativity[$opCharacter] ? self::$_operatorPrecedence[$opCharacter] < self::$_operatorPrecedence[$o2['value']] : self::$_operatorPrecedence[$opCharacter] <= self::$_operatorPrecedence[$o2['value']])) {
$output[] = $stack->pop();
// Swap operands and higher precedence operators from the stack to the output
}
$stack->push('Binary Operator', '|');
// Put an Intersect Operator on the stack
$expectingOperator = FALSE;
}
}
}
while (($op = $stack->pop()) !== NULL) {
// pop everything off the stack and push onto output
if (is_array($op) && $op['value'] == '(' || $op === '(') {
return $this->_raiseFormulaError("Formula Error: Expecting ')'");
}
// if there are any opening braces on the stack, then braces were unbalanced
$output[] = $op;
}
return $output;
}