1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26:
27:
28:
29: 30: 31: 32: 33: 34: 35:
36: class PHPExcel_Style_NumberFormat extends PHPExcel_Style_Supervisor implements PHPExcel_IComparable
37: {
38:
39: const FORMAT_GENERAL = 'General';
40:
41: const FORMAT_TEXT = '@';
42:
43: const FORMAT_NUMBER = '0';
44: const FORMAT_NUMBER_00 = '0.00';
45: const FORMAT_NUMBER_COMMA_SEPARATED1 = '#,##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:
78: 79: 80: 81: 82:
83: protected static $_builtInFormats;
84:
85: 86: 87: 88: 89:
90: protected static $_flippedBuiltInFormats;
91:
92: 93: 94: 95: 96:
97: protected $_formatCode = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
98:
99: 100: 101: 102: 103:
104: protected $_builtInFormatCode = 0;
105:
106: 107: 108: 109: 110: 111: 112: 113: 114: 115:
116: public function __construct($isSupervisor = FALSE, $isConditional = FALSE)
117: {
118:
119: parent::__construct($isSupervisor);
120:
121: if ($isConditional) {
122: $this->_formatCode = NULL;
123: }
124: }
125:
126: 127: 128: 129: 130: 131:
132: public function getSharedComponent()
133: {
134: return $this->_parent->getSharedComponent()->getNumberFormat();
135: }
136:
137: 138: 139: 140: 141: 142:
143: public function getStyleArray($array)
144: {
145: return array('numberformat' => $array);
146: }
147:
148: 149: 150: 151: 152: 153: 154: 155: 156: 157: 158: 159: 160: 161: 162:
163: public function applyFromArray($pStyles = null)
164: {
165: if (is_array($pStyles)) {
166: if ($this->_isSupervisor) {
167: $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($this->getStyleArray($pStyles));
168: } else {
169: if (array_key_exists('code', $pStyles)) {
170: $this->setFormatCode($pStyles['code']);
171: }
172: }
173: } else {
174: throw new PHPExcel_Exception("Invalid style array passed.");
175: }
176: return $this;
177: }
178:
179: 180: 181: 182: 183:
184: public function getFormatCode()
185: {
186: if ($this->_isSupervisor) {
187: return $this->getSharedComponent()->getFormatCode();
188: }
189: if ($this->_builtInFormatCode !== false)
190: {
191: return self::builtInFormatCode($this->_builtInFormatCode);
192: }
193: return $this->_formatCode;
194: }
195:
196: 197: 198: 199: 200: 201:
202: public function setFormatCode($pValue = PHPExcel_Style_NumberFormat::FORMAT_GENERAL)
203: {
204: if ($pValue == '') {
205: $pValue = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
206: }
207: if ($this->_isSupervisor) {
208: $styleArray = $this->getStyleArray(array('code' => $pValue));
209: $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray);
210: } else {
211: $this->_formatCode = $pValue;
212: $this->_builtInFormatCode = self::builtInFormatCodeIndex($pValue);
213: }
214: return $this;
215: }
216:
217: 218: 219: 220: 221:
222: public function getBuiltInFormatCode()
223: {
224: if ($this->_isSupervisor) {
225: return $this->getSharedComponent()->getBuiltInFormatCode();
226: }
227: return $this->_builtInFormatCode;
228: }
229:
230: 231: 232: 233: 234: 235:
236: public function setBuiltInFormatCode($pValue = 0)
237: {
238:
239: if ($this->_isSupervisor) {
240: $styleArray = $this->getStyleArray(array('code' => self::builtInFormatCode($pValue)));
241: $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray);
242: } else {
243: $this->_builtInFormatCode = $pValue;
244: $this->_formatCode = self::builtInFormatCode($pValue);
245: }
246: return $this;
247: }
248:
249: 250: 251:
252: private static function fillBuiltInFormatCodes()
253: {
254:
255: if (is_null(self::$_builtInFormats)) {
256: self::$_builtInFormats = array();
257:
258:
259: self::$_builtInFormats[0] = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
260: self::$_builtInFormats[1] = '0';
261: self::$_builtInFormats[2] = '0.00';
262: self::$_builtInFormats[3] = '#,##0';
263: self::$_builtInFormats[4] = '#,##0.00';
264:
265: self::$_builtInFormats[9] = '0%';
266: self::$_builtInFormats[10] = '0.00%';
267: self::$_builtInFormats[11] = '0.00E+00';
268: self::$_builtInFormats[12] = '# ?/?';
269: self::$_builtInFormats[13] = '# ??/??';
270: self::$_builtInFormats[14] = 'mm-dd-yy';
271: self::$_builtInFormats[15] = 'd-mmm-yy';
272: self::$_builtInFormats[16] = 'd-mmm';
273: self::$_builtInFormats[17] = 'mmm-yy';
274: self::$_builtInFormats[18] = 'h:mm AM/PM';
275: self::$_builtInFormats[19] = 'h:mm:ss AM/PM';
276: self::$_builtInFormats[20] = 'h:mm';
277: self::$_builtInFormats[21] = 'h:mm:ss';
278: self::$_builtInFormats[22] = 'm/d/yy h:mm';
279:
280: self::$_builtInFormats[37] = '#,##0 ;(#,##0)';
281: self::$_builtInFormats[38] = '#,##0 ;[Red](#,##0)';
282: self::$_builtInFormats[39] = '#,##0.00;(#,##0.00)';
283: self::$_builtInFormats[40] = '#,##0.00;[Red](#,##0.00)';
284:
285: self::$_builtInFormats[44] = '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)';
286: self::$_builtInFormats[45] = 'mm:ss';
287: self::$_builtInFormats[46] = '[h]:mm:ss';
288: self::$_builtInFormats[47] = 'mmss.0';
289: self::$_builtInFormats[48] = '##0.0E+0';
290: self::$_builtInFormats[49] = '@';
291:
292:
293: self::$_builtInFormats[27] = '[$-404]e/m/d';
294: self::$_builtInFormats[30] = 'm/d/yy';
295: self::$_builtInFormats[36] = '[$-404]e/m/d';
296: self::$_builtInFormats[50] = '[$-404]e/m/d';
297: self::$_builtInFormats[57] = '[$-404]e/m/d';
298:
299:
300: self::$_builtInFormats[59] = 't0';
301: self::$_builtInFormats[60] = 't0.00';
302: self::$_builtInFormats[61] = 't#,##0';
303: self::$_builtInFormats[62] = 't#,##0.00';
304: self::$_builtInFormats[67] = 't0%';
305: self::$_builtInFormats[68] = 't0.00%';
306: self::$_builtInFormats[69] = 't# ?/?';
307: self::$_builtInFormats[70] = 't# ??/??';
308:
309:
310: self::$_flippedBuiltInFormats = array_flip(self::$_builtInFormats);
311: }
312: }
313:
314: 315: 316: 317: 318: 319:
320: public static function builtInFormatCode($pIndex)
321: {
322:
323: $pIndex = intval($pIndex);
324:
325:
326: self::fillBuiltInFormatCodes();
327:
328:
329: if (isset(self::$_builtInFormats[$pIndex])) {
330: return self::$_builtInFormats[$pIndex];
331: }
332:
333: return '';
334: }
335:
336: 337: 338: 339: 340: 341:
342: public static function builtInFormatCodeIndex($formatCode)
343: {
344:
345: self::fillBuiltInFormatCodes();
346:
347:
348: if (isset(self::$_flippedBuiltInFormats[$formatCode])) {
349: return self::$_flippedBuiltInFormats[$formatCode];
350: }
351:
352: return false;
353: }
354:
355: 356: 357: 358: 359:
360: public function getHashCode()
361: {
362: if ($this->_isSupervisor) {
363: return $this->getSharedComponent()->getHashCode();
364: }
365: return md5(
366: $this->_formatCode
367: . $this->_builtInFormatCode
368: . __CLASS__
369: );
370: }
371:
372: 373: 374: 375: 376:
377: private static $_dateFormatReplacements = array(
378:
379: '\\' => '',
380:
381: 'am/pm' => 'A',
382:
383: 'e' => 'Y',
384: 'yyyy' => 'Y',
385:
386: 'yy' => 'y',
387:
388: 'mmmmm' => 'M',
389:
390: 'mmmm' => 'F',
391:
392: 'mmm' => 'M',
393:
394:
395:
396: ':mm' => ':i',
397: 'mm:' => 'i:',
398:
399: 'mm' => 'm',
400:
401: 'm' => 'n',
402:
403: 'dddd' => 'l',
404:
405: 'ddd' => 'D',
406:
407: 'dd' => 'd',
408:
409: 'd' => 'j',
410:
411: 'ss' => 's',
412:
413: '.s' => ''
414: );
415: 416: 417: 418: 419:
420: private static $_dateFormatReplacements24 = array(
421: 'hh' => 'H',
422: 'h' => 'G'
423: );
424: 425: 426: 427: 428:
429: private static $_dateFormatReplacements12 = array(
430: 'hh' => 'h',
431: 'h' => 'g'
432: );
433:
434: private static function _formatAsDate(&$value, &$format)
435: {
436:
437:
438:
439:
440:
441: $format = preg_replace('/^(\[\$[A-Z]*-[0-9A-F]*\])/i', '', $format);
442:
443:
444: $format = strtolower($format);
445:
446: $format = strtr($format,self::$_dateFormatReplacements);
447: if (!strpos($format,'A')) {
448: $format = strtr($format,self::$_dateFormatReplacements24);
449: } else {
450: $format = strtr($format,self::$_dateFormatReplacements12);
451: }
452:
453: $dateObj = PHPExcel_Shared_Date::ExcelToPHPObject($value);
454: $value = $dateObj->format($format);
455: }
456:
457: private static function _formatAsPercentage(&$value, &$format)
458: {
459: if ($format === self::FORMAT_PERCENTAGE) {
460: $value = round( (100 * $value), 0) . '%';
461: } else {
462: if (preg_match('/\.[#0]+/i', $format, $m)) {
463: $s = substr($m[0], 0, 1) . (strlen($m[0]) - 1);
464: $format = str_replace($m[0], $s, $format);
465: }
466: if (preg_match('/^[#0]+/', $format, $m)) {
467: $format = str_replace($m[0], strlen($m[0]), $format);
468: }
469: $format = '%' . str_replace('%', 'f%%', $format);
470:
471: $value = sprintf($format, 100 * $value);
472: }
473: }
474:
475: private static function _formatAsFraction(&$value, &$format)
476: {
477: $sign = ($value < 0) ? '-' : '';
478:
479: $integerPart = floor(abs($value));
480: $decimalPart = trim(fmod(abs($value),1),'0.');
481: $decimalLength = strlen($decimalPart);
482: $decimalDivisor = pow(10,$decimalLength);
483:
484: $GCD = PHPExcel_Calculation_MathTrig::GCD($decimalPart,$decimalDivisor);
485:
486: $adjustedDecimalPart = $decimalPart/$GCD;
487: $adjustedDecimalDivisor = $decimalDivisor/$GCD;
488:
489: if ((strpos($format,'0') !== false) || (strpos($format,'#') !== false) || (substr($format,0,3) == '? ?')) {
490: if ($integerPart == 0) {
491: $integerPart = '';
492: }
493: $value = "$sign$integerPart $adjustedDecimalPart/$adjustedDecimalDivisor";
494: } else {
495: $adjustedDecimalPart += $integerPart * $adjustedDecimalDivisor;
496: $value = "$sign$adjustedDecimalPart/$adjustedDecimalDivisor";
497: }
498: }
499:
500: private static function _complexNumberFormatMask($number, $mask) {
501: if (strpos($mask,'.') !== false) {
502: $numbers = explode('.', $number . '.0');
503: $masks = explode('.', $mask . '.0');
504: $result1 = self::_complexNumberFormatMask($numbers[0], $masks[0]);
505: $result2 = strrev(self::_complexNumberFormatMask(strrev($numbers[1]), strrev($masks[1])));
506: return $result1 . '.' . $result2;
507: }
508:
509: $r = preg_match_all('/0+/', $mask, $result, PREG_OFFSET_CAPTURE);
510: if ($r > 1) {
511: $result = array_reverse($result[0]);
512:
513: foreach($result as $block) {
514: $divisor = 1 . $block[0];
515: $size = strlen($block[0]);
516: $offset = $block[1];
517:
518: $blockValue = sprintf(
519: '%0' . $size . 'd',
520: fmod($number, $divisor)
521: );
522: $number = floor($number / $divisor);
523: $mask = substr_replace($mask,$blockValue, $offset, $size);
524: }
525: if ($number > 0) {
526: $mask = substr_replace($mask, $number, $offset, 0);
527: }
528: $result = $mask;
529: } else {
530: $result = $number;
531: }
532:
533: return $result;
534: }
535:
536: 537: 538: 539: 540: 541: 542: 543:
544: public static function toFormattedString($value = '0', $format = PHPExcel_Style_NumberFormat::FORMAT_GENERAL, $callBack = null)
545: {
546:
547: if (!is_numeric($value)) return $value;
548:
549:
550:
551: if (($format === PHPExcel_Style_NumberFormat::FORMAT_GENERAL) || ($format === PHPExcel_Style_NumberFormat::FORMAT_TEXT)) {
552: return $value;
553: }
554:
555:
556: $sections = explode(';', $format);
557:
558:
559:
560:
561:
562:
563:
564:
565: switch (count($sections)) {
566: case 1:
567: $format = $sections[0];
568: break;
569:
570: case 2:
571: $format = ($value >= 0) ? $sections[0] : $sections[1];
572: $value = abs($value);
573: break;
574:
575: case 3:
576: $format = ($value > 0) ?
577: $sections[0] : ( ($value < 0) ?
578: $sections[1] : $sections[2]);
579: $value = abs($value);
580: break;
581:
582: case 4:
583: $format = ($value > 0) ?
584: $sections[0] : ( ($value < 0) ?
585: $sections[1] : $sections[2]);
586: $value = abs($value);
587: break;
588:
589: default:
590:
591: $format = $sections[0];
592: break;
593: }
594:
595:
596: $formatColor = $format;
597:
598:
599: $color_regex = '/^\\[[a-zA-Z]+\\]/';
600: $format = preg_replace($color_regex, '', $format);
601:
602:
603: if (preg_match('/^(\[\$[A-Z]*-[0-9A-F]*\])*[hmsdy]/i', $format)) {
604: self::_formatAsDate($value, $format);
605: } else if (preg_match('/%$/', $format)) {
606: self::_formatAsPercentage($value, $format);
607: } else {
608: if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) {
609: $value = 'EUR ' . sprintf('%1.2f', $value);
610: } else {
611:
612: $format = preg_replace('/_./', '', $format);
613:
614:
615: $format = preg_replace("/\\\\/", '', $format);
616:
617:
618: $format = str_replace(array('"','*'), '', $format);
619:
620:
621:
622:
623: $useThousands = preg_match('/(#,#|0,0)/', $format);
624: if ($useThousands) {
625: $format = preg_replace('/0,0/', '00', $format);
626: $format = preg_replace('/#,#/', '##', $format);
627: }
628:
629:
630:
631:
632: $scale = 1;
633: $matches = array();
634: if (preg_match('/(#|0)(,+)/', $format, $matches)) {
635: $scale = pow(1000, strlen($matches[2]));
636:
637:
638: $format = preg_replace('/0,+/', '0', $format);
639: $format = preg_replace('/#,+/', '#', $format);
640: }
641:
642: if (preg_match('/#?.*\?\/\?/', $format, $m)) {
643:
644: if ($value != (int)$value) {
645: self::_formatAsFraction($value, $format);
646: }
647:
648: } else {
649:
650:
651:
652: $value = $value / $scale;
653:
654:
655: $format = preg_replace('/\\#/', '0', $format);
656:
657: $n = "/\[[^\]]+\]/";
658: $m = preg_replace($n, '', $format);
659: $number_regex = "/(0+)(\.?)(0*)/";
660: if (preg_match($number_regex, $m, $matches)) {
661: $left = $matches[1];
662: $dec = $matches[2];
663: $right = $matches[3];
664:
665:
666: $minWidth = strlen($left) + strlen($dec) + strlen($right);
667: if ($useThousands) {
668: $value = number_format(
669: $value
670: , strlen($right)
671: , PHPExcel_Shared_String::getDecimalSeparator()
672: , PHPExcel_Shared_String::getThousandsSeparator()
673: );
674: $value = preg_replace($number_regex, $value, $format);
675: } else {
676: if (preg_match('/[0#]E[+-]0/i', $format)) {
677:
678: $value = sprintf('%5.2E', $value);
679: } elseif (preg_match('/0([^\d\.]+)0/', $format)) {
680: $value = self::_complexNumberFormatMask($value, $format);
681: } else {
682: $sprintf_pattern = "%0$minWidth." . strlen($right) . "f";
683: $value = sprintf($sprintf_pattern, $value);
684: $value = preg_replace($number_regex, $value, $format);
685: }
686: }
687: }
688: }
689: if (preg_match('/\[\$(.*)\]/u', $format, $m)) {
690:
691: $currencyFormat = $m[0];
692: $currencyCode = $m[1];
693: list($currencyCode) = explode('-',$currencyCode);
694: if ($currencyCode == '') {
695: $currencyCode = PHPExcel_Shared_String::getCurrencyCode();
696: }
697: $value = preg_replace('/\[\$([^\]]*)\]/u',$currencyCode,$value);
698: }
699: }
700: }
701:
702:
703: if ($callBack !== null) {
704: list($writerInstance, $function) = $callBack;
705: $value = $writerInstance->$function($value, $formatColor);
706: }
707:
708: return $value;
709: }
710:
711: }
712: