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