/**
  * TEXTFORMAT
  *
  * @param	mixed	$value	Value to check
  * @param	string	$format	Format mask to use
  * @return	boolean
  */
 public static function TEXTFORMAT($value, $format)
 {
     $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
     $format = PHPExcel_Calculation_Functions::flattenSingleValue($format);
     if (is_string($value) && !is_numeric($value) && PHPExcel_Shared_Date::isDateTimeFormatCode($format)) {
         $value = PHPExcel_Calculation_DateTime::DATEVALUE($value);
     }
     return (string) PHPExcel_Style_NumberFormat::toFormattedString($value, $format);
 }
Ejemplo n.º 2
0
 /**
  * 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 = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
     $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
     $issue = PHPExcel_Calculation_Functions::flattenSingleValue($issue);
     $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
     $price = PHPExcel_Calculation_Functions::flattenSingleValue($price);
     $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
     //	Validate
     if (is_numeric($rate) && is_numeric($price)) {
         if ($rate <= 0 || $price <= 0) {
             return PHPExcel_Calculation_Functions::NaN();
         }
         $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement), $basis);
         if (!is_numeric($daysPerYear)) {
             return $daysPerYear;
         }
         $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
         if (!is_numeric($daysBetweenIssueAndSettlement)) {
             //	return date error
             return $daysBetweenIssueAndSettlement;
         }
         $daysBetweenIssueAndSettlement *= $daysPerYear;
         $daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $maturity, $basis);
         if (!is_numeric($daysBetweenIssueAndMaturity)) {
             //	return date error
             return $daysBetweenIssueAndMaturity;
         }
         $daysBetweenIssueAndMaturity *= $daysPerYear;
         $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_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 PHPExcel_Calculation_Functions::VALUE();
 }
Ejemplo n.º 3
0
 /**
  *	Convert a dynamic rule daterange to a custom filter range expression for ease of calculation
  *
  *	@param	string										$dynamicRuleType
  *	@param	PHPExcel_Worksheet_AutoFilter_Column		&$filterColumn
  *	@return mixed[]
  */
 private function _dynamicFilterDateRange($dynamicRuleType, &$filterColumn)
 {
     $rDateType = PHPExcel_Calculation_Functions::getReturnDateType();
     PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC);
     $val = $maxVal = NULL;
     $ruleValues = array();
     $baseDate = PHPExcel_Calculation_DateTime::DATENOW();
     //	Calculate start/end dates for the required date range based on current date
     switch ($dynamicRuleType) {
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK:
             $baseDate = strtotime('-7 days', $baseDate);
             break;
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK:
             $baseDate = strtotime('-7 days', $baseDate);
             break;
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH:
             $baseDate = strtotime('-1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
             break;
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH:
             $baseDate = strtotime('+1 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
             break;
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER:
             $baseDate = strtotime('-3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
             break;
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER:
             $baseDate = strtotime('+3 month', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
             break;
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR:
             $baseDate = strtotime('-1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
             break;
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR:
             $baseDate = strtotime('+1 year', gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
             break;
     }
     switch ($dynamicRuleType) {
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TODAY:
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY:
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW:
             $maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(strtotime('+1 day', $baseDate));
             $val = (int) PHPExcel_Shared_Date::PHPToExcel($baseDate);
             break;
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE:
             $maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(strtotime('+1 day', $baseDate));
             $val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate)));
             break;
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR:
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR:
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR:
             $maxVal = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 31, 12, date('Y', $baseDate)));
             ++$maxVal;
             $val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1, date('Y', $baseDate)));
             break;
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER:
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER:
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER:
             $thisMonth = date('m', $baseDate);
             $thisQuarter = floor(--$thisMonth / 3);
             $maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), (1 + $thisQuarter) * 3, date('Y', $baseDate)));
             ++$maxVal;
             $val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, 1 + $thisQuarter * 3, date('Y', $baseDate)));
             break;
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH:
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH:
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH:
             $maxVal = (int) PHPExcel_Shared_Date::PHPtoExcel(gmmktime(0, 0, 0, date('t', $baseDate), date('m', $baseDate), date('Y', $baseDate)));
             ++$maxVal;
             $val = (int) PHPExcel_Shared_Date::PHPToExcel(gmmktime(0, 0, 0, 1, date('m', $baseDate), date('Y', $baseDate)));
             break;
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK:
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK:
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK:
             $dayOfWeek = date('w', $baseDate);
             $val = (int) PHPExcel_Shared_Date::PHPToExcel($baseDate) - $dayOfWeek;
             $maxVal = $val + 7;
             break;
     }
     switch ($dynamicRuleType) {
         //	Adjust Today dates for Yesterday and Tomorrow
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY:
             --$maxVal;
             --$val;
             break;
         case PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW:
             ++$maxVal;
             ++$val;
             break;
     }
     //	Set the filter column rule attributes ready for writing
     $filterColumn->setAttributes(array('val' => $val, 'maxVal' => $maxVal));
     //	Set the rules for identifying rows for hide/show
     $ruleValues[] = array('operator' => PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_GREATERTHANOREQUAL, 'value' => $val);
     $ruleValues[] = array('operator' => PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_LESSTHAN, 'value' => $maxVal);
     PHPExcel_Calculation_Functions::setReturnDateType($rDateType);
     return array('method' => '_filterTestInCustomDataSet', 'arguments' => array('filterRules' => $ruleValues, 'join' => PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND));
 }
Ejemplo n.º 4
0
 /**
  * VALUE
  *
  * @param    mixed    $value    Value to check
  * @return    boolean
  */
 public static function VALUE($value = '')
 {
     $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
     if (!is_numeric($value)) {
         $numberValue = str_replace(PHPExcel_Shared_String::getThousandsSeparator(), '', trim($value, " \t\n\r\v" . PHPExcel_Shared_String::getCurrencyCode()));
         if (is_numeric($numberValue)) {
             return (double) $numberValue;
         }
         $dateSetting = PHPExcel_Calculation_Functions::getReturnDateType();
         PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
         if (strpos($value, ':') !== false) {
             $timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($value);
             if ($timeValue !== PHPExcel_Calculation_Functions::VALUE()) {
                 PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
                 return $timeValue;
             }
         }
         $dateValue = PHPExcel_Calculation_DateTime::DATEVALUE($value);
         if ($dateValue !== PHPExcel_Calculation_Functions::VALUE()) {
             PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
             return $dateValue;
         }
         PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
         return PHPExcel_Calculation_Functions::VALUE();
     }
     return (double) $value;
 }
Ejemplo n.º 5
0
 /**
  * Convert a date/time string to Excel time
  *
  * @param	string	$dateValue		Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
  * @return	float|FALSE		Excel date/time serial value
  */
 public static function stringToExcel($dateValue = '')
 {
     if (strlen($dateValue) < 2) {
         return FALSE;
     }
     if (!preg_match('/^(\\d{1,4}[ \\.\\/\\-][A-Z]{3,9}([ \\.\\/\\-]\\d{1,4})?|[A-Z]{3,9}[ \\.\\/\\-]\\d{1,4}([ \\.\\/\\-]\\d{1,4})?|\\d{1,4}[ \\.\\/\\-]\\d{1,4}([ \\.\\/\\-]\\d{1,4})?)( \\d{1,2}:\\d{1,2}(:\\d{1,2})?)?$/iu', $dateValue)) {
         return FALSE;
     }
     $dateValueNew = PHPExcel_Calculation_DateTime::DATEVALUE($dateValue);
     if ($dateValueNew === PHPExcel_Calculation_Functions::VALUE()) {
         return FALSE;
     } else {
         if (strpos($dateValue, ':') !== FALSE) {
             $timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($dateValue);
             if ($timeValue === PHPExcel_Calculation_Functions::VALUE()) {
                 return FALSE;
             }
             $dateValueNew += $timeValue;
         }
         return $dateValueNew;
     }
 }
Ejemplo n.º 6
0
 /**
  * Convert a date/time string to Excel time
  *
  * @param	string	$dateValue		Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
  * @return	float|false		Excel date/time serial value
  */
 public static function stringToExcel($dateValue = '')
 {
     if (strlen($dateValue) < 2) {
         return false;
     }
     $dateValueNew = PHPExcel_Calculation_DateTime::DATEVALUE($dateValue);
     if ($dateValueNew === PHPExcel_Calculation_Functions::VALUE()) {
         return false;
     } else {
         if (strpos($dateValue, ':') !== false) {
             $timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($dateValue);
             if ($timeValue === PHPExcel_Calculation_Functions::VALUE()) {
                 return false;
             }
             $dateValueNew += $timeValue;
         }
         return $dateValueNew;
     }
 }
Ejemplo n.º 7
0
 public function testEOMONTHtoPHPObject()
 {
     PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT);
     $result = PHPExcel_Calculation_DateTime::EOMONTH('2012-1-26', -1);
     PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
     //    Must return an object...
     $this->assertTrue(is_object($result));
     //    ... of the correct type
     $this->assertTrue(is_a($result, 'DateTime'));
     //    ... with the correct value
     $this->assertEquals($result->format('d-M-Y'), '31-Dec-2011');
 }