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