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);
}
/**
* Convert a value in a pre-defined format to a PHP string
*
* @param mixed $value Value to format
* @param string $format Format code
* @param array $callBack Callback function for additional formatting of string
* @return string Formatted string
*/
public static function toFormattedString($value = '', $format = '', $callBack = null)
{
// For now we do not treat strings although section 4 of a format code affects strings
if (!is_numeric($value)) {
return $value;
}
// For 'General' format code, we just pass the value although this is not entirely the way Excel does it,
// it seems to round numbers to a total of 10 digits.
if ($format === PHPExcel_Style_NumberFormat::FORMAT_GENERAL || $format === PHPExcel_Style_NumberFormat::FORMAT_TEXT) {
return $value;
}
// Get the sections, there can be up to four sections
$sections = explode(';', $format);
// Fetch the relevant section depending on whether number is positive, negative, or zero?
// Text not supported yet.
// Here is how the sections apply to various values in Excel:
// 1 section: [POSITIVE/NEGATIVE/ZERO/TEXT]
// 2 sections: [POSITIVE/ZERO/TEXT] [NEGATIVE]
// 3 sections: [POSITIVE/TEXT] [NEGATIVE] [ZERO]
// 4 sections: [POSITIVE] [NEGATIVE] [ZERO] [TEXT]
switch (count($sections)) {
case 1:
$format = $sections[0];
break;
case 2:
$format = $value >= 0 ? $sections[0] : $sections[1];
$value = abs($value);
// Use the absolute value
break;
case 3:
$format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]);
$value = abs($value);
// Use the absolute value
break;
case 4:
$format = $value > 0 ? $sections[0] : ($value < 0 ? $sections[1] : $sections[2]);
$value = abs($value);
// Use the absolute value
break;
default:
// something is wrong, just use first section
$format = $sections[0];
break;
}
// Save format with color information for later use below
$formatColor = $format;
// Strip color information
$color_regex = '/^\\[[a-zA-Z]+\\]/';
$format = preg_replace($color_regex, '', $format);
// Let's begin inspecting the format and converting the value to a formatted string
if (preg_match('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])*[hmsdy]/i', $format)) {
// datetime format
// dvc: convert Excel formats to PHP date formats
// strip off first part containing e.g. [$-F800] or [$USD-409]
// general syntax: [$<Currency string>-<language info>]
// language info is in hexadecimal
$format = preg_replace('/^(\\[\\$[A-Z]*-[0-9A-F]*\\])/i', '', $format);
// OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case
$format = strtolower($format);
$format = strtr($format, self::$_dateFormatReplacements);
if (!strpos($format, 'A')) {
// 24-hour time format
$format = strtr($format, self::$_dateFormatReplacements24);
} else {
// 12-hour time format
$format = strtr($format, self::$_dateFormatReplacements12);
}
$dateObj = PHPExcel_Shared_Date::ExcelToPHPObject($value);
$value = $dateObj->format($format);
} else {
if (preg_match('/%$/', $format)) {
// % number format
if ($format === self::FORMAT_PERCENTAGE) {
$value = round(100 * $value, 0) . '%';
} else {
if (preg_match('/\\.[#0]+/i', $format, $m)) {
$s = substr($m[0], 0, 1) . (strlen($m[0]) - 1);
$format = str_replace($m[0], $s, $format);
}
if (preg_match('/^[#0]+/', $format, $m)) {
$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()
