private function _processTokenStack($tokens, $cellID = NULL, PHPExcel_Cell $pCell = NULL) { if ($tokens == FALSE) { return FALSE; } // If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent cell collection), // so we store the parent cell collection so that we can re-attach it when necessary $pCellWorksheet = $pCell !== NULL ? $pCell->getWorksheet() : NULL; $pCellParent = $pCell !== NULL ? $pCell->getParent() : null; $stack = new PHPExcel_Calculation_Token_Stack(); // Loop through each token in turn foreach ($tokens as $tokenData) { // print_r($tokenData); // echo '<br />'; $token = $tokenData['value']; // echo '<b>Token is '.$token.'</b><br />'; // if the token is a binary operator, pop the top two values off the stack, do the operation, and push the result back on the stack if (isset(self::$_binaryOperators[$token])) { // echo 'Token is a binary operator<br />'; // We must have two operands, error if we don't if (($operand2Data = $stack->pop()) === NULL) { return $this->_raiseFormulaError('Internal error - Operand value missing from stack'); } if (($operand1Data = $stack->pop()) === NULL) { return $this->_raiseFormulaError('Internal error - Operand value missing from stack'); } $operand1 = self::_dataTestReference($operand1Data); $operand2 = self::_dataTestReference($operand2Data); // Log what we're doing if ($token == ':') { $this->_debugLog->writeDebugLog('Evaluating Range ', $this->_showValue($operand1Data['reference']), ' ', $token, ' ', $this->_showValue($operand2Data['reference'])); } else { $this->_debugLog->writeDebugLog('Evaluating ', $this->_showValue($operand1), ' ', $token, ' ', $this->_showValue($operand2)); } // Process the operation in the appropriate manner switch ($token) { // Comparison (Boolean) Operators case '>': // Greater than // Greater than case '<': // Less than // Less than case '>=': // Greater than or Equal to // Greater than or Equal to case '<=': // Less than or Equal to // Less than or Equal to case '=': // Equality // Equality case '<>': // Inequality $this->_executeBinaryComparisonOperation($cellID, $operand1, $operand2, $token, $stack); break; // Binary Operators // Binary Operators case ':': // Range $sheet1 = $sheet2 = ''; if (strpos($operand1Data['reference'], '!') !== FALSE) { list($sheet1, $operand1Data['reference']) = explode('!', $operand1Data['reference']); } else { $sheet1 = $pCellParent !== NULL ? $pCellWorksheet->getTitle() : ''; } if (strpos($operand2Data['reference'], '!') !== FALSE) { list($sheet2, $operand2Data['reference']) = explode('!', $operand2Data['reference']); } else { $sheet2 = $sheet1; } if ($sheet1 == $sheet2) { if ($operand1Data['reference'] === NULL) { if (trim($operand1Data['value']) != '' && is_numeric($operand1Data['value'])) { $operand1Data['reference'] = $pCell->getColumn() . $operand1Data['value']; } elseif (trim($operand1Data['reference']) == '') { $operand1Data['reference'] = $pCell->getCoordinate(); } else { $operand1Data['reference'] = $operand1Data['value'] . $pCell->getRow(); } } if ($operand2Data['reference'] === NULL) { if (trim($operand2Data['value']) != '' && is_numeric($operand2Data['value'])) { $operand2Data['reference'] = $pCell->getColumn() . $operand2Data['value']; } elseif (trim($operand2Data['reference']) == '') { $operand2Data['reference'] = $pCell->getCoordinate(); } else { $operand2Data['reference'] = $operand2Data['value'] . $pCell->getRow(); } } $oData = array_merge(explode(':', $operand1Data['reference']), explode(':', $operand2Data['reference'])); $oCol = $oRow = array(); foreach ($oData as $oDatum) { $oCR = PHPExcel_Cell::coordinateFromString($oDatum); $oCol[] = PHPExcel_Cell::columnIndexFromString($oCR[0]) - 1; $oRow[] = $oCR[1]; } $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)) . min($oRow) . ':' . PHPExcel_Cell::stringFromColumnIndex(max($oCol)) . max($oRow); if ($pCellParent !== NULL) { $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($sheet1), FALSE); } else { return $this->_raiseFormulaError('Unable to access Cell Reference'); } $stack->push('Cell Reference', $cellValue, $cellRef); } else { $stack->push('Error', PHPExcel_Calculation_Functions::REF(), NULL); } break; case '+': // Addition $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'plusEquals', $stack); break; case '-': // Subtraction $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'minusEquals', $stack); break; case '*': // Multiplication $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'arrayTimesEquals', $stack); break; case '/': // Division $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'arrayRightDivide', $stack); break; case '^': // Exponential $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'power', $stack); break; case '&': // Concatenation // If either of the operands is a matrix, we need to treat them both as matrices // (converting the other operand to a matrix if need be); then perform the required // matrix operation if (is_bool($operand1)) { $operand1 = $operand1 ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE']; } if (is_bool($operand2)) { $operand2 = $operand2 ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE']; } if (is_array($operand1) || is_array($operand2)) { // Ensure that both operands are arrays/matrices self::_checkMatrixOperands($operand1, $operand2, 2); try { // Convert operand 1 from a PHP array to a matrix $matrix = new PHPExcel_Shared_JAMA_Matrix($operand1); // Perform the required operation against the operand 1 matrix, passing in operand 2 $matrixResult = $matrix->concat($operand2); $result = $matrixResult->getArray(); } catch (PHPExcel_Exception $ex) { $this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage()); $result = '#VALUE!'; } } else { $result = '"' . str_replace('""', '"', self::_unwrapResult($operand1, '"') . self::_unwrapResult($operand2, '"')) . '"'; } $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result)); $stack->push('Value', $result); break; case '|': // Intersect $rowIntersect = array_intersect_key($operand1, $operand2); $cellIntersect = $oCol = $oRow = array(); foreach (array_keys($rowIntersect) as $row) { $oRow[] = $row; foreach ($rowIntersect[$row] as $col => $data) { $oCol[] = PHPExcel_Cell::columnIndexFromString($col) - 1; $cellIntersect[$row] = array_intersect_key($operand1[$row], $operand2[$row]); } } $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)) . min($oRow) . ':' . PHPExcel_Cell::stringFromColumnIndex(max($oCol)) . max($oRow); $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($cellIntersect)); $stack->push('Value', $cellIntersect, $cellRef); break; } // if the token is a unary operator, pop one value off the stack, do the operation, and push it back on } elseif ($token === '~' || $token === '%') { // echo 'Token is a unary operator<br />'; if (($arg = $stack->pop()) === NULL) { return $this->_raiseFormulaError('Internal error - Operand value missing from stack'); } $arg = $arg['value']; if ($token === '~') { // echo 'Token is a negation operator<br />'; $this->_debugLog->writeDebugLog('Evaluating Negation of ', $this->_showValue($arg)); $multiplier = -1; } else { // echo 'Token is a percentile operator<br />'; $this->_debugLog->writeDebugLog('Evaluating Percentile of ', $this->_showValue($arg)); $multiplier = 0.01; } if (is_array($arg)) { self::_checkMatrixOperands($arg, $multiplier, 2); try { $matrix1 = new PHPExcel_Shared_JAMA_Matrix($arg); $matrixResult = $matrix1->arrayTimesEquals($multiplier); $result = $matrixResult->getArray(); } catch (PHPExcel_Exception $ex) { $this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage()); $result = '#VALUE!'; } $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result)); $stack->push('Value', $result); } else { $this->_executeNumericBinaryOperation($cellID, $multiplier, $arg, '*', 'arrayTimesEquals', $stack); } } elseif (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $token, $matches)) { $cellRef = NULL; // echo 'Element '.$token.' is a Cell reference<br />'; if (isset($matches[8])) { // echo 'Reference is a Range of cells<br />'; if ($pCell === NULL) { // We can't access the range, so return a REF error $cellValue = PHPExcel_Calculation_Functions::REF(); } else { $cellRef = $matches[6] . $matches[7] . ':' . $matches[9] . $matches[10]; if ($matches[2] > '') { $matches[2] = trim($matches[2], "\"'"); if (strpos($matches[2], '[') !== FALSE || strpos($matches[2], ']') !== FALSE) { // It's a Reference to an external workbook (not currently supported) return $this->_raiseFormulaError('Unable to access External Workbook'); } $matches[2] = trim($matches[2], "\"'"); // echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />'; $this->_debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in worksheet ', $matches[2]); if ($pCellParent !== NULL) { $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($matches[2]), FALSE); } else { return $this->_raiseFormulaError('Unable to access Cell Reference'); } $this->_debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->_showTypeDetails($cellValue)); // $cellRef = $matches[2].'!'.$cellRef; } else { // echo '$cellRef='.$cellRef.' in current worksheet<br />'; $this->_debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in current worksheet'); if ($pCellParent !== NULL) { $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, FALSE); } else { return $this->_raiseFormulaError('Unable to access Cell Reference'); } $this->_debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' is ', $this->_showTypeDetails($cellValue)); } } } else { // echo 'Reference is a single Cell<br />'; if ($pCell === NULL) { // We can't access the cell, so return a REF error $cellValue = PHPExcel_Calculation_Functions::REF(); } else { $cellRef = $matches[6] . $matches[7]; if ($matches[2] > '') { $matches[2] = trim($matches[2], "\"'"); if (strpos($matches[2], '[') !== FALSE || strpos($matches[2], ']') !== FALSE) { // It's a Reference to an external workbook (not currently supported) return $this->_raiseFormulaError('Unable to access External Workbook'); } // echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />'; $this->_debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in worksheet ', $matches[2]); if ($pCellParent !== NULL) { $cellSheet = $this->_workbook->getSheetByName($matches[2]); if ($cellSheet && $cellSheet->cellExists($cellRef)) { $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($matches[2]), FALSE); $pCell->attach($pCellParent); } else { $cellValue = NULL; } } else { return $this->_raiseFormulaError('Unable to access Cell Reference'); } $this->_debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->_showTypeDetails($cellValue)); // $cellRef = $matches[2].'!'.$cellRef; } else { // echo '$cellRef='.$cellRef.' in current worksheet<br />'; $this->_debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in current worksheet'); if ($pCellParent->isDataSet($cellRef)) { $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, FALSE); $pCell->attach($pCellParent); } else { $cellValue = NULL; } $this->_debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' is ', $this->_showTypeDetails($cellValue)); } } } $stack->push('Value', $cellValue, $cellRef); // if the token is a function, pop arguments off the stack, hand them to the function, and push the result back on } elseif (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $token, $matches)) { // echo 'Token is a function<br />'; $functionName = $matches[1]; $argCount = $stack->pop(); $argCount = $argCount['value']; if ($functionName != 'MKMATRIX') { $this->_debugLog->writeDebugLog('Evaluating Function ', self::_localeFunc($functionName), '() with ', $argCount == 0 ? 'no' : $argCount, ' argument', $argCount == 1 ? '' : 's'); } if (isset(self::$_PHPExcelFunctions[$functionName]) || isset(self::$_controlFunctions[$functionName])) { // function if (isset(self::$_PHPExcelFunctions[$functionName])) { $functionCall = self::$_PHPExcelFunctions[$functionName]['functionCall']; $passByReference = isset(self::$_PHPExcelFunctions[$functionName]['passByReference']); $passCellReference = isset(self::$_PHPExcelFunctions[$functionName]['passCellReference']); } elseif (isset(self::$_controlFunctions[$functionName])) { $functionCall = self::$_controlFunctions[$functionName]['functionCall']; $passByReference = isset(self::$_controlFunctions[$functionName]['passByReference']); $passCellReference = isset(self::$_controlFunctions[$functionName]['passCellReference']); } // get the arguments for this function // echo 'Function '.$functionName.' expects '.$argCount.' arguments<br />'; $args = $argArrayVals = array(); for ($i = 0; $i < $argCount; ++$i) { $arg = $stack->pop(); $a = $argCount - $i - 1; if ($passByReference && isset(self::$_PHPExcelFunctions[$functionName]['passByReference'][$a]) && self::$_PHPExcelFunctions[$functionName]['passByReference'][$a]) { if ($arg['reference'] === NULL) { $args[] = $cellID; if ($functionName != 'MKMATRIX') { $argArrayVals[] = $this->_showValue($cellID); } } else { $args[] = $arg['reference']; if ($functionName != 'MKMATRIX') { $argArrayVals[] = $this->_showValue($arg['reference']); } } } else { $args[] = self::_unwrapResult($arg['value']); if ($functionName != 'MKMATRIX') { $argArrayVals[] = $this->_showValue($arg['value']); } } } // Reverse the order of the arguments krsort($args); if ($passByReference && $argCount == 0) { $args[] = $cellID; $argArrayVals[] = $this->_showValue($cellID); } // echo 'Arguments are: '; // print_r($args); // echo '<br />'; if ($functionName != 'MKMATRIX') { if ($this->_debugLog->getWriteDebugLog()) { krsort($argArrayVals); $this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', implode(self::$_localeArgumentSeparator . ' ', PHPExcel_Calculation_Functions::flattenArray($argArrayVals)), ' )'); } } // Process each argument in turn, building the return value as an array // if (($argCount == 1) && (is_array($args[1])) && ($functionName != 'MKMATRIX')) { // $operand1 = $args[1]; // $this->_debugLog->writeDebugLog('Argument is a matrix: ', $this->_showValue($operand1)); // $result = array(); // $row = 0; // foreach($operand1 as $args) { // if (is_array($args)) { // foreach($args as $arg) { // $this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', $this->_showValue($arg), ' )'); // $r = call_user_func_array($functionCall,$arg); // $this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($r)); // $result[$row][] = $r; // } // ++$row; // } else { // $this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', $this->_showValue($args), ' )'); // $r = call_user_func_array($functionCall,$args); // $this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($r)); // $result[] = $r; // } // } // } else { // Process the argument with the appropriate function call if ($passCellReference) { $args[] = $pCell; } if (strpos($functionCall, '::') !== FALSE) { $result = call_user_func_array(explode('::', $functionCall), $args); } else { foreach ($args as &$arg) { $arg = PHPExcel_Calculation_Functions::flattenSingleValue($arg); } unset($arg); $result = call_user_func_array($functionCall, $args); } // } if ($functionName != 'MKMATRIX') { $this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($result)); } $stack->push('Value', self::_wrapResult($result)); } } else { // if the token is a number, boolean, string or an Excel error, push it onto the stack if (isset(self::$_ExcelConstants[strtoupper($token)])) { $excelConstant = strtoupper($token); // echo 'Token is a PHPExcel constant: '.$excelConstant.'<br />'; $stack->push('Constant Value', self::$_ExcelConstants[$excelConstant]); $this->_debugLog->writeDebugLog('Evaluating Constant ', $excelConstant, ' as ', $this->_showTypeDetails(self::$_ExcelConstants[$excelConstant])); } elseif (is_numeric($token) || $token === NULL || is_bool($token) || $token == '' || $token[0] == '"' || $token[0] == '#') { // echo 'Token is a number, boolean, string, null or an Excel error<br />'; $stack->push('Value', $token); // if the token is a named range, push the named range name onto the stack } elseif (preg_match('/^' . self::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $token, $matches)) { // echo 'Token is a named range<br />'; $namedRange = $matches[6]; // echo 'Named Range is '.$namedRange.'<br />'; $this->_debugLog->writeDebugLog('Evaluating Named Range ', $namedRange); $cellValue = $this->extractNamedRange($namedRange, NULL !== $pCell ? $pCellWorksheet : NULL, FALSE); $pCell->attach($pCellParent); $this->_debugLog->writeDebugLog('Evaluation Result for named range ', $namedRange, ' is ', $this->_showTypeDetails($cellValue)); $stack->push('Named Range', $cellValue, $namedRange); } else { return $this->_raiseFormulaError("undefined variable '{$token}'"); } } } // when we're out of tokens, the stack should have a single element, the final result if ($stack->count() != 1) { return $this->_raiseFormulaError("internal error"); } $output = $stack->pop(); $output = $output['value']; // if ((is_array($output)) && (self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY)) { // return array_shift(PHPExcel_Calculation_Functions::flattenArray($output)); // } return $output; }
private function _parseFormula($formula, PHPExcel_Cell $pCell = null) { if (($formula = self::_convertMatrixReferences(trim($formula))) === false) { return false; } // If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent worksheet), // so we store the parent worksheet so that we can re-attach it when necessary $pCellParent = $pCell !== null ? $pCell->getWorksheet() : null; $regexpMatchString = '/^(' . self::CALCULATION_REGEXP_FUNCTION . '|' . self::CALCULATION_REGEXP_CELLREF . '|' . self::CALCULATION_REGEXP_NUMBER . '|' . self::CALCULATION_REGEXP_STRING . '|' . self::CALCULATION_REGEXP_OPENBRACE . '|' . self::CALCULATION_REGEXP_NAMEDRANGE . '|' . self::CALCULATION_REGEXP_ERROR . ')/si'; // Start with initialisation $index = 0; $stack = new PHPExcel_Calculation_Token_Stack(); $output = array(); $expectingOperator = false; // We use this test in syntax-checking the expression to determine when a // - is a negation or + is a positive operator rather than an operation $expectingOperand = false; // We use this test in syntax-checking the expression to determine whether an operand // should be null in a function call // The guts of the lexical parser // Loop through the formula extracting each operator and operand in turn while (true) { //echo 'Assessing Expression '.substr($formula, $index),PHP_EOL; $opCharacter = $formula[$index]; // Get the first character of the value at the current index position //echo 'Initial character of expression block is '.$opCharacter,PHP_EOL; if (isset(self::$_comparisonOperators[$opCharacter]) && strlen($formula) > $index && isset(self::$_comparisonOperators[$formula[$index + 1]])) { $opCharacter .= $formula[++$index]; //echo 'Initial character of expression block is comparison operator '.$opCharacter.PHP_EOL; } // Find out if we're currently at the beginning of a number, variable, cell reference, function, parenthesis or operand $isOperandOrFunction = preg_match($regexpMatchString, substr($formula, $index), $match); //echo '$isOperandOrFunction is '.(($isOperandOrFunction) ? 'True' : 'False').PHP_EOL; //var_dump($match); if ($opCharacter == '-' && !$expectingOperator) { // Is it a negation instead of a minus? //echo 'Element is a Negation operator',PHP_EOL; $stack->push('Unary Operator', '~'); // Put a negation on the stack ++$index; // and drop the negation symbol } elseif ($opCharacter == '%' && $expectingOperator) { //echo 'Element is a Percentage operator',PHP_EOL; $stack->push('Unary Operator', '%'); // Put a percentage on the stack ++$index; } elseif ($opCharacter == '+' && !$expectingOperator) { // Positive (unary plus rather than binary operator plus) can be discarded? //echo 'Element is a Positive number, not Plus operator',PHP_EOL; ++$index; // Drop the redundant plus symbol } elseif (($opCharacter == '~' || $opCharacter == '|') && !$isOperandOrFunction) { // We have to explicitly deny a tilde or pipe, because they are legal return $this->_raiseFormulaError("Formula Error: Illegal character '~'"); // on the stack but not in the input expression } elseif ((isset(self::$_operators[$opCharacter]) or $isOperandOrFunction) && $expectingOperator) { // Are we putting an operator on the stack? //echo 'Element with value '.$opCharacter.' is an Operator',PHP_EOL; while ($stack->count() > 0 && ($o2 = $stack->last()) && isset(self::$_operators[$o2['value']]) && @(self::$_operatorAssociativity[$opCharacter] ? self::$_operatorPrecedence[$opCharacter] < self::$_operatorPrecedence[$o2['value']] : self::$_operatorPrecedence[$opCharacter] <= self::$_operatorPrecedence[$o2['value']])) { $output[] = $stack->pop(); // Swap operands and higher precedence operators from the stack to the output } $stack->push('Binary Operator', $opCharacter); // Finally put our current operator onto the stack ++$index; $expectingOperator = false; } elseif ($opCharacter == ')' && $expectingOperator) { // Are we expecting to close a parenthesis? //echo 'Element is a Closing bracket',PHP_EOL; $expectingOperand = false; while (($o2 = $stack->pop()) && $o2['value'] != '(') { // Pop off the stack back to the last ( if ($o2 === null) { return $this->_raiseFormulaError('Formula Error: Unexpected closing brace ")"'); } else { $output[] = $o2; } } $d = $stack->last(2); if (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $d['value'], $matches)) { // Did this parenthesis just close a function? $functionName = $matches[1]; // Get the function name //echo 'Closed Function is '.$functionName,PHP_EOL; $d = $stack->pop(); $argumentCount = $d['value']; // See how many arguments there were (argument count is the next value stored on the stack) //if ($argumentCount == 0) { // echo 'With no arguments',PHP_EOL; //} elseif ($argumentCount == 1) { // echo 'With 1 argument',PHP_EOL; //} else { // echo 'With '.$argumentCount.' arguments',PHP_EOL; //} $output[] = $d; // Dump the argument count on the output $output[] = $stack->pop(); // Pop the function and push onto the output if (isset(self::$_controlFunctions[$functionName])) { //echo 'Built-in function '.$functionName,PHP_EOL; $expectedArgumentCount = self::$_controlFunctions[$functionName]['argumentCount']; $functionCall = self::$_controlFunctions[$functionName]['functionCall']; } elseif (isset(self::$_PHPExcelFunctions[$functionName])) { //echo 'PHPExcel function '.$functionName,PHP_EOL; $expectedArgumentCount = self::$_PHPExcelFunctions[$functionName]['argumentCount']; $functionCall = self::$_PHPExcelFunctions[$functionName]['functionCall']; } else { // did we somehow push a non-function on the stack? this should never happen return $this->_raiseFormulaError("Formula Error: Internal error, non-function on stack"); } // Check the argument count $argumentCountError = false; if (is_numeric($expectedArgumentCount)) { if ($expectedArgumentCount < 0) { //echo '$expectedArgumentCount is between 0 and '.abs($expectedArgumentCount),PHP_EOL; if ($argumentCount > abs($expectedArgumentCount)) { $argumentCountError = true; $expectedArgumentCountString = 'no more than ' . abs($expectedArgumentCount); } } else { //echo '$expectedArgumentCount is numeric '.$expectedArgumentCount,PHP_EOL; if ($argumentCount != $expectedArgumentCount) { $argumentCountError = true; $expectedArgumentCountString = $expectedArgumentCount; } } } elseif ($expectedArgumentCount != '*') { $isOperandOrFunction = preg_match('/(\\d*)([-+,])(\\d*)/', $expectedArgumentCount, $argMatch); //print_r($argMatch); //echo PHP_EOL; switch ($argMatch[2]) { case '+': if ($argumentCount < $argMatch[1]) { $argumentCountError = true; $expectedArgumentCountString = $argMatch[1] . ' or more '; } break; case '-': if ($argumentCount < $argMatch[1] || $argumentCount > $argMatch[3]) { $argumentCountError = true; $expectedArgumentCountString = 'between ' . $argMatch[1] . ' and ' . $argMatch[3]; } break; case ',': if ($argumentCount != $argMatch[1] && $argumentCount != $argMatch[3]) { $argumentCountError = true; $expectedArgumentCountString = 'either ' . $argMatch[1] . ' or ' . $argMatch[3]; } break; } } if ($argumentCountError) { return $this->_raiseFormulaError("Formula Error: Wrong number of arguments for {$functionName}() function: {$argumentCount} given, " . $expectedArgumentCountString . " expected"); } } ++$index; } elseif ($opCharacter == ',') { // Is this the separator for function arguments? //echo 'Element is a Function argument separator',PHP_EOL; while (($o2 = $stack->pop()) && $o2['value'] != '(') { // Pop off the stack back to the last ( if ($o2 === null) { return $this->_raiseFormulaError("Formula Error: Unexpected ,"); } else { $output[] = $o2; } // pop the argument expression stuff and push onto the output } // If we've a comma when we're expecting an operand, then what we actually have is a null operand; // so push a null onto the stack if ($expectingOperand || !$expectingOperator) { $output[] = array('type' => 'NULL Value', 'value' => self::$_ExcelConstants['NULL'], 'reference' => null); } // make sure there was a function $d = $stack->last(2); if (!preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $d['value'], $matches)) { return $this->_raiseFormulaError("Formula Error: Unexpected ,"); } $d = $stack->pop(); $stack->push($d['type'], ++$d['value'], $d['reference']); // increment the argument count $stack->push('Brace', '('); // put the ( back on, we'll need to pop back to it again $expectingOperator = false; $expectingOperand = true; ++$index; } elseif ($opCharacter == '(' && !$expectingOperator) { // echo 'Element is an Opening Bracket<br />'; $stack->push('Brace', '('); ++$index; } elseif ($isOperandOrFunction && !$expectingOperator) { // do we now have a function/variable/number? $expectingOperator = true; $expectingOperand = false; $val = $match[1]; $length = strlen($val); // echo 'Element with value '.$val.' is an Operand, Variable, Constant, String, Number, Cell Reference or Function<br />'; if (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $val, $matches)) { $val = preg_replace('/\\s/', '', $val); // echo 'Element '.$val.' is a Function<br />'; if (isset(self::$_PHPExcelFunctions[strtoupper($matches[1])]) || isset(self::$_controlFunctions[strtoupper($matches[1])])) { // it's a function $stack->push('Function', strtoupper($val)); $ax = preg_match('/^\\s*(\\s*\\))/i', substr($formula, $index + $length), $amatch); if ($ax) { $stack->push('Operand Count for Function ' . strtoupper($val) . ')', 0); $expectingOperator = true; } else { $stack->push('Operand Count for Function ' . strtoupper($val) . ')', 1); $expectingOperator = false; } $stack->push('Brace', '('); } else { // it's a var w/ implicit multiplication $output[] = array('type' => 'Value', 'value' => $matches[1], 'reference' => null); } } elseif (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $val, $matches)) { // echo 'Element '.$val.' is a Cell reference<br />'; // Watch for this case-change when modifying to allow cell references in different worksheets... // Should only be applied to the actual cell column, not the worksheet name // If the last entry on the stack was a : operator, then we have a cell range reference $testPrevOp = $stack->last(1); if ($testPrevOp['value'] == ':') { // If we have a worksheet reference, then we're playing with a 3D reference if ($matches[2] == '') { // Otherwise, we 'inherit' the worksheet reference from the start cell reference // The start of the cell range reference should be the last entry in $output $startCellRef = $output[count($output) - 1]['value']; preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $startCellRef, $startMatches); if ($startMatches[2] > '') { $val = $startMatches[2] . '!' . $val; } } else { return $this->_raiseFormulaError("3D Range references are not yet supported"); } } $output[] = array('type' => 'Cell Reference', 'value' => $val, 'reference' => $val); // $expectingOperator = FALSE; } else { // it's a variable, constant, string, number or boolean // echo 'Element is a Variable, Constant, String, Number or Boolean<br />'; // If the last entry on the stack was a : operator, then we may have a row or column range reference $testPrevOp = $stack->last(1); if ($testPrevOp['value'] == ':') { $startRowColRef = $output[count($output) - 1]['value']; $rangeWS1 = ''; if (strpos('!', $startRowColRef) !== false) { list($rangeWS1, $startRowColRef) = explode('!', $startRowColRef); } if ($rangeWS1 != '') { $rangeWS1 .= '!'; } $rangeWS2 = $rangeWS1; if (strpos('!', $val) !== false) { list($rangeWS2, $val) = explode('!', $val); } if ($rangeWS2 != '') { $rangeWS2 .= '!'; } if (is_integer($startRowColRef) && ctype_digit($val) && $startRowColRef <= 1048576 && $val <= 1048576) { // Row range $endRowColRef = $pCellParent !== null ? $pCellParent->getHighestColumn() : 'XFD'; // Max 16,384 columns for Excel2007 $output[count($output) - 1]['value'] = $rangeWS1 . 'A' . $startRowColRef; $val = $rangeWS2 . $endRowColRef . $val; } elseif (ctype_alpha($startRowColRef) && ctype_alpha($val) && strlen($startRowColRef) <= 3 && strlen($val) <= 3) { // Column range $endRowColRef = $pCellParent !== null ? $pCellParent->getHighestRow() : 1048576; // Max 1,048,576 rows for Excel2007 $output[count($output) - 1]['value'] = $rangeWS1 . strtoupper($startRowColRef) . '1'; $val = $rangeWS2 . $val . $endRowColRef; } } $localeConstant = false; if ($opCharacter == '"') { // echo 'Element is a String<br />'; // UnEscape any quotes within the string $val = self::_wrapResult(str_replace('""', '"', self::_unwrapResult($val))); } elseif (is_numeric($val)) { // echo 'Element is a Number<br />'; if (strpos($val, '.') !== false || stripos($val, 'e') !== false || $val > PHP_INT_MAX || $val < -PHP_INT_MAX) { // echo 'Casting '.$val.' to float<br />'; $val = (double) $val; } else { // echo 'Casting '.$val.' to integer<br />'; $val = (int) $val; } } elseif (isset(self::$_ExcelConstants[trim(strtoupper($val))])) { $excelConstant = trim(strtoupper($val)); // echo 'Element '.$excelConstant.' is an Excel Constant<br />'; $val = self::$_ExcelConstants[$excelConstant]; } elseif (($localeConstant = array_search(trim(strtoupper($val)), self::$_localeBoolean)) !== false) { // echo 'Element '.$localeConstant.' is an Excel Constant<br />'; $val = self::$_ExcelConstants[$localeConstant]; } $details = array('type' => 'Value', 'value' => $val, 'reference' => null); if ($localeConstant) { $details['localeValue'] = $localeConstant; } $output[] = $details; } $index += $length; } elseif ($opCharacter == '$') { // absolute row or column range ++$index; } elseif ($opCharacter == ')') { // miscellaneous error checking if ($expectingOperand) { $output[] = array('type' => 'NULL Value', 'value' => self::$_ExcelConstants['NULL'], 'reference' => null); $expectingOperand = false; $expectingOperator = true; } else { return $this->_raiseFormulaError("Formula Error: Unexpected ')'"); } } elseif (isset(self::$_operators[$opCharacter]) && !$expectingOperator) { return $this->_raiseFormulaError("Formula Error: Unexpected operator '{$opCharacter}'"); } else { // I don't even want to know what you did to get here return $this->_raiseFormulaError("Formula Error: An unexpected error occured"); } // Test for end of formula string if ($index == strlen($formula)) { // Did we end with an operator?. // Only valid for the % unary operator if (isset(self::$_operators[$opCharacter]) && $opCharacter != '%') { return $this->_raiseFormulaError("Formula Error: Operator '{$opCharacter}' has no operands"); } else { break; } } // Ignore white space while ($formula[$index] == "\n" || $formula[$index] == "\r") { ++$index; } if ($formula[$index] == ' ') { while ($formula[$index] == ' ') { ++$index; } // If we're expecting an operator, but only have a space between the previous and next operands (and both are // Cell References) then we have an INTERSECTION operator // echo 'Possible Intersect Operator<br />'; if ($expectingOperator && preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '.*/Ui', substr($formula, $index), $match) && $output[count($output) - 1]['type'] == 'Cell Reference') { // echo 'Element is an Intersect Operator<br />'; while ($stack->count() > 0 && ($o2 = $stack->last()) && isset(self::$_operators[$o2['value']]) && @(self::$_operatorAssociativity[$opCharacter] ? self::$_operatorPrecedence[$opCharacter] < self::$_operatorPrecedence[$o2['value']] : self::$_operatorPrecedence[$opCharacter] <= self::$_operatorPrecedence[$o2['value']])) { $output[] = $stack->pop(); // Swap operands and higher precedence operators from the stack to the output } $stack->push('Binary Operator', '|'); // Put an Intersect Operator on the stack $expectingOperator = false; } } } while (($op = $stack->pop()) !== null) { // pop everything off the stack and push onto output if (is_array($op) && $op['value'] == '(' || $op === '(') { return $this->_raiseFormulaError("Formula Error: Expecting ')'"); } // if there are any opening braces on the stack, then braces were unbalanced $output[] = $op; } return $output; }