PHP Functions::flattenArray方法代码示例

 /**
  * SUMXMY2
  *
  * @param    mixed[]    $matrixData1    Matrix #1
  * @param    mixed[]    $matrixData2    Matrix #2
  * @return    float
  */
 public static function SUMXMY2($matrixData1, $matrixData2)
 {
     $array1 = Functions::flattenArray($matrixData1);
     $array2 = Functions::flattenArray($matrixData2);
     $count = min(count($array1), count($array2));
     $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;
 }

 /**
  * MATCH
  *
  * The MATCH function searches for a specified item in a range of cells
  *
  * Excel Function:
  *        =MATCH(lookup_value, lookup_array, [match_type])
  *
  * @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 = Functions::flattenArray($lookup_array);
     $lookup_value = Functions::flattenSingleValue($lookup_value);
     $match_type = is_null($match_type) ? 1 : (int) 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 Functions::NA();
     }
     //    match_type is 0, 1 or -1
     if ($match_type !== 0 && $match_type !== -1 && $match_type !== 1) {
         return Functions::NA();
     }
     //    lookup_array should not be empty
     $lookupArraySize = count($lookup_array);
     if ($lookupArraySize <= 0) {
         return 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 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
     // **
     foreach ($lookup_array as $i => $lookupArrayValue) {
         if ($match_type == 0 && $lookupArrayValue == $lookup_value) {
             //    exact match
             return ++$i;
         } elseif ($match_type == -1 && $lookupArrayValue <= $lookup_value) {
             $i = array_search($i, $keySet);
             // if match_type is -1 <=> find the smallest value that is greater than or equal to lookup_value
             if ($i < 1) {
                 // 1st cell was already 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) {
             $i = array_search($i, $keySet);
             // if match_type is 1 <=> find the largest value that is less than or equal to lookup_value
             if ($i < 1) {
                 // 1st cell was already 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 Functions::NA();
 }

 /**
  * CONCATENATE
  *
  * @return    string
  */
 public static function CONCATENATE()
 {
     $returnValue = '';
     // Loop through arguments
     $aArgs = Functions::flattenArray(func_get_args());
     foreach ($aArgs as $arg) {
         if (is_bool($arg)) {
             if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) {
                 $arg = (int) $arg;
             } else {
                 $arg = $arg ? \PHPExcel\Calculation::getTRUE() : \PHPExcel\Calculation::getFALSE();
             }
         }
         $returnValue .= $arg;
     }
     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 = Functions::DIV0();
     $summerA = $summerB = 0;
     // Loop through arguments
     $aArgs = 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;
         }
     }
     if ($aCount > 0) {
         $summerA *= $aCount;
         $summerB *= $summerB;
         $returnValue = ($summerA - $summerB) / ($aCount * $aCount);
     }
     return $returnValue;
 }

 /**
  * 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 = Functions::flattenSingleValue($rate);
     if (!is_numeric($rate)) {
         return Functions::VALUE();
     }
     if (!is_array($values) || !is_array($dates)) {
         return Functions::VALUE();
     }
     $values = Functions::flattenArray($values);
     $dates = Functions::flattenArray($dates);
     $valCount = count($values);
     if ($valCount != count($dates)) {
         return Functions::NAN();
     }
     if (min($values) > 0 || max($values) < 0) {
         return Functions::VALUE();
     }
     $xnpv = 0.0;
     for ($i = 0; $i < $valCount; ++$i) {
         if (!is_numeric($values[$i])) {
             return Functions::VALUE();
         }
         $xnpv += $values[$i] / pow(1 + $rate, DateTime::DATEDIF($dates[0], $dates[$i], 'd') / 365);
     }
     return is_finite($xnpv) ? $xnpv : Functions::VALUE();
 }

 /**
  * WORKDAY
  *
  * Returns the date that is the indicated number of working days before or after a date (the
  * starting date). Working days exclude weekends and any dates identified as holidays.
  * Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected
  * delivery times, or the number of days of work performed.
  *
  * Excel Function:
  *        WORKDAY(startDate,endDays[,holidays[,holiday[,...]]])
  *
  * @access    public
  * @category Date/Time Functions
  * @param    mixed        $startDate        Excel date serial value (float), PHP date timestamp (integer),
  *                                        PHP DateTime object, or a standard date string
  * @param    integer        $endDays        The number of nonweekend and nonholiday days before or after
  *                                        startDate. A positive value for days yields a future date; a
  *                                        negative value yields a past date.
  * @param    mixed        $holidays,...    Optional series of Excel date serial value (float), PHP date
  *                                        timestamp (integer), PHP DateTime object, or a standard date
  *                                        strings that will be excluded from the working calendar, such
  *                                        as state and federal holidays and floating holidays.
  * @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 WORKDAY($startDate, $endDays)
 {
     //    Retrieve the mandatory start date and days that are referenced in the function definition
     $startDate = Functions::flattenSingleValue($startDate);
     $endDays = Functions::flattenSingleValue($endDays);
     //    Flush the mandatory start date and days that are referenced in the function definition, and get the optional days
     $dateArgs = Functions::flattenArray(func_get_args());
     array_shift($dateArgs);
     array_shift($dateArgs);
     if (is_string($startDate = self::getDateValue($startDate)) || !is_numeric($endDays)) {
         return Functions::VALUE();
     }
     $startDate = (double) floor($startDate);
     $endDays = (int) floor($endDays);
     //    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 (!empty($dateArgs)) {
         $holidayCountedArray = $holidayDates = array();
         foreach ($dateArgs as $holidayDate) {
             if ($holidayDate !== null && trim($holidayDate) > '') {
                 if (is_string($holidayDate = self::getDateValue($holidayDate))) {
                     return 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 (Functions::getReturnDateType()) {
         case Functions::RETURNDATE_EXCEL:
//.........这里部分代码省略.........

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

 /**
  * 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 logicalOr()
 {
     // Return value
     $returnValue = false;
     // Loop through the arguments
     $aArgs = 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 Functions::VALUE();
             }
             $returnValue = $returnValue || $arg != 0;
         }
     }
     // Return
     if ($argCount < 0) {
         return Functions::VALUE();
     }
     return $returnValue;
 }