/**
* MATCH
*
* The MATCH function searches for a specified item in a range of cells
*
* @param lookup_value The value that you want to match in lookup_array
* @param lookup_array The range of cells being searched
* @param match_type The number -1, 0, or 1. -1 means above, 0 means exact match, 1 means below. If match_type is 1 or -1, the list has to be ordered.
* @return integer The relative position of the found item
*/
public static function MATCH($lookup_value, $lookup_array, $match_type = 1)
{
$lookup_array = PHPExcel_Calculation_Functions::flattenArray($lookup_array);
$lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
$match_type = is_null($match_type) ? 1 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($match_type);
// MATCH is not case sensitive
$lookup_value = strtolower($lookup_value);
// lookup_value type has to be number, text, or logical values
if (!is_numeric($lookup_value) && !is_string($lookup_value) && !is_bool($lookup_value)) {
return PHPExcel_Calculation_Functions::NA();
}
// match_type is 0, 1 or -1
if ($match_type !== 0 && $match_type !== -1 && $match_type !== 1) {
return PHPExcel_Calculation_Functions::NA();
}
// lookup_array should not be empty
$lookupArraySize = count($lookup_array);
if ($lookupArraySize <= 0) {
return PHPExcel_Calculation_Functions::NA();
}
// lookup_array should contain only number, text, or logical values, or empty (null) cells
foreach ($lookup_array as $i => $lookupArrayValue) {
// check the type of the value
if (!is_numeric($lookupArrayValue) && !is_string($lookupArrayValue) && !is_bool($lookupArrayValue) && !is_null($lookupArrayValue)) {
return PHPExcel_Calculation_Functions::NA();
}
// convert strings to lowercase for case-insensitive testing
if (is_string($lookupArrayValue)) {
$lookup_array[$i] = strtolower($lookupArrayValue);
}
if (is_null($lookupArrayValue) && ($match_type == 1 || $match_type == -1)) {
$lookup_array = array_slice($lookup_array, 0, $i - 1);
}
}
// if match_type is 1 or -1, the list has to be ordered
if ($match_type == 1) {
asort($lookup_array);
$keySet = array_keys($lookup_array);
} elseif ($match_type == -1) {
arsort($lookup_array);
$keySet = array_keys($lookup_array);
}
// **
// find the match
// **
// loop on the cells
// var_dump($lookup_array);
// echo '<br>';
foreach ($lookup_array as $i => $lookupArrayValue) {
if ($match_type == 0 && $lookupArrayValue == $lookup_value) {
// exact match
return ++$i;
} elseif ($match_type == -1 && $lookupArrayValue <= $lookup_value) {
// echo '$i = '.$i.' => ';
// var_dump($lookupArrayValue);
// echo '<br>';
// echo 'Keyset = ';
// var_dump($keySet);
// echo '<br>';
$i = array_search($i, $keySet);
// echo '$i='.$i.'<br>';
// if match_type is -1 <=> find the smallest value that is greater than or equal to lookup_value
if ($i < 1) {
// 1st cell was allready smaller than the lookup_value
break;
} else {
// the previous cell was the match
return $keySet[$i - 1] + 1;
}
} elseif ($match_type == 1 && $lookupArrayValue >= $lookup_value) {
// echo '$i = '.$i.' => ';
// var_dump($lookupArrayValue);
// echo '<br>';
// echo 'Keyset = ';
// var_dump($keySet);
// echo '<br>';
$i = array_search($i, $keySet);
// echo '$i='.$i.'<br>';
// if match_type is 1 <=> find the largest value that is less than or equal to lookup_value
if ($i < 1) {
// 1st cell was allready bigger than the lookup_value
break;
} else {
// the previous cell was the match
return $keySet[$i - 1] + 1;
}
}
}
// unsuccessful in finding a match, return #N/A error value
return PHPExcel_Calculation_Functions::NA();
}
/**
* WORKDAY
*
* @param mixed Start date
* @param mixed number of days for adjustment
* @param array of mixed Optional Date Series
* @return long Interval between the dates
*/
public static function WORKDAY($startDate, $endDays)
{
// Retrieve the mandatory start date and days that are referenced in the function definition
$startDate = PHPExcel_Calculation_Functions::flattenSingleValue($startDate);
$endDays = (int) PHPExcel_Calculation_Functions::flattenSingleValue($endDays);
// Flush the mandatory start date and days that are referenced in the function definition, and get the optional days
$dateArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
array_shift($dateArgs);
array_shift($dateArgs);
if (is_string($startDate = self::_getDateValue($startDate)) || !is_numeric($endDays)) {
return PHPExcel_Calculation_Functions::VALUE();
}
$startDate = (double) floor($startDate);
// If endDays is 0, we always return startDate
if ($endDays == 0) {
return $startDate;
}
$decrementing = $endDays < 0 ? True : False;
// Adjust the start date if it falls over a weekend
$startDoW = self::DAYOFWEEK($startDate, 3);
if (self::DAYOFWEEK($startDate, 3) >= 5) {
$startDate += $decrementing ? -$startDoW + 4 : 7 - $startDoW;
$decrementing ? $endDays++ : $endDays--;
}
// Add endDays
$endDate = (double) $startDate + intval($endDays / 5) * 7 + $endDays % 5;
// Adjust the calculated end date if it falls over a weekend
$endDoW = self::DAYOFWEEK($endDate, 3);
if ($endDoW >= 5) {
$endDate += $decrementing ? -$endDoW + 4 : 7 - $endDoW;
}
// Test any extra holiday parameters
if (count($dateArgs) > 0) {
$holidayCountedArray = $holidayDates = array();
foreach ($dateArgs as $holidayDate) {
if (!is_null($holidayDate) && trim($holidayDate) > '') {
if (is_string($holidayDate = self::_getDateValue($holidayDate))) {
return PHPExcel_Calculation_Functions::VALUE();
}
if (self::DAYOFWEEK($holidayDate, 3) < 5) {
$holidayDates[] = $holidayDate;
}
}
}
if ($decrementing) {
rsort($holidayDates, SORT_NUMERIC);
} else {
sort($holidayDates, SORT_NUMERIC);
}
foreach ($holidayDates as $holidayDate) {
if ($decrementing) {
if ($holidayDate <= $startDate && $holidayDate >= $endDate) {
if (!in_array($holidayDate, $holidayCountedArray)) {
--$endDate;
$holidayCountedArray[] = $holidayDate;
}
}
} else {
if ($holidayDate >= $startDate && $holidayDate <= $endDate) {
if (!in_array($holidayDate, $holidayCountedArray)) {
++$endDate;
$holidayCountedArray[] = $holidayDate;
}
}
}
// Adjust the calculated end date if it falls over a weekend
$endDoW = self::DAYOFWEEK($endDate, 3);
if ($endDoW >= 5) {
$endDate += $decrementing ? -$endDoW + 4 : 7 - $endDoW;
}
}
}
switch (PHPExcel_Calculation_Functions::getReturnDateType()) {
case PHPExcel_Calculation_Functions::RETURNDATE_EXCEL:
return (double) $endDate;
break;
case PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC:
return (int) PHPExcel_Shared_Date::ExcelToPHP($endDate);
break;
case PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT:
return PHPExcel_Shared_Date::ExcelToPHPObject($endDate);
break;
}
}
private function _calculateTopTenValue($columnID, $startRow, $endRow, $ruleType, $ruleValue)
{
$range = $columnID . $startRow . ':' . $columnID . $endRow;
$dataValues = PHPExcel_Calculation_Functions::flattenArray($this->_workSheet->rangeToArray($range, NULL, TRUE, FALSE));
$dataValues = array_filter($dataValues);
if ($ruleType == PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) {
rsort($dataValues);
} else {
sort($dataValues);
}
return array_pop(array_slice($dataValues, 0, $ruleValue));
}
/**
* CONCATENATE
*
* @return string
*/
public static function CONCATENATE()
{
// Return value
$returnValue = '';
// Loop through arguments
$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
foreach ($aArgs as $arg) {
if (is_bool($arg)) {
if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
$arg = (int) $arg;
} else {
$arg = $arg ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
}
}
$returnValue .= $arg;
}
// Return
return $returnValue;
}
/**
* Format type and details of an operand for display in the log (based on operand type)
*
* @param mixed $value First matrix operand
* @return mixed
*/
private static function _showTypeDetails($value)
{
$testArray = PHPExcel_Calculation_Functions::flattenArray($value);
if (count($testArray) == 1) {
$value = array_pop($testArray);
}
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);
}
/**
* Format type and details of an operand for display in the log (based on operand type)
*
* @param mixed $value First matrix operand
* @return mixed
*/
private static function _showTypeDetails($value)
{
$testArray = PHPExcel_Calculation_Functions::flattenArray($value);
if (count($testArray) == 1) {
$value = array_pop($testArray);
}
if (is_null($value)) {
return 'a null value';
} elseif (is_float($value)) {
$typeString = 'a floating point number';
} elseif (is_int($value)) {
$typeString = 'an integer number';
} elseif (is_bool($value)) {
$typeString = 'a boolean';
} elseif (is_array($value)) {
$typeString = 'a matrix';
} else {
if ($value == '') {
return 'an empty string';
} elseif ($value[0] == '#') {
return 'a ' . $value . ' error';
} else {
$typeString = 'a string';
}
}
return $typeString . ' with a value of ' . self::_showValue($value);
}
/**
* Calculate cell value (using formula)
*
* @param PHPExcel_Cell $pCell Cell to calculate
* @return mixed
* @throws Exception
*/
public function calculate(PHPExcel_Cell $pCell = null)
{
// Return value
$returnValue = '';
// Is the value present in calculation cache?
if ($this->getCalculationCacheEnabled()) {
if (isset($this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()])) {
if (time() + microtime() - $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['time'] < $this->_calculationCacheExpirationTime) {
// Return result
$returnValue = $this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['data'];
if (is_array($returnValue) && self::$returnArrayAsType == self::RETURN_ARRAY_AS_VALUE) {
return array_shift(PHPExcel_Calculation_Functions::flattenArray($returnValue));
}
return $returnValue;
} else {
unset($this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]);
}
}
}
// Formula
$formula = $pCell->getValue();
// Executable formula array
$executableFormulaArray = array();
// Parse formula into a tree of tokens
$objParser = new PHPExcel_Calculation_FormulaParser($formula);
// Loop trough parsed tokens and create an executable formula
$inFunction = false;
$token = null;
$tokenCount = $objParser->getTokenCount();
for ($i = 0; $i < $tokenCount; ++$i) {
$token = $objParser->getToken($i);
$tokenType = $token->getTokenType();
$tokenSubType = $token->getTokenSubType();
$tokenValue = $token->getValue();
// Is it a cell reference?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_RANGE) {
// Adjust reference
$reference = str_replace('$', '', $tokenValue);
// Add to executable formula array
$executableFormulaArray[] = '$this->extractRange("' . $reference . '", $pCell->getParent())';
continue;
}
// Is it a concatenation operator?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_CONCATENATION) {
// Add to executable formula array
$executableFormulaArray[] = '.';
continue;
}
// Is it a logical operator?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERATORINFIX && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_LOGICAL) {
// Temporary variable
$tmp = '';
switch ($tokenValue) {
case '=':
$tmp = '==';
break;
case '<>':
$tmp = '!=';
break;
default:
$tmp = $tokenValue;
}
// Add to executable formula array
$executableFormulaArray[] = $tmp;
continue;
}
// Is it a subexpression?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_SUBEXPRESSION) {
// Temporary variable
$tmp = '';
switch ($tokenSubType) {
case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START:
$tmp = '(';
break;
case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP:
$tmp = ')';
break;
}
// Add to executable formula array
$executableFormulaArray[] = $tmp;
continue;
}
// Is it a function?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_FUNCTION) {
// Temporary variable
$tmp = '';
// Check the function type
if ($tokenValue == 'ARRAY' || $tokenValue == 'ARRAYROW') {
// An array or an array row...
$tmp = 'array(';
} else {
// A regular function call...
switch ($tokenSubType) {
case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_START:
// Check if the function call is allowed...
if (!isset($this->_functionMappings[strtoupper($tokenValue)])) {
return '#NAME?';
}
// Map the function call
$tmp = $this->_functionMappings[strtoupper($tokenValue)]->getPHPExcelName() . '(';
$inFunction = true;
break;
case PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_STOP:
$tmp = ')';
break;
}
}
// Add to executable formula array
$executableFormulaArray[] = $tmp;
continue;
}
// Is it text?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_TEXT) {
// Temporary variable
$tmp = $tokenValue;
$tmp = str_replace('"', '\\"', $tmp);
// Add to executable formula array
$executableFormulaArray[] = '"' . $tmp . '"';
continue;
}
// Is it a number?
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_NUMBER) {
// Add to executable formula array
$executableFormulaArray[] = $tokenValue;
continue;
}
// Is it an error? Add it as text...
if ($tokenType == PHPExcel_Calculation_FormulaToken::TOKEN_TYPE_OPERAND && $tokenSubType == PHPExcel_Calculation_FormulaToken::TOKEN_SUBTYPE_ERROR) {
// Add to executable formula array
$executableFormulaArray[] = '"' . $tokenValue . '"';
continue;
}
// Is it something else?
$executableFormulaArray[] = $tokenValue;
}
$fromArray = array('(,', ',,', ',)', '( ,', ', ,', ', )', '$this');
$toArray = array('(null,', ',null,', ',null)', '(null,', ',null,', ',null)', '$pThat');
// Evaluate formula
try {
$formula = implode(' ', $executableFormulaArray);
$formula = str_replace($fromArray, $toArray, $formula);
/*
* The following code block can cause an error like:
* Fatal error: Unsupported operand types in ...: runtime-created function on line 1
*
* This is due to the fact that a FATAL error is an E_ERROR,
* and it can not be caught using try/catch or any other
* Exception/error handling feature in PHP.
*
* A feature request seems to be made once, but it has been
* closed without any deliverables:
* http://bugs.php.net/bug.php?id=40014
*/
$temporaryCalculationFunction = @create_function('$pThat, $pCell', "return {$formula};");
if ($temporaryCalculationFunction === FALSE) {
$returnValue = '#N/A';
} else {
$calculationExceptionHandler = new PHPExcel_Calculation_ExceptionHandler();
$returnValue = $temporaryCalculationFunction($this, $pCell);
}
} catch (Exception $ex) {
$returnValue = '#N/A';
}
// Save to calculation cache
if ($this->getCalculationCacheEnabled()) {
$this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['time'] = time() + microtime();
$this->_calculationCache[$pCell->getParent()->getTitle()][$pCell->getCoordinate()]['data'] = $returnValue;
}
// Return result
if (is_array($returnValue) && self::$returnArrayAsType == self::RETURN_ARRAY_AS_VALUE) {
return array_shift(PHPExcel_Calculation_Functions::flattenArray($returnValue));
}
return $returnValue;
}
/**
* VARP
*
* Calculates variance based on the entire population
*
* Excel Function:
* VARP(value1[,value2[, ...]])
*
* @access public
* @category Statistical Functions
* @param mixed $arg,... Data values
* @return float
*/
public static function VARP()
{
// Return value
$returnValue = PHPExcel_Calculation_Functions::DIV0();
$summerA = $summerB = 0;
// Loop through arguments
$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
$aCount = 0;
foreach ($aArgs as $arg) {
if (is_bool($arg)) {
$arg = (int) $arg;
}
// Is it a numeric value?
if (is_numeric($arg) && !is_string($arg)) {
$summerA += $arg * $arg;
$summerB += $arg;
++$aCount;
}
}
// Return
if ($aCount > 0) {
$summerA *= $aCount;
$summerB *= $summerB;
$returnValue = ($summerA - $summerB) / ($aCount * $aCount);
}
return $returnValue;
}
public function refresh(PHPExcel_Worksheet $worksheet, $flatten = TRUE)
{
if ($this->_dataSource !== NULL) {
$calcEngine = PHPExcel_Calculation::getInstance();
$newDataValues = PHPExcel_Calculation::_unwrapResult($calcEngine->_calculateFormulaValue('=' . $this->_dataSource, NULL, $worksheet->getCell('A1')));
if ($flatten) {
$this->_dataValues = PHPExcel_Calculation_Functions::flattenArray($newDataValues);
} else {
$newArray = array_values(array_shift($newDataValues));
foreach ($newArray as $i => $newDataSet) {
$newArray[$i] = array($newDataSet);
}
foreach ($newDataValues as $newDataSet) {
$i = 0;
foreach ($newDataSet as $newDataVal) {
array_unshift($newArray[$i++], $newDataVal);
}
}
$this->_dataValues = $newArray;
}
$this->_pointCount = count($this->_dataValues);
}
}
/**
* IMPRODUCT
*
* Returns the product of two or more complex numbers in x + yi or x + yj text format.
*
* Excel Function:
* IMPRODUCT(complexNumber[,complexNumber[,...]])
*
* @param string $complexNumber,... Series of complex numbers to multiply
* @return string
*/
public static function IMPRODUCT()
{
// Return value
$returnValue = self::_parseComplex('1');
$activeSuffix = '';
// Loop through the arguments
$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
foreach ($aArgs as $arg) {
$parsedComplex = self::_parseComplex($arg);
$workValue = $returnValue;
if ($parsedComplex['suffix'] != '' && $activeSuffix == '') {
$activeSuffix = $parsedComplex['suffix'];
} elseif ($parsedComplex['suffix'] != '' && $activeSuffix != $parsedComplex['suffix']) {
return PHPExcel_Calculation_Functions::NaN();
}
$returnValue['real'] = $workValue['real'] * $parsedComplex['real'] - $workValue['imaginary'] * $parsedComplex['imaginary'];
$returnValue['imaginary'] = $workValue['real'] * $parsedComplex['imaginary'] + $workValue['imaginary'] * $parsedComplex['real'];
}
if ($returnValue['imaginary'] == 0.0) {
$activeSuffix = '';
}
return self::COMPLEX($returnValue['real'], $returnValue['imaginary'], $activeSuffix);
}
public function refresh(PHPExcel_Worksheet $worksheet, $flatten = TRUE)
{
if ($this->_dataSource !== NULL) {
$calcEngine = PHPExcel_Calculation::getInstance($worksheet->getParent());
$newDataValues = PHPExcel_Calculation::_unwrapResult($calcEngine->_calculateFormulaValue('=' . $this->_dataSource, NULL, $worksheet->getCell('A1')));
if ($flatten) {
$this->_dataValues = PHPExcel_Calculation_Functions::flattenArray($newDataValues);
foreach ($this->_dataValues as &$dataValue) {
if (!empty($dataValue) && $dataValue[0] == '#') {
$dataValue = 0.0;
}
}
unset($dataValue);
} else {
$cellRange = explode('!', $this->_dataSource);
if (count($cellRange) > 1) {
list(, $cellRange) = $cellRange;
}
$dimensions = PHPExcel_Cell::rangeDimension(str_replace('$', '', $cellRange));
if ($dimensions[0] == 1 || $dimensions[1] == 1) {
$this->_dataValues = PHPExcel_Calculation_Functions::flattenArray($newDataValues);
} else {
$newArray = array_values(array_shift($newDataValues));
foreach ($newArray as $i => $newDataSet) {
$newArray[$i] = array($newDataSet);
}
foreach ($newDataValues as $newDataSet) {
$i = 0;
foreach ($newDataSet as $newDataVal) {
array_unshift($newArray[$i++], $newDataVal);
}
}
$this->_dataValues = $newArray;
}
}
$this->_pointCount = count($this->_dataValues);
}
}
public function refresh(PHPExcel_Worksheet $worksheet)
{
if ($this->_dataSource !== NULL) {
$calcEngine = PHPExcel_Calculation::getInstance();
$newDataValues = PHPExcel_Calculation::_unwrapResult($calcEngine->_calculateFormulaValue('=' . $this->_dataSource, NULL, $worksheet->getCell('A1')));
$this->_dataValues = PHPExcel_Calculation_Functions::flattenArray($newDataValues);
}
}
/**
* Set Series Data Values
*
* @param array $dataValues
* @param boolean $refreshDataSource
* TRUE - refresh the value of _dataSource based on the values of $dataValues
* FALSE - don't change the value of _dataSource
* @return PHPExcel_Chart_DataSeriesValues
*/
public function setDataValues($dataValues = array(), $refreshDataSource = true)
{
$this->_dataValues = PHPExcel_Calculation_Functions::flattenArray($dataValues);
$this->_pointCount = count($dataValues);
if ($refreshDataSource) {
// TO DO
}
return $this;
}
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;
}
/**
* SUMXMY2
*
* @param mixed[] $matrixData1 Matrix #1
* @param mixed[] $matrixData2 Matrix #2
* @return float
*/
public static function SUMXMY2($matrixData1, $matrixData2)
{
$array1 = PHPExcel_Calculation_Functions::flattenArray($matrixData1);
$array2 = PHPExcel_Calculation_Functions::flattenArray($matrixData2);
$count1 = count($array1);
$count2 = count($array2);
if ($count1 < $count2) {
$count = $count1;
} else {
$count = $count2;
}
$result = 0;
for ($i = 0; $i < $count; ++$i) {
if (is_numeric($array1[$i]) && !is_string($array1[$i]) && (is_numeric($array2[$i]) && !is_string($array2[$i]))) {
$result += ($array1[$i] - $array2[$i]) * ($array1[$i] - $array2[$i]);
}
}
return $result;
}
/**
* LOGICAL_OR
*
* Returns boolean TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
*
* Excel Function:
* =OR(logical1[,logical2[, ...]])
*
* The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays
* or references that contain logical values.
*
* Boolean arguments are treated as True or False as appropriate
* Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
* If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
* the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
*
* @access public
* @category Logical Functions
*
* @param mixed $arg,... Data values
*
* @return boolean The logical OR of the arguments.
*/
public static function LOGICAL_OR()
{
// Return value
$returnValue = false;
// Loop through the arguments
$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
$argCount = -1;
foreach ($aArgs as $argCount => $arg) {
// Is it a boolean value?
if (is_bool($arg)) {
$returnValue = $returnValue || $arg;
} elseif (is_numeric($arg) && !is_string($arg)) {
$returnValue = $returnValue || $arg != 0;
} elseif (is_string($arg)) {
$arg = strtoupper($arg);
if ($arg == 'TRUE' || $arg == PHPExcel_Calculation::getTRUE()) {
$arg = true;
} elseif ($arg == 'FALSE' || $arg == PHPExcel_Calculation::getFALSE()) {
$arg = false;
} else {
return PHPExcel_Calculation_Functions::VALUE();
}
$returnValue = $returnValue || $arg != 0;
}
}
// Return
if ($argCount < 0) {
return PHPExcel_Calculation_Functions::VALUE();
}
return $returnValue;
}
/**
* Parse a cell formula and calculate its value
*
* @param string $formula The formula to parse and calculate
* @param string $cellID The ID (e.g. A3) of the cell that we are calculating
* @param PHPExcel_Cell $pCell Cell to calculate
* @return mixed
* @throws Exception
*/
public function _calculateFormulaValue($formula, $cellID = null, PHPExcel_Cell $pCell = null)
{
// 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);
}
$wsTitle = 'Wrk';
if (!is_null($pCell)) {
$wsTitle = urlencode($pCell->getParent()->getTitle());
}
// 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[$wsTitle][$cellID])) {
// echo 'Value is in cache<br />';
$this->_writeDebug('Testing cache value for cell ' . $cellID);
// Is cache still valid?
if (time() + microtime() - $this->_calculationCache[$wsTitle][$cellID]['time'] < $this->_calculationCacheExpirationTime) {
// echo 'Cache time is still valid<br />';
$this->_writeDebug('Retrieving value for ' . $cellID . ' from cache');
// Return the cached result
$returnValue = $this->_calculationCache[$wsTitle][$cellID]['data'];
// echo 'Retrieving data value of '.$returnValue.' for '.$cellID.' 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('Cache value for ' . $cellID . ' has expired');
// Clear the cache if it's no longer valid
unset($this->_calculationCache[$wsTitle][$cellID]);
}
}
}
}
$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[$wsTitle][$cellID]['time'] = time() + microtime();
$this->_calculationCache[$wsTitle][$cellID]['data'] = $cellValue;
}
}
// Return the calculated value
// while (is_array($cellValue)) {
// $cellValue = array_shift($cellValue);
// }
return $cellValue;
}
/**
* XNPV
*
* Returns the net present value for a schedule of cash flows that is not necessarily periodic.
* To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
*
* Excel Function:
* =XNPV(rate,values,dates)
*
* @param float $rate The discount rate to apply to the cash flows.
* @param array of float $values A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive value and one negative value.
* @param array of mixed $dates A schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order.
* @return float
*/
public static function XNPV($rate, $values, $dates)
{
$rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
if (!is_numeric($rate)) {
return PHPExcel_Calculation_Functions::VALUE();
}
if (!is_array($values) || !is_array($dates)) {
return PHPExcel_Calculation_Functions::VALUE();
}
$values = PHPExcel_Calculation_Functions::flattenArray($values);
$dates = PHPExcel_Calculation_Functions::flattenArray($dates);
$valCount = count($values);
if ($valCount != count($dates)) {
return PHPExcel_Calculation_Functions::NaN();
}
if (min($values) > 0 || max($values) < 0) {
return PHPExcel_Calculation_Functions::VALUE();
}
$xnpv = 0.0;
for ($i = 0; $i < $valCount; ++$i) {
if (!is_numeric($values[$i])) {
return PHPExcel_Calculation_Functions::VALUE();
}
$xnpv += $values[$i] / pow(1 + $rate, PHPExcel_Calculation_DateTime::DATEDIF($dates[0], $dates[$i], 'd') / 365);
}
return is_finite($xnpv) ? $xnpv : PHPExcel_Calculation_Functions::VALUE();
}
/**
* NPV
*
* Returns the Net Present Value of a cash flow series given a discount rate.
*
* @param float Discount interest rate
* @param array Cash flow series
* @return float
*/
public static function NPV()
{
// Return value
$returnValue = 0;
// Loop trough arguments
$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
// Calculate
$rate = array_shift($aArgs);
for ($i = 1; $i <= count($aArgs); ++$i) {
// Is it a numeric value?
if (is_numeric($aArgs[$i - 1])) {
$returnValue += $aArgs[$i - 1] / pow(1 + $rate, $i);
}
}
// Return
return $returnValue;
}
