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 = !is_null($pCell) ? $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, '*' => 0, '/' => 0, '+' => 0, '-' => 0, '&' => 0, '|' => 0, ':' => 0, '>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0);
// Comparison (Boolean) Operators
// These operators work on two values, but always return a boolean result
$comparisonOperators = array('>', '<', '=', '>=', '<=', '<>');
// 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, '|' => 7, '~' => 6, '%' => 5, '^' => 4, '*' => 3, '/' => 3, '+' => 2, '-' => 2, '&' => 1, '>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0);
$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 (in_array($opCharacter, $comparisonOperators) && strlen($formula) > $index && in_array($formula[$index + 1], $comparisonOperators)) {
$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 />';
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 (rather than plus) can be discarded?
// echo 'Element is a Positive number, not Plus operator<br />';
++$index;
// Drop the redundant plus symbol
} elseif ($opCharacter == '~' && !$isOperandOrFunction) {
// We have to explicitly deny a tilde, because it's legal
return $this->_raiseFormulaError("Formula Error: Illegal character '~'");
// on the stack but not in the input expression
} elseif ((in_array($opCharacter, self::$_operators) or $isOperandOrFunction) && $expectingOperator) {
// Are we putting an operator on the stack?
// echo 'Element with value '.$opCharacter.' is an Operator<br />';
while ($stack->count() > 0 && ($o2 = $stack->last()) && in_array($o2['value'], self::$_operators) && @($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 (is_null($o2)) {
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 (array_key_exists($functionName, self::$_controlFunctions)) {
// echo 'Built-in function '.$functionName.'<br />';
$expectedArgumentCount = self::$_controlFunctions[$functionName]['argumentCount'];
$functionCall = self::$_controlFunctions[$functionName]['functionCall'];
} elseif (array_key_exists($functionName, self::$_PHPExcelFunctions)) {
// 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");
}
}
++$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 (is_null($o2)) {
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 (array_key_exists(strtoupper($matches[1]), self::$_PHPExcelFunctions) || array_key_exists(strtoupper($matches[1]), self::$_controlFunctions)) {
// it's a func
$stack->push('Function', strtoupper($val));
$ax = preg_match('/^\\s*(\\s*\\))/i', substr($formula, $index + $length), $amatch);
if ($ax) {
$stack->push('Operand Count for Function ' . self::_localeFunc(strtoupper($val)) . ')', 0);
$expectingOperator = true;
} else {
$stack->push('Operand Count for Function ' . self::_localeFunc(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;
}
}
}
$cellRef = strtoupper($val);
$output[] = array('type' => 'Cell Reference', 'value' => $val, 'reference' => $cellRef);
// $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 = !is_null($pCellParent) ? $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 = !is_null($pCellParent) ? $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 (array_key_exists(trim(strtoupper($val)), self::$_ExcelConstants)) {
$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 (in_array($opCharacter, self::$_operators) && !$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 (in_array($opCharacter, self::$_operators) && $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 . '.*/i', 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()) && in_array($o2['value'], self::$_operators) && @($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 (!is_null($op = $stack->pop())) {
// pop everything off the stack and push onto output
if ($opCharacter['value'] == '(') {
return $this->_raiseFormulaError("Formula Error: Expecting ')'");
}
// if there are any opening braces on the stack, then braces were unbalanced
$output[] = $op;
}
return $output;
}
private function _parseFormula($formula)
{
if (($formula = self::_convertMatrixReferences(trim($formula))) === false) {
return false;
}
// 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, '*' => 0, '/' => 0, '+' => 0, '-' => 0, '&' => 1, '>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0);
// Comparison (Boolean) Operators
// These operators work on two values, but always return a boolean result
$comparisonOperators = array('>', '<', '=', '>=', '<=', '<>');
// 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('_' => 6, '%' => 5, '^' => 4, '*' => 3, '/' => 3, '+' => 2, '-' => 2, '&' => 1, '>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0);
$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 . ')/i';
// 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 (in_array($opCharacter, $comparisonOperators) && strlen($formula) > $index && in_array($formula[$index + 1], $comparisonOperators)) {
$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 />';
if ($opCharacter == '-' && !$expectingOperator) {
// Is it a negation instead of a minus?
// echo 'Element is a Negation operator<br />';
$stack->push('_');
// Put a negation on the stack
++$index;
// and drop the negation symbol
} elseif ($opCharacter == '%' && $expectingOperator) {
// echo 'Element is a Percentage operator<br />';
$stack->push('%');
// Put a percentage on the stack
++$index;
} elseif ($opCharacter == '+' && !$expectingOperator) {
// Positive (rather than plus) can be discarded?
// echo 'Element is a Positive number, not Plus operator<br />';
++$index;
// Drop the redundant plus symbol
} elseif ($opCharacter == '_' && !$isOperandOrFunction) {
// We have to explicitly deny an underscore, because it's legal on
return $this->_raiseFormulaError("Formula Error: Illegal character '_'");
// the stack but not in the input expression
// Note that _ is a valid first character in named ranges
// and this will need modifying soon when we start integrating
// with PHPExcel proper
} elseif ((in_array($opCharacter, $this->_operators) or $isOperandOrFunction) && $expectingOperator) {
// Are we putting an operator on the stack?
// echo 'Element with value '.$opCharacter.' is an Operator<br />';
while ($stack->count() > 0 && ($o2 = $stack->last()) && in_array($o2, $this->_operators) && ($operatorAssociativity[$opCharacter] ? $operatorPrecedence[$opCharacter] < $operatorPrecedence[$o2] : $operatorPrecedence[$opCharacter] <= $operatorPrecedence[$o2])) {
$output[] = $stack->pop();
// Swap operands and higher precedence operators from the stack to the output
}
$stack->push($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()) != '(') {
// Pop off the stack back to the last (
if (is_null($o2)) {
return $this->_raiseFormulaError('Formula Error: Unexpected closing brace ")"');
} else {
$output[] = $o2;
}
}
if (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $stack->last(2), $matches)) {
// Did this parenthesis just close a function?
$functionName = $matches[1];
// Get the function name
// echo 'Closed Function is '.$functionName.'<br />';
$argumentCount = $stack->pop();
// 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[] = $argumentCount;
// Dump the argument count on the output
$output[] = $stack->pop();
// Pop the function and push onto the output
if (array_key_exists($functionName, $this->_controlFunctions)) {
// echo 'Built-in function '.$functionName.'<br />';
$expectedArgumentCount = $this->_controlFunctions[$functionName]['argumentCount'];
$functionCall = $this->_controlFunctions[$functionName]['functionCall'];
} elseif (array_key_exists($functionName, $this->_PHPExcelFunctions)) {
// echo 'PHPExcel function '.$functionName.'<br />';
$expectedArgumentCount = $this->_PHPExcelFunctions[$functionName]['argumentCount'];
$functionCall = $this->_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");
}
}
++$index;
} elseif ($opCharacter == ',') {
// Is this the comma separator for function arguments?
// echo 'Element is a Function argument separator<br />';
while (($o2 = $stack->pop()) != '(') {
if (is_null($o2)) {
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[] = $this->_ExcelConstants['NULL'];
}
// make sure there was a function
if (!preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $stack->last(2), $matches)) {
return $this->_raiseFormulaError("Formula Error: Unexpected ','");
}
$stack->push($stack->pop() + 1);
// increment the argument count
$stack->push('(');
// 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('(');
++$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 (array_key_exists(strtoupper($matches[1]), $this->_controlFunctions) || array_key_exists(strtoupper($matches[1]), $this->_PHPExcelFunctions)) {
// it's a func
$stack->push(strtoupper($val));
$ax = preg_match('/^\\s*(\\s*\\))/i', substr($formula, $index + $length), $amatch);
if ($ax) {
$stack->push(0);
$expectingOperator = true;
} else {
$stack->push(1);
$expectingOperator = false;
}
$stack->push('(');
} else {
// it's a var w/ implicit multiplication
$val = $matches[1];
$output[] = $val;
}
} 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
// $cellRef = strtoupper($val);
// $output[] = $cellRef;
$output[] = $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 ($opCharacter == '"') {
// echo 'Element is a String<br />';
$val = str_replace('""', '"', $val);
} elseif (is_numeric($val)) {
// echo 'Element is a Number<br />';
if (strpos($val, '.') !== False || stripos($val, 'e') !== False) {
// echo 'Casting '.$val.' to float<br />';
$val = (double) $val;
} else {
// echo 'Casting '.$val.' to integer<br />';
$val = (int) $val;
}
// } elseif (array_key_exists(trim(strtoupper($val)), $this->_ExcelConstants)) {
// $excelConstant = trim(strtoupper($val));
// echo 'Element '.$val.' is an Excel Constant<br />';
// $val = $this->_ExcelConstants[$excelConstant];
}
$output[] = $val;
}
$index += $length;
} elseif ($opCharacter == ')') {
// miscellaneous error checking
if ($expectingOperand) {
$output[] = $this->_ExcelConstants['NULL'];
$expectingOperand = false;
$expectingOperator = True;
} else {
return $this->_raiseFormulaError("Formula Error: Unexpected ')'");
}
} elseif (in_array($opCharacter, $this->_operators) && !$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 (in_array($opCharacter, $this->_operators) && $opCharacter != '%') {
return $this->_raiseFormulaError("Formula Error: Operator '{$opCharacter}' has no operands");
} else {
break;
}
}
// Ignore white space
while (substr($formula, $index, 1) == ' ') {
++$index;
}
}
while (!is_null($opCharacter = $stack->pop())) {
// pop everything off the stack and push onto output
if ($opCharacter == '(') {
return $this->_raiseFormulaError("Formula Error: Expecting ')'");
}
// if there are any opening braces on the stack, then braces were unbalanced
$output[] = $opCharacter;
}
return $output;
}
