Exemplo n.º 1
0
 /**
  * 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 = 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
                 $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 = PHPExcel_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), 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);
                     }
                 }
             }
         }
     }
     // Additional formatting provided by callback function
     if ($callBack !== null) {
         list($writerInstance, $function) = $callBack;
         $value = $writerInstance->{$function}($value, $formatColor);
     }
     return $value;
 }
Exemplo n.º 2
0
 /**
  * Convert a value in a pre-defined format to a PHP string
  *
  * @param mixed 	$value		Value to format
  * @param string 	$format		Format code
  * @return string	Formatted string
  */
 public static function toFormattedString($value = '', $format = '')
 {
     // For now we do not treat strings although part 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 parts, there can be up to four parts
     $parts = explode(';', $format);
     // We should really fetch the relevant part depending on whether we have a positive number,
     // negative number, zero, or text. But for now we just use first part
     $format = $parts[0];
     if (preg_match("/^[hmsdy]/i", $format)) {
         // custom datetime format
         // dvc: convert Excel formats to PHP date formats
         // first remove escapes related to non-format characters
         // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case
         $format = strtolower($format);
         $format = str_replace('\\', '', $format);
         //	4-digit year
         $format = str_replace('yyyy', 'Y', $format);
         //	2-digit year
         $format = str_replace('yy', 'y', $format);
         //	first letter of month - no php equivalent
         $format = str_replace('mmmmm', 'M', $format);
         //	full month name
         $format = str_replace('mmmm', 'F', $format);
         //	short month name
         $format = str_replace('mmm', 'M', $format);
         //	mm is minutes if time or month w/leading zero
         $format = str_replace(':mm', ':i', $format);
         //	tmp place holder
         $format = str_replace('mm', 'x', $format);
         //	month no leading zero
         $format = str_replace('m', 'n', $format);
         //	month leading zero
         $format = str_replace('x', 'm', $format);
         //	12-hour suffix
         $format = str_replace('am/pm', 'A', $format);
         //	full day of week name
         $format = str_replace('dddd', 'l', $format);
         //	short day of week name
         $format = str_replace('ddd', 'D', $format);
         //	tmp place holder
         $format = str_replace('dd', 'x', $format);
         //	days no leading zero
         $format = str_replace('d', 'j', $format);
         //	days leading zero
         $format = str_replace('x', 'd', $format);
         //	seconds
         $format = str_replace('ss', 's', $format);
         //	fractional seconds - no php equivalent
         $format = str_replace('.s', '', $format);
         if (!strpos($format, 'A')) {
             // 24-hour format
             $format = str_replace('h', 'H', $format);
         }
         return gmdate($format, PHPExcel_Shared_Date::ExcelToPHP($value));
     } else {
         if (preg_match('/%$/', $format)) {
             // % number format
             if ($format === self::FORMAT_PERCENTAGE) {
                 return round(100 * $value, 0) . '%';
             }
             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);
             return sprintf($format, 100 * $value);
         } else {
             if (preg_match("/^([0-9.,-]+)\$/", $value)) {
                 if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) {
                     return '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);
                     // TEMPORARY - Convert # to 0
                     $format = preg_replace('/\\#/', '0', $format);
                     // Find out if we need thousands separator
                     $useThousands = preg_match('/,/', $format);
                     if ($useThousands) {
                         $format = preg_replace('/,/', '', $format);
                     }
                     if (preg_match('/0?.*\\?\\/\\?/', $format, $m)) {
                         //echo 'Format mask is fractional '.$format.' <br />';
                         $sign = $value < 0 ? '-' : '';
                         $integerPart = floor(abs($value));
                         $decimalPart = trim(fmod(abs($value), 1), '0.');
                         $decimalLength = strlen($decimalPart);
                         $decimalDivisor = pow(10, $decimalLength);
                         $GCD = PHPExcel_Calculation_Functions::GCD($decimalPart, $decimalDivisor);
                         $adjustedDecimalPart = $decimalPart / $GCD;
                         $adjustedDecimalDivisor = $decimalDivisor / $GCD;
                         if (strpos($format, '0') !== false) {
                             $value = "{$sign}{$integerPart} {$adjustedDecimalPart}/{$adjustedDecimalDivisor}";
                         } else {
                             $adjustedDecimalPart += $integerPart * $adjustedDecimalDivisor;
                             $value = "{$sign}{$adjustedDecimalPart}/{$adjustedDecimalDivisor}";
                         }
                     } else {
                         // Handle the number itself
                         $number_regex = "/(\\d+)(\\.?)(\\d*)/";
                         if (preg_match($number_regex, $format, $matches)) {
                             $left = $matches[1];
                             $dec = $matches[2];
                             $right = $matches[3];
                             if ($useThousands) {
                                 $localeconv = localeconv();
                                 if ($localeconv['thousands_sep'] == '' || $localeconv['decimal_point'] == '') {
                                     $value = number_format($value, strlen($right), $localeconv['mon_decimal_point'], $localeconv['mon_thousands_sep']);
                                 } else {
                                     $value = number_format($value, strlen($right), $localeconv['decimal_point'], $localeconv['thousands_sep']);
                                 }
                             } else {
                                 $sprintf_pattern = "%1." . strlen($right) . "f";
                                 $value = sprintf($sprintf_pattern, $value);
                             }
                             $value = preg_replace($number_regex, $value, $format);
                         }
                     }
                     return $value;
                 }
             }
             return $value;
         }
     }
 }