Differences Between: [Versions 400 and 401] [Versions 400 and 402] [Versions 400 and 403]
1 <?php 2 3 namespace PhpOffice\PhpSpreadsheet\Style\NumberFormat; 4 5 use PhpOffice\PhpSpreadsheet\Style\Color; 6 use PhpOffice\PhpSpreadsheet\Style\NumberFormat; 7 8 class Formatter 9 { 10 private static function splitFormatCompare($value, $cond, $val, $dfcond, $dfval) 11 { 12 if (!$cond) { 13 $cond = $dfcond; 14 $val = $dfval; 15 } 16 switch ($cond) { 17 case '>': 18 return $value > $val; 19 20 case '<': 21 return $value < $val; 22 23 case '<=': 24 return $value <= $val; 25 26 case '<>': 27 return $value != $val; 28 29 case '=': 30 return $value == $val; 31 } 32 33 return $value >= $val; 34 } 35 36 private static function splitFormat($sections, $value) 37 { 38 // Extract the relevant section depending on whether number is positive, negative, or zero? 39 // Text not supported yet. 40 // Here is how the sections apply to various values in Excel: 41 // 1 section: [POSITIVE/NEGATIVE/ZERO/TEXT] 42 // 2 sections: [POSITIVE/ZERO/TEXT] [NEGATIVE] 43 // 3 sections: [POSITIVE/TEXT] [NEGATIVE] [ZERO] 44 // 4 sections: [POSITIVE] [NEGATIVE] [ZERO] [TEXT] 45 $cnt = count($sections); 46 $color_regex = '/\\[(' . implode('|', Color::NAMED_COLORS) . ')\\]/mui'; 47 $cond_regex = '/\\[(>|>=|<|<=|=|<>)([+-]?\\d+([.]\\d+)?)\\]/'; 48 $colors = ['', '', '', '', '']; 49 $condops = ['', '', '', '', '']; 50 $condvals = [0, 0, 0, 0, 0]; 51 for ($idx = 0; $idx < $cnt; ++$idx) { 52 if (preg_match($color_regex, $sections[$idx], $matches)) { 53 $colors[$idx] = $matches[0]; 54 $sections[$idx] = preg_replace($color_regex, '', $sections[$idx]); 55 } 56 if (preg_match($cond_regex, $sections[$idx], $matches)) { 57 $condops[$idx] = $matches[1]; 58 $condvals[$idx] = $matches[2]; 59 $sections[$idx] = preg_replace($cond_regex, '', $sections[$idx]); 60 } 61 } 62 $color = $colors[0]; 63 $format = $sections[0]; 64 $absval = $value; 65 switch ($cnt) { 66 case 2: 67 $absval = abs($value); 68 if (!self::splitFormatCompare($value, $condops[0], $condvals[0], '>=', 0)) { 69 $color = $colors[1]; 70 $format = $sections[1]; 71 } 72 73 break; 74 case 3: 75 case 4: 76 $absval = abs($value); 77 if (!self::splitFormatCompare($value, $condops[0], $condvals[0], '>', 0)) { 78 if (self::splitFormatCompare($value, $condops[1], $condvals[1], '<', 0)) { 79 $color = $colors[1]; 80 $format = $sections[1]; 81 } else { 82 $color = $colors[2]; 83 $format = $sections[2]; 84 } 85 } 86 87 break; 88 } 89 90 return [$color, $format, $absval]; 91 } 92 93 /** 94 * Convert a value in a pre-defined format to a PHP string. 95 * 96 * @param mixed $value Value to format 97 * @param string $format Format code, see = NumberFormat::FORMAT_* 98 * @param array $callBack Callback function for additional formatting of string 99 * 100 * @return string Formatted string 101 */ 102 public static function toFormattedString($value, $format, $callBack = null) 103 { 104 // For now we do not treat strings although section 4 of a format code affects strings 105 if (!is_numeric($value)) { 106 return $value; 107 } 108 109 // For 'General' format code, we just pass the value although this is not entirely the way Excel does it, 110 // it seems to round numbers to a total of 10 digits. 111 if (($format === NumberFormat::FORMAT_GENERAL) || ($format === NumberFormat::FORMAT_TEXT)) { 112 return $value; 113 } 114 115 $format = preg_replace_callback( 116 '/(["])(?:(?=(\\\\?))\\2.)*?\\1/u', 117 function ($matches) { 118 return str_replace('.', chr(0x00), $matches[0]); 119 }, 120 $format 121 ); 122 123 // Convert any other escaped characters to quoted strings, e.g. (\T to "T") 124 $format = preg_replace('/(\\\(((.)(?!((AM\/PM)|(A\/P))))|([^ ])))(?=(?:[^"]|"[^"]*")*$)/ui', '"$2}"', $format); 125 126 // Get the sections, there can be up to four sections, separated with a semi-colon (but only if not a quoted literal) 127 $sections = preg_split('/(;)(?=(?:[^"]|"[^"]*")*$)/u', $format); 128 129 [$colors, $format, $value] = self::splitFormat($sections, $value); 130 131 // In Excel formats, "_" is used to add spacing, 132 // The following character indicates the size of the spacing, which we can't do in HTML, so we just use a standard space 133 $format = preg_replace('/_.?/ui', ' ', $format); 134 135 // Let's begin inspecting the format and converting the value to a formatted string 136 137 // Check for date/time characters (not inside quotes) 138 if (preg_match('/(\[\$[A-Z]*-[0-9A-F]*\])*[hmsdy](?=(?:[^"]|"[^"]*")*$)/miu', $format, $matches)) { 139 // datetime format 140 $value = DateFormatter::format($value, $format); 141 } else { 142 if (substr($format, 0, 1) === '"' && substr($format, -1, 1) === '"' && substr_count($format, '"') === 2) { 143 $value = substr($format, 1, -1); 144 } elseif (preg_match('/[0#, ]%/', $format)) { 145 // % number format 146 $value = PercentageFormatter::format($value, $format); 147 } else { 148 $value = NumberFormatter::format($value, $format); 149 } 150 } 151 152 // Additional formatting provided by callback function 153 if ($callBack !== null) { 154 [$writerInstance, $function] = $callBack; 155 $value = $writerInstance->$function($value, $colors); 156 } 157 158 $value = str_replace(chr(0x00), '.', $value); 159 160 return $value; 161 } 162 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body