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