ILIAS  Release_4_0_x_branch Revision 61816
 All Data Structures Namespaces Files Functions Variables Groups Pages
Public Member Functions | Static Public Member Functions | Data Fields | Private Member Functions | Static Private Member Functions | Private Attributes | Static Private Attributes
PHPExcel_Calculation Class Reference
+ Collaboration diagram for PHPExcel_Calculation:

Public Member Functions

 __clone ()
 __clone implementation.
 getCalculationCacheEnabled ()
 Is calculation caching enabled?
 setCalculationCacheEnabled ($pValue=true)
 Enable/disable calculation cache.
 enableCalculationCache ()
 Enable calculation cache.
 disableCalculationCache ()
 Disable calculation cache.
 clearCalculationCache ()
 Clear calculation cache.
 getCalculationCacheExpirationTime ()
 Get calculation cache expiration time.
 setCalculationCacheExpirationTime ($pValue=0.01)
 Set calculation cache expiration time.
 calculate (PHPExcel_Cell $pCell=null)
 Calculate cell value (using formula from a cell ID) Retained for backward compatibility.
 calculateCellValue (PHPExcel_Cell $pCell=null, $resetLog=true)
 Calculate the value of a cell formula.
 parseFormula ($formula)
 Validate and parse a formula string.
 calculateFormula ($formula, $cellID=null, PHPExcel_Cell $pCell=null)
 Calculate the value of a formula.
 _calculateFormulaValue ($formula, $cellID=null, PHPExcel_Cell $pCell=null)
 Parse a cell formula and calculate its value.
 extractCellRange ($pRange= 'A1', PHPExcel_Worksheet $pSheet=null, $resetLog=true)
 Extract range values.
 extractNamedRange ($pRange= 'A1', PHPExcel_Worksheet $pSheet=null, $resetLog=true)
 Extract range values.
 isImplemented ($pFunction= '')
 Is a specific function implemented?
 listFunctions ()
 Get a list of all implemented functions as an array of function objects.
 listFunctionNames ()
 Get a list of implemented Excel function names.

Static Public Member Functions

static getInstance ()
 Get an instance of this class.
static setArrayReturnType ($returnType)
 Set the Array Return Type (Array or Value of first element in the array)
static getArrayReturnType ()
 Return the Array Return Type (Array or Value of first element in the array)
static _wrapResult ($value)
 Wrap string values in quotes.
static _unwrapResult ($value)
 Remove quotes used as a wrapper to identify string values.

Data Fields

const CALCULATION_REGEXP_NUMBER = '[-+]?\d*\.?\d+(e[-+]?\d+)?'
 Constants.
const CALCULATION_REGEXP_STRING = '"(?:[^"]|"")*"'
const CALCULATION_REGEXP_OPENBRACE = '\('
const CALCULATION_REGEXP_FUNCTION = '([A-Z][A-Z0-9\.]*)[\s]*\('
const CALCULATION_REGEXP_CELLREF = '(((\w*)|(\'.*\')|(\".*\"))!)?\$?([a-z]+)\$?(\d+)(:\$?([a-z]+)\$?(\d+))?'
const CALCULATION_REGEXP_NAMEDRANGE = '(((\w*)|(\'.*\')|(\".*\"))!)?([_A-Z][_A-Z0-9]*)'
const CALCULATION_REGEXP_ERROR = '\#[^!]+!'
const RETURN_ARRAY_AS_VALUE = 'value'
 constants
const RETURN_ARRAY_AS_ARRAY = 'array'
 $suppressFormulaErrors = false
 $formulaError = null
 $writeDebugLog = false
 $debugLog = array()

Private Member Functions

 _parseFormula ($formula)
 _processTokenStack ($tokens, $cellID=null, PHPExcel_Cell $pCell=null)
 _validateBinaryOperand ($cellID, &$operand, &$stack)
 _executeBinaryComparisonOperation ($cellID, $operand1, $operand2, $operation, &$stack)
 _executeNumericBinaryOperation ($cellID, $operand1, $operand2, $operation, $matrixFunction, &$stack)
 _writeDebug ($cellID, $message)
 _raiseFormulaError ($errorMessage)

Static Private Member Functions

static _checkMatrixOperands (&$operand1, &$operand2, $resize=1)
 Ensure that paired matrix operands are both matrices and of the same size.
static _reindexMatrixDimensions ($matrix)
 Re-index a matrix with straight numeric keys starting from row 0, column 0.
static _getMatrixDimensions (&$matrix)
 Read the dimensions of a matrix, and re-index it with straight numeric keys starting from row 0, column 0.
static _resizeMatricesShrink (&$matrix1, &$matrix2)
 Ensure that paired matrix operands are both matrices of the same size.
static _resizeMatricesExtend (&$matrix1, &$matrix2)
 Ensure that paired matrix operands are both matrices of the same size.
static _showValue ($value)
 Format details of an operand for display in the log (based on operand type)
static _showTypeDetails ($value)
 Format type and details of an operand for display in the log (based on operand type)
static _convertMatrixReferences ($formula)
static _mkMatrix ()

Private Attributes

 $_calculationCache = array ()
 $_calculationCacheEnabled = true
 $_calculationCacheExpirationTime = 0.01
 $_operators = array('+', '-', '*', '/', '^', '&', '%', '_', '>', '<', '=', '>=', '<=', '<>')
 $_binaryOperators = array('+', '-', '*', '/', '^', '&', '>', '<', '=', '>=', '<=', '<>')
 $debugLogStack = array()
 $_ExcelConstants
 $_PHPExcelFunctions
 $_controlFunctions

Static Private Attributes

static $returnArrayAsType = self::RETURN_ARRAY_AS_ARRAY
static $_instance

Detailed Description

Definition at line 54 of file Calculation.php.

Member Function Documentation

PHPExcel_Calculation::__clone ( )
final

__clone implementation.

Cloning should not be allowed in a Singleton!

public

Exceptions
Exception

Definition at line 1594 of file Calculation.php.

{
throw new Exception ( 'Cloning a Singleton is not allowed!' );
} // function __clone()
PHPExcel_Calculation::_calculateFormulaValue (   $formula,
  $cellID = null,
PHPExcel_Cell  $pCell = null 
)

Parse a cell formula and calculate its value.

Parameters
string$formulaThe formula to parse and calculate
string$cellIDThe ID (e.g. A3) of the cell that we are calculating
PHPExcel_Cell$pCellCell to calculate
Returns
mixed
Exceptions
Exception

Definition at line 1840 of file Calculation.php.

References $_calculationCacheExpirationTime, _parseFormula(), _processTokenStack(), _wrapResult(), _writeDebug(), and PHPExcel_Calculation_Functions\flattenArray().

Referenced by calculateCellValue(), and calculateFormula().

{
// echo '<b>'.$cellID.'</b><br />';
$cellValue = '';
// Basic validation that this is indeed a formula
// We simply return the "cell value" (formula) if not
$formula = trim($formula);
if ($formula{0} != '=') return self::_wrapResult($formula);
$formula = trim(substr($formula,1));
$formulaLength = strlen($formula);
if ($formulaLength < 1) return self::_wrapResult($formula);
// Is calculation cacheing enabled?
if (!is_null($cellID)) {
if ($this->_calculationCacheEnabled) {
// Is the value present in calculation cache?
// echo 'Testing cache value<br />';
if (isset($this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()])) {
// echo 'Value is in cache<br />';
$this->_writeDebug($cellID,'Testing cache value');
// Is cache still valid?
if ((time() + microtime()) - $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['time'] < $this->_calculationCacheExpirationTime) {
// echo 'Cache time is still valid<br />';
$this->_writeDebug($cellID,'Retrieving value from cache');
// Return the cached result
$returnValue = $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['data'];
// echo 'Retrieving data value of '.$returnValue.' for '.$pCell->getCoordinate().' from cache<br />';
if (is_array($returnValue)) {
return array_shift(PHPExcel_Calculation_Functions::flattenArray($returnValue));
}
return $returnValue;
} else {
// echo 'Cache has expired<br />';
$this->_writeDebug($cellID,'Cache value has expired');
// Clear the cache if it's no longer valid
unset($this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]);
}
}
}
}
$this->debugLogStack[] = $cellID;
// Parse the formula onto the token stack and calculate the value
$cellValue = $this->_processTokenStack($this->_parseFormula($formula), $cellID, $pCell);
array_pop($this->debugLogStack);
// Save to calculation cache
if (!is_null($cellID)) {
if ($this->_calculationCacheEnabled) {
$this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['time'] = (time() + microtime());
$this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['data'] = $cellValue;
}
}
// Return the calculated value
if (is_array($cellValue)) {
$cellValue = array_shift(PHPExcel_Calculation_Functions::flattenArray($cellValue));
}
return $cellValue;
} // function _calculateFormulaValue()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

static PHPExcel_Calculation::_checkMatrixOperands ( &  $operand1,
&  $operand2,
  $resize = 1 
)
staticprivate

Ensure that paired matrix operands are both matrices and of the same size.

Parameters
mixed$operand1First matrix operand
mixed$operand2Second matrix operand

Definition at line 1909 of file Calculation.php.

References _getMatrixDimensions(), _resizeMatricesExtend(), _resizeMatricesShrink(), and elseif().

Referenced by _executeNumericBinaryOperation(), and _processTokenStack().

{
// Examine each of the two operands, and turn them into an array if they aren't one already
// Note that this function should only be called if one or both of the operand is already an array
if (!is_array($operand1)) {
list($matrixRows,$matrixColumns) = self::_getMatrixDimensions($operand2);
$operand1 = array_fill(0,$matrixRows,array_fill(0,$matrixColumns,$operand1));
$resize = 0;
} elseif (!is_array($operand2)) {
list($matrixRows,$matrixColumns) = self::_getMatrixDimensions($operand1);
$operand2 = array_fill(0,$matrixRows,array_fill(0,$matrixColumns,$operand2));
$resize = 0;
}
// Given two matrices of (potentially) unequal size, convert the smaller in each dimension to match the larger
if ($resize == 2) {
self::_resizeMatricesExtend($operand1,$operand2);
} elseif ($resize == 1) {
self::_resizeMatricesShrink($operand1,$operand2);
}
} // function _checkMatrixOperands()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

static PHPExcel_Calculation::_convertMatrixReferences (   $formula)
staticprivate

Definition at line 2125 of file Calculation.php.

References _raiseFormulaError(), and elseif().

{
static $matrixReplaceFrom = array('{',';','}');
static $matrixReplaceTo = array('MKMATRIX(MKMATRIX(','),MKMATRIX(','))');
// Convert any Excel matrix references to the MKMATRIX() function
if (strpos($formula,'{') !== false) {
// Open and Closed counts used for trapping mismatched braces in the formula
$openCount = $closeCount = 0;
// If there is the possibility of braces within a quoted string, then we don't treat those as matrix indicators
if (strpos($formula,'"') !== false) {
// So instead we skip replacing in any quoted strings by only replacing in every other array element after we've exploded
// the formula
$temp = explode('"',$formula);
$i = 0;
foreach($temp as &$value) {
// Only count/replace in alternate array entries
if (($i++ % 2) == 0) {
$openCount += substr_count($value,'{');
$closeCount += substr_count($value,'}');
$value = str_replace($matrixReplaceFrom,$matrixReplaceTo,$value);
}
}
unset($value);
// Then rebuild the formula string
$formula = implode('"',$temp);
} else {
// If there's no quoted strings, then we do a simple count/replace
$openCount += substr_count($formula,'{');
$closeCount += substr_count($formula,'}');
$formula = str_replace($matrixReplaceFrom,$matrixReplaceTo,$formula);
}
// Trap for mismatched braces and trigger an appropriate error
if ($openCount < $closeCount) {
if ($openCount > 0) {
return $this->_raiseFormulaError("Formula Error: Mismatched matrix braces '}'");
} else {
return $this->_raiseFormulaError("Formula Error: Unexpected '}' encountered");
}
} elseif ($openCount > $closeCount) {
if ($closeCount > 0) {
return $this->_raiseFormulaError("Formula Error: Mismatched matrix braces '{'");
} else {
return $this->_raiseFormulaError("Formula Error: Unexpected '{' encountered");
}
}
}
return $formula;
} // function _convertMatrixReferences()

+ Here is the call graph for this function:

PHPExcel_Calculation::_executeBinaryComparisonOperation (   $cellID,
  $operand1,
  $operand2,
  $operation,
&  $stack 
)
private

Definition at line 2747 of file Calculation.php.

References $result, and _writeDebug().

Referenced by _processTokenStack().

{
// Validate the two operands
// If we're dealing with non-matrix operations, execute the necessary operation
switch ($operation) {
// Greater than
case '>':
$result = ($operand1 > $operand2);
break;
// Less than
case '<':
$result = ($operand1 < $operand2);
break;
// Equality
case '=':
$result = ($operand1 == $operand2);
break;
// Greater than or equal
case '>=':
$result = ($operand1 >= $operand2);
break;
// Less than or equal
case '<=':
$result = ($operand1 <= $operand2);
break;
// Inequality
case '<>':
$result = ($operand1 != $operand2);
break;
}
// Log the result details
$this->_writeDebug($cellID,'Evaluation Result is '.self::_showTypeDetails($result));
// And push the result onto the stack
$stack->push($result);
return true;
} // function _executeBinaryComparisonOperation()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

PHPExcel_Calculation::_executeNumericBinaryOperation (   $cellID,
  $operand1,
  $operand2,
  $operation,
  $matrixFunction,
&  $stack 
)
private

Definition at line 2785 of file Calculation.php.

References $result, _checkMatrixOperands(), _validateBinaryOperand(), and _writeDebug().

Referenced by _processTokenStack().

{
// Validate the two operands
if (!$this->_validateBinaryOperand($cellID,$operand1,$stack)) return false;
if (!$this->_validateBinaryOperand($cellID,$operand2,$stack)) return false;
// 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_array($operand1)) || (is_array($operand2))) {
// Ensure that both operands are arrays/matrices
self::_checkMatrixOperands($operand1,$operand2);
try {
// Convert operand 1 from a PHP array to a matrix
$matrix = new Matrix($operand1);
// Perform the required operation against the operand 1 matrix, passing in operand 2
$matrixResult = $matrix->$matrixFunction($operand2);
$result = $matrixResult->getArray();
} catch (Exception $ex) {
$this->_writeDebug($cellID,'JAMA Matrix Exception: '.$ex->getMessage());
$result = '#VALUE!';
}
} else {
// If we're dealing with non-matrix operations, execute the necessary operation
switch ($operation) {
// Addition
case '+':
$result = $operand1+$operand2;
break;
// Subtraction
case '-':
$result = $operand1-$operand2;
break;
// Multiplication
case '*':
$result = $operand1*$operand2;
break;
// Division
case '/':
if ($operand2 == 0) {
// Trap for Divide by Zero error
$stack->push('#DIV/0!');
$this->_writeDebug($cellID,'Evaluation Result is '.self::_showTypeDetails('#DIV/0!'));
return false;
} else {
$result = $operand1/$operand2;
}
break;
// Power
case '^':
$result = pow($operand1,$operand2);
break;
}
}
// Log the result details
$this->_writeDebug($cellID,'Evaluation Result is '.self::_showTypeDetails($result));
// And push the result onto the stack
$stack->push($result);
return true;
} // function _executeNumericBinaryOperation()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

static PHPExcel_Calculation::_getMatrixDimensions ( &  $matrix)
staticprivate

Read the dimensions of a matrix, and re-index it with straight numeric keys starting from row 0, column 0.

Parameters
mixed$matrixmatrix operand
Returns
array An array comprising the number of rows, and number of columns

Definition at line 1951 of file Calculation.php.

Referenced by _checkMatrixOperands(), _resizeMatricesExtend(), and _resizeMatricesShrink().

{
$matrixRows = count($matrix);
$matrixColumns = 0;
foreach($matrix as $rowKey => $rowValue) {
$colCount = count($rowValue);
if ($colCount > $matrixColumns) {
$matrixColumns = $colCount;
}
$matrix[$rowKey] = array_values($rowValue);
}
$matrix = array_values($matrix);
return array($matrixRows,$matrixColumns);
} // function _getMatrixDimensions()

+ Here is the caller graph for this function:

static PHPExcel_Calculation::_mkMatrix ( )
staticprivate

Definition at line 2176 of file Calculation.php.

{
return func_get_args();
} // function _mkMatrix()
PHPExcel_Calculation::_parseFormula (   $formula)
private

Definition at line 2182 of file Calculation.php.

References _raiseFormulaError(), and elseif().

Referenced by _calculateFormulaValue(), and parseFormula().

{
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, // Exponentiation
'*' => 0, '/' => 0, // Multiplication and Division
'+' => 0, '-' => 0, // Addition and Subtraction
'&' => 1, // Concatenation
'>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0 // Comparison
);
// 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, // Negation
'%' => 5, // Percentage
'^' => 4, // Exponentiation
'*' => 3, '/' => 3, // Multiplication and Division
'+' => 2, '-' => 2, // Addition and Subtraction
'&' => 1, // Concatenation
'>' => 0, '<' => 0, '=' => 0, '>=' => 0, '<=' => 0, '<>' => 0 // Comparison
);
$regexpMatchString = '/^('.self::CALCULATION_REGEXP_FUNCTION.
'|'.self::CALCULATION_REGEXP_NUMBER.
'|'.self::CALCULATION_REGEXP_STRING.
'|'.self::CALCULATION_REGEXP_OPENBRACE.
'|'.self::CALCULATION_REGEXP_CELLREF.
'|'.self::CALCULATION_REGEXP_NAMEDRANGE.
')/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 = (float) $val;
} else {
// echo 'Casting '.$val.' to integer<br />';
$val = (integer) $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;
} // function _parseFormula()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

PHPExcel_Calculation::_processTokenStack (   $tokens,
  $cellID = null,
PHPExcel_Cell  $pCell = null 
)
private

Definition at line 2469 of file Calculation.php.

References $result, _checkMatrixOperands(), _executeBinaryComparisonOperation(), _executeNumericBinaryOperation(), _raiseFormulaError(), _showValue(), _unwrapResult(), _writeDebug(), elseif(), extractCellRange(), extractNamedRange(), and PHPExcel_Calculation_Functions\REF().

Referenced by _calculateFormulaValue().

{
if ($tokens == false) return false;
$stack = new PHPExcel_Token_Stack;
// Loop through each token in turn
foreach ($tokens as $token) {
// 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 (in_array($token, $this->_binaryOperators, true)) {
// echo 'Token is a binary operator<br />';
// We must have two operands, error if we don't
if (is_null($operand2 = $stack->pop())) return $this->_raiseFormulaError('Internal error - Operand value missing from stack');
if (is_null($operand1 = $stack->pop())) return $this->_raiseFormulaError('Internal error - Operand value missing from stack');
// Log what we're doing
$this->_writeDebug($cellID,'Evaluating '.self::_showValue($operand1).' '.$token.' '.self::_showValue($operand2));
// Process the operation in the appropriate manner
switch ($token) {
// Comparison (Boolean) Operators
case '>' : // Greater than
case '<' : // Less than
case '>=' : // Greater than or Equal to
case '<=' : // Less than or Equal to
case '=' : // Equality
case '<>' : // Inequality
$this->_executeBinaryComparisonOperation($cellID,$operand1,$operand2,$token,$stack);
break;
// Binary Operators
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_array($operand1)) || (is_array($operand2))) {
// Ensure that both operands are arrays/matrices
self::_checkMatrixOperands($operand1,$operand2);
try {
// Convert operand 1 from a PHP array to a matrix
$matrix = new Matrix($operand1);
// Perform the required operation against the operand 1 matrix, passing in operand 2
$matrixResult = $matrix->concat($operand2);
$result = $matrixResult->getArray();
} catch (Exception $ex) {
$this->_writeDebug($cellID,'JAMA Matrix Exception: '.$ex->getMessage());
$result = '#VALUE!';
}
} else {
$result = '"'.str_replace('""','"',self::_unwrapResult($operand1,'"').self::_unwrapResult($operand2,'"')).'"';
}
$this->_writeDebug($cellID,'Evaluation Result is '.self::_showTypeDetails($result));
$stack->push($result);
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 (is_null($arg = $stack->pop())) return $this->_raiseFormulaError('Internal error - Operand value missing from stack');
if ($token === "_") {
// echo 'Token is a negation operator<br />';
$this->_writeDebug($cellID,'Evaluating Negation of '.self::_showValue($arg));
$multiplier = -1;
} else {
// echo 'Token is a percentile operator<br />';
$this->_writeDebug($cellID,'Evaluating Percentile of '.self::_showValue($arg));
$multiplier = 0.01;
}
if (is_array($arg)) {
self::_checkMatrixOperands($arg,$multiplier);
try {
$matrix1 = new Matrix($arg);
$matrixResult = $matrix1->arrayTimesEquals($multiplier);
$result = $matrixResult->getArray();
} catch (Exception $ex) {
$this->_writeDebug($cellID,'JAMA Matrix Exception: '.$ex->getMessage());
$result = '#VALUE!';
}
} else {
$result = $multiplier * $arg;
}
$this->_writeDebug($cellID,'Evaluation Result is '.self::_showTypeDetails($result));
$stack->push($result);
} elseif (preg_match('/^'.self::CALCULATION_REGEXP_CELLREF.'$/i', $token, $matches)) {
// echo 'Element '.$token.' is a Cell reference<br />';
if (isset($matches[8])) {
// echo 'Reference is a Range of cells<br />';
if (is_null($pCell)) {
// 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] > '') {
// echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />';
$this->_writeDebug($cellID,'Evaluating Cell Range '.$cellRef.' in worksheet '.$matches[2]);
$cellValue = $this->extractCellRange($cellRef, $pCell->getParent()->getParent()->getSheetByName($matches[2]), false);
$this->_writeDebug($cellID,'Evaluation Result for cells '.$cellRef.' in worksheet '.$matches[2].' is '.self::_showTypeDetails($cellValue));
} else {
// echo '$cellRef='.$cellRef.' in current worksheet<br />';
$this->_writeDebug($cellID,'Evaluating Cell Range '.$cellRef.' in current worksheet');
$cellValue = $this->extractCellRange($cellRef, $pCell->getParent(), false);
$this->_writeDebug($cellID,'Evaluation Result for cells '.$cellRef.' is '.self::_showTypeDetails($cellValue));
}
}
} else {
// echo 'Reference is a single Cell<br />';
if (is_null($pCell)) {
// 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] > '') {
// echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />';
$this->_writeDebug($cellID,'Evaluating Cell '.$cellRef.' in worksheet '.$matches[2]);
if ($pCell->getParent()->cellExists($cellRef)) {
$cellValue = $this->extractCellRange($cellRef, $pCell->getParent()->getParent()->getSheetByName($matches[2]), false);
} else {
$cellValue = null;
}
$this->_writeDebug($cellID,'Evaluation Result for cell '.$cellRef.' in worksheet '.$matches[2].' is '.self::_showTypeDetails($cellValue));
} else {
// echo '$cellRef='.$cellRef.' in current worksheet<br />';
$this->_writeDebug($cellID,'Evaluating Cell '.$cellRef.' in current worksheet');
if ($pCell->getParent()->cellExists($cellRef)) {
$cellValue = $pCell->getParent()->getCell($cellRef)->getCalculatedValue(false);
} else {
$cellValue = '';
}
$this->_writeDebug($cellID,'Evaluation Result for cell '.$cellRef.' is '.self::_showTypeDetails($cellValue));
}
}
}
$stack->push($cellValue);
// 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();
if ($functionName != 'MKMATRIX') {
$this->_writeDebug($cellID,'Evaluating Function '.$functionName.'() with '.(($argCount == 0) ? 'no' : $argCount).' argument'.(($argCount == 1) ? '' : 's'));
}
if ((array_key_exists($functionName, $this->_controlFunctions)) || (array_key_exists($functionName, $this->_PHPExcelFunctions))) { // function
if (array_key_exists($functionName, $this->_controlFunctions)) {
$functionCall = $this->_controlFunctions[$functionName]['functionCall'];
} elseif (array_key_exists($functionName, $this->_PHPExcelFunctions)) {
$functionCall = $this->_PHPExcelFunctions[$functionName]['functionCall'];
}
// get the arguments for this function
// echo 'Function '.$functionName.' expects '.$argCount.' arguments<br />';
$args = array();
for ($i = $argCount; $i > 0; --$i) {
$arg = $stack->pop();
// if (is_null($arg)) return $this->_raiseFormulaError("internal error");
$args[$i] = $arg;
}
// Reverse the order of the arguments
ksort($args);
// echo 'Arguments are: ';
// print_r($args);
// echo '<br />';
if ($functionName != 'MKMATRIX') {
$argArrayVals = array();
foreach($args as &$arg) {
$argArrayVals[] = self::_showValue($arg);
$arg = self::_unwrapResult($arg);
}
unset($arg);
$this->_writeDebug($cellID,'Evaluating '. $functionName.'( '.implode(', ',$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->_writeDebug($cellID,'Argument is a matrix: '.self::_showValue($operand1));
// $result = array();
// $row = 0;
// foreach($operand1 as $args) {
// if (is_array($args)) {
// foreach($args as $arg) {
// $this->_writeDebug($cellID,'Evaluating '. $functionName.'( '.self::_showValue($arg).' )');
// $r = call_user_func_array($functionCall,$arg);
// $this->_writeDebug($cellID,'Evaluation Result is '.self::_showTypeDetails($r));
// $result[$row][] = $r;
// }
// ++$row;
// } else {
// $this->_writeDebug($cellID,'Evaluating '. $functionName.'( '.self::_showValue($args).' )');
// $r = call_user_func_array($functionCall,$args);
// $this->_writeDebug($cellID,'Evaluation Result is '.self::_showTypeDetails($r));
// $result[] = $r;
// }
// }
// } else {
// Process the argument with the appropriate function call
$result = call_user_func_array($functionCall,$args);
// }
if ($functionName != 'MKMATRIX') {
$this->_writeDebug($cellID,'Evaluation Result is '.self::_showTypeDetails($result));
}
$stack->push(self::_wrapResult($result));
}
} else {
// if the token is a number, boolean, string or an Excel error, push it onto the stack
if (array_key_exists(strtoupper($token), $this->_ExcelConstants)) {
$excelConstant = strtoupper($token);
// echo 'Token is a PHPExcel constant: '.$excelConstant.'<br />';
$stack->push($this->_ExcelConstants[$excelConstant]);
$this->_writeDebug($cellID,'Evaluating Constant '.$excelConstant.' as '.self::_showTypeDetails($this->_ExcelConstants[$excelConstant]));
} elseif ((is_null($token)) || ($token == '') || (is_bool($token)) || (is_numeric($token)) || ($token{0} == '"') || ($token{0} == '#')) {
// echo 'Token is a number, boolean, string or an Excel error<br />';
$stack->push($token);
// if the token is a constant, push the constant value on 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->_writeDebug($cellID,'Evaluating Named Range '.$namedRange);
$cellValue = $this->extractNamedRange($namedRange, $pCell->getParent(), false);
$this->_writeDebug($cellID,'Evaluation Result for named range '.$namedRange.' is '.self::_showTypeDetails($cellValue));
$stack->push($cellValue);
} 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();
if ((is_array($output)) && (self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY)) {
return array_unshift(array_unshift($output));
}
return $output;
} // function _processTokenStack()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

PHPExcel_Calculation::_raiseFormulaError (   $errorMessage)
private

Definition at line 2858 of file Calculation.php.

Referenced by _convertMatrixReferences(), _parseFormula(), and _processTokenStack().

{
$this->formulaError = $errorMessage;
if (!$this->suppressFormulaErrors) throw new Exception($errorMessage);
trigger_error($errorMessage, E_USER_ERROR);
} // function _raiseFormulaError()

+ Here is the caller graph for this function:

static PHPExcel_Calculation::_reindexMatrixDimensions (   $matrix)
staticprivate

Re-index a matrix with straight numeric keys starting from row 0, column 0.

Parameters
mixed$matrixmatrix operand
Returns
array The re-indexed matrix

Definition at line 1937 of file Calculation.php.

{
foreach($matrix as $rowKey => $rowValue) {
$matrix[$rowKey] = array_values($rowValue);
}
return array_values($matrix);
} // function _getMatrixDimensions()
static PHPExcel_Calculation::_resizeMatricesExtend ( &  $matrix1,
&  $matrix2 
)
staticprivate

Ensure that paired matrix operands are both matrices of the same size.

Parameters
mixed$matrix1First matrix operand
mixed$matrix2Second matrix operand

Definition at line 2014 of file Calculation.php.

References $x, and _getMatrixDimensions().

Referenced by _checkMatrixOperands().

{
list($matrix1Rows,$matrix1Columns) = self::_getMatrixDimensions($matrix1);
list($matrix2Rows,$matrix2Columns) = self::_getMatrixDimensions($matrix2);
if (($matrix2Columns < $matrix1Columns) || ($matrix2Rows < $matrix1Rows)) {
if ($matrix2Columns < $matrix1Columns) {
for ($i = 0; $i < $matrix2Rows; ++$i) {
$x = $matrix2[$i][$matrix2Columns-1];
for ($j = $matrix2Columns; $j < $matrix1Columns; ++$j) {
$matrix2[$i][$j] = $x;
}
}
}
if ($matrix2Rows < $matrix1Rows) {
$x = $matrix2[$matrix2Rows-1];
for ($i = 0; $i < $matrix1Rows; ++$i) {
$matrix2[$i] = $x;
}
}
}
if (($matrix1Columns < $matrix2Columns) || ($matrix1Rows < $matrix2Rows)) {
if ($matrix1Columns < $matrix2Columns) {
for ($i = 0; $i < $matrix1Rows; ++$i) {
$x = $matrix1[$i][$matrix1Columns-1];
for ($j = $matrix1Columns; $j < $matrix2Columns; ++$j) {
$matrix1[$i][$j] = $x;
}
}
}
if ($matrix1Rows < $matrix2Rows) {
$x = $matrix1[$matrix1Rows-1];
for ($i = 0; $i < $matrix2Rows; ++$i) {
$matrix1[$i] = $x;
}
}
}
} // function _resizeMatricesExtend()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

static PHPExcel_Calculation::_resizeMatricesShrink ( &  $matrix1,
&  $matrix2 
)
staticprivate

Ensure that paired matrix operands are both matrices of the same size.

Parameters
mixed$matrix1First matrix operand
mixed$matrix2Second matrix operand

Definition at line 1972 of file Calculation.php.

References _getMatrixDimensions().

Referenced by _checkMatrixOperands().

{
list($matrix1Rows,$matrix1Columns) = self::_getMatrixDimensions($matrix1);
list($matrix2Rows,$matrix2Columns) = self::_getMatrixDimensions($matrix2);
if (($matrix2Columns < $matrix1Columns) || ($matrix2Rows < $matrix1Rows)) {
if ($matrix2Columns < $matrix1Columns) {
for ($i = 0; $i < $matrix1Rows; ++$i) {
for ($j = $matrix2Columns; $j < $matrix1Columns; ++$j) {
unset($matrix1[$i][$j]);
}
}
}
if ($matrix2Rows < $matrix1Rows) {
for ($i = $matrix2Rows; $i < $matrix1Rows; ++$i) {
unset($matrix1[$i]);
}
}
}
if (($matrix1Columns < $matrix2Columns) || ($matrix1Rows < $matrix2Rows)) {
if ($matrix1Columns < $matrix2Columns) {
for ($i = 0; $i < $matrix2Rows; ++$i) {
for ($j = $matrix1Columns; $j < $matrix2Columns; ++$j) {
unset($matrix2[$i][$j]);
}
}
}
if ($matrix1Rows < $matrix2Rows) {
for ($i = $matrix1Rows; $i < $matrix2Rows; ++$i) {
unset($matrix2[$i]);
}
}
}
} // function _resizeMatricesShrink()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

static PHPExcel_Calculation::_showTypeDetails (   $value)
staticprivate

Format type and details of an operand for display in the log (based on operand type)

Parameters
mixed$valueFirst matrix operand
Returns
mixed

Definition at line 2094 of file Calculation.php.

References _showValue(), and elseif().

{
switch (gettype($value)) {
case 'double' :
case 'float' :
$typeString = 'a floating point number';
break;
case 'integer' :
$typeString = 'an integer number';
break;
case 'boolean' :
$typeString = 'a boolean';
break;
case 'array' :
$typeString = 'a matrix';
break;
case 'string' :
if ($value == '') {
return 'an empty string';
} elseif ($value{0} == '#') {
return 'a '.$value.' error';
} else {
$typeString = 'a string';
}
break;
case 'NULL' :
return 'a null value';
}
return $typeString.' with a value of '.self::_showValue($value);
} // function _showTypeDetails()

+ Here is the call graph for this function:

static PHPExcel_Calculation::_showValue (   $value)
staticprivate

Format details of an operand for display in the log (based on operand type)

Parameters
mixed$valueFirst matrix operand
Returns
mixed

Definition at line 2060 of file Calculation.php.

References $row, and elseif().

Referenced by _processTokenStack(), and _showTypeDetails().

{
if (is_array($value)) {
$retVal = '';
$i = 0;
foreach($value as $row) {
if (is_array($row)) {
if ($i > 0) { $retVal .= '; '; }
$j = 0;
foreach($row as $column) {
if ($j > 0) { $retVal .= ', '; }
$retVal .= $column;
++$j;
}
} else {
if ($i > 0) { $retVal .= ', '; }
$retVal .= $row;
}
++$i;
}
return '{ '.$retVal.' }';
} elseif(is_bool($value)) {
return ($value) ? 'TRUE' : 'FALSE';
}
return $value;
} // function _showValue()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

static PHPExcel_Calculation::_unwrapResult (   $value)
static

Remove quotes used as a wrapper to identify string values.

Parameters
mixed$value
Returns
mixed

Definition at line 1726 of file Calculation.php.

References PHPExcel_Calculation_Functions\NaN().

Referenced by _processTokenStack(), _validateBinaryOperand(), calculateCellValue(), and calculateFormula().

{
if (is_string($value)) {
if ((strlen($value) > 0) && ($value{0} == '"') && (substr($value,-1) == '"')) {
return substr($value,1,-1);
}
// Convert numeric errors to NaN error
} else if((is_float($value)) && ((is_nan($value)) || (is_infinite($value)))) {
return PHPExcel_Calculation_Functions::NaN();
}
return $value;
} // function _unwrapResult()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

PHPExcel_Calculation::_validateBinaryOperand (   $cellID,
&  $operand,
&  $stack 
)
private

Definition at line 2720 of file Calculation.php.

References _unwrapResult(), and _writeDebug().

Referenced by _executeNumericBinaryOperation().

{
// Numbers, matrices and booleans can pass straight through, as they're already valid
if (is_string($operand)) {
// We only need special validations for the operand if it is a string
// Start by stripping off the quotation marks we use to identify true excel string values internally
if ($operand > '' && $operand{0} == '"') { $operand = self::_unwrapResult($operand,'"'); }
// If the string is a numeric value, we treat it as a numeric, so no further testing
if (!is_numeric($operand)) {
// If not a numeric, test to see if the value is an Excel error, and so can't be used in normal binary operations
if ($operand > '' && $operand{0} == '#') {
$stack->push($operand);
$this->_writeDebug($cellID,'Evaluation Result is '.self::_showTypeDetails($operand));
return false;
} else {
// If not a numeric, then it's a text string, and so can't be used in mathematical binary operations
$stack->push('#VALUE!');
$this->_writeDebug($cellID,'Evaluation Result is a '.self::_showTypeDetails('#VALUE!'));
return false;
}
}
}
// return a true if the value of the operand is one that we can use in normal binary operations
return true;
} // function _validateBinaryOperand()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

static PHPExcel_Calculation::_wrapResult (   $value)
static

Wrap string values in quotes.

Parameters
mixed$value
Returns
mixed

Definition at line 1702 of file Calculation.php.

References PHPExcel_Calculation_Functions\NaN().

Referenced by _calculateFormulaValue(), PHPExcel_Calculation_Functions\COUNTIF(), and PHPExcel_Calculation_Functions\SUMIF().

{
if (is_string($value)) {
// Error values cannot be "wrapped"
if (preg_match('/^'.self::CALCULATION_REGEXP_ERROR.'$/i', $value, $match)) {
// Return Excel errors "as is"
return $value;
}
// Return strings wrapped in quotes
return '"'.$value.'"';
// Convert numeric errors to NaN error
} else if((is_float($value)) && ((is_nan($value)) || (is_infinite($value)))) {
return PHPExcel_Calculation_Functions::NaN();
}
return $value;
} // function _wrapResult()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

PHPExcel_Calculation::_writeDebug (   $cellID,
  $message 
)
private

Definition at line 2847 of file Calculation.php.

Referenced by _calculateFormulaValue(), _executeBinaryComparisonOperation(), _executeNumericBinaryOperation(), _processTokenStack(), and _validateBinaryOperand().

{
// Only write the debug log if logging is enabled
if ($this->writeDebugLog) {
// $prefix = substr(implode(' -> ',$this->debugLogStack).' -> ',4+strlen($this->debugLogStack[0]));
$prefix = implode(' -> ',$this->debugLogStack).' -> ';
$this->debugLog[] = $prefix.$message;
}
} // function _writeDebug()

+ Here is the caller graph for this function:

PHPExcel_Calculation::calculate ( PHPExcel_Cell  $pCell = null)

Calculate cell value (using formula from a cell ID) Retained for backward compatibility.

public

Parameters
PHPExcel_Cell$pCellCell to calculate
Returns
mixed
Exceptions
Exception

Definition at line 1750 of file Calculation.php.

References calculateCellValue().

{
return $this->calculateCellValue($pCell);
} // function calculate()

+ Here is the call graph for this function:

PHPExcel_Calculation::calculateCellValue ( PHPExcel_Cell  $pCell = null,
  $resetLog = true 
)

Calculate the value of a cell formula.

public

Parameters
PHPExcel_Cell$pCellCell to calculate
Boolean$resetLogFlag indicating whether the debug log should be reset or not
Returns
mixed
Exceptions
Exception

Definition at line 1764 of file Calculation.php.

References _calculateFormulaValue(), and _unwrapResult().

Referenced by calculate().

{
if ($resetLog) {
// Initialise the logging settings if requested
$this->formulaError = null;
$this->debugLog = array();
$this->debugLogStack = array();
}
// Read the formula from the cell
if (is_null($pCell)) {
return null;
}
$formula = $pCell->getValue();
$cellID = $pCell->getCoordinate();
// Execute the calculation for the cell formula
return self::_unwrapResult($this->_calculateFormulaValue($formula, $cellID, $pCell));
} // function calculateCellValue(

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

PHPExcel_Calculation::calculateFormula (   $formula,
  $cellID = null,
PHPExcel_Cell  $pCell = null 
)

Calculate the value of a formula.

Parameters
string$formulaFormula to parse
Returns
mixed
Exceptions
Exception

Definition at line 1812 of file Calculation.php.

References $result, _calculateFormulaValue(), _unwrapResult(), and getCalculationCacheEnabled().

{
// Initialise the logging settings
$this->formulaError = null;
$this->debugLog = array();
$this->debugLogStack = array();
// Disable calculation cacheing because it only applies to cell calculations, not straight formulae
// But don't actually flush any cache
$resetCache = $this->getCalculationCacheEnabled();
$this->_calculationCacheEnabled = false;
// Execute the calculation
$result = self::_unwrapResult($this->_calculateFormulaValue($formula, $cellID, $pCell));
// Reset calculation cacheing to its previous state
$this->_calculationCacheEnabled = $resetCache;
return $result;
} // function calculateFormula()

+ Here is the call graph for this function:

PHPExcel_Calculation::clearCalculationCache ( )

Clear calculation cache.

Definition at line 1669 of file Calculation.php.

Referenced by setCalculationCacheEnabled().

{
$this->_calculationCache = array();
} // function clearCalculationCache()

+ Here is the caller graph for this function:

PHPExcel_Calculation::disableCalculationCache ( )

Disable calculation cache.

Definition at line 1661 of file Calculation.php.

References setCalculationCacheEnabled().

{
$this->setCalculationCacheEnabled(false);
} // function disableCalculationCache()

+ Here is the call graph for this function:

PHPExcel_Calculation::enableCalculationCache ( )

Enable calculation cache.

Definition at line 1653 of file Calculation.php.

References setCalculationCacheEnabled().

{
$this->setCalculationCacheEnabled(true);
} // function enableCalculationCache()

+ Here is the call graph for this function:

PHPExcel_Calculation::extractCellRange (   $pRange = 'A1',
PHPExcel_Worksheet  $pSheet = null,
  $resetLog = true 
)

Extract range values.

Parameters
string$pRangeString based range representation
PHPExcel_Worksheet$pSheetWorksheet
Returns
mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
Exceptions
Exception

Definition at line 2873 of file Calculation.php.

References PHPExcel_Cell\coordinateFromString(), PHPExcel_Cell\extractAllCellReferencesInRange(), and PHPExcel_Worksheet\extractSheetTitle().

Referenced by _processTokenStack().

{
// Return value
$returnValue = array ( );
// echo 'extractCellRange('.$pRange.')<br />';
// Worksheet given?
if (!is_null($pSheet)) {
// Worksheet reference?
// echo 'Current sheet name is '.$pSheet->getTitle().'<br />';
// echo 'Range reference is '.$pRange.'<br />';
if (strpos ($pRange, '!') !== false) {
// echo '$pRange reference includes sheet reference<br />';
$worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
$pSheet = $pSheet->getParent()->getSheetByName($worksheetReference[0]);
// echo 'New sheet name is '.$pSheet->getTitle().'<br />';
$pRange = $worksheetReference[1];
// echo 'Adjusted Range reference is '.$pRange.'<br />';
}
// Extract range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
if (count($aReferences) == 1) {
return $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
}
// Extract cell data
foreach ($aReferences as $reference) {
// Extract range
list($currentCol,$currentRow) = PHPExcel_Cell::coordinateFromString($reference);
$returnValue[$currentCol][$currentRow] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
}
}
// Return
return $returnValue;
} // function extractCellRange()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

PHPExcel_Calculation::extractNamedRange (   $pRange = 'A1',
PHPExcel_Worksheet  $pSheet = null,
  $resetLog = true 
)

Extract range values.

Parameters
string$pRangeString based range representation
PHPExcel_Worksheet$pSheetWorksheet
Returns
mixed Array of values in range if range contains more than one element. Otherwise, a single value is returned.
Exceptions
Exception

Definition at line 2920 of file Calculation.php.

References PHPExcel_Cell\coordinateFromString(), PHPExcel_Cell\extractAllCellReferencesInRange(), PHPExcel_Worksheet\extractSheetTitle(), and PHPExcel_NamedRange\resolveRange().

Referenced by _processTokenStack().

{
// Return value
$returnValue = array ( );
// echo 'extractNamedRange('.$pRange.')<br />';
// Worksheet given?
if (!is_null($pSheet)) {
// Worksheet reference?
// echo 'Current sheet name is '.$pSheet->getTitle().'<br />';
// echo 'Range reference is '.$pRange.'<br />';
if (strpos ($pRange, '!') !== false) {
// echo '$pRange reference includes sheet reference<br />';
$worksheetReference = PHPExcel_Worksheet::extractSheetTitle($pRange, true);
$pSheet = $pSheet->getParent()->getSheetByName($worksheetReference[0]);
// echo 'New sheet name is '.$pSheet->getTitle().'<br />';
$pRange = $worksheetReference[1];
// echo 'Adjusted Range reference is '.$pRange.'<br />';
}
// Named range?
$namedRange = PHPExcel_NamedRange::resolveRange($pRange, $pSheet);
if (!is_null($namedRange)) {
// echo 'Named Range '.$pRange.' (';
$pRange = $namedRange->getRange();
// echo $pRange.') is in sheet '.$namedRange->getWorksheet()->getTitle().'<br />';
if ($pSheet->getTitle() != $namedRange->getWorksheet()->getTitle()) {
if (!$namedRange->getLocalOnly()) {
$pSheet = $namedRange->getWorksheet();
} else {
return $returnValue;
}
}
}
// Extract range
$aReferences = PHPExcel_Cell::extractAllCellReferencesInRange($pRange);
if (count($aReferences) == 1) {
return $pSheet->getCell($aReferences[0])->getCalculatedValue($resetLog);
}
// Extract cell data
foreach ($aReferences as $reference) {
// Extract range
list($currentCol,$currentRow) = PHPExcel_Cell::coordinateFromString($reference);
// echo 'NAMED RANGE: $currentCol='.$currentCol.' $currentRow='.$currentRow.'<br />';
$returnValue[$currentRow][$currentCol] = $pSheet->getCell($reference)->getCalculatedValue($resetLog);
}
// print_r($returnValue);
// echo '<br />';
$returnValue = array_values($returnValue);
foreach($returnValue as &$rr) {
$rr = array_values($rr);
}
unset($rr);
// print_r($returnValue);
// echo '<br />';
}
// Return
return $returnValue;
} // function extractNamedRange()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

static PHPExcel_Calculation::getArrayReturnType ( )
static

Return the Array Return Type (Array or Value of first element in the array)

public

Returns
string $returnType Array return type

Definition at line 1622 of file Calculation.php.

References $returnArrayAsType.

Referenced by PHPExcel_Writer_PDF\save(), PHPExcel_Writer_CSV\save(), and PHPExcel_Writer_HTML\save().

{
return self::$returnArrayAsType;
} // function getExcelCalendar()

+ Here is the caller graph for this function:

PHPExcel_Calculation::getCalculationCacheEnabled ( )

Is calculation caching enabled?

public

Returns
boolean

Definition at line 1633 of file Calculation.php.

References $_calculationCacheEnabled.

Referenced by calculateFormula().

{
return $this->_calculationCacheEnabled;
} // function getCalculationCacheEnabled()

+ Here is the caller graph for this function:

PHPExcel_Calculation::getCalculationCacheExpirationTime ( )

Get calculation cache expiration time.

Returns
float

Definition at line 1679 of file Calculation.php.

References $_calculationCacheExpirationTime.

{
return $this->_calculationCacheExpirationTime;
} // getCalculationCacheExpirationTime()
static PHPExcel_Calculation::getInstance ( )
static

Get an instance of this class.

public

Returns
PHPExcel_Calculation

Definition at line 1579 of file Calculation.php.

References $_instance.

Referenced by PHPExcel_Calculation_Functions\COUNTIF(), PHPExcel_Cell\getCalculatedValue(), and PHPExcel_Calculation_Functions\SUMIF().

{
if (!isset(self::$_instance) || is_null(self::$_instance)) {
self::$_instance = new PHPExcel_Calculation();
}
return self::$_instance;
} // function getInstance()

+ Here is the caller graph for this function:

PHPExcel_Calculation::isImplemented (   $pFunction = '')

Is a specific function implemented?

Parameters
string$pFunctionFunction Name
Returns
boolean

Definition at line 2989 of file Calculation.php.

{
$pFunction = strtoupper ($pFunction);
if (isset ($this->_PHPExcelFunctions[$pFunction])) {
return ($this->_PHPExcelFunctions[$pFunction]['functionCall'] != 'PHPExcel_Calculation_Functions::DUMMY');
} else {
return false;
}
} // function isImplemented()
PHPExcel_Calculation::listFunctionNames ( )

Get a list of implemented Excel function names.

Returns
array

Definition at line 3027 of file Calculation.php.

{
// Return value
$returnValue = array();
// Loop functions
foreach ($this->_PHPExcelFunctions as $functionName => $function) {
$returnValue[] = $functionName;
}
// Return
return $returnValue;
} // function listFunctionNames()
PHPExcel_Calculation::listFunctions ( )

Get a list of all implemented functions as an array of function objects.

Returns
array of PHPExcel_Calculation_Function

Definition at line 3004 of file Calculation.php.

{
// Return value
$returnValue = array();
// Loop functions
foreach($this->_PHPExcelFunctions as $functionName => $function) {
if ($function['functionCall'] != 'PHPExcel_Calculation_Functions::DUMMY') {
$returnValue[$functionName] = new PHPExcel_Calculation_Function($function['category'],
$functionName,
$function['functionCall']
);
}
}
// Return
return $returnValue;
} // function listFunctions()
PHPExcel_Calculation::parseFormula (   $formula)

Validate and parse a formula string.

Parameters
string$formulaFormula to parse
Returns
array
Exceptions
Exception

Definition at line 1791 of file Calculation.php.

References _parseFormula().

{
// Basic validation that this is indeed a formula
// We return an empty array if not
$formula = trim($formula);
if ($formula{0} != '=') return array();
$formula = trim(substr($formula,1));
$formulaLength = strlen($formula);
if ($formulaLength < 1) return array();
// Parse the formula and return the token stack
return $this->_parseFormula($formula);
} // function parseFormula()

+ Here is the call graph for this function:

static PHPExcel_Calculation::setArrayReturnType (   $returnType)
static

Set the Array Return Type (Array or Value of first element in the array)

public

Parameters
string$returnTypeArray return type
Returns
boolean Success or failure

Definition at line 1606 of file Calculation.php.

Referenced by PHPExcel_Writer_PDF\save(), PHPExcel_Writer_CSV\save(), and PHPExcel_Writer_HTML\save().

{
if (($returnType == self::RETURN_ARRAY_AS_VALUE) ||
($returnType == self::RETURN_ARRAY_AS_ARRAY)) {
self::$returnArrayAsType = $returnType;
return True;
}
return False;
} // function setExcelCalendar()

+ Here is the caller graph for this function:

PHPExcel_Calculation::setCalculationCacheEnabled (   $pValue = true)

Enable/disable calculation cache.

public

Parameters
boolean$pValue

Definition at line 1644 of file Calculation.php.

References clearCalculationCache().

Referenced by disableCalculationCache(), and enableCalculationCache().

{
$this->_calculationCacheEnabled = $pValue;
$this->clearCalculationCache();
} // function setCalculationCacheEnabled()

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

PHPExcel_Calculation::setCalculationCacheExpirationTime (   $pValue = 0.01)

Set calculation cache expiration time.

Parameters
float$pValue

Definition at line 1689 of file Calculation.php.

{
$this->_calculationCacheExpirationTime = $pValue;
} // function setCalculationCacheExpirationTime()

Field Documentation

PHPExcel_Calculation::$_binaryOperators = array('+', '-', '*', '/', '^', '&', '>', '<', '=', '>=', '<=', '<>')
private

Definition at line 131 of file Calculation.php.

PHPExcel_Calculation::$_calculationCache = array ()
private

Definition at line 95 of file Calculation.php.

PHPExcel_Calculation::$_calculationCacheEnabled = true
private

Definition at line 104 of file Calculation.php.

Referenced by getCalculationCacheEnabled().

PHPExcel_Calculation::$_calculationCacheExpirationTime = 0.01
private

Definition at line 113 of file Calculation.php.

Referenced by _calculateFormulaValue(), and getCalculationCacheExpirationTime().

PHPExcel_Calculation::$_controlFunctions
private
Initial value:
array(
'MKMATRIX' => array('argumentCount' => '*',
'functionCall' => array('self','_mkMatrix')
)
)

Definition at line 1564 of file Calculation.php.

PHPExcel_Calculation::$_ExcelConstants
private
Initial value:
array('TRUE' => True,
'FALSE' => False,
'NULL' => Null
)

Definition at line 141 of file Calculation.php.

PHPExcel_Calculation::$_instance
staticprivate

Definition at line 86 of file Calculation.php.

Referenced by getInstance().

PHPExcel_Calculation::$_operators = array('+', '-', '*', '/', '^', '&', '%', '_', '>', '<', '=', '>=', '<=', '<>')
private

Definition at line 122 of file Calculation.php.

PHPExcel_Calculation::$_PHPExcelFunctions
private

Definition at line 147 of file Calculation.php.

PHPExcel_Calculation::$debugLog = array()

Definition at line 137 of file Calculation.php.

PHPExcel_Calculation::$debugLogStack = array()
private

Definition at line 136 of file Calculation.php.

PHPExcel_Calculation::$formulaError = null

Definition at line 134 of file Calculation.php.

PHPExcel_Calculation::$returnArrayAsType = self::RETURN_ARRAY_AS_ARRAY
staticprivate

Definition at line 78 of file Calculation.php.

Referenced by getArrayReturnType().

PHPExcel_Calculation::$suppressFormulaErrors = false

Definition at line 133 of file Calculation.php.

PHPExcel_Calculation::$writeDebugLog = false

Definition at line 135 of file Calculation.php.

const PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF = '(((\w*)|(\'.*\')|(\".*\"))!)?\$?([a-z]+)\$?(\d+)(:\$?([a-z]+)\$?(\d+))?'

Definition at line 67 of file Calculation.php.

Referenced by PHPExcel_Worksheet\cellExists(), PHPExcel_Worksheet\getCell(), and PHPExcel_ReferenceHelper\updateFormulaReferences().

const PHPExcel_Calculation::CALCULATION_REGEXP_ERROR = '\#[^!]+!'

Definition at line 71 of file Calculation.php.

const PHPExcel_Calculation::CALCULATION_REGEXP_FUNCTION = '([A-Z][A-Z0-9\.]*)[\s]*\('

Definition at line 65 of file Calculation.php.

const PHPExcel_Calculation::CALCULATION_REGEXP_NAMEDRANGE = '(((\w*)|(\'.*\')|(\".*\"))!)?([_A-Z][_A-Z0-9]*)'

Definition at line 69 of file Calculation.php.

const PHPExcel_Calculation::CALCULATION_REGEXP_NUMBER = '[-+]?\d*\.?\d+(e[-+]?\d+)?'

Constants.

Regular Expressions

Definition at line 59 of file Calculation.php.

const PHPExcel_Calculation::CALCULATION_REGEXP_OPENBRACE = '\('

Definition at line 63 of file Calculation.php.

const PHPExcel_Calculation::CALCULATION_REGEXP_STRING = '"(?:[^"]|"")*"'

Definition at line 61 of file Calculation.php.

const PHPExcel_Calculation::RETURN_ARRAY_AS_ARRAY = 'array'

Definition at line 76 of file Calculation.php.

const PHPExcel_Calculation::RETURN_ARRAY_AS_VALUE = 'value'

constants

Definition at line 75 of file Calculation.php.

Referenced by PHPExcel_Writer_PDF\save(), PHPExcel_Writer_CSV\save(), and PHPExcel_Writer_HTML\save().

The documentation for this class was generated from the following file: