PHP PHPExcel_Calculation_MathTrig类代码示例

 public function createTrueFalseArrayAndProductArray($objPHPExcel)
 {
     $firstSheet = $objPHPExcel->getSheet(0);
     $richtigFalschWerte = new PHPExcel_Worksheet($objPHPExcel);
     $richtigFalschWerte->setTitle('RichtigFalschWerte');
     $objPHPExcel->addSheet($richtigFalschWerte);
     $lastColumnRawData = $firstSheet->getHighestColumn();
     $maxColumn = $lastColumnRawData;
     $maxColumn++;
     $anzahlTeilnehmer = $firstSheet->getCell('C4')->getValue();
     //Erreichbare Punktzahl = $anzahlTeilnehmer + 12
     $endrow = $anzahlTeilnehmer + 6;
     $richtigFalschColumn = 'A';
     for ($column = 'G'; $column != $maxColumn; $column++) {
         $erreichbarePunkte = $firstSheet->getCell($column . ($anzahlTeilnehmer + 12))->getValue();
         for ($row = 7; $row <= $endrow; $row++) {
             if ($firstSheet->getCell($column . $row)->getCalculatedValue() >= 0.5 * (double) $erreichbarePunkte) {
                 $richtigFalschWerte->setCellValue($richtigFalschColumn . ($row - 6), 1);
             } else {
                 $richtigFalschWerte->setCellValue($richtigFalschColumn . ($row - 6), 0);
             }
         }
         $richtigFalschColumn++;
     }
     $lastColumnTrueFalseData = $richtigFalschWerte->getHighestColumn();
     $lastRowTrueFalseData = $richtigFalschWerte->getHighestRow();
     $summaryColumn = $lastColumnTrueFalseData;
     $summaryColumn++;
     for ($row = 1; $row <= $lastRowTrueFalseData; $row++) {
         $richtigFalschWerte->setCellValue($summaryColumn . $row, '=SUM( A' . $row . ':' . $lastColumnTrueFalseData . $row . ')');
     }
     $richtigFalschWerte->setCellValue($summaryColumn . ($lastRowTrueFalseData + 1), '=AVERAGE(' . $summaryColumn . '1:' . $summaryColumn . $lastRowTrueFalseData . ')');
     $richtigFalschWerte->setCellValue($summaryColumn . ($lastRowTrueFalseData + 2), '=VARP(' . $summaryColumn . '1:' . $summaryColumn . $lastRowTrueFalseData . ')');
     $richtigFalschWerte->setCellValue($summaryColumn . ($lastRowTrueFalseData + 3), '=SQRT(' . $summaryColumn . ($lastRowTrueFalseData + 2) . ')');
     $richtigFalschProdukte = new PHPExcel_Worksheet($objPHPExcel);
     $richtigFalschProdukte->setTitle('RichtigFalsch Produkte');
     $objPHPExcel->addSheet($richtigFalschProdukte);
     $aufgabenwerte = $richtigFalschWerte->rangeToArray('A1:' . $lastColumnTrueFalseData . $lastRowTrueFalseData, 0, true, false);
     //$produkteAufgaben->fromArray($aufgabenwerte, NULL, 'A1', true);
     $transponierteAufgabenwerte = PHPExcel_Calculation_LookupRef::TRANSPOSE($aufgabenwerte);
     $endmatrix = PHPExcel_Calculation_MathTrig::MMULT($transponierteAufgabenwerte, $aufgabenwerte);
     $richtigFalschProdukte->fromArray($endmatrix, NULL, 'A1', true);
     $lastColumnMMULTData = $richtigFalschProdukte->getHighestColumn();
     $lastRowMMULTData = $richtigFalschProdukte->getHighestRow();
     $maxColumn = $lastColumnMMULTData;
     $maxColumn++;
     $writeRow = $lastRowMMULTData + 2;
     for ($column = 'A'; $column != $maxColumn; $column++) {
         $cell = $richtigFalschProdukte->getCell($column . $writeRow);
         $cell->setValue('=SUM(' . $column . '1:' . $column . $lastRowMMULTData . ')');
     }
 }

 /**
  * POISSON
  *
  * Returns the Poisson distribution. A common application of the Poisson distribution
  * is predicting the number of events over a specific time, such as the number of
  * cars arriving at a toll plaza in 1 minute.
  *
  * @param	float		$value
  * @param	float		$mean		Mean Value
  * @param	boolean		$cumulative
  * @return	float
  *
  */
 public static function POISSON($value, $mean, $cumulative)
 {
     $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
     $mean = PHPExcel_Calculation_Functions::flattenSingleValue($mean);
     if (is_numeric($value) && is_numeric($mean)) {
         if ($value <= 0 || $mean <= 0) {
             return PHPExcel_Calculation_Functions::NaN();
         }
         if (is_numeric($cumulative) || is_bool($cumulative)) {
             if ($cumulative) {
                 $summer = 0;
                 for ($i = 0; $i <= floor($value); ++$i) {
                     $summer += pow($mean, $i) / PHPExcel_Calculation_MathTrig::FACT($i);
                 }
                 return exp(0 - $mean) * $summer;
             } else {
                 return exp(0 - $mean) * pow($mean, $value) / PHPExcel_Calculation_MathTrig::FACT($value);
             }
         }
     }
     return PHPExcel_Calculation_Functions::VALUE();
 }

 /**
  * DSUM
  *
  * Adds the numbers in a column of a list or database that match conditions that you specify.
  *
  * Excel Function:
  *        DSUM(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    float
  *
  */
 public static function DSUM($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
     return PHPExcel_Calculation_MathTrig::SUM($colData);
 }

//.........这里部分代码省略.........
                     $format = str_replace($m[0], strlen($m[0]), $format);
                 }
                 $format = '%' . str_replace('%', 'f%%', $format);
                 $value = sprintf($format, 100 * $value);
             }
         } else {
             if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) {
                 $value = 'EUR ' . sprintf('%1.2f', $value);
             } else {
                 // In Excel formats, "_" is used to add spacing, which we can't do in HTML
                 $format = preg_replace('/_./', '', $format);
                 // Some non-number characters are escaped with \, which we don't need
                 $format = preg_replace("/\\\\/", '', $format);
                 // Some non-number strings are quoted, so we'll get rid of the quotes, likewise any positional * symbols
                 $format = str_replace(array('"', '*'), '', $format);
                 // Find out if we need thousands separator
                 // This is indicated by a comma enclosed by a digit placeholder:
                 //		#,#   or   0,0
                 $useThousands = preg_match('/(#,#|0,0)/', $format);
                 if ($useThousands) {
                     $format = preg_replace('/0,0/', '00', $format);
                     $format = preg_replace('/#,#/', '##', $format);
                 }
                 // Scale thousands, millions,...
                 // This is indicated by a number of commas after a digit placeholder:
                 //		#,   or	0.0,,
                 $scale = 1;
                 // same as no scale
                 $matches = array();
                 if (preg_match('/(#|0)(,+)/', $format, $matches)) {
                     $scale = pow(1000, strlen($matches[2]));
                     // strip the commas
                     $format = preg_replace('/0,+/', '0', $format);
                     $format = preg_replace('/#,+/', '#', $format);
                 }
                 if (preg_match('/#?.*\\?\\/\\?/', $format, $m)) {
                     //echo 'Format mask is fractional '.$format.' <br />';
                     if ($value != (int) $value) {
                         $sign = $value < 0 ? '-' : '';
                         $integerPart = floor(abs($value));
                         $decimalPart = trim(fmod(abs($value), 1), '0.');
                         $decimalLength = strlen($decimalPart);
                         $decimalDivisor = pow(10, $decimalLength);
                         $GCD = PHPExcel_Calculation_MathTrig::GCD($decimalPart, $decimalDivisor);
                         $adjustedDecimalPart = $decimalPart / $GCD;
                         $adjustedDecimalDivisor = $decimalDivisor / $GCD;
                         if (strpos($format, '0') !== false || strpos($format, '#') !== false || substr($format, 0, 3) == '? ?') {
                             if ($integerPart == 0) {
                                 $integerPart = '';
                             }
                             $value = "{$sign}{$integerPart} {$adjustedDecimalPart}/{$adjustedDecimalDivisor}";
                         } else {
                             $adjustedDecimalPart += $integerPart * $adjustedDecimalDivisor;
                             $value = "{$sign}{$adjustedDecimalPart}/{$adjustedDecimalDivisor}";
                         }
                     }
                 } else {
                     // Handle the number itself
                     // scale number
                     $value = $value / $scale;
                     // Strip #
                     $format = preg_replace('/\\#/', '', $format);
                     $n = "/\\[[^\\]]+\\]/";
                     $m = preg_replace($n, '', $format);
                     $number_regex = "/(0+)(\\.?)(0*)/";
                     if (preg_match($number_regex, $m, $matches)) {
                         $left = $matches[1];
                         $dec = $matches[2];
                         $right = $matches[3];
                         // minimun width of formatted number (including dot)
                         $minWidth = strlen($left) + strlen($dec) + strlen($right);
                         if ($useThousands) {
                             $value = number_format($value, strlen($right), PHPExcel_Shared_String::getDecimalSeparator(), PHPExcel_Shared_String::getThousandsSeparator());
                         } else {
                             $sprintf_pattern = "%0{$minWidth}." . strlen($right) . "f";
                             $value = sprintf($sprintf_pattern, $value);
                         }
                         $value = preg_replace($number_regex, $value, $format);
                     }
                 }
                 if (preg_match('/\\[\\$(.*)\\]/u', $format, $m)) {
                     //	Currency or Accounting
                     $currencyFormat = $m[0];
                     $currencyCode = $m[1];
                     list($currencyCode) = explode('-', $currencyCode);
                     if ($currencyCode == '') {
                         $currencyCode = PHPExcel_Shared_String::getCurrencyCode();
                     }
                     $value = preg_replace('/\\[\\$([^\\]]*)\\]/u', $currencyCode, $value);
                 }
             }
         }
     }
     // Additional formatting provided by callback function
     if ($callBack !== null) {
         list($writerInstance, $function) = $callBack;
         $value = $writerInstance->{$function}($value, $formatColor);
     }
     return $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);
     }
 }

 private static function _formatAsFraction(&$value, &$format)
 {
     $sign = $value < 0 ? '-' : '';
     $integerPart = floor(abs($value));
     $decimalPart = trim(fmod(abs($value), 1), '0.');
     $decimalLength = strlen($decimalPart);
     $decimalDivisor = pow(10, $decimalLength);
     $GCD = PHPExcel_Calculation_MathTrig::GCD($decimalPart, $decimalDivisor);
     $adjustedDecimalPart = $decimalPart / $GCD;
     $adjustedDecimalDivisor = $decimalDivisor / $GCD;
     if (strpos($format, '0') !== false || strpos($format, '#') !== false || substr($format, 0, 3) == '? ?') {
         if ($integerPart == 0) {
             $integerPart = '';
         }
         $value = "{$sign}{$integerPart} {$adjustedDecimalPart}/{$adjustedDecimalDivisor}";
     } else {
         $adjustedDecimalPart += $integerPart * $adjustedDecimalDivisor;
         $value = "{$sign}{$adjustedDecimalPart}/{$adjustedDecimalDivisor}";
     }
 }

 /**
  * 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);
     $mask = '$#,##0';
     if ($decimals > 0) {
         $mask .= '.' . str_repeat('0', $decimals);
     } else {
         $round = pow(10, abs($decimals));
         if ($value < 0) {
             $round = 0 - $round;
         }
         $value = PHPExcel_Calculation_MathTrig::MROUND($value, $round);
     }
     return PHPExcel_Style_NumberFormat::toFormattedString($value, $mask);
 }

 /**
  *	BESSELJ
  *
  *	Returns the Bessel function
  *
  *	Excel Function:
  *		BESSELJ(x,ord)
  *
  *	@access	public
  *	@category Engineering Functions
  *	@param	float		$x		The value at which to evaluate the function.
  *								If x is nonnumeric, BESSELJ returns the #VALUE! error value.
  *	@param	integer		$ord	The order of the Bessel function. If n is not an integer, it is truncated.
  *								If $ord is nonnumeric, BESSELJ returns the #VALUE! error value.
  *								If $ord < 0, BESSELJ returns the #NUM! error value.
  *	@return	float
  *
  */
 public static function BESSELJ($x, $ord)
 {
     $x = is_null($x) ? 0.0 : PHPExcel_Calculation_Functions::flattenSingleValue($x);
     $ord = is_null($ord) ? 0.0 : PHPExcel_Calculation_Functions::flattenSingleValue($ord);
     if (is_numeric($x) && is_numeric($ord)) {
         $ord = floor($ord);
         if ($ord < 0) {
             return PHPExcel_Calculation_Functions::NaN();
         }
         $fResult = 0;
         if (abs($x) <= 30) {
             $fResult = $fTerm = pow($x / 2, $ord) / PHPExcel_Calculation_MathTrig::FACT($ord);
             $ordK = 1;
             $fSqrX = $x * $x / -4;
             do {
                 $fTerm *= $fSqrX;
                 $fTerm /= $ordK * ($ordK + $ord);
                 $fResult += $fTerm;
             } while (abs($fTerm) > 1.0E-12 && ++$ordK < 100);
         } else {
             $f_PI_DIV_2 = M_PI / 2;
             $f_PI_DIV_4 = M_PI / 4;
             $fXAbs = abs($x);
             $fResult = sqrt(M_2DIVPI / $fXAbs) * cos($fXAbs - $ord * $f_PI_DIV_2 - $f_PI_DIV_4);
             if ($ord & 1 && $x < 0) {
                 $fResult = -$fResult;
             }
         }
         return is_nan($fResult) ? PHPExcel_Calculation_Functions::NaN() : $fResult;
     }
     return PHPExcel_Calculation_Functions::VALUE();
 }

 /**
  * DSUM
  *
  * Adds the numbers in a column of a list or database that match conditions that you specify.
  *
  * Excel Function:
  *        DSUM(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 float
  *
  */
 public static function DSUM($database, $field, $criteria)
 {
     $field = self::fieldExtract($database, $field);
     if (is_null($field)) {
         return null;
     }
     // Return
     return PHPExcel_Calculation_MathTrig::SUM(self::getFilteredColumn($database, $field, $criteria));
 }

	/**
	 * BESSELJ
	 *
	 * Returns the Bessel function
	 *
	 * @param float $x        	
	 * @param float $n        	
	 * @return int
	 */
	public static function BESSELJ($x, $n) {
		$x = (is_null ( $x )) ? 0.0 : PHPExcel_Calculation_Functions::flattenSingleValue ( $x );
		$n = (is_null ( $n )) ? 0.0 : PHPExcel_Calculation_Functions::flattenSingleValue ( $n );
		
		if ((is_numeric ( $x )) && (is_numeric ( $n ))) {
			$n = floor ( $n );
			if ($n < 0) {
				return PHPExcel_Calculation_Functions::NaN ();
			}
			$f_PI_DIV_2 = M_PI / 2;
			$f_PI_DIV_4 = M_PI / 4;
			
			$fResult = 0;
			if (abs ( $x ) <= 30) {
				$fTerm = pow ( $x / 2, $n ) / PHPExcel_Calculation_MathTrig::FACT ( $n );
				$nK = 1;
				$fResult = $fTerm;
				$fSqrX = ($x * $x) / - 4;
				do {
					$fTerm *= $fSqrX;
					$fTerm /= ($nK * ($nK + $n));
					$fResult += $fTerm;
				} while ( (abs ( $fTerm ) > 1e-10) && (++ $nK < 100) );
			} else {
				$fXAbs = abs ( $x );
				$fResult = sqrt ( M_2DIVPI / $fXAbs ) * cos ( $fXAbs - $n * $f_PI_DIV_2 - $f_PI_DIV_4 );
				if (($n && 1) && ($x < 0)) {
					$fResult = - $fResult;
				}
			}
			return $fResult;
		}
		return PHPExcel_Calculation_Functions::VALUE ();
	} // function BESSELJ()