1<?php 2 3namespace PhpOffice\PhpSpreadsheet\Style; 4 5class NumberFormat extends Supervisor 6{ 7 // Pre-defined formats 8 const FORMAT_GENERAL = 'General'; 9 10 const FORMAT_TEXT = '@'; 11 12 const FORMAT_NUMBER = '0'; 13 const FORMAT_NUMBER_00 = '0.00'; 14 const FORMAT_NUMBER_COMMA_SEPARATED1 = '#,##0.00'; 15 const FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-'; 16 17 const FORMAT_PERCENTAGE = '0%'; 18 const FORMAT_PERCENTAGE_00 = '0.00%'; 19 20 const FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd'; 21 const FORMAT_DATE_YYYYMMDD = 'yyyy-mm-dd'; 22 const FORMAT_DATE_DDMMYYYY = 'dd/mm/yyyy'; 23 const FORMAT_DATE_DMYSLASH = 'd/m/yy'; 24 const FORMAT_DATE_DMYMINUS = 'd-m-yy'; 25 const FORMAT_DATE_DMMINUS = 'd-m'; 26 const FORMAT_DATE_MYMINUS = 'm-yy'; 27 const FORMAT_DATE_XLSX14 = 'mm-dd-yy'; 28 const FORMAT_DATE_XLSX15 = 'd-mmm-yy'; 29 const FORMAT_DATE_XLSX16 = 'd-mmm'; 30 const FORMAT_DATE_XLSX17 = 'mmm-yy'; 31 const FORMAT_DATE_XLSX22 = 'm/d/yy h:mm'; 32 const FORMAT_DATE_DATETIME = 'd/m/yy h:mm'; 33 const FORMAT_DATE_TIME1 = 'h:mm AM/PM'; 34 const FORMAT_DATE_TIME2 = 'h:mm:ss AM/PM'; 35 const FORMAT_DATE_TIME3 = 'h:mm'; 36 const FORMAT_DATE_TIME4 = 'h:mm:ss'; 37 const FORMAT_DATE_TIME5 = 'mm:ss'; 38 const FORMAT_DATE_TIME6 = 'h:mm:ss'; 39 const FORMAT_DATE_TIME7 = 'i:s.S'; 40 const FORMAT_DATE_TIME8 = 'h:mm:ss;@'; 41 const FORMAT_DATE_YYYYMMDDSLASH = 'yyyy/mm/dd;@'; 42 43 const FORMAT_CURRENCY_USD_SIMPLE = '"$"#,##0.00_-'; 44 const FORMAT_CURRENCY_USD = '$#,##0_-'; 45 const FORMAT_CURRENCY_EUR_SIMPLE = '#,##0.00_-"€"'; 46 const FORMAT_CURRENCY_EUR = '#,##0_-"€"'; 47 const FORMAT_ACCOUNTING_USD = '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)'; 48 const FORMAT_ACCOUNTING_EUR = '_("€"* #,##0.00_);_("€"* \(#,##0.00\);_("€"* "-"??_);_(@_)'; 49 50 /** 51 * Excel built-in number formats. 52 * 53 * @var array 54 */ 55 protected static $builtInFormats; 56 57 /** 58 * Excel built-in number formats (flipped, for faster lookups). 59 * 60 * @var array 61 */ 62 protected static $flippedBuiltInFormats; 63 64 /** 65 * Format Code. 66 * 67 * @var null|string 68 */ 69 protected $formatCode = self::FORMAT_GENERAL; 70 71 /** 72 * Built-in format Code. 73 * 74 * @var false|int 75 */ 76 protected $builtInFormatCode = 0; 77 78 /** 79 * Create a new NumberFormat. 80 * 81 * @param bool $isSupervisor Flag indicating if this is a supervisor or not 82 * Leave this value at default unless you understand exactly what 83 * its ramifications are 84 * @param bool $isConditional Flag indicating if this is a conditional style or not 85 * Leave this value at default unless you understand exactly what 86 * its ramifications are 87 */ 88 public function __construct($isSupervisor = false, $isConditional = false) 89 { 90 // Supervisor? 91 parent::__construct($isSupervisor); 92 93 if ($isConditional) { 94 $this->formatCode = null; 95 $this->builtInFormatCode = false; 96 } 97 } 98 99 /** 100 * Get the shared style component for the currently active cell in currently active sheet. 101 * Only used for style supervisor. 102 * 103 * @return NumberFormat 104 */ 105 public function getSharedComponent() 106 { 107 return $this->parent->getSharedComponent()->getNumberFormat(); 108 } 109 110 /** 111 * Build style array from subcomponents. 112 * 113 * @param array $array 114 * 115 * @return array 116 */ 117 public function getStyleArray($array) 118 { 119 return ['numberFormat' => $array]; 120 } 121 122 /** 123 * Apply styles from array. 124 * 125 * <code> 126 * $spreadsheet->getActiveSheet()->getStyle('B2')->getNumberFormat()->applyFromArray( 127 * [ 128 * 'formatCode' => NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE 129 * ] 130 * ); 131 * </code> 132 * 133 * @param array $pStyles Array containing style information 134 * 135 * @return $this 136 */ 137 public function applyFromArray(array $pStyles) 138 { 139 if ($this->isSupervisor) { 140 $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($this->getStyleArray($pStyles)); 141 } else { 142 if (isset($pStyles['formatCode'])) { 143 $this->setFormatCode($pStyles['formatCode']); 144 } 145 } 146 147 return $this; 148 } 149 150 /** 151 * Get Format Code. 152 * 153 * @return null|string 154 */ 155 public function getFormatCode() 156 { 157 if ($this->isSupervisor) { 158 return $this->getSharedComponent()->getFormatCode(); 159 } 160 if ($this->builtInFormatCode !== false) { 161 return self::builtInFormatCode($this->builtInFormatCode); 162 } 163 164 return $this->formatCode; 165 } 166 167 /** 168 * Set Format Code. 169 * 170 * @param string $pValue see self::FORMAT_* 171 * 172 * @return $this 173 */ 174 public function setFormatCode($pValue) 175 { 176 if ($pValue == '') { 177 $pValue = self::FORMAT_GENERAL; 178 } 179 if ($this->isSupervisor) { 180 $styleArray = $this->getStyleArray(['formatCode' => $pValue]); 181 $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray); 182 } else { 183 $this->formatCode = $pValue; 184 $this->builtInFormatCode = self::builtInFormatCodeIndex($pValue); 185 } 186 187 return $this; 188 } 189 190 /** 191 * Get Built-In Format Code. 192 * 193 * @return false|int 194 */ 195 public function getBuiltInFormatCode() 196 { 197 if ($this->isSupervisor) { 198 return $this->getSharedComponent()->getBuiltInFormatCode(); 199 } 200 201 return $this->builtInFormatCode; 202 } 203 204 /** 205 * Set Built-In Format Code. 206 * 207 * @param int $pValue 208 * 209 * @return $this 210 */ 211 public function setBuiltInFormatCode($pValue) 212 { 213 if ($this->isSupervisor) { 214 $styleArray = $this->getStyleArray(['formatCode' => self::builtInFormatCode($pValue)]); 215 $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray); 216 } else { 217 $this->builtInFormatCode = $pValue; 218 $this->formatCode = self::builtInFormatCode($pValue); 219 } 220 221 return $this; 222 } 223 224 /** 225 * Fill built-in format codes. 226 */ 227 private static function fillBuiltInFormatCodes(): void 228 { 229 // [MS-OI29500: Microsoft Office Implementation Information for ISO/IEC-29500 Standard Compliance] 230 // 18.8.30. numFmt (Number Format) 231 // 232 // The ECMA standard defines built-in format IDs 233 // 14: "mm-dd-yy" 234 // 22: "m/d/yy h:mm" 235 // 37: "#,##0 ;(#,##0)" 236 // 38: "#,##0 ;[Red](#,##0)" 237 // 39: "#,##0.00;(#,##0.00)" 238 // 40: "#,##0.00;[Red](#,##0.00)" 239 // 47: "mmss.0" 240 // KOR fmt 55: "yyyy-mm-dd" 241 // Excel defines built-in format IDs 242 // 14: "m/d/yyyy" 243 // 22: "m/d/yyyy h:mm" 244 // 37: "#,##0_);(#,##0)" 245 // 38: "#,##0_);[Red](#,##0)" 246 // 39: "#,##0.00_);(#,##0.00)" 247 // 40: "#,##0.00_);[Red](#,##0.00)" 248 // 47: "mm:ss.0" 249 // KOR fmt 55: "yyyy/mm/dd" 250 251 // Built-in format codes 252 if (self::$builtInFormats === null) { 253 self::$builtInFormats = []; 254 255 // General 256 self::$builtInFormats[0] = self::FORMAT_GENERAL; 257 self::$builtInFormats[1] = '0'; 258 self::$builtInFormats[2] = '0.00'; 259 self::$builtInFormats[3] = '#,##0'; 260 self::$builtInFormats[4] = '#,##0.00'; 261 262 self::$builtInFormats[9] = '0%'; 263 self::$builtInFormats[10] = '0.00%'; 264 self::$builtInFormats[11] = '0.00E+00'; 265 self::$builtInFormats[12] = '# ?/?'; 266 self::$builtInFormats[13] = '# ??/??'; 267 self::$builtInFormats[14] = 'm/d/yyyy'; // Despite ECMA 'mm-dd-yy'; 268 self::$builtInFormats[15] = 'd-mmm-yy'; 269 self::$builtInFormats[16] = 'd-mmm'; 270 self::$builtInFormats[17] = 'mmm-yy'; 271 self::$builtInFormats[18] = 'h:mm AM/PM'; 272 self::$builtInFormats[19] = 'h:mm:ss AM/PM'; 273 self::$builtInFormats[20] = 'h:mm'; 274 self::$builtInFormats[21] = 'h:mm:ss'; 275 self::$builtInFormats[22] = 'm/d/yyyy h:mm'; // Despite ECMA 'm/d/yy h:mm'; 276 277 self::$builtInFormats[37] = '#,##0_);(#,##0)'; // Despite ECMA '#,##0 ;(#,##0)'; 278 self::$builtInFormats[38] = '#,##0_);[Red](#,##0)'; // Despite ECMA '#,##0 ;[Red](#,##0)'; 279 self::$builtInFormats[39] = '#,##0.00_);(#,##0.00)'; // Despite ECMA '#,##0.00;(#,##0.00)'; 280 self::$builtInFormats[40] = '#,##0.00_);[Red](#,##0.00)'; // Despite ECMA '#,##0.00;[Red](#,##0.00)'; 281 282 self::$builtInFormats[44] = '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)'; 283 self::$builtInFormats[45] = 'mm:ss'; 284 self::$builtInFormats[46] = '[h]:mm:ss'; 285 self::$builtInFormats[47] = 'mm:ss.0'; // Despite ECMA 'mmss.0'; 286 self::$builtInFormats[48] = '##0.0E+0'; 287 self::$builtInFormats[49] = '@'; 288 289 // CHT 290 self::$builtInFormats[27] = '[$-404]e/m/d'; 291 self::$builtInFormats[30] = 'm/d/yy'; 292 self::$builtInFormats[36] = '[$-404]e/m/d'; 293 self::$builtInFormats[50] = '[$-404]e/m/d'; 294 self::$builtInFormats[57] = '[$-404]e/m/d'; 295 296 // THA 297 self::$builtInFormats[59] = 't0'; 298 self::$builtInFormats[60] = 't0.00'; 299 self::$builtInFormats[61] = 't#,##0'; 300 self::$builtInFormats[62] = 't#,##0.00'; 301 self::$builtInFormats[67] = 't0%'; 302 self::$builtInFormats[68] = 't0.00%'; 303 self::$builtInFormats[69] = 't# ?/?'; 304 self::$builtInFormats[70] = 't# ??/??'; 305 306 // JPN 307 self::$builtInFormats[28] = '[$-411]ggge"年"m"月"d"日"'; 308 self::$builtInFormats[29] = '[$-411]ggge"年"m"月"d"日"'; 309 self::$builtInFormats[31] = 'yyyy"年"m"月"d"日"'; 310 self::$builtInFormats[32] = 'h"時"mm"分"'; 311 self::$builtInFormats[33] = 'h"時"mm"分"ss"秒"'; 312 self::$builtInFormats[34] = 'yyyy"年"m"月"'; 313 self::$builtInFormats[35] = 'm"月"d"日"'; 314 self::$builtInFormats[51] = '[$-411]ggge"年"m"月"d"日"'; 315 self::$builtInFormats[52] = 'yyyy"年"m"月"'; 316 self::$builtInFormats[53] = 'm"月"d"日"'; 317 self::$builtInFormats[54] = '[$-411]ggge"年"m"月"d"日"'; 318 self::$builtInFormats[55] = 'yyyy"年"m"月"'; 319 self::$builtInFormats[56] = 'm"月"d"日"'; 320 self::$builtInFormats[58] = '[$-411]ggge"年"m"月"d"日"'; 321 322 // Flip array (for faster lookups) 323 self::$flippedBuiltInFormats = array_flip(self::$builtInFormats); 324 } 325 } 326 327 /** 328 * Get built-in format code. 329 * 330 * @param int $pIndex 331 * 332 * @return string 333 */ 334 public static function builtInFormatCode($pIndex) 335 { 336 // Clean parameter 337 $pIndex = (int) $pIndex; 338 339 // Ensure built-in format codes are available 340 self::fillBuiltInFormatCodes(); 341 342 // Lookup format code 343 if (isset(self::$builtInFormats[$pIndex])) { 344 return self::$builtInFormats[$pIndex]; 345 } 346 347 return ''; 348 } 349 350 /** 351 * Get built-in format code index. 352 * 353 * @param string $formatCode 354 * 355 * @return bool|int 356 */ 357 public static function builtInFormatCodeIndex($formatCode) 358 { 359 // Ensure built-in format codes are available 360 self::fillBuiltInFormatCodes(); 361 362 // Lookup format code 363 if (array_key_exists($formatCode, self::$flippedBuiltInFormats)) { 364 return self::$flippedBuiltInFormats[$formatCode]; 365 } 366 367 return false; 368 } 369 370 /** 371 * Get hash code. 372 * 373 * @return string Hash code 374 */ 375 public function getHashCode() 376 { 377 if ($this->isSupervisor) { 378 return $this->getSharedComponent()->getHashCode(); 379 } 380 381 return md5( 382 $this->formatCode . 383 $this->builtInFormatCode . 384 __CLASS__ 385 ); 386 } 387 388 /** 389 * Convert a value in a pre-defined format to a PHP string. 390 * 391 * @param mixed $value Value to format 392 * @param string $format Format code, see = self::FORMAT_* 393 * @param array $callBack Callback function for additional formatting of string 394 * 395 * @return string Formatted string 396 */ 397 public static function toFormattedString($value, $format, $callBack = null) 398 { 399 return NumberFormat\Formatter::toFormattedString($value, $format, $callBack); 400 } 401 402 protected function exportArray1(): array 403 { 404 $exportedArray = []; 405 $this->exportArray2($exportedArray, 'formatCode', $this->getFormatCode()); 406 407 return $exportedArray; 408 } 409} 410