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()