Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]
1 <?php 2 3 namespace PhpOffice\PhpSpreadsheet\Style; 4 5 use PhpOffice\PhpSpreadsheet\Calculation\MathTrig; 6 use PhpOffice\PhpSpreadsheet\Shared\Date; 7 use PhpOffice\PhpSpreadsheet\Shared\StringHelper; 8 9 class NumberFormat extends Supervisor 10 { 11 // Pre-defined formats 12 const FORMAT_GENERAL = 'General'; 13 14 const FORMAT_TEXT = '@'; 15 16 const FORMAT_NUMBER = '0'; 17 const FORMAT_NUMBER_00 = '0.00'; 18 const FORMAT_NUMBER_COMMA_SEPARATED1 = '#,##0.00'; 19 const FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-'; 20 21 const FORMAT_PERCENTAGE = '0%'; 22 const FORMAT_PERCENTAGE_00 = '0.00%'; 23 24 const FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd'; 25 const FORMAT_DATE_YYYYMMDD = 'yyyy-mm-dd'; 26 const FORMAT_DATE_DDMMYYYY = 'dd/mm/yyyy'; 27 const FORMAT_DATE_DMYSLASH = 'd/m/yy'; 28 const FORMAT_DATE_DMYMINUS = 'd-m-yy'; 29 const FORMAT_DATE_DMMINUS = 'd-m'; 30 const FORMAT_DATE_MYMINUS = 'm-yy'; 31 const FORMAT_DATE_XLSX14 = 'mm-dd-yy'; 32 const FORMAT_DATE_XLSX15 = 'd-mmm-yy'; 33 const FORMAT_DATE_XLSX16 = 'd-mmm'; 34 const FORMAT_DATE_XLSX17 = 'mmm-yy'; 35 const FORMAT_DATE_XLSX22 = 'm/d/yy h:mm'; 36 const FORMAT_DATE_DATETIME = 'd/m/yy h:mm'; 37 const FORMAT_DATE_TIME1 = 'h:mm AM/PM'; 38 const FORMAT_DATE_TIME2 = 'h:mm:ss AM/PM'; 39 const FORMAT_DATE_TIME3 = 'h:mm'; 40 const FORMAT_DATE_TIME4 = 'h:mm:ss'; 41 const FORMAT_DATE_TIME5 = 'mm:ss'; 42 const FORMAT_DATE_TIME6 = 'h:mm:ss'; 43 const FORMAT_DATE_TIME7 = 'i:s.S'; 44 const FORMAT_DATE_TIME8 = 'h:mm:ss;@'; 45 const FORMAT_DATE_YYYYMMDDSLASH = 'yyyy/mm/dd;@'; 46 47 const FORMAT_CURRENCY_USD_SIMPLE = '"$"#,##0.00_-'; 48 const FORMAT_CURRENCY_USD = '$#,##0_-'; 49 const FORMAT_CURRENCY_EUR_SIMPLE = '#,##0.00_-"€"'; 50 const FORMAT_CURRENCY_EUR = '#,##0_-"€"'; 51 const FORMAT_ACCOUNTING_USD = '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)'; 52 const FORMAT_ACCOUNTING_EUR = '_("€"* #,##0.00_);_("€"* \(#,##0.00\);_("€"* "-"??_);_(@_)'; 53 54 /** 55 * Excel built-in number formats. 56 * 57 * @var array 58 */ 59 protected static $builtInFormats; 60 61 /** 62 * Excel built-in number formats (flipped, for faster lookups). 63 * 64 * @var array 65 */ 66 protected static $flippedBuiltInFormats; 67 68 /** 69 * Format Code. 70 * 71 * @var string 72 */ 73 protected $formatCode = self::FORMAT_GENERAL; 74 75 /** 76 * Built-in format Code. 77 * 78 * @var string 79 */ 80 protected $builtInFormatCode = 0; 81 82 /** 83 * Create a new NumberFormat. 84 * 85 * @param bool $isSupervisor Flag indicating if this is a supervisor or not 86 * Leave this value at default unless you understand exactly what 87 * its ramifications are 88 * @param bool $isConditional Flag indicating if this is a conditional style or not 89 * Leave this value at default unless you understand exactly what 90 * its ramifications are 91 */ 92 public function __construct($isSupervisor = false, $isConditional = false) 93 { 94 // Supervisor? 95 parent::__construct($isSupervisor); 96 97 if ($isConditional) { 98 $this->formatCode = null; 99 $this->builtInFormatCode = false; 100 } 101 } 102 103 /** 104 * Get the shared style component for the currently active cell in currently active sheet. 105 * Only used for style supervisor. 106 * 107 * @return NumberFormat 108 */ 109 public function getSharedComponent() 110 { 111 return $this->parent->getSharedComponent()->getNumberFormat(); 112 } 113 114 /** 115 * Build style array from subcomponents. 116 * 117 * @param array $array 118 * 119 * @return array 120 */ 121 public function getStyleArray($array) 122 { 123 return ['numberFormat' => $array]; 124 } 125 126 /** 127 * Apply styles from array. 128 * 129 * <code> 130 * $spreadsheet->getActiveSheet()->getStyle('B2')->getNumberFormat()->applyFromArray( 131 * [ 132 * 'formatCode' => NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE 133 * ] 134 * ); 135 * </code> 136 * 137 * @param array $pStyles Array containing style information 138 * 139 * @return $this 140 */ 141 public function applyFromArray(array $pStyles) 142 { 143 if ($this->isSupervisor) { 144 $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($this->getStyleArray($pStyles)); 145 } else { 146 if (isset($pStyles['formatCode'])) { 147 $this->setFormatCode($pStyles['formatCode']); 148 } 149 } 150 151 return $this; 152 } 153 154 /** 155 * Get Format Code. 156 * 157 * @return string 158 */ 159 public function getFormatCode() 160 { 161 if ($this->isSupervisor) { 162 return $this->getSharedComponent()->getFormatCode(); 163 } 164 if ($this->builtInFormatCode !== false) { 165 return self::builtInFormatCode($this->builtInFormatCode); 166 } 167 168 return $this->formatCode; 169 } 170 171 /** 172 * Set Format Code. 173 * 174 * @param string $pValue see self::FORMAT_* 175 * 176 * @return $this 177 */ 178 public function setFormatCode($pValue) 179 { 180 if ($pValue == '') { 181 $pValue = self::FORMAT_GENERAL; 182 } 183 if ($this->isSupervisor) { 184 $styleArray = $this->getStyleArray(['formatCode' => $pValue]); 185 $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray); 186 } else { 187 $this->formatCode = $pValue; 188 $this->builtInFormatCode = self::builtInFormatCodeIndex($pValue); 189 } 190 191 return $this; 192 } 193 194 /** 195 * Get Built-In Format Code. 196 * 197 * @return int 198 */ 199 public function getBuiltInFormatCode() 200 { 201 if ($this->isSupervisor) { 202 return $this->getSharedComponent()->getBuiltInFormatCode(); 203 } 204 205 return $this->builtInFormatCode; 206 } 207 208 /** 209 * Set Built-In Format Code. 210 * 211 * @param int $pValue 212 * 213 * @return $this 214 */ 215 public function setBuiltInFormatCode($pValue) 216 { 217 if ($this->isSupervisor) { 218 $styleArray = $this->getStyleArray(['formatCode' => self::builtInFormatCode($pValue)]); 219 $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray); 220 } else { 221 $this->builtInFormatCode = $pValue; 222 $this->formatCode = self::builtInFormatCode($pValue); 223 } 224 225 return $this; 226 } 227 228 /** 229 * Fill built-in format codes. 230 */ 231 private static function fillBuiltInFormatCodes(): void 232 { 233 // [MS-OI29500: Microsoft Office Implementation Information for ISO/IEC-29500 Standard Compliance] 234 // 18.8.30. numFmt (Number Format) 235 // 236 // The ECMA standard defines built-in format IDs 237 // 14: "mm-dd-yy" 238 // 22: "m/d/yy h:mm" 239 // 37: "#,##0 ;(#,##0)" 240 // 38: "#,##0 ;[Red](#,##0)" 241 // 39: "#,##0.00;(#,##0.00)" 242 // 40: "#,##0.00;[Red](#,##0.00)" 243 // 47: "mmss.0" 244 // KOR fmt 55: "yyyy-mm-dd" 245 // Excel defines built-in format IDs 246 // 14: "m/d/yyyy" 247 // 22: "m/d/yyyy h:mm" 248 // 37: "#,##0_);(#,##0)" 249 // 38: "#,##0_);[Red](#,##0)" 250 // 39: "#,##0.00_);(#,##0.00)" 251 // 40: "#,##0.00_);[Red](#,##0.00)" 252 // 47: "mm:ss.0" 253 // KOR fmt 55: "yyyy/mm/dd" 254 255 // Built-in format codes 256 if (self::$builtInFormats === null) { 257 self::$builtInFormats = []; 258 259 // General 260 self::$builtInFormats[0] = self::FORMAT_GENERAL; 261 self::$builtInFormats[1] = '0'; 262 self::$builtInFormats[2] = '0.00'; 263 self::$builtInFormats[3] = '#,##0'; 264 self::$builtInFormats[4] = '#,##0.00'; 265 266 self::$builtInFormats[9] = '0%'; 267 self::$builtInFormats[10] = '0.00%'; 268 self::$builtInFormats[11] = '0.00E+00'; 269 self::$builtInFormats[12] = '# ?/?'; 270 self::$builtInFormats[13] = '# ??/??'; 271 self::$builtInFormats[14] = 'm/d/yyyy'; // Despite ECMA 'mm-dd-yy'; 272 self::$builtInFormats[15] = 'd-mmm-yy'; 273 self::$builtInFormats[16] = 'd-mmm'; 274 self::$builtInFormats[17] = 'mmm-yy'; 275 self::$builtInFormats[18] = 'h:mm AM/PM'; 276 self::$builtInFormats[19] = 'h:mm:ss AM/PM'; 277 self::$builtInFormats[20] = 'h:mm'; 278 self::$builtInFormats[21] = 'h:mm:ss'; 279 self::$builtInFormats[22] = 'm/d/yyyy h:mm'; // Despite ECMA 'm/d/yy h:mm'; 280 281 self::$builtInFormats[37] = '#,##0_);(#,##0)'; // Despite ECMA '#,##0 ;(#,##0)'; 282 self::$builtInFormats[38] = '#,##0_);[Red](#,##0)'; // Despite ECMA '#,##0 ;[Red](#,##0)'; 283 self::$builtInFormats[39] = '#,##0.00_);(#,##0.00)'; // Despite ECMA '#,##0.00;(#,##0.00)'; 284 self::$builtInFormats[40] = '#,##0.00_);[Red](#,##0.00)'; // Despite ECMA '#,##0.00;[Red](#,##0.00)'; 285 286 self::$builtInFormats[44] = '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)'; 287 self::$builtInFormats[45] = 'mm:ss'; 288 self::$builtInFormats[46] = '[h]:mm:ss'; 289 self::$builtInFormats[47] = 'mm:ss.0'; // Despite ECMA 'mmss.0'; 290 self::$builtInFormats[48] = '##0.0E+0'; 291 self::$builtInFormats[49] = '@'; 292 293 // CHT 294 self::$builtInFormats[27] = '[$-404]e/m/d'; 295 self::$builtInFormats[30] = 'm/d/yy'; 296 self::$builtInFormats[36] = '[$-404]e/m/d'; 297 self::$builtInFormats[50] = '[$-404]e/m/d'; 298 self::$builtInFormats[57] = '[$-404]e/m/d'; 299 300 // THA 301 self::$builtInFormats[59] = 't0'; 302 self::$builtInFormats[60] = 't0.00'; 303 self::$builtInFormats[61] = 't#,##0'; 304 self::$builtInFormats[62] = 't#,##0.00'; 305 self::$builtInFormats[67] = 't0%'; 306 self::$builtInFormats[68] = 't0.00%'; 307 self::$builtInFormats[69] = 't# ?/?'; 308 self::$builtInFormats[70] = 't# ??/??'; 309 310 // JPN 311 self::$builtInFormats[28] = '[$-411]ggge"年"m"月"d"日"'; 312 self::$builtInFormats[29] = '[$-411]ggge"年"m"月"d"日"'; 313 self::$builtInFormats[31] = 'yyyy"年"m"月"d"日"'; 314 self::$builtInFormats[32] = 'h"時"mm"分"'; 315 self::$builtInFormats[33] = 'h"時"mm"分"ss"秒"'; 316 self::$builtInFormats[34] = 'yyyy"年"m"月"'; 317 self::$builtInFormats[35] = 'm"月"d"日"'; 318 self::$builtInFormats[51] = '[$-411]ggge"年"m"月"d"日"'; 319 self::$builtInFormats[52] = 'yyyy"年"m"月"'; 320 self::$builtInFormats[53] = 'm"月"d"日"'; 321 self::$builtInFormats[54] = '[$-411]ggge"年"m"月"d"日"'; 322 self::$builtInFormats[55] = 'yyyy"年"m"月"'; 323 self::$builtInFormats[56] = 'm"月"d"日"'; 324 self::$builtInFormats[58] = '[$-411]ggge"年"m"月"d"日"'; 325 326 // Flip array (for faster lookups) 327 self::$flippedBuiltInFormats = array_flip(self::$builtInFormats); 328 } 329 } 330 331 /** 332 * Get built-in format code. 333 * 334 * @param int $pIndex 335 * 336 * @return string 337 */ 338 public static function builtInFormatCode($pIndex) 339 { 340 // Clean parameter 341 $pIndex = (int) $pIndex; 342 343 // Ensure built-in format codes are available 344 self::fillBuiltInFormatCodes(); 345 346 // Lookup format code 347 if (isset(self::$builtInFormats[$pIndex])) { 348 return self::$builtInFormats[$pIndex]; 349 } 350 351 return ''; 352 } 353 354 /** 355 * Get built-in format code index. 356 * 357 * @param string $formatCode 358 * 359 * @return bool|int 360 */ 361 public static function builtInFormatCodeIndex($formatCode) 362 { 363 // Ensure built-in format codes are available 364 self::fillBuiltInFormatCodes(); 365 366 // Lookup format code 367 if (array_key_exists($formatCode, self::$flippedBuiltInFormats)) { 368 return self::$flippedBuiltInFormats[$formatCode]; 369 } 370 371 return false; 372 } 373 374 /** 375 * Get hash code. 376 * 377 * @return string Hash code 378 */ 379 public function getHashCode() 380 { 381 if ($this->isSupervisor) { 382 return $this->getSharedComponent()->getHashCode(); 383 } 384 385 return md5( 386 $this->formatCode . 387 $this->builtInFormatCode . 388 __CLASS__ 389 ); 390 } 391 392 /** 393 * Search/replace values to convert Excel date/time format masks to PHP format masks. 394 * 395 * @var array 396 */ 397 private static $dateFormatReplacements = [ 398 // first remove escapes related to non-format characters 399 '\\' => '', 400 // 12-hour suffix 401 'am/pm' => 'A', 402 // 4-digit year 403 'e' => 'Y', 404 'yyyy' => 'Y', 405 // 2-digit year 406 'yy' => 'y', 407 // first letter of month - no php equivalent 408 'mmmmm' => 'M', 409 // full month name 410 'mmmm' => 'F', 411 // short month name 412 'mmm' => 'M', 413 // mm is minutes if time, but can also be month w/leading zero 414 // so we try to identify times be the inclusion of a : separator in the mask 415 // It isn't perfect, but the best way I know how 416 ':mm' => ':i', 417 'mm:' => 'i:', 418 // month leading zero 419 'mm' => 'm', 420 // month no leading zero 421 'm' => 'n', 422 // full day of week name 423 'dddd' => 'l', 424 // short day of week name 425 'ddd' => 'D', 426 // days leading zero 427 'dd' => 'd', 428 // days no leading zero 429 'd' => 'j', 430 // seconds 431 'ss' => 's', 432 // fractional seconds - no php equivalent 433 '.s' => '', 434 ]; 435 436 /** 437 * Search/replace values to convert Excel date/time format masks hours to PHP format masks (24 hr clock). 438 * 439 * @var array 440 */ 441 private static $dateFormatReplacements24 = [ 442 'hh' => 'H', 443 'h' => 'G', 444 ]; 445 446 /** 447 * Search/replace values to convert Excel date/time format masks hours to PHP format masks (12 hr clock). 448 * 449 * @var array 450 */ 451 private static $dateFormatReplacements12 = [ 452 'hh' => 'h', 453 'h' => 'g', 454 ]; 455 456 private static function setLowercaseCallback($matches) 457 { 458 return mb_strtolower($matches[0]); 459 } 460 461 private static function escapeQuotesCallback($matches) 462 { 463 return '\\' . implode('\\', str_split($matches[1])); 464 } 465 466 private static function formatAsDate(&$value, &$format): void 467 { 468 // strip off first part containing e.g. [$-F800] or [$USD-409] 469 // general syntax: [$<Currency string>-<language info>] 470 // language info is in hexadecimal 471 // strip off chinese part like [DBNum1][$-804] 472 $format = preg_replace('/^(\[[0-9A-Za-z]*\])*(\[\$[A-Z]*-[0-9A-F]*\])/i', '', $format); 473 474 // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case; 475 // but we don't want to change any quoted strings 476 $format = preg_replace_callback('/(?:^|")([^"]*)(?:$|")/', ['self', 'setLowercaseCallback'], $format); 477 478 // Only process the non-quoted blocks for date format characters 479 $blocks = explode('"', $format); 480 foreach ($blocks as $key => &$block) { 481 if ($key % 2 == 0) { 482 $block = strtr($block, self::$dateFormatReplacements); 483 if (!strpos($block, 'A')) { 484 // 24-hour time format 485 // when [h]:mm format, the [h] should replace to the hours of the value * 24 486 if (false !== strpos($block, '[h]')) { 487 $hours = (int) ($value * 24); 488 $block = str_replace('[h]', $hours, $block); 489 490 continue; 491 } 492 $block = strtr($block, self::$dateFormatReplacements24); 493 } else { 494 // 12-hour time format 495 $block = strtr($block, self::$dateFormatReplacements12); 496 } 497 } 498 } 499 $format = implode('"', $blocks); 500 501 // escape any quoted characters so that DateTime format() will render them correctly 502 $format = preg_replace_callback('/"(.*)"/U', ['self', 'escapeQuotesCallback'], $format); 503 504 $dateObj = Date::excelToDateTimeObject($value); 505 $value = $dateObj->format($format); 506 } 507 508 private static function formatAsPercentage(&$value, &$format): void 509 { 510 if ($format === self::FORMAT_PERCENTAGE) { 511 $value = round((100 * $value), 0) . '%'; 512 } else { 513 if (preg_match('/\.[#0]+/', $format, $m)) { 514 $s = substr($m[0], 0, 1) . (strlen($m[0]) - 1); 515 $format = str_replace($m[0], $s, $format); 516 } 517 if (preg_match('/^[#0]+/', $format, $m)) { 518 $format = str_replace($m[0], strlen($m[0]), $format); 519 } 520 $format = '%' . str_replace('%', 'f%%', $format); 521 522 $value = sprintf($format, 100 * $value); 523 } 524 } 525 526 private static function formatAsFraction(&$value, &$format): void 527 { 528 $sign = ($value < 0) ? '-' : ''; 529 530 $integerPart = floor(abs($value)); 531 $decimalPart = trim(fmod(abs($value), 1), '0.'); 532 $decimalLength = strlen($decimalPart); 533 $decimalDivisor = 10 ** $decimalLength; 534 535 $GCD = MathTrig::GCD($decimalPart, $decimalDivisor); 536 537 $adjustedDecimalPart = $decimalPart / $GCD; 538 $adjustedDecimalDivisor = $decimalDivisor / $GCD; 539 540 if ((strpos($format, '0') !== false)) { 541 $value = "$sign$integerPart $adjustedDecimalPart/$adjustedDecimalDivisor"; 542 } elseif ((strpos($format, '#') !== false)) { 543 if ($integerPart == 0) { 544 $value = "$sign$adjustedDecimalPart/$adjustedDecimalDivisor"; 545 } else { 546 $value = "$sign$integerPart $adjustedDecimalPart/$adjustedDecimalDivisor"; 547 } 548 } elseif ((substr($format, 0, 3) == '? ?')) { 549 if ($integerPart == 0) { 550 $integerPart = ''; 551 } 552 $value = "$sign$integerPart $adjustedDecimalPart/$adjustedDecimalDivisor"; 553 } else { 554 $adjustedDecimalPart += $integerPart * $adjustedDecimalDivisor; 555 $value = "$sign$adjustedDecimalPart/$adjustedDecimalDivisor"; 556 } 557 } 558 559 private static function mergeComplexNumberFormatMasks($numbers, $masks) 560 { 561 $decimalCount = strlen($numbers[1]); 562 $postDecimalMasks = []; 563 564 do { 565 $tempMask = array_pop($masks); 566 $postDecimalMasks[] = $tempMask; 567 $decimalCount -= strlen($tempMask); 568 } while ($decimalCount > 0); 569 570 return [ 571 implode('.', $masks), 572 implode('.', array_reverse($postDecimalMasks)), 573 ]; 574 } 575 576 private static function processComplexNumberFormatMask($number, $mask) 577 { 578 $result = $number; 579 $maskingBlockCount = preg_match_all('/0+/', $mask, $maskingBlocks, PREG_OFFSET_CAPTURE); 580 581 if ($maskingBlockCount > 1) { 582 $maskingBlocks = array_reverse($maskingBlocks[0]); 583 584 foreach ($maskingBlocks as $block) { 585 $divisor = 1 . $block[0]; 586 $size = strlen($block[0]); 587 $offset = $block[1]; 588 589 $blockValue = sprintf( 590 '%0' . $size . 'd', 591 fmod($number, $divisor) 592 ); 593 $number = floor($number / $divisor); 594 $mask = substr_replace($mask, $blockValue, $offset, $size); 595 } 596 if ($number > 0) { 597 $mask = substr_replace($mask, $number, $offset, 0); 598 } 599 $result = $mask; 600 } 601 602 return $result; 603 } 604 605 private static function complexNumberFormatMask($number, $mask, $splitOnPoint = true) 606 { 607 $sign = ($number < 0.0); 608 $number = abs($number); 609 610 if ($splitOnPoint && strpos($mask, '.') !== false && strpos($number, '.') !== false) { 611 $numbers = explode('.', $number); 612 $masks = explode('.', $mask); 613 if (count($masks) > 2) { 614 $masks = self::mergeComplexNumberFormatMasks($numbers, $masks); 615 } 616 $result1 = self::complexNumberFormatMask($numbers[0], $masks[0], false); 617 $result2 = strrev(self::complexNumberFormatMask(strrev($numbers[1]), strrev($masks[1]), false)); 618 619 return (($sign) ? '-' : '') . $result1 . '.' . $result2; 620 } 621 622 $result = self::processComplexNumberFormatMask($number, $mask); 623 624 return (($sign) ? '-' : '') . $result; 625 } 626 627 private static function formatStraightNumericValue($value, $format, array $matches, $useThousands, $number_regex) 628 { 629 $left = $matches[1]; 630 $dec = $matches[2]; 631 $right = $matches[3]; 632 633 // minimun width of formatted number (including dot) 634 $minWidth = strlen($left) + strlen($dec) + strlen($right); 635 if ($useThousands) { 636 $value = number_format( 637 $value, 638 strlen($right), 639 StringHelper::getDecimalSeparator(), 640 StringHelper::getThousandsSeparator() 641 ); 642 $value = preg_replace($number_regex, $value, $format); 643 } else { 644 if (preg_match('/[0#]E[+-]0/i', $format)) { 645 // Scientific format 646 $value = sprintf('%5.2E', $value); 647 } elseif (preg_match('/0([^\d\.]+)0/', $format) || substr_count($format, '.') > 1) { 648 if ($value == (int) $value && substr_count($format, '.') === 1) { 649 $value *= 10 ** strlen(explode('.', $format)[1]); 650 } 651 $value = self::complexNumberFormatMask($value, $format); 652 } else { 653 $sprintf_pattern = "%0$minWidth." . strlen($right) . 'f'; 654 $value = sprintf($sprintf_pattern, $value); 655 $value = preg_replace($number_regex, $value, $format); 656 } 657 } 658 659 return $value; 660 } 661 662 private static function formatAsNumber($value, $format) 663 { 664 // The "_" in this string has already been stripped out, 665 // so this test is never true. Furthermore, testing 666 // on Excel shows this format uses Euro symbol, not "EUR". 667 //if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) { 668 // return 'EUR ' . sprintf('%1.2f', $value); 669 //} 670 671 // Some non-number strings are quoted, so we'll get rid of the quotes, likewise any positional * symbols 672 $format = str_replace(['"', '*'], '', $format); 673 674 // Find out if we need thousands separator 675 // This is indicated by a comma enclosed by a digit placeholder: 676 // #,# or 0,0 677 $useThousands = preg_match('/(#,#|0,0)/', $format); 678 if ($useThousands) { 679 $format = preg_replace('/0,0/', '00', $format); 680 $format = preg_replace('/#,#/', '##', $format); 681 } 682 683 // Scale thousands, millions,... 684 // This is indicated by a number of commas after a digit placeholder: 685 // #, or 0.0,, 686 $scale = 1; // same as no scale 687 $matches = []; 688 if (preg_match('/(#|0)(,+)/', $format, $matches)) { 689 $scale = 1000 ** strlen($matches[2]); 690 691 // strip the commas 692 $format = preg_replace('/0,+/', '0', $format); 693 $format = preg_replace('/#,+/', '#', $format); 694 } 695 696 if (preg_match('/#?.*\?\/\?/', $format, $m)) { 697 if ($value != (int) $value) { 698 self::formatAsFraction($value, $format); 699 } 700 } else { 701 // Handle the number itself 702 703 // scale number 704 $value = $value / $scale; 705 // Strip # 706 $format = preg_replace('/\\#/', '0', $format); 707 // Remove locale code [$-###] 708 $format = preg_replace('/\[\$\-.*\]/', '', $format); 709 710 $n = '/\\[[^\\]]+\\]/'; 711 $m = preg_replace($n, '', $format); 712 $number_regex = '/(0+)(\\.?)(0*)/'; 713 if (preg_match($number_regex, $m, $matches)) { 714 $value = self::formatStraightNumericValue($value, $format, $matches, $useThousands, $number_regex); 715 } 716 } 717 718 if (preg_match('/\[\$(.*)\]/u', $format, $m)) { 719 // Currency or Accounting 720 $currencyCode = $m[1]; 721 [$currencyCode] = explode('-', $currencyCode); 722 if ($currencyCode == '') { 723 $currencyCode = StringHelper::getCurrencyCode(); 724 } 725 $value = preg_replace('/\[\$([^\]]*)\]/u', $currencyCode, $value); 726 } 727 728 return $value; 729 } 730 731 private static function splitFormatCompare($value, $cond, $val, $dfcond, $dfval) 732 { 733 if (!$cond) { 734 $cond = $dfcond; 735 $val = $dfval; 736 } 737 switch ($cond) { 738 case '>': 739 return $value > $val; 740 741 case '<': 742 return $value < $val; 743 744 case '<=': 745 return $value <= $val; 746 747 case '<>': 748 return $value != $val; 749 750 case '=': 751 return $value == $val; 752 } 753 754 return $value >= $val; 755 } 756 757 private static function splitFormat($sections, $value) 758 { 759 // Extract the relevant section depending on whether number is positive, negative, or zero? 760 // Text not supported yet. 761 // Here is how the sections apply to various values in Excel: 762 // 1 section: [POSITIVE/NEGATIVE/ZERO/TEXT] 763 // 2 sections: [POSITIVE/ZERO/TEXT] [NEGATIVE] 764 // 3 sections: [POSITIVE/TEXT] [NEGATIVE] [ZERO] 765 // 4 sections: [POSITIVE] [NEGATIVE] [ZERO] [TEXT] 766 $cnt = count($sections); 767 $color_regex = '/\\[(' . implode('|', Color::NAMED_COLORS) . ')\\]/'; 768 $cond_regex = '/\\[(>|>=|<|<=|=|<>)([+-]?\\d+([.]\\d+)?)\\]/'; 769 $colors = ['', '', '', '', '']; 770 $condops = ['', '', '', '', '']; 771 $condvals = [0, 0, 0, 0, 0]; 772 for ($idx = 0; $idx < $cnt; ++$idx) { 773 if (preg_match($color_regex, $sections[$idx], $matches)) { 774 $colors[$idx] = $matches[0]; 775 $sections[$idx] = preg_replace($color_regex, '', $sections[$idx]); 776 } 777 if (preg_match($cond_regex, $sections[$idx], $matches)) { 778 $condops[$idx] = $matches[1]; 779 $condvals[$idx] = $matches[2]; 780 $sections[$idx] = preg_replace($cond_regex, '', $sections[$idx]); 781 } 782 } 783 $color = $colors[0]; 784 $format = $sections[0]; 785 $absval = $value; 786 switch ($cnt) { 787 case 2: 788 $absval = abs($value); 789 if (!self::splitFormatCompare($value, $condops[0], $condvals[0], '>=', 0)) { 790 $color = $colors[1]; 791 $format = $sections[1]; 792 } 793 794 break; 795 case 3: 796 case 4: 797 $absval = abs($value); 798 if (!self::splitFormatCompare($value, $condops[0], $condvals[0], '>', 0)) { 799 if (self::splitFormatCompare($value, $condops[1], $condvals[1], '<', 0)) { 800 $color = $colors[1]; 801 $format = $sections[1]; 802 } else { 803 $color = $colors[2]; 804 $format = $sections[2]; 805 } 806 } 807 808 break; 809 } 810 811 return [$color, $format, $absval]; 812 } 813 814 /** 815 * Convert a value in a pre-defined format to a PHP string. 816 * 817 * @param mixed $value Value to format 818 * @param string $format Format code, see = self::FORMAT_* 819 * @param array $callBack Callback function for additional formatting of string 820 * 821 * @return string Formatted string 822 */ 823 public static function toFormattedString($value, $format, $callBack = null) 824 { 825 // For now we do not treat strings although section 4 of a format code affects strings 826 if (!is_numeric($value)) { 827 return $value; 828 } 829 830 // For 'General' format code, we just pass the value although this is not entirely the way Excel does it, 831 // it seems to round numbers to a total of 10 digits. 832 if (($format === self::FORMAT_GENERAL) || ($format === self::FORMAT_TEXT)) { 833 return $value; 834 } 835 836 // Convert any other escaped characters to quoted strings, e.g. (\T to "T") 837 $format = preg_replace('/(\\\(((.)(?!((AM\/PM)|(A\/P))))|([^ ])))(?=(?:[^"]|"[^"]*")*$)/u', '"$2}"', $format); 838 839 // Get the sections, there can be up to four sections, separated with a semi-colon (but only if not a quoted literal) 840 $sections = preg_split('/(;)(?=(?:[^"]|"[^"]*")*$)/u', $format); 841 842 [$colors, $format, $value] = self::splitFormat($sections, $value); 843 844 // In Excel formats, "_" is used to add spacing, 845 // The following character indicates the size of the spacing, which we can't do in HTML, so we just use a standard space 846 $format = preg_replace('/_./', ' ', $format); 847 848 // Let's begin inspecting the format and converting the value to a formatted string 849 850 // Check for date/time characters (not inside quotes) 851 if (preg_match('/(\[\$[A-Z]*-[0-9A-F]*\])*[hmsdy](?=(?:[^"]|"[^"]*")*$)/miu', $format, $matches)) { 852 // datetime format 853 self::formatAsDate($value, $format); 854 } else { 855 if (substr($format, 0, 1) === '"' && substr($format, -1, 1) === '"') { 856 $value = substr($format, 1, -1); 857 } elseif (preg_match('/%$/', $format)) { 858 // % number format 859 self::formatAsPercentage($value, $format); 860 } else { 861 $value = self::formatAsNumber($value, $format); 862 } 863 } 864 865 // Additional formatting provided by callback function 866 if ($callBack !== null) { 867 [$writerInstance, $function] = $callBack; 868 $value = $writerInstance->$function($value, $colors); 869 } 870 871 return $value; 872 } 873 874 protected function exportArray1(): array 875 { 876 $exportedArray = []; 877 $this->exportArray2($exportedArray, 'formatCode', $this->getFormatCode()); 878 879 return $exportedArray; 880 } 881 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body