PHP Functions::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 = Functions::flattenSingleValue($value);
     $digits = Functions::flattenSingleValue($digits);
     // Validate parameters
     if (!is_numeric($value) || !is_numeric($digits)) {
         return 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;
 }

 /**
  * 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 = Functions::flattenSingleValue($lookup_value);
     $index_number = Functions::flattenSingleValue($index_number);
     $not_exact_match = Functions::flattenSingleValue($not_exact_match);
     // index_number must be greater than or equal to 1
     if ($index_number < 1) {
         return 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 Functions::REF();
     } else {
         $f = array_keys($lookup_array);
         $firstRow = array_pop($f);
         if (!is_array($lookup_array[$firstRow]) || $index_number - 1 > count($lookup_array[$firstRow])) {
             return 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 Functions::NA();
         } else {
             //  otherwise return the appropriate value
             return $lookup_array[$returnColumn][$rowNumber];
         }
     }
     return Functions::NA();
 }

 /**
  * VALUE
  *
  * @param    mixed    $value    Value to check
  * @return    boolean
  */
 public static function VALUE($value = '')
 {
     $value = Functions::flattenSingleValue($value);
     if (!is_numeric($value)) {
         $numberValue = str_replace(\PHPExcel\Shared\StringHelper::getThousandsSeparator(), '', trim($value, " \t\n\r\v" . \PHPExcel\Shared\StringHelper::getCurrencyCode()));
         if (is_numeric($numberValue)) {
             return (double) $numberValue;
         }
         $dateSetting = Functions::getReturnDateType();
         Functions::setReturnDateType(Functions::RETURNDATE_EXCEL);
         if (strpos($value, ':') !== false) {
             $timeValue = DateTime::TIMEVALUE($value);
             if ($timeValue !== Functions::VALUE()) {
                 Functions::setReturnDateType($dateSetting);
                 return $timeValue;
             }
         }
         $dateValue = DateTime::DATEVALUE($value);
         if ($dateValue !== Functions::VALUE()) {
             Functions::setReturnDateType($dateSetting);
             return $dateValue;
         }
         Functions::setReturnDateType($dateSetting);
         return Functions::VALUE();
     }
     return (double) $value;
 }

 /**
  * 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 = Functions::flattenSingleValue($value);
     $alpha = Functions::flattenSingleValue($alpha);
     $beta = Functions::flattenSingleValue($beta);
     if (is_numeric($value) && is_numeric($alpha) && is_numeric($beta)) {
         if ($value < 0 || $alpha <= 0 || $beta <= 0) {
             return 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 Functions::VALUE();
 }

 /**
  * 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 = Functions::flattenSingleValue($settlement);
     $maturity = Functions::flattenSingleValue($maturity);
     $issue = Functions::flattenSingleValue($issue);
     $rate = Functions::flattenSingleValue($rate);
     $price = Functions::flattenSingleValue($price);
     $basis = (int) Functions::flattenSingleValue($basis);
     //    Validate
     if (is_numeric($rate) && is_numeric($price)) {
         if ($rate <= 0 || $price <= 0) {
             return Functions::NAN();
         }
         $daysPerYear = self::daysPerYear(DateTime::YEAR($settlement), $basis);
         if (!is_numeric($daysPerYear)) {
             return $daysPerYear;
         }
         $daysBetweenIssueAndSettlement = DateTime::YEARFRAC($issue, $settlement, $basis);
         if (!is_numeric($daysBetweenIssueAndSettlement)) {
             //    return date error
             return $daysBetweenIssueAndSettlement;
         }
         $daysBetweenIssueAndSettlement *= $daysPerYear;
         $daysBetweenIssueAndMaturity = DateTime::YEARFRAC($issue, $maturity, $basis);
         if (!is_numeric($daysBetweenIssueAndMaturity)) {
             //    return date error
             return $daysBetweenIssueAndMaturity;
         }
         $daysBetweenIssueAndMaturity *= $daysPerYear;
         $daysBetweenSettlementAndMaturity = 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 Functions::VALUE();
 }

 /**
  * EOMONTH
  *
  * Returns the date value 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.
  *
  * Excel Function:
  *        EOMONTH(dateValue,adjustmentMonths)
  *
  * @param    mixed    $dateValue            Excel date serial value (float), PHP date timestamp (integer),
  *                                        PHP DateTime object, or a standard date string
  * @param    int        $adjustmentMonths    The number of months before or after start_date.
  *                                        A positive value for months yields a future date;
  *                                        a negative value yields a past date.
  * @return    mixed    Excel date/time serial value, PHP date/time serial value or PHP date/time object,
  *                        depending on the value of the ReturnDateType flag
  */
 public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0)
 {
     $dateValue = Functions::flattenSingleValue($dateValue);
     $adjustmentMonths = Functions::flattenSingleValue($adjustmentMonths);
     if (!is_numeric($adjustmentMonths)) {
         return Functions::VALUE();
     }
     $adjustmentMonths = floor($adjustmentMonths);
     if (is_string($dateValue = self::getDateValue($dateValue))) {
         return Functions::VALUE();
     }
     // Execute function
     $PHPDateObject = self::adjustDateByMonths($dateValue, $adjustmentMonths + 1);
     $adjustDays = (int) $PHPDateObject->format('d');
     $adjustDaysString = '-' . $adjustDays . ' days';
     $PHPDateObject->modify($adjustDaysString);
     switch (Functions::getReturnDateType()) {
         case Functions::RETURNDATE_EXCEL:
             return (double) \PHPExcel\Shared\Date::PHPToExcel($PHPDateObject);
         case Functions::RETURNDATE_PHP_NUMERIC:
             return (int) \PHPExcel\Shared\Date::excelToPHP(\PHPExcel\Shared\Date::PHPToExcel($PHPDateObject));
         case Functions::RETURNDATE_PHP_OBJECT:
             return $PHPDateObject;
     }
 }

 /**
  *    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 = Functions::flattenSingleValue($value);
     $fromUOM = Functions::flattenSingleValue($fromUOM);
     $toUOM = Functions::flattenSingleValue($toUOM);
     if (!is_numeric($value)) {
         return 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 Functions::NA();
         }
         if (isset(self::$conversionUnits[$fromUOM]) && self::$conversionUnits[$fromUOM]['AllowPrefix']) {
             $unitGroup1 = self::$conversionUnits[$fromUOM]['Group'];
         } else {
             return 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 Functions::NA();
         }
         if (isset(self::$conversionUnits[$toUOM]) && self::$conversionUnits[$toUOM]['AllowPrefix']) {
             $unitGroup2 = self::$conversionUnits[$toUOM]['Group'];
         } else {
             return Functions::NA();
         }
     }
     if ($unitGroup1 != $unitGroup2) {
         return 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;
 }

 /**
  * 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 = 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 Functions::VALUE();
         }
     }
     return !$logical;
 }