PHP Functions::flattenSingleValue方法代码示例

 /**
  * fieldExtract
  *
  * Extracts the column ID to use for the data field.
  *
  * @access    private
  * @param    mixed[]        $database        The range of cells that makes up the list or database.
  *                                        A database is a list of related data in which rows of related
  *                                        information are records, and columns of data are fields. The
  *                                        first row of the list contains labels for each column.
  * @param    mixed        $field            Indicates which column is used in the function. Enter the
  *                                        column label enclosed between double quotation marks, such as
  *                                        "Age" or "Yield," or a number (without quotation marks) that
  *                                        represents the position of the column within the list: 1 for
  *                                        the first column, 2 for the second column, and so on.
  * @return    string|NULL
  *
  */
 private static function fieldExtract($database, $field)
 {
     $field = strtoupper(Functions::flattenSingleValue($field));
     $fieldNames = array_map('strtoupper', array_shift($database));
     if (is_numeric($field)) {
         $keys = array_keys($fieldNames);
         return $keys[$field - 1];
     }
     $key = array_search($field, $fieldNames);
     return $key ? $key : null;
 }

 /**
  * 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;
 }

 /**
  * 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;
 }

 /**
  * LOOKUP
  * The LOOKUP function searches for value either from a one-row or one-column range or from an array.
  * @param    lookup_value    The value that you want to match in lookup_array
  * @param    lookup_vector    The range of cells being searched
  * @param    result_vector    The column from which the matching value must be returned
  * @return    mixed            The value of the found cell
  */
 public static function LOOKUP($lookup_value, $lookup_vector, $result_vector = null)
 {
     $lookup_value = Functions::flattenSingleValue($lookup_value);
     if (!is_array($lookup_vector)) {
         return Functions::NA();
     }
     $lookupRows = count($lookup_vector);
     $l = array_keys($lookup_vector);
     $l = array_shift($l);
     $lookupColumns = count($lookup_vector[$l]);
     if ($lookupRows == 1 && $lookupColumns > 1 || $lookupRows == 2 && $lookupColumns != 2) {
         $lookup_vector = self::TRANSPOSE($lookup_vector);
         $lookupRows = count($lookup_vector);
         $l = array_keys($lookup_vector);
         $lookupColumns = count($lookup_vector[array_shift($l)]);
     }
     if (is_null($result_vector)) {
         $result_vector = $lookup_vector;
     }
     $resultRows = count($result_vector);
     $l = array_keys($result_vector);
     $l = array_shift($l);
     $resultColumns = count($result_vector[$l]);
     if ($resultRows == 1 && $resultColumns > 1 || $resultRows == 2 && $resultColumns != 2) {
         $result_vector = self::TRANSPOSE($result_vector);
         $resultRows = count($result_vector);
         $r = array_keys($result_vector);
         $resultColumns = count($result_vector[array_shift($r)]);
     }
     if ($lookupRows == 2) {
         $result_vector = array_pop($lookup_vector);
         $lookup_vector = array_shift($lookup_vector);
     }
     if ($lookupColumns != 2) {
         foreach ($lookup_vector as &$value) {
             if (is_array($value)) {
                 $k = array_keys($value);
                 $key1 = $key2 = array_shift($k);
                 $key2++;
                 $dataValue1 = $value[$key1];
             } else {
                 $key1 = 0;
                 $key2 = 1;
                 $dataValue1 = $value;
             }
             $dataValue2 = array_shift($result_vector);
             if (is_array($dataValue2)) {
                 $dataValue2 = array_shift($dataValue2);
             }
             $value = array($key1 => $dataValue1, $key2 => $dataValue2);
         }
         unset($value);
     }
     return self::VLOOKUP($lookup_value, $lookup_vector, 2);
 }

 /**
  * ZTEST
  *
  * Returns the Weibull distribution. Use this distribution in reliability
  * analysis, such as calculating a device's mean time to failure.
  *
  * @param    float        $dataSet
  * @param    float        $m0        Alpha Parameter
  * @param    float        $sigma    Beta Parameter
  * @param    boolean        $cumulative
  * @return    float
  *
  */
 public static function ZTEST($dataSet, $m0, $sigma = null)
 {
     $dataSet = Functions::flattenArrayIndexed($dataSet);
     $m0 = Functions::flattenSingleValue($m0);
     $sigma = Functions::flattenSingleValue($sigma);
     if (is_null($sigma)) {
         $sigma = self::STDEV($dataSet);
     }
     $n = count($dataSet);
     return 1 - self::NORMSDIST((self::AVERAGE($dataSet) - $m0) / ($sigma / SQRT($n)));
 }

 /**
  * 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();
 }

 public static function ifCondition($condition)
 {
     $condition = Functions::flattenSingleValue($condition);
     if (!isset($condition[0])) {
         $condition = '=""';
     }
     if (!in_array($condition[0], array('>', '<', '='))) {
         if (!is_numeric($condition)) {
             $condition = \PHPExcel\Calculation::wrapResult(strtoupper($condition));
         }
         return '=' . $condition;
     } else {
         preg_match('/([<>=]+)(.*)/', $condition, $matches);
         list(, $operator, $operand) = $matches;
         if (!is_numeric($operand)) {
             $operand = str_replace('"', '""', $operand);
             $operand = \PHPExcel\Calculation::wrapResult(strtoupper($operand));
         }
         return $operator . $operand;
     }
 }

 /**
  * 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;
 }

 /**
  * IFERROR
  *
  * Excel Function:
  *        =IFERROR(testValue,errorpart)
  *
  * @access    public
  * @category Logical Functions
  * @param    mixed    $testValue    Value to check, is also the value returned when no error
  * @param    mixed    $errorpart    Value to return when testValue is an error condition
  * @return    mixed    The value of errorpart or testValue determined by error condition
  */
 public static function IFERROR($testValue = '', $errorpart = '')
 {
     $testValue = is_null($testValue) ? '' : Functions::flattenSingleValue($testValue);
     $errorpart = is_null($errorpart) ? '' : Functions::flattenSingleValue($errorpart);
     return self::statementIf(Functions::isError($testValue), $errorpart, $testValue);
 }