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; }