private static function _coupFirstPeriodDate($settlement, $maturity, $frequency, $next) { $months = 12 / $frequency; $result = Shared_Date::ExcelToPHPObject($maturity); $eom = self::_lastDayOfMonth($result); while ($settlement < Shared_Date::PHPToExcel($result)) { $result->modify('-' . $months . ' months'); } if ($next) { $result->modify('+' . $months . ' months'); } if ($eom) { $result->modify('-1 day'); } return Shared_Date::PHPToExcel($result); }
/** * 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 === 'General') { 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 = 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 $format = preg_replace('/"/', '', $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 = Calculation_Functions::GCD($decimalPart, $decimalDivisor); $adjustedDecimalPart = $decimalPart / $GCD; $adjustedDecimalDivisor = $decimalDivisor / $GCD; if (strpos($format, '0') !== 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); $number_regex = "/(0+)(\\.?)(0*)/"; $matches = array(); if (preg_match($number_regex, $format, $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), Shared_String::getDecimalSeparator(), Shared_String::getThousandsSeparator()); } else { $sprintf_pattern = "%0{$minWidth}." . strlen($right) . "f"; $value = sprintf($sprintf_pattern, $value); } $value = preg_replace($number_regex, $value, $format); } } } } } // Additional formatting provided by callback function if ($callBack !== null) { list($writerInstance, $function) = $callBack; $value = $writerInstance->{$function}($value, $formatColor); } return $value; }