private function _executeNumericBinaryOperation($cellID, $operand1, $operand2, $operation, $matrixFunction, &$stack)
{
// 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 of the same size
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->{$matrixFunction}($operand2);
$result = $matrixResult->getArray();
} catch (PHPExcel_Exception $ex) {
$this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage());
$result = '#VALUE!';
}
} else {
if (PHPExcel_Calculation_Functions::getCompatibilityMode() != PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE && (is_string($operand1) && !is_numeric($operand1) && strlen($operand1) > 0 || is_string($operand2) && !is_numeric($operand2) && strlen($operand2) > 0)) {
$result = PHPExcel_Calculation_Functions::VALUE();
} else {
// If we're dealing with non-matrix operations, execute the necessary operation
switch ($operation) {
// Addition
case '+':
$result = $operand1 + $operand2;
break;
// Subtraction
// Subtraction
case '-':
$result = $operand1 - $operand2;
break;
// Multiplication
// Multiplication
case '*':
$result = $operand1 * $operand2;
break;
// Division
// Division
case '/':
if ($operand2 == 0) {
// Trap for Divide by Zero error
$stack->push('Value', '#DIV/0!');
$this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails('#DIV/0!'));
return FALSE;
} else {
$result = $operand1 / $operand2;
}
break;
// Power
// Power
case '^':
$result = pow($operand1, $operand2);
break;
}
}
}
// Log the result details
$this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result));
// And push the result onto the stack
$stack->push('Value', $result);
return TRUE;
}
/**
* NOT
*
* Returns the boolean inverse of the argument.
*
* Excel Function:
* =NOT(logical)
*
* The argument must evaluate to a logical value such as TRUE or FALSE
*
* 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 $logical A value or expression that can be evaluated to TRUE or FALSE
*
* @return boolean The boolean inverse of the argument.
*/
public static function NOT($logical = false)
{
$logical = PHPExcel_Calculation_Functions::flattenSingleValue($logical);
if (is_string($logical)) {
$logical = strtoupper($logical);
if ($logical == 'TRUE' || $logical == PHPExcel_Calculation::getTRUE()) {
return false;
} elseif ($logical == 'FALSE' || $logical == PHPExcel_Calculation::getFALSE()) {
return true;
} else {
return PHPExcel_Calculation_Functions::VALUE();
}
}
return !$logical;
}
/**
* VLOOKUP
* The VLOOKUP function searches for value in the left-most column of lookup_array and returns the value in the same row based on the index_number.
* @param lookup_value The value that you want to match in lookup_array
* @param lookup_array The range of cells being searched
* @param index_number The column number in table_array from which the matching value must be returned. The first column is 1.
* @param not_exact_match Determines if you are looking for an exact match based on lookup_value.
* @return mixed The value of the found cell
*/
public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true)
{
$lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
$index_number = PHPExcel_Calculation_Functions::flattenSingleValue($index_number);
$not_exact_match = PHPExcel_Calculation_Functions::flattenSingleValue($not_exact_match);
// index_number must be greater than or equal to 1
if ($index_number < 1) {
return PHPExcel_Calculation_Functions::VALUE();
}
// index_number must be less than or equal to the number of columns in lookup_array
if (!is_array($lookup_array) || count($lookup_array) < 1) {
return PHPExcel_Calculation_Functions::REF();
} else {
$f = array_keys($lookup_array);
$firstRow = array_pop($f);
if (!is_array($lookup_array[$firstRow]) || $index_number > count($lookup_array[$firstRow])) {
return PHPExcel_Calculation_Functions::REF();
} else {
$columnKeys = array_keys($lookup_array[$firstRow]);
$returnColumn = $columnKeys[--$index_number];
$firstColumn = array_shift($columnKeys);
}
}
if (!$not_exact_match) {
uasort($lookup_array, array('self', '_vlookupSort'));
}
$rowNumber = $rowValue = False;
foreach ($lookup_array as $rowKey => $rowData) {
if (strtolower($rowData[$firstColumn]) > strtolower($lookup_value)) {
break;
}
$rowNumber = $rowKey;
$rowValue = $rowData[$firstColumn];
}
if ($rowNumber !== false) {
if (!$not_exact_match && $rowValue != $lookup_value) {
// if an exact match is required, we have what we need to return an appropriate response
return PHPExcel_Calculation_Functions::NA();
} else {
// otherwise return the appropriate value
return $lookup_array[$rowNumber][$returnColumn];
}
}
return PHPExcel_Calculation_Functions::NA();
}
/**
* EOMONTH
*
* Returns the serial number for the last day of the month that is the indicated number of months before or after start_date.
* Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
*
* @param long $dateValue Excel date serial value or a standard date string
* @param int $adjustmentMonths Number of months to adjust by
* @return long Excel date serial value
*/
public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0)
{
$dateValue = PHPExcel_Calculation_Functions::flattenSingleValue($dateValue);
$adjustmentMonths = floor(PHPExcel_Calculation_Functions::flattenSingleValue($adjustmentMonths));
if (!is_numeric($adjustmentMonths)) {
return PHPExcel_Calculation_Functions::VALUE();
}
if (is_string($dateValue = self::_getDateValue($dateValue))) {
return PHPExcel_Calculation_Functions::VALUE();
}
// Execute function
$PHPDateObject = self::_adjustDateByMonths($dateValue, $adjustmentMonths + 1);
$adjustDays = (int) $PHPDateObject->format('d');
$adjustDaysString = '-' . $adjustDays . ' days';
$PHPDateObject->modify($adjustDaysString);
switch (PHPExcel_Calculation_Functions::getReturnDateType()) {
case PHPExcel_Calculation_Functions::RETURNDATE_EXCEL:
return (double) PHPExcel_Shared_Date::PHPToExcel($PHPDateObject);
break;
case PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC:
return (int) PHPExcel_Shared_Date::ExcelToPHP(PHPExcel_Shared_Date::PHPToExcel($PHPDateObject));
break;
case PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT:
return $PHPDateObject;
break;
}
}
/**
* RIGHT
*
* @param string $value Value
* @param int $chars Number of characters
* @return string
*/
public static function RIGHT($value = '', $chars = 1)
{
$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
$chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
if ($chars < 0) {
return PHPExcel_Calculation_Functions::VALUE();
}
if (is_bool($value)) {
$value = $value ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
}
if (function_exists('mb_substr') && function_exists('mb_strlen')) {
return mb_substr($value, mb_strlen($value, 'UTF-8') - $chars, $chars, 'UTF-8');
} else {
return substr($value, strlen($value) - $chars);
}
}
/**
* HLOOKUP
* The HLOOKUP function searches for value in the top-most row of lookup_array and returns the value in the same column based on the index_number.
* @param lookup_value The value that you want to match in lookup_array
* @param lookup_array The range of cells being searched
* @param index_number The row number in table_array from which the matching value must be returned. The first row is 1.
* @param not_exact_match Determines if you are looking for an exact match based on lookup_value.
* @return mixed The value of the found cell
*/
public static function HLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true)
{
$lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value);
$index_number = PHPExcel_Calculation_Functions::flattenSingleValue($index_number);
$not_exact_match = PHPExcel_Calculation_Functions::flattenSingleValue($not_exact_match);
// index_number must be greater than or equal to 1
if ($index_number < 1) {
return PHPExcel_Calculation_Functions::VALUE();
}
// index_number must be less than or equal to the number of columns in lookup_array
if (!is_array($lookup_array) || empty($lookup_array)) {
return PHPExcel_Calculation_Functions::REF();
} else {
$f = array_keys($lookup_array);
$firstRow = array_pop($f);
if (!is_array($lookup_array[$firstRow]) || $index_number > count($lookup_array[$firstRow])) {
return PHPExcel_Calculation_Functions::REF();
} else {
$columnKeys = array_keys($lookup_array[$firstRow]);
$firstkey = $f[0] - 1;
$returnColumn = $firstkey + $index_number;
$firstColumn = array_shift($f);
}
}
if (!$not_exact_match) {
$firstRowH = asort($lookup_array[$firstColumn]);
}
$rowNumber = $rowValue = False;
foreach ($lookup_array[$firstColumn] as $rowKey => $rowData) {
if (is_numeric($lookup_value) && is_numeric($rowData) && $rowData > $lookup_value || !is_numeric($lookup_value) && !is_numeric($rowData) && strtolower($rowData) > strtolower($lookup_value)) {
break;
}
$rowNumber = $rowKey;
$rowValue = $rowData;
}
if ($rowNumber !== false) {
if (!$not_exact_match && $rowValue != $lookup_value) {
// if an exact match is required, we have what we need to return an appropriate response
return PHPExcel_Calculation_Functions::NA();
} else {
// otherwise return the appropriate value
$result = $lookup_array[$returnColumn][$rowNumber];
return $result;
}
}
return PHPExcel_Calculation_Functions::NA();
}
/**
* Calculate the value of a cell formula
*
* @access public
* @param PHPExcel_Cell $pCell Cell to calculate
* @param Boolean $resetLog Flag indicating whether the debug log should be reset or not
* @return mixed
* @throws Exception
*/
public function calculateCellValue(PHPExcel_Cell $pCell = null, $resetLog = true)
{
if ($resetLog) {
// Initialise the logging settings if requested
$this->formulaError = null;
$this->debugLog = $this->debugLogStack = array();
$this->_cyclicFormulaCount = 1;
$returnArrayAsType = self::$returnArrayAsType;
self::$returnArrayAsType = self::RETURN_ARRAY_AS_ARRAY;
}
// Read the formula from the cell
if (is_null($pCell)) {
return null;
}
if ($resetLog) {
self::$returnArrayAsType = $returnArrayAsType;
}
// Execute the calculation for the cell formula
try {
$result = self::_unwrapResult($this->_calculateFormulaValue($pCell->getValue(), $pCell->getCoordinate(), $pCell));
} catch (Exception $e) {
throw new Exception($e->getMessage());
}
if (is_array($result) && self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY) {
$testResult = PHPExcel_Calculation_Functions::flattenArray($result);
if (self::$returnArrayAsType == self::RETURN_ARRAY_AS_ERROR) {
return PHPExcel_Calculation_Functions::VALUE();
}
// If there's only a single cell in the array, then we allow it
if (count($testResult) != 1) {
// If keys are numeric, then it's a matrix result rather than a cell range result, so we permit it
$r = array_keys($result);
$r = array_shift($r);
if (!is_numeric($r)) {
return PHPExcel_Calculation_Functions::VALUE();
}
if (is_array($result[$r])) {
$c = array_keys($result[$r]);
$c = array_shift($c);
if (!is_numeric($c)) {
return PHPExcel_Calculation_Functions::VALUE();
}
}
}
$result = array_shift($testResult);
}
if (is_null($result)) {
return 0;
} elseif (is_float($result) && (is_nan($result) || is_infinite($result))) {
return PHPExcel_Calculation_Functions::NaN();
}
return $result;
}
/**
* TRUNC
*
* Truncates value to the number of fractional digits by number_digits.
*
* @param float $value
* @param int $number_digits
* @return float Truncated value
*/
public static function TRUNC($value = 0, $number_digits = 0)
{
$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
$number_digits = PHPExcel_Calculation_Functions::flattenSingleValue($number_digits);
// Validate parameters
if ($number_digits < 0) {
return PHPExcel_Calculation_Functions::VALUE();
}
// Truncate
if ($number_digits > 0) {
$value = $value * pow(10, $number_digits);
}
$value = intval($value);
if ($number_digits > 0) {
$value = $value / pow(10, $number_digits);
}
// Return
return $value;
}
/**
* TRUNC
*
* Truncates value to the number of fractional digits by number_digits.
*
* @param float $value
* @param int $digits
* @return float Truncated value
*/
public static function TRUNC($value = 0, $digits = 0)
{
$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
$digits = PHPExcel_Calculation_Functions::flattenSingleValue($digits);
// Validate parameters
if (!is_numeric($value) || !is_numeric($digits)) {
return PHPExcel_Calculation_Functions::VALUE();
}
$digits = floor($digits);
// Truncate
$adjust = pow(10, $digits);
if ($digits > 0 && rtrim(intval((abs($value) - abs(intval($value))) * $adjust), '0') < $adjust / 10) {
return $value;
}
return intval($value * $adjust) / $adjust;
}
/**
* VALUE
*
* @param mixed $value Value to check
* @return boolean
*/
public static function VALUE($value = '')
{
$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
if (!is_numeric($value)) {
$numberValue = str_replace(PHPExcel_Shared_String::getThousandsSeparator(), '', trim($value, " \t\n\r\v" . PHPExcel_Shared_String::getCurrencyCode()));
if (is_numeric($numberValue)) {
return (double) $numberValue;
}
$dateSetting = PHPExcel_Calculation_Functions::getReturnDateType();
PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
if (strpos($value, ':') !== false) {
$timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($value);
if ($timeValue !== PHPExcel_Calculation_Functions::VALUE()) {
PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
return $timeValue;
}
}
$dateValue = PHPExcel_Calculation_DateTime::DATEVALUE($value);
if ($dateValue !== PHPExcel_Calculation_Functions::VALUE()) {
PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
return $dateValue;
}
PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
return PHPExcel_Calculation_Functions::VALUE();
}
return (double) $value;
}
/**
* CONVERTUOM
*
* Converts a number from one measurement system to another.
* For example, CONVERT can translate a table of distances in miles to a table of distances
* in kilometers.
*
* Excel Function:
* CONVERT(value,fromUOM,toUOM)
*
* @param float $value The value in fromUOM to convert.
* @param string $fromUOM The units for value.
* @param string $toUOM The units for the result.
*
* @return float
*/
public static function CONVERTUOM($value, $fromUOM, $toUOM)
{
$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
$fromUOM = PHPExcel_Calculation_Functions::flattenSingleValue($fromUOM);
$toUOM = PHPExcel_Calculation_Functions::flattenSingleValue($toUOM);
if (!is_numeric($value)) {
return PHPExcel_Calculation_Functions::VALUE();
}
$fromMultiplier = 1.0;
if (isset(self::$_conversionUnits[$fromUOM])) {
$unitGroup1 = self::$_conversionUnits[$fromUOM]['Group'];
} else {
$fromMultiplier = substr($fromUOM, 0, 1);
$fromUOM = substr($fromUOM, 1);
if (isset(self::$_conversionMultipliers[$fromMultiplier])) {
$fromMultiplier = self::$_conversionMultipliers[$fromMultiplier]['multiplier'];
} else {
return PHPExcel_Calculation_Functions::NA();
}
if (isset(self::$_conversionUnits[$fromUOM]) && self::$_conversionUnits[$fromUOM]['AllowPrefix']) {
$unitGroup1 = self::$_conversionUnits[$fromUOM]['Group'];
} else {
return PHPExcel_Calculation_Functions::NA();
}
}
$value *= $fromMultiplier;
$toMultiplier = 1.0;
if (isset(self::$_conversionUnits[$toUOM])) {
$unitGroup2 = self::$_conversionUnits[$toUOM]['Group'];
} else {
$toMultiplier = substr($toUOM, 0, 1);
$toUOM = substr($toUOM, 1);
if (isset(self::$_conversionMultipliers[$toMultiplier])) {
$toMultiplier = self::$_conversionMultipliers[$toMultiplier]['multiplier'];
} else {
return PHPExcel_Calculation_Functions::NA();
}
if (isset(self::$_conversionUnits[$toUOM]) && self::$_conversionUnits[$toUOM]['AllowPrefix']) {
$unitGroup2 = self::$_conversionUnits[$toUOM]['Group'];
} else {
return PHPExcel_Calculation_Functions::NA();
}
}
if ($unitGroup1 != $unitGroup2) {
return PHPExcel_Calculation_Functions::NA();
}
if ($fromUOM == $toUOM && $fromMultiplier == $toMultiplier) {
// We've already factored $fromMultiplier into the value, so we need
// to reverse it again
return $value / $fromMultiplier;
} elseif ($unitGroup1 == 'Temperature') {
if ($fromUOM == 'F' || $fromUOM == 'fah') {
if ($toUOM == 'F' || $toUOM == 'fah') {
return $value;
} else {
$value = ($value - 32) / 1.8;
if ($toUOM == 'K' || $toUOM == 'kel') {
$value += 273.15;
}
return $value;
}
} elseif (($fromUOM == 'K' || $fromUOM == 'kel') && ($toUOM == 'K' || $toUOM == 'kel')) {
return $value;
} elseif (($fromUOM == 'C' || $fromUOM == 'cel') && ($toUOM == 'C' || $toUOM == 'cel')) {
return $value;
}
if ($toUOM == 'F' || $toUOM == 'fah') {
if ($fromUOM == 'K' || $fromUOM == 'kel') {
$value -= 273.15;
}
return $value * 1.8 + 32;
}
if ($toUOM == 'C' || $toUOM == 'cel') {
return $value - 273.15;
}
return $value + 273.15;
}
return $value * self::$_unitConversions[$unitGroup1][$fromUOM][$toUOM] / $toMultiplier;
}
/**
* Convert a date/time string to Excel time
*
* @param string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
* @return float|FALSE Excel date/time serial value
*/
public static function stringToExcel($dateValue = '')
{
if (strlen($dateValue) < 2) {
return FALSE;
}
if (!preg_match('/^(\\d{1,4}[ \\.\\/\\-][A-Z]{3,9}([ \\.\\/\\-]\\d{1,4})?|[A-Z]{3,9}[ \\.\\/\\-]\\d{1,4}([ \\.\\/\\-]\\d{1,4})?|\\d{1,4}[ \\.\\/\\-]\\d{1,4}([ \\.\\/\\-]\\d{1,4})?)( \\d{1,2}:\\d{1,2}(:\\d{1,2})?)?$/iu', $dateValue)) {
return FALSE;
}
$dateValueNew = PHPExcel_Calculation_DateTime::DATEVALUE($dateValue);
if ($dateValueNew === PHPExcel_Calculation_Functions::VALUE()) {
return FALSE;
} else {
if (strpos($dateValue, ':') !== FALSE) {
$timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($dateValue);
if ($timeValue === PHPExcel_Calculation_Functions::VALUE()) {
return FALSE;
}
$dateValueNew += $timeValue;
}
return $dateValueNew;
}
}
/**
* Convert a date/time string to Excel time
*
* @param string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
* @return float|false Excel date/time serial value
*/
public static function stringToExcel($dateValue = '')
{
if (strlen($dateValue) < 2) {
return false;
}
$dateValueNew = PHPExcel_Calculation_DateTime::DATEVALUE($dateValue);
if ($dateValueNew === PHPExcel_Calculation_Functions::VALUE()) {
return false;
} else {
if (strpos($dateValue, ':') !== false) {
$timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($dateValue);
if ($timeValue === PHPExcel_Calculation_Functions::VALUE()) {
return false;
}
$dateValueNew += $timeValue;
}
return $dateValueNew;
}
}
/**
* WEIBULL
*
* Returns the Weibull distribution. Use this distribution in reliability
* analysis, such as calculating a device's mean time to failure.
*
* @param float $value
* @param float $alpha Alpha Parameter
* @param float $beta Beta Parameter
* @param boolean $cumulative
* @return float
*
*/
public static function WEIBULL($value, $alpha, $beta, $cumulative)
{
$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
$alpha = PHPExcel_Calculation_Functions::flattenSingleValue($alpha);
$beta = PHPExcel_Calculation_Functions::flattenSingleValue($beta);
if (is_numeric($value) && is_numeric($alpha) && is_numeric($beta)) {
if ($value < 0 || $alpha <= 0 || $beta <= 0) {
return PHPExcel_Calculation_Functions::NaN();
}
if (is_numeric($cumulative) || is_bool($cumulative)) {
if ($cumulative) {
return 1 - exp(0 - pow($value / $beta, $alpha));
} else {
return $alpha / pow($beta, $alpha) * pow($value, $alpha - 1) * exp(0 - pow($value / $beta, $alpha));
}
}
}
return PHPExcel_Calculation_Functions::VALUE();
}
public function testVALUE()
{
$result = PHPExcel_Calculation_Functions::VALUE();
$this->assertEquals('#VALUE!', $result);
}
/**
* YIELDMAT
*
* Returns the annual yield of a security that pays interest at maturity.
*
* @param mixed settlement The security's settlement date.
* The security's settlement date is the date after the issue date when the security is traded to the buyer.
* @param mixed maturity The security's maturity date.
* The maturity date is the date when the security expires.
* @param mixed issue The security's issue date.
* @param int rate The security's interest rate at date of issue.
* @param int price The security's price per $100 face value.
* @param int basis The type of day count to use.
* 0 or omitted US (NASD) 30/360
* 1 Actual/actual
* 2 Actual/360
* 3 Actual/365
* 4 European 30/360
* @return float
*/
public static function YIELDMAT($settlement, $maturity, $issue, $rate, $price, $basis = 0)
{
$settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
$maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
$issue = PHPExcel_Calculation_Functions::flattenSingleValue($issue);
$rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
$price = PHPExcel_Calculation_Functions::flattenSingleValue($price);
$basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
// Validate
if (is_numeric($rate) && is_numeric($price)) {
if ($rate <= 0 || $price <= 0) {
return PHPExcel_Calculation_Functions::NaN();
}
$daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement), $basis);
if (!is_numeric($daysPerYear)) {
return $daysPerYear;
}
$daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
if (!is_numeric($daysBetweenIssueAndSettlement)) {
// return date error
return $daysBetweenIssueAndSettlement;
}
$daysBetweenIssueAndSettlement *= $daysPerYear;
$daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $maturity, $basis);
if (!is_numeric($daysBetweenIssueAndMaturity)) {
// return date error
return $daysBetweenIssueAndMaturity;
}
$daysBetweenIssueAndMaturity *= $daysPerYear;
$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
if (!is_numeric($daysBetweenSettlementAndMaturity)) {
// return date error
return $daysBetweenSettlementAndMaturity;
}
$daysBetweenSettlementAndMaturity *= $daysPerYear;
return (1 + $daysBetweenIssueAndMaturity / $daysPerYear * $rate - ($price / 100 + $daysBetweenIssueAndSettlement / $daysPerYear * $rate)) / ($price / 100 + $daysBetweenIssueAndSettlement / $daysPerYear * $rate) * ($daysPerYear / $daysBetweenSettlementAndMaturity);
}
return PHPExcel_Calculation_Functions::VALUE();
}
/**
* CONVERTUOM
*
* @param float $value
* @param string $fromUOM
* @param string $toUOM
* @return float
*/
public static function CONVERTUOM($value, $fromUOM, $toUOM) {
$value = PHPExcel_Calculation_Functions::flattenSingleValue ( $value );
$fromUOM = PHPExcel_Calculation_Functions::flattenSingleValue ( $fromUOM );
$toUOM = PHPExcel_Calculation_Functions::flattenSingleValue ( $toUOM );
if (! is_numeric ( $value )) {
return PHPExcel_Calculation_Functions::VALUE ();
}
$fromMultiplier = 1;
if (isset ( self::$_conversionUnits [$fromUOM] )) {
$unitGroup1 = self::$_conversionUnits [$fromUOM] ['Group'];
} else {
$fromMultiplier = substr ( $fromUOM, 0, 1 );
$fromUOM = substr ( $fromUOM, 1 );
if (isset ( self::$_conversionMultipliers [$fromMultiplier] )) {
$fromMultiplier = self::$_conversionMultipliers [$fromMultiplier] ['multiplier'];
} else {
return PHPExcel_Calculation_Functions::NA ();
}
if ((isset ( self::$_conversionUnits [$fromUOM] )) && (self::$_conversionUnits [$fromUOM] ['AllowPrefix'])) {
$unitGroup1 = self::$_conversionUnits [$fromUOM] ['Group'];
} else {
return PHPExcel_Calculation_Functions::NA ();
}
}
$value *= $fromMultiplier;
$toMultiplier = 1;
if (isset ( self::$_conversionUnits [$toUOM] )) {
$unitGroup2 = self::$_conversionUnits [$toUOM] ['Group'];
} else {
$toMultiplier = substr ( $toUOM, 0, 1 );
$toUOM = substr ( $toUOM, 1 );
if (isset ( self::$_conversionMultipliers [$toMultiplier] )) {
$toMultiplier = self::$_conversionMultipliers [$toMultiplier] ['multiplier'];
} else {
return PHPExcel_Calculation_Functions::NA ();
}
if ((isset ( self::$_conversionUnits [$toUOM] )) && (self::$_conversionUnits [$toUOM] ['AllowPrefix'])) {
$unitGroup2 = self::$_conversionUnits [$toUOM] ['Group'];
} else {
return PHPExcel_Calculation_Functions::NA ();
}
}
if ($unitGroup1 != $unitGroup2) {
return PHPExcel_Calculation_Functions::NA ();
}
if ($fromUOM == $toUOM) {
return 1.0;
} elseif ($unitGroup1 == 'Temperature') {
if (($fromUOM == 'F') || ($fromUOM == 'fah')) {
if (($toUOM == 'F') || ($toUOM == 'fah')) {
return 1.0;
} else {
$value = (($value - 32) / 1.8);
if (($toUOM == 'K') || ($toUOM == 'kel')) {
$value += 273.15;
}
return $value;
}
} elseif ((($fromUOM == 'K') || ($fromUOM == 'kel')) && (($toUOM == 'K') || ($toUOM == 'kel'))) {
return 1.0;
} elseif ((($fromUOM == 'C') || ($fromUOM == 'cel')) && (($toUOM == 'C') || ($toUOM == 'cel'))) {
return 1.0;
}
if (($toUOM == 'F') || ($toUOM == 'fah')) {
if (($fromUOM == 'K') || ($fromUOM == 'kel')) {
$value -= 273.15;
}
return ($value * 1.8) + 32;
}
if (($toUOM == 'C') || ($toUOM == 'cel')) {
return $value - 273.15;
}
return $value + 273.15;
}
return ($value * self::$_unitConversions [$unitGroup1] [$fromUOM] [$toUOM]) / $toMultiplier;
} // function CONVERTUOM()
