PHP PHPExcel_Calculation_Functions::NaN方法代码示例

 /**
  * DGET
  *
  * Extracts a single value from a column of a list or database that matches conditions that you
  * specify.
  *
  * Excel Function:
  *        DGET(database,field,criteria)
  *
  * @access    public
  * @category Database Functions
  * @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    string|integer $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.
  * @param    mixed[] $criteria The range of cells that contains the conditions you specify.
  *                                        You can use any range for the criteria argument, as long as it
  *                                        includes at least one column label and at least one cell below
  *                                        the column label in which you specify a condition for the
  *                                        column.
  * @return    mixed
  *
  */
 public static function DGET($database, $field, $criteria)
 {
     $field = self::__fieldExtract($database, $field);
     if (is_null($field)) {
         return NULL;
     }
     //	reduce the database to a set of rows that match all the criteria
     $database = self::__filter($database, $criteria);
     //	extract an array of values for the requested column
     $colData = array();
     foreach ($database as $row) {
         $colData[] = $row[$field];
     }
     // Return
     if (count($colData) > 1) {
         return PHPExcel_Calculation_Functions::NaN();
     }
     return $colData[0];
 }

 /**
  *    power
  *
  *    A = A ^ B
  *    @param mixed $B Matrix/Array
  *    @return Matrix Sum
  */
 public function power()
 {
     if (func_num_args() > 0) {
         $args = func_get_args();
         $match = implode(",", array_map('gettype', $args));
         switch ($match) {
             case 'object':
                 if ($args[0] instanceof PHPExcel_Shared_JAMA_Matrix) {
                     $M = $args[0];
                 } else {
                     throw new PHPExcel_Calculation_Exception(self::ARGUMENT_TYPE_EXCEPTION);
                 }
                 break;
             case 'array':
                 $M = new PHPExcel_Shared_JAMA_Matrix($args[0]);
                 break;
             default:
                 throw new PHPExcel_Calculation_Exception(self::POLYMORPHIC_ARGUMENT_EXCEPTION);
                 break;
         }
         $this->checkMatrixDimensions($M);
         for ($i = 0; $i < $this->m; ++$i) {
             for ($j = 0; $j < $this->n; ++$j) {
                 $validValues = true;
                 $value = $M->get($i, $j);
                 if (is_string($this->A[$i][$j]) && strlen($this->A[$i][$j]) > 0 && !is_numeric($this->A[$i][$j])) {
                     $this->A[$i][$j] = trim($this->A[$i][$j], '"');
                     $validValues &= PHPExcel_Shared_String::convertToNumberIfFraction($this->A[$i][$j]);
                 }
                 if (is_string($value) && strlen($value) > 0 && !is_numeric($value)) {
                     $value = trim($value, '"');
                     $validValues &= PHPExcel_Shared_String::convertToNumberIfFraction($value);
                 }
                 if ($validValues) {
                     $this->A[$i][$j] = pow($this->A[$i][$j], $value);
                 } else {
                     $this->A[$i][$j] = PHPExcel_Calculation_Functions::NaN();
                 }
             }
         }
         return $this;
     } else {
         throw new PHPExcel_Calculation_Exception(self::POLYMORPHIC_ARGUMENT_EXCEPTION);
     }
 }

 /**
  * 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	PHPExcel_Calculation_Exception
  */
 public function calculateCellValue(PHPExcel_Cell $pCell = NULL, $resetLog = TRUE)
 {
     if ($pCell === NULL) {
         return NULL;
     }
     $returnArrayAsType = self::$returnArrayAsType;
     if ($resetLog) {
         //	Initialise the logging settings if requested
         $this->formulaError = null;
         $this->_debugLog->clearLog();
         $this->_cyclicReferenceStack->clear();
         $this->_cyclicFormulaCount = 1;
         self::$returnArrayAsType = self::RETURN_ARRAY_AS_ARRAY;
     }
     //	Execute the calculation for the cell formula
     $this->_cellStack[] = array('sheet' => $pCell->getWorksheet()->getTitle(), 'cell' => $pCell->getCoordinate());
     try {
         $result = self::_unwrapResult($this->_calculateFormulaValue($pCell->getValue(), $pCell->getCoordinate(), $pCell));
         $cellAddress = array_pop($this->_cellStack);
         $this->_workbook->getSheetByName($cellAddress['sheet'])->getCell($cellAddress['cell']);
     } catch (PHPExcel_Exception $e) {
         $cellAddress = array_pop($this->_cellStack);
         $this->_workbook->getSheetByName($cellAddress['sheet'])->getCell($cellAddress['cell']);
         throw new PHPExcel_Calculation_Exception($e->getMessage());
     }
     if (is_array($result) && self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY) {
         self::$returnArrayAsType = $returnArrayAsType;
         $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);
     }
     self::$returnArrayAsType = $returnArrayAsType;
     if ($result === NULL) {
         return 0;
     } elseif (is_float($result) && (is_nan($result) || is_infinite($result))) {
         return PHPExcel_Calculation_Functions::NaN();
     }
     return $result;
 }

 /**
  * FIXEDFORMAT
  *
  * @param	mixed		$value	Value to check
  * @param	integer		$decimals
  * @param	boolean		$no_commas
  * @return	boolean
  */
 public static function FIXEDFORMAT($value, $decimals = 2, $no_commas = FALSE)
 {
     $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
     $decimals = PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
     $no_commas = PHPExcel_Calculation_Functions::flattenSingleValue($no_commas);
     // Validate parameters
     if (!is_numeric($value) || !is_numeric($decimals)) {
         return PHPExcel_Calculation_Functions::NaN();
     }
     $decimals = floor($decimals);
     $valueResult = round($value, $decimals);
     if ($decimals < 0) {
         $decimals = 0;
     }
     if (!$no_commas) {
         $valueResult = number_format($valueResult, $decimals);
     }
     return (string) $valueResult;
 }

 /**
  * SECONDOFMINUTE
  *
  * @param	long	$timeValue		Excel time serial value or a standard time string
  * @return	int		Second
  */
 public static function SECONDOFMINUTE($timeValue = 0)
 {
     $timeValue = PHPExcel_Calculation_Functions::flattenSingleValue($timeValue);
     if (!is_numeric($timeValue)) {
         if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
             $testVal = strtok($timeValue, '/-: ');
             if (strlen($testVal) < strlen($timeValue)) {
                 return PHPExcel_Calculation_Functions::VALUE();
             }
         }
         $timeValue = self::_getTimeValue($timeValue);
         if (is_string($timeValue)) {
             return PHPExcel_Calculation_Functions::VALUE();
         }
     }
     // Execute function
     if ($timeValue >= 1) {
         $timeValue = fmod($timeValue, 1);
     } elseif ($timeValue < 0.0) {
         return PHPExcel_Calculation_Functions::NaN();
     }
     $timeValue = PHPExcel_Shared_Date::ExcelToPHP($timeValue);
     return (int) gmdate('s', $timeValue);
 }

 /**
  * IMSUB
  *
  * Returns the difference of two complex numbers in x + yi or x + yj text format.
  *
  * Excel Function:
  *        IMSUB(complexNumber1,complexNumber2)
  *
  * @param    string $complexNumber1 The complex number from which to subtract complexNumber2.
  * @param    string $complexNumber2 The complex number to subtract from complexNumber1.
  *
  * @return    string
  */
 public static function IMSUB($complexNumber1, $complexNumber2)
 {
     $complexNumber1 = PHPExcel_Calculation_Functions::flattenSingleValue($complexNumber1);
     $complexNumber2 = PHPExcel_Calculation_Functions::flattenSingleValue($complexNumber2);
     $parsedComplex1 = self::_parseComplex($complexNumber1);
     $parsedComplex2 = self::_parseComplex($complexNumber2);
     if ($parsedComplex1['suffix'] != '' && $parsedComplex2['suffix'] != '' && $parsedComplex1['suffix'] != $parsedComplex2['suffix']) {
         return PHPExcel_Calculation_Functions::NaN();
     } elseif ($parsedComplex1['suffix'] == '' && $parsedComplex2['suffix'] != '') {
         $parsedComplex1['suffix'] = $parsedComplex2['suffix'];
     }
     $d1 = $parsedComplex1['real'] - $parsedComplex2['real'];
     $d2 = $parsedComplex1['imaginary'] - $parsedComplex2['imaginary'];
     return self::COMPLEX($d1, $d2, $parsedComplex1['suffix']);
 }

 public function testNaN()
 {
     $result = PHPExcel_Calculation_Functions::NaN();
     $this->assertEquals('#NUM!', $result);
 }

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

 /**
  * SQRTPI
  *
  * Returns the square root of (number * pi).
  *
  * @param	float	$number		Number
  * @return	float	Square Root of Number * Pi
  */
 public static function SQRTPI($number)
 {
     $number = PHPExcel_Calculation_Functions::flattenSingleValue($number);
     if (is_numeric($number)) {
         if ($number < 0) {
             return PHPExcel_Calculation_Functions::NaN();
         }
         return sqrt($number * M_PI);
     }
     return PHPExcel_Calculation_Functions::VALUE();
 }

 /**
  *	DOLLAR
  *
  *	This function converts a number to text using currency format, with the decimals rounded to the specified place.
  *	The format used is $#,##0.00_);($#,##0.00)..
  *
  *	@param	float	$value			The value to format
  *	@param	int		$decimals		The number of digits to display to the right of the decimal point.
  *									If decimals is negative, number is rounded to the left of the decimal point.
  *									If you omit decimals, it is assumed to be 2
  *	@return	string
  */
 public static function DOLLAR($value = 0, $decimals = 2)
 {
     $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
     $decimals = is_null($decimals) ? 0 : PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
     // Validate parameters
     if (!is_numeric($value) || !is_numeric($decimals)) {
         return PHPExcel_Calculation_Functions::NaN();
     }
     $decimals = floor($decimals);
     if ($decimals > 0) {
         return money_format('%.' . $decimals . 'n', $value);
     } else {
         $round = pow(10, abs($decimals));
         if ($value < 0) {
             $round = 0 - $round;
         }
         $value = PHPExcel_Calculation_MathTrig::MROUND($value, $round);
         //	The implementation of money_format used if the standard PHP function is not available can't handle decimal places of 0,
         //		so we display to 1 dp and chop off that character and the decimal separator using substr
         return substr(money_format('%.1n', $value), 0, -2);
     }
 }

 /**
  * ERFC
  *
  * Returns the complementary ERF function integrated between x and infinity
  *
  * @param	float		$x		The lower bound for integrating ERF
  * @return	int
  */
 public static function ERFC($x)
 {
     $x = PHPExcel_Calculation_Functions::flattenSingleValue($x);
     if (is_numeric($x)) {
         if ($x < 0) {
             return PHPExcel_Calculation_Functions::NaN();
         }
         return self::_erfcVal($x);
     }
     return PHPExcel_Calculation_Functions::VALUE();
 }

 /**
  * Wrap string values in quotes
  *
  * @param mixed $value
  *
  * @return mixed
  */
 public static function _wrapResult($value)
 {
     if (is_string($value)) {
         //	Error values cannot be "wrapped"
         if (preg_match('/^' . self::CALCULATION_REGEXP_ERROR . '$/i', $value, $match)) {
             //	Return Excel errors "as is"
             return $value;
         }
         //	Return strings wrapped in quotes
         return '"' . $value . '"';
         //	Convert numeric errors to NaN error
     } else {
         if (is_float($value) && (is_nan($value) || is_infinite($value))) {
             return PHPExcel_Calculation_Functions::NaN();
         }
     }
     return $value;
 }

 /**
  *	Remove quotes used as a wrapper to identify string values
  *
  *	@param mixed $value
  *	@return mixed
  */
 public static function _unwrapResult($value)
 {
     if (is_string($value)) {
         if (strlen($value) > 0 && $value[0] == '"' && substr($value, -1) == '"') {
             return substr($value, 1, -1);
         }
         //	Convert numeric errors to NaN error
     } else {
         if (is_float($value) && (is_nan($value) || is_infinite($value))) {
             return PHPExcel_Calculation_Functions::NaN();
         }
     }
     return $value;
 }

 /**
  * DGET
  *
  * Extracts a single value from a column of a list or database that matches conditions that you
  * specify.
  *
  * Excel Function:
  *        DGET(database,field,criteria)
  *
  * @access    public
  * @category Database Functions
  * @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 string|integer $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.
  * @param mixed[] $criteria The range of cells that contains the conditions you specify.
  *                                        You can use any range for the criteria argument, as long as it
  *                                        includes at least one column label and at least one cell below
  *                                        the column label in which you specify a condition for the
  *                                        column.
  * @return mixed
  *
  */
 public static function DGET($database, $field, $criteria)
 {
     $field = self::fieldExtract($database, $field);
     if (is_null($field)) {
         return null;
     }
     // Return
     $colData = self::getFilteredColumn($database, $field, $criteria);
     if (count($colData) > 1) {
         return PHPExcel_Calculation_Functions::NaN();
     }
     return $colData[0];
 }

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