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