ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
NumberFormat.php
Go to the documentation of this file.
1<?php
37{
38 /* Pre-defined formats */
39 const FORMAT_GENERAL = 'General';
40
41 const FORMAT_TEXT = '@';
42
43 const FORMAT_NUMBER = '0';
44 const FORMAT_NUMBER_00 = '0.00';
46 const FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-';
47
48 const FORMAT_PERCENTAGE = '0%';
49 const FORMAT_PERCENTAGE_00 = '0.00%';
50
51 const FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd';
52 const FORMAT_DATE_YYYYMMDD = 'yy-mm-dd';
53 const FORMAT_DATE_DDMMYYYY = 'dd/mm/yy';
54 const FORMAT_DATE_DMYSLASH = 'd/m/y';
55 const FORMAT_DATE_DMYMINUS = 'd-m-y';
56 const FORMAT_DATE_DMMINUS = 'd-m';
57 const FORMAT_DATE_MYMINUS = 'm-y';
58 const FORMAT_DATE_XLSX14 = 'mm-dd-yy';
59 const FORMAT_DATE_XLSX15 = 'd-mmm-yy';
60 const FORMAT_DATE_XLSX16 = 'd-mmm';
61 const FORMAT_DATE_XLSX17 = 'mmm-yy';
62 const FORMAT_DATE_XLSX22 = 'm/d/yy h:mm';
63 const FORMAT_DATE_DATETIME = 'd/m/y h:mm';
64 const FORMAT_DATE_TIME1 = 'h:mm AM/PM';
65 const FORMAT_DATE_TIME2 = 'h:mm:ss AM/PM';
66 const FORMAT_DATE_TIME3 = 'h:mm';
67 const FORMAT_DATE_TIME4 = 'h:mm:ss';
68 const FORMAT_DATE_TIME5 = 'mm:ss';
69 const FORMAT_DATE_TIME6 = 'h:mm:ss';
70 const FORMAT_DATE_TIME7 = 'i:s.S';
71 const FORMAT_DATE_TIME8 = 'h:mm:ss;@';
72 const FORMAT_DATE_YYYYMMDDSLASH = 'yy/mm/dd;@';
73
74 const FORMAT_CURRENCY_USD_SIMPLE = '"$"#,##0.00_-';
75 const FORMAT_CURRENCY_USD = '$#,##0_-';
76 const FORMAT_CURRENCY_EUR_SIMPLE = '[$EUR ]#,##0.00_-';
77
83 protected static $_builtInFormats;
84
90 protected static $_flippedBuiltInFormats;
91
98
104 protected $_builtInFormatCode = 0;
105
116 public function __construct($isSupervisor = FALSE, $isConditional = FALSE)
117 {
118 // Supervisor?
119 parent::__construct($isSupervisor);
120
121 if ($isConditional) {
122 $this->_formatCode = NULL;
123 $this->_builtInFormatCode = FALSE;
124 }
125 }
126
133 public function getSharedComponent()
134 {
135 return $this->_parent->getSharedComponent()->getNumberFormat();
136 }
137
144 public function getStyleArray($array)
145 {
146 return array('numberformat' => $array);
147 }
148
164 public function applyFromArray($pStyles = null)
165 {
166 if (is_array($pStyles)) {
167 if ($this->_isSupervisor) {
168 $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($this->getStyleArray($pStyles));
169 } else {
170 if (array_key_exists('code', $pStyles)) {
171 $this->setFormatCode($pStyles['code']);
172 }
173 }
174 } else {
175 throw new PHPExcel_Exception("Invalid style array passed.");
176 }
177 return $this;
178 }
179
185 public function getFormatCode()
186 {
187 if ($this->_isSupervisor) {
188 return $this->getSharedComponent()->getFormatCode();
189 }
190 if ($this->_builtInFormatCode !== false)
191 {
192 return self::builtInFormatCode($this->_builtInFormatCode);
193 }
194 return $this->_formatCode;
195 }
196
204 {
205 if ($pValue == '') {
207 }
208 if ($this->_isSupervisor) {
209 $styleArray = $this->getStyleArray(array('code' => $pValue));
210 $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray);
211 } else {
212 $this->_formatCode = $pValue;
213 $this->_builtInFormatCode = self::builtInFormatCodeIndex($pValue);
214 }
215 return $this;
216 }
217
223 public function getBuiltInFormatCode()
224 {
225 if ($this->_isSupervisor) {
226 return $this->getSharedComponent()->getBuiltInFormatCode();
227 }
229 }
230
237 public function setBuiltInFormatCode($pValue = 0)
238 {
239
240 if ($this->_isSupervisor) {
241 $styleArray = $this->getStyleArray(array('code' => self::builtInFormatCode($pValue)));
242 $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray);
243 } else {
244 $this->_builtInFormatCode = $pValue;
245 $this->_formatCode = self::builtInFormatCode($pValue);
246 }
247 return $this;
248 }
249
253 private static function fillBuiltInFormatCodes()
254 {
255 // Built-in format codes
256 if (is_null(self::$_builtInFormats)) {
257 self::$_builtInFormats = array();
258
259 // General
260 self::$_builtInFormats[0] = PHPExcel_Style_NumberFormat::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] = '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/yy h:mm';
280
281 self::$_builtInFormats[37] = '#,##0 ;(#,##0)';
282 self::$_builtInFormats[38] = '#,##0 ;[Red](#,##0)';
283 self::$_builtInFormats[39] = '#,##0.00;(#,##0.00)';
284 self::$_builtInFormats[40] = '#,##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] = '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 // Flip array (for faster lookups)
311 self::$_flippedBuiltInFormats = array_flip(self::$_builtInFormats);
312 }
313 }
314
321 public static function builtInFormatCode($pIndex)
322 {
323 // Clean parameter
324 $pIndex = intval($pIndex);
325
326 // Ensure built-in format codes are available
328
329 // Lookup format code
330 if (isset(self::$_builtInFormats[$pIndex])) {
331 return self::$_builtInFormats[$pIndex];
332 }
333
334 return '';
335 }
336
343 public static function builtInFormatCodeIndex($formatCode)
344 {
345 // Ensure built-in format codes are available
347
348 // Lookup format code
349 if (isset(self::$_flippedBuiltInFormats[$formatCode])) {
350 return self::$_flippedBuiltInFormats[$formatCode];
351 }
352
353 return false;
354 }
355
361 public function getHashCode()
362 {
363 if ($this->_isSupervisor) {
364 return $this->getSharedComponent()->getHashCode();
365 }
366 return md5(
367 $this->_formatCode
368 . $this->_builtInFormatCode
369 . __CLASS__
370 );
371 }
372
378 private static $_dateFormatReplacements = array(
379 // first remove escapes related to non-format characters
380 '\\' => '',
381 // 12-hour suffix
382 'am/pm' => 'A',
383 // 4-digit year
384 'e' => 'Y',
385 'yyyy' => 'Y',
386 // 2-digit year
387 'yy' => 'y',
388 // first letter of month - no php equivalent
389 'mmmmm' => 'M',
390 // full month name
391 'mmmm' => 'F',
392 // short month name
393 'mmm' => 'M',
394 // mm is minutes if time, but can also be month w/leading zero
395 // so we try to identify times be the inclusion of a : separator in the mask
396 // It isn't perfect, but the best way I know how
397 ':mm' => ':i',
398 'mm:' => 'i:',
399 // month leading zero
400 'mm' => 'm',
401 // month no leading zero
402 'm' => 'n',
403 // full day of week name
404 'dddd' => 'l',
405 // short day of week name
406 'ddd' => 'D',
407 // days leading zero
408 'dd' => 'd',
409 // days no leading zero
410 'd' => 'j',
411 // seconds
412 'ss' => 's',
413 // fractional seconds - no php equivalent
414 '.s' => ''
415 );
421 private static $_dateFormatReplacements24 = array(
422 'hh' => 'H',
423 'h' => 'G'
424 );
430 private static $_dateFormatReplacements12 = array(
431 'hh' => 'h',
432 'h' => 'g'
433 );
434
435 private static function _formatAsDate(&$value, &$format)
436 {
437 // dvc: convert Excel formats to PHP date formats
438
439 // strip off first part containing e.g. [$-F800] or [$USD-409]
440 // general syntax: [$<Currency string>-<language info>]
441 // language info is in hexadecimal
442 $format = preg_replace('/^(\[\$[A-Z]*-[0-9A-F]*\])/i', '', $format);
443
444 // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case
445 $format = strtolower($format);
446
447 $format = strtr($format,self::$_dateFormatReplacements);
448 if (!strpos($format,'A')) { // 24-hour time format
449 $format = strtr($format,self::$_dateFormatReplacements24);
450 } else { // 12-hour time format
451 $format = strtr($format,self::$_dateFormatReplacements12);
452 }
453
455 $value = $dateObj->format($format);
456 }
457
458 private static function _formatAsPercentage(&$value, &$format)
459 {
460 if ($format === self::FORMAT_PERCENTAGE) {
461 $value = round( (100 * $value), 0) . '%';
462 } else {
463 if (preg_match('/\.[#0]+/i', $format, $m)) {
464 $s = substr($m[0], 0, 1) . (strlen($m[0]) - 1);
465 $format = str_replace($m[0], $s, $format);
466 }
467 if (preg_match('/^[#0]+/', $format, $m)) {
468 $format = str_replace($m[0], strlen($m[0]), $format);
469 }
470 $format = '%' . str_replace('%', 'f%%', $format);
471
472 $value = sprintf($format, 100 * $value);
473 }
474 }
475
476 private static function _formatAsFraction(&$value, &$format)
477 {
478 $sign = ($value < 0) ? '-' : '';
479
480 $integerPart = floor(abs($value));
481 $decimalPart = trim(fmod(abs($value),1),'0.');
482 $decimalLength = strlen($decimalPart);
483 $decimalDivisor = pow(10,$decimalLength);
484
485 $GCD = PHPExcel_Calculation_MathTrig::GCD($decimalPart,$decimalDivisor);
486
487 $adjustedDecimalPart = $decimalPart/$GCD;
488 $adjustedDecimalDivisor = $decimalDivisor/$GCD;
489
490 if ((strpos($format,'0') !== false) || (strpos($format,'#') !== false) || (substr($format,0,3) == '? ?')) {
491 if ($integerPart == 0) {
492 $integerPart = '';
493 }
494 $value = "$sign$integerPart $adjustedDecimalPart/$adjustedDecimalDivisor";
495 } else {
496 $adjustedDecimalPart += $integerPart * $adjustedDecimalDivisor;
497 $value = "$sign$adjustedDecimalPart/$adjustedDecimalDivisor";
498 }
499 }
500
501 private static function _complexNumberFormatMask($number, $mask, $level = 0) {
502 $sign = ($number < 0.0);
503 $number = abs($number);
504 if (strpos($mask,'.') !== false) {
505 $numbers = explode('.', $number . '.0');
506 $masks = explode('.', $mask . '.0');
507 $result1 = self::_complexNumberFormatMask($numbers[0], $masks[0], 1);
508 $result2 = strrev(self::_complexNumberFormatMask(strrev($numbers[1]), strrev($masks[1]), 1));
509 return (($sign) ? '-' : '') . $result1 . '.' . $result2;
510 }
511
512 $r = preg_match_all('/0+/', $mask, $result, PREG_OFFSET_CAPTURE);
513 if ($r > 1) {
514 $result = array_reverse($result[0]);
515
516 foreach($result as $block) {
517 $divisor = 1 . $block[0];
518 $size = strlen($block[0]);
519 $offset = $block[1];
520
521 $blockValue = sprintf(
522 '%0' . $size . 'd',
523 fmod($number, $divisor)
524 );
525 $number = floor($number / $divisor);
526 $mask = substr_replace($mask, $blockValue, $offset, $size);
527 }
528 if ($number > 0) {
529 $mask = substr_replace($mask, $number, $offset, 0);
530 }
531 $result = $mask;
532 } else {
533 $result = $number;
534 }
535
536 return (($sign) ? '-' : '') . $result;
537 }
538
547 public static function toFormattedString($value = '0', $format = PHPExcel_Style_NumberFormat::FORMAT_GENERAL, $callBack = null)
548 {
549 // For now we do not treat strings although section 4 of a format code affects strings
550 if (!is_numeric($value)) return $value;
551
552 // For 'General' format code, we just pass the value although this is not entirely the way Excel does it,
553 // it seems to round numbers to a total of 10 digits.
555 return $value;
556 }
557
558 // Get the sections, there can be up to four sections
559 $sections = explode(';', $format);
560
561 // Fetch the relevant section depending on whether number is positive, negative, or zero?
562 // Text not supported yet.
563 // Here is how the sections apply to various values in Excel:
564 // 1 section: [POSITIVE/NEGATIVE/ZERO/TEXT]
565 // 2 sections: [POSITIVE/ZERO/TEXT] [NEGATIVE]
566 // 3 sections: [POSITIVE/TEXT] [NEGATIVE] [ZERO]
567 // 4 sections: [POSITIVE] [NEGATIVE] [ZERO] [TEXT]
568 switch (count($sections)) {
569 case 1:
570 $format = $sections[0];
571 break;
572
573 case 2:
574 $format = ($value >= 0) ? $sections[0] : $sections[1];
575 $value = abs($value); // Use the absolute value
576 break;
577
578 case 3:
579 $format = ($value > 0) ?
580 $sections[0] : ( ($value < 0) ?
581 $sections[1] : $sections[2]);
582 $value = abs($value); // Use the absolute value
583 break;
584
585 case 4:
586 $format = ($value > 0) ?
587 $sections[0] : ( ($value < 0) ?
588 $sections[1] : $sections[2]);
589 $value = abs($value); // Use the absolute value
590 break;
591
592 default:
593 // something is wrong, just use first section
594 $format = $sections[0];
595 break;
596 }
597
598 // Save format with color information for later use below
599 $formatColor = $format;
600
601 // Strip color information
602 $color_regex = '/^\\[[a-zA-Z]+\\]/';
603 $format = preg_replace($color_regex, '', $format);
604
605 // Let's begin inspecting the format and converting the value to a formatted string
606 if (preg_match('/^(\[\$[A-Z]*-[0-9A-F]*\])*[hmsdy]/i', $format)) { // datetime format
608 } else if (preg_match('/%$/', $format)) { // % number format
610 } else {
611 if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) {
612 $value = 'EUR ' . sprintf('%1.2f', $value);
613 } else {
614 // In Excel formats, "_" is used to add spacing, which we can't do in HTML
615 $format = preg_replace('/_./', '', $format);
616
617 // Some non-number characters are escaped with \, which we don't need
618 $format = preg_replace("/\\\\/", '', $format);
619// Handle escaped characters, such as \" to display a literal " or \\ to display a literal \
620// $format = preg_replace('/(?<!\\\\‍)\"/', '', $format);
621// $format = str_replace(array('\\"', '*'), array('"', ''), $format);
622
623 // Some non-number strings are quoted, so we'll get rid of the quotes, likewise any positional * symbols
624 $format = str_replace(array('"', '*'), '', $format);
625
626 // Find out if we need thousands separator
627 // This is indicated by a comma enclosed by a digit placeholder:
628 // #,# or 0,0
629 $useThousands = preg_match('/(#,#|0,0)/', $format);
630 if ($useThousands) {
631 $format = preg_replace('/0,0/', '00', $format);
632 $format = preg_replace('/#,#/', '##', $format);
633 }
634
635 // Scale thousands, millions,...
636 // This is indicated by a number of commas after a digit placeholder:
637 // #, or 0.0,,
638 $scale = 1; // same as no scale
639 $matches = array();
640 if (preg_match('/(#|0)(,+)/', $format, $matches)) {
641 $scale = pow(1000, strlen($matches[2]));
642
643 // strip the commas
644 $format = preg_replace('/0,+/', '0', $format);
645 $format = preg_replace('/#,+/', '#', $format);
646 }
647
648 if (preg_match('/#?.*\?\/\?/', $format, $m)) {
649 //echo 'Format mask is fractional '.$format.' <br />';
650 if ($value != (int)$value) {
652 }
653
654 } else {
655 // Handle the number itself
656
657 // scale number
658 $value = $value / $scale;
659
660 // Strip #
661 $format = preg_replace('/\\#/', '0', $format);
662
663 $n = "/\[[^\]]+\]/";
664 $m = preg_replace($n, '', $format);
665 $number_regex = "/(0+)(\.?)(0*)/";
666 if (preg_match($number_regex, $m, $matches)) {
667 $left = $matches[1];
668 $dec = $matches[2];
669 $right = $matches[3];
670
671 // minimun width of formatted number (including dot)
672 $minWidth = strlen($left) + strlen($dec) + strlen($right);
673 if ($useThousands) {
674 $value = number_format(
675 $value
676 , strlen($right)
679 );
680 $value = preg_replace($number_regex, $value, $format);
681 } else {
682 if (preg_match('/[0#]E[+-]0/i', $format)) {
683 // Scientific format
684 $value = sprintf('%5.2E', $value);
685 } elseif (preg_match('/0([^\d\.]+)0/', $format)) {
686 $value = self::_complexNumberFormatMask($value, $format);
687 } else {
688 $sprintf_pattern = "%0$minWidth." . strlen($right) . "f";
689 $value = sprintf($sprintf_pattern, $value);
690 $value = preg_replace($number_regex, $value, $format);
691 }
692 }
693 }
694 }
695 if (preg_match('/\[\$(.*)\]/u', $format, $m)) {
696 // Currency or Accounting
697 $currencyFormat = $m[0];
698 $currencyCode = $m[1];
699 list($currencyCode) = explode('-',$currencyCode);
700 if ($currencyCode == '') {
702 }
703 $value = preg_replace('/\[\$([^\]]*)\]/u',$currencyCode,$value);
704 }
705 }
706 }
707
708 // Additional formatting provided by callback function
709 if ($callBack !== null) {
710 list($writerInstance, $function) = $callBack;
711 $value = $writerInstance->$function($value, $formatColor);
712 }
713
714 return $value;
715 }
716
717}
sprintf('%.4f', $callTime)
$result
$n
Definition: RandomTest.php:85
$size
Definition: RandomTest.php:84
$function
Definition: cas.php:28
An exception for terminatinating execution or to throw for unit testing.
static ExcelToPHPObject($dateValue=0)
Convert a date from Excel to a PHP Date/Time object.
Definition: Date.php:160
static getDecimalSeparator()
Get the decimal separator.
Definition: String.php:687
static getCurrencyCode()
Definition: String.php:751
static getThousandsSeparator()
Get the thousands separator.
Definition: String.php:719
setFormatCode($pValue=PHPExcel_Style_NumberFormat::FORMAT_GENERAL)
Set Format Code.
static _formatAsDate(&$value, &$format)
getFormatCode()
Get Format Code.
__construct($isSupervisor=FALSE, $isConditional=FALSE)
Create a new PHPExcel_Style_NumberFormat.
static builtInFormatCodeIndex($formatCode)
Get built-in format code index.
getStyleArray($array)
Build style array from subcomponents.
static builtInFormatCode($pIndex)
Get built-in format code.
static toFormattedString($value='0', $format=PHPExcel_Style_NumberFormat::FORMAT_GENERAL, $callBack=null)
Convert a value in a pre-defined format to a PHP string.
static _complexNumberFormatMask($number, $mask, $level=0)
static _formatAsFraction(&$value, &$format)
applyFromArray($pStyles=null)
Apply styles from array.
getSharedComponent()
Get the shared style component for the currently active cell in currently active sheet.
setBuiltInFormatCode($pValue=0)
Set Built-In Format Code.
static fillBuiltInFormatCodes()
Fill built-in format codes.
static _formatAsPercentage(&$value, &$format)
getBuiltInFormatCode()
Get Built-In Format Code.
getActiveSheet()
Get the currently active sheet.
Definition: Supervisor.php:92
getSelectedCells()
Get the currently active cell coordinate in currently active sheet.
Definition: Supervisor.php:103
$r
Definition: example_031.php:79
$mask
Definition: example_042.php:90
$format
Definition: metadata.php:141
$s
Definition: pwgen.php:45