ILIAS  Release_4_0_x_branch Revision 61816
 All Data Structures Namespaces Files Functions Variables Groups Pages
NumberFormat.php
Go to the documentation of this file.
1 <?php
30 if (!defined('PHPEXCEL_ROOT')) {
34  define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35 }
36 
38 require_once PHPEXCEL_ROOT . 'PHPExcel/IComparable.php';
39 
41 require_once PHPEXCEL_ROOT . 'PHPExcel/Shared/Date.php';
42 
44 require_once PHPEXCEL_ROOT . 'PHPExcel/Calculation/Functions.php';
45 
46 
54 class PHPExcel_Style_NumberFormat implements PHPExcel_IComparable
55 {
56  /* Pre-defined formats */
57  const FORMAT_GENERAL = 'General';
58 
59  const FORMAT_TEXT = '@';
60 
61  const FORMAT_NUMBER = '0';
62  const FORMAT_NUMBER_00 = '0.00';
63  const FORMAT_NUMBER_COMMA_SEPARATED1 = '#,##0.00';
64  const FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-';
65 
66  const FORMAT_PERCENTAGE = '0%';
67  const FORMAT_PERCENTAGE_00 = '0.00%';
68 
69  const FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd';
70  const FORMAT_DATE_YYYYMMDD = 'yy-mm-dd';
71  const FORMAT_DATE_DDMMYYYY = 'dd/mm/yy';
72  const FORMAT_DATE_DMYSLASH = 'd/m/y';
73  const FORMAT_DATE_DMYMINUS = 'd-m-y';
74  const FORMAT_DATE_DMMINUS = 'd-m';
75  const FORMAT_DATE_MYMINUS = 'm-y';
76  const FORMAT_DATE_XLSX14 = 'mm-dd-yy';
77  const FORMAT_DATE_XLSX15 = 'd-mmm-yy';
78  const FORMAT_DATE_XLSX16 = 'd-mmm';
79  const FORMAT_DATE_XLSX17 = 'mmm-yy';
80  const FORMAT_DATE_XLSX22 = 'm/d/yy h:mm';
81  const FORMAT_DATE_DATETIME = 'd/m/y h:mm';
82  const FORMAT_DATE_TIME1 = 'h:mm AM/PM';
83  const FORMAT_DATE_TIME2 = 'h:mm:ss AM/PM';
84  const FORMAT_DATE_TIME3 = 'h:mm';
85  const FORMAT_DATE_TIME4 = 'h:mm:ss';
86  const FORMAT_DATE_TIME5 = 'mm:ss';
87  const FORMAT_DATE_TIME6 = 'h:mm:ss';
88  const FORMAT_DATE_TIME7 = 'i:s.S';
89  const FORMAT_DATE_TIME8 = 'h:mm:ss;@';
90  const FORMAT_DATE_YYYYMMDDSLASH = 'yy/mm/dd;@';
91 
92  const FORMAT_CURRENCY_USD_SIMPLE = '"$"#,##0.00_-';
93  const FORMAT_CURRENCY_USD = '$#,##0_-';
94  const FORMAT_CURRENCY_EUR_SIMPLE = '[$EUR ]#,##0.00_-';
95 
101  private static $_builtInFormats;
102 
108  private static $_flippedBuiltInFormats;
109 
115  private $_formatCode;
116 
122  private $_builtInFormatCode;
123 
129  private $_parentPropertyName;
130 
136  private $_isSupervisor;
137 
143  private $_parent;
144 
148  public function __construct($isSupervisor = false)
149  {
150  // Supervisor?
151  $this->_isSupervisor = $isSupervisor;
152 
153  // Initialise values
154  $this->_formatCode = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
155  $this->_builtInFormatCode = 0;
156  }
157 
164  public function bindParent($parent)
165  {
166  $this->_parent = $parent;
167  }
168 
174  public function getIsSupervisor()
175  {
176  return $this->_isSupervisor;
177  }
178 
185  public function getSharedComponent()
186  {
187  return $this->_parent->getSharedComponent()->getNumberFormat();
188  }
189 
195  public function getActiveSheet()
196  {
197  return $this->_parent->getActiveSheet();
198  }
199 
206  public function getXSelectedCells()
207  {
208  return $this->getActiveSheet()->getXSelectedCells();
209  }
210 
217  public function getXActiveCell()
218  {
219  return $this->getActiveSheet()->getXActiveCell();
220  }
221 
228  public function getStyleArray($array)
229  {
230  return array('numberformat' => $array);
231  }
232 
248  public function applyFromArray($pStyles = null) {
249  if (is_array($pStyles)) {
250  if ($this->_isSupervisor) {
251  $this->getActiveSheet()->getStyle($this->getXSelectedCells())->applyFromArray($this->getStyleArray($pStyles));
252  } else {
253  if (array_key_exists('code', $pStyles)) {
254  $this->setFormatCode($pStyles['code']);
255  }
256  }
257  } else {
258  throw new Exception("Invalid style array passed.");
259  }
260  return $this;
261  }
262 
268  public function getFormatCode() {
269  if ($this->_isSupervisor) {
270  return $this->getSharedComponent()->getFormatCode();
271  }
272  if ($this->_builtInFormatCode !== false)
273  {
274  return self::builtInFormatCode($this->_builtInFormatCode);
275  }
276  return $this->_formatCode;
277  }
278 
285  public function setFormatCode($pValue = PHPExcel_Style_NumberFormat::FORMAT_GENERAL) {
286  if ($pValue == '') {
287  $pValue = PHPExcel_Style_NumberFormat::FORMAT_GENERAL;
288  }
289  if ($this->_isSupervisor) {
290  $styleArray = $this->getStyleArray(array('code' => $pValue));
291  $this->getActiveSheet()->getStyle($this->getXSelectedCells())->applyFromArray($styleArray);
292  } else {
293  $this->_formatCode = $pValue;
294  $this->_builtInFormatCode = self::builtInFormatCodeIndex($pValue);
295  }
296  return $this;
297  }
298 
304  public function getBuiltInFormatCode() {
305  if ($this->_isSupervisor) {
306  return $this->getSharedComponent()->getBuiltInFormatCode();
307  }
308  return $this->_builtInFormatCode;
309  }
310 
317  public function setBuiltInFormatCode($pValue = 0) {
318 
319  if ($this->_isSupervisor) {
320  $styleArray = $this->getStyleArray(array('code' => self::builtInFormatCode($pValue)));
321  $this->getActiveSheet()->getStyle($this->getXSelectedCells())->applyFromArray($styleArray);
322  } else {
323  $this->_builtInFormatCode = $pValue;
324  $this->_formatCode = self::builtInFormatCode($pValue);
325  }
326  return $this;
327  }
328 
332  private static function fillBuiltInFormatCodes()
333  {
334  // Built-in format codes
335  if (is_null(self::$_builtInFormats)) {
336  self::$_builtInFormats = array();
337 
338  // General
339  self::$_builtInFormats[0] = 'General';
340  self::$_builtInFormats[1] = '0';
341  self::$_builtInFormats[2] = '0.00';
342  self::$_builtInFormats[3] = '#,##0';
343  self::$_builtInFormats[4] = '#,##0.00';
344 
345  self::$_builtInFormats[9] = '0%';
346  self::$_builtInFormats[10] = '0.00%';
347  self::$_builtInFormats[11] = '0.00E+00';
348  self::$_builtInFormats[12] = '# ?/?';
349  self::$_builtInFormats[13] = '# ??/??';
350  self::$_builtInFormats[14] = 'mm-dd-yy';
351  self::$_builtInFormats[15] = 'd-mmm-yy';
352  self::$_builtInFormats[16] = 'd-mmm';
353  self::$_builtInFormats[17] = 'mmm-yy';
354  self::$_builtInFormats[18] = 'h:mm AM/PM';
355  self::$_builtInFormats[19] = 'h:mm:ss AM/PM';
356  self::$_builtInFormats[20] = 'h:mm';
357  self::$_builtInFormats[21] = 'h:mm:ss';
358  self::$_builtInFormats[22] = 'm/d/yy h:mm';
359 
360  self::$_builtInFormats[37] = '#,##0 ;(#,##0)';
361  self::$_builtInFormats[38] = '#,##0 ;[Red](#,##0)';
362  self::$_builtInFormats[39] = '#,##0.00;(#,##0.00)';
363  self::$_builtInFormats[40] = '#,##0.00;[Red](#,##0.00)';
364 
365  self::$_builtInFormats[44] = '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)';
366  self::$_builtInFormats[45] = 'mm:ss';
367  self::$_builtInFormats[46] = '[h]:mm:ss';
368  self::$_builtInFormats[47] = 'mmss.0';
369  self::$_builtInFormats[48] = '##0.0E+0';
370  self::$_builtInFormats[49] = '@';
371 
372  // CHT
373  self::$_builtInFormats[27] = '[$-404]e/m/d';
374  self::$_builtInFormats[30] = 'm/d/yy';
375  self::$_builtInFormats[36] = '[$-404]e/m/d';
376  self::$_builtInFormats[50] = '[$-404]e/m/d';
377  self::$_builtInFormats[57] = '[$-404]e/m/d';
378 
379  // THA
380  self::$_builtInFormats[59] = 't0';
381  self::$_builtInFormats[60] = 't0.00';
382  self::$_builtInFormats[61] = 't#,##0';
383  self::$_builtInFormats[62] = 't#,##0.00';
384  self::$_builtInFormats[67] = 't0%';
385  self::$_builtInFormats[68] = 't0.00%';
386  self::$_builtInFormats[69] = 't# ?/?';
387  self::$_builtInFormats[70] = 't# ??/??';
388 
389  // Flip array (for faster lookups)
390  self::$_flippedBuiltInFormats = array_flip(self::$_builtInFormats);
391  }
392  }
393 
400  public static function builtInFormatCode($pIndex) {
401  // Clean parameter
402  $pIndex = intval($pIndex);
403 
404  // Ensure built-in format codes are available
405  self::fillBuiltInFormatCodes();
406 
407  // Lookup format code
408  if (array_key_exists($pIndex, self::$_builtInFormats)) {
409  return self::$_builtInFormats[$pIndex];
410  }
411 
412  return '';
413  }
414 
421  public static function builtInFormatCodeIndex($formatCode) {
422  // Ensure built-in format codes are available
423  self::fillBuiltInFormatCodes();
424 
425  // Lookup format code
426  if (array_key_exists($formatCode, self::$_flippedBuiltInFormats)) {
427  return self::$_flippedBuiltInFormats[$formatCode];
428  }
429 
430  return false;
431  }
432 
438  public function getHashCode() {
439  if ($this->_isSupervisor) {
440  return $this->getSharedComponent()->getHashCode();
441  }
442  return md5(
443  $this->_formatCode
444  . $this->_builtInFormatCode
445  . __CLASS__
446  );
447  }
448 
454  private $_hashIndex;
455 
464  public function getHashIndex() {
465  return $this->_hashIndex;
466  }
467 
476  public function setHashIndex($value) {
477  $this->_hashIndex = $value;
478  }
479 
483  public function __clone() {
484  $vars = get_object_vars($this);
485  foreach ($vars as $key => $value) {
486  if (is_object($value)) {
487  $this->$key = clone $value;
488  } else {
489  $this->$key = $value;
490  }
491  }
492  }
493 
501  public static function toFormattedString($value = '', $format = '') {
502  // For now we do not treat strings although part 4 of a format code affects strings
503  if (!is_numeric($value)) return $value;
504 
505  // For 'General' format code, we just pass the value although this is not entirely the way Excel does it,
506  // it seems to round numbers to a total of 10 digits.
507  if ($format === 'General') {
508  return $value;
509  }
510 
511  // Get the parts, there can be up to four parts
512  $parts = explode(';', $format);
513 
514  // We should really fetch the relevant part depending on whether we have a positive number,
515  // negative number, zero, or text. But for now we just use first part
516  $format = $parts[0];
517 
518  if (preg_match("/^[hmsdy]/i", $format)) { // custom datetime format
519  // dvc: convert Excel formats to PHP date formats
520  // first remove escapes related to non-format characters
521 
522  // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case
523  $format = strtolower($format);
524 
525  $format = str_replace('\\', '', $format);
526 
527  // 4-digit year
528  $format = str_replace('yyyy', 'Y', $format);
529  // 2-digit year
530  $format = str_replace('yy', 'y', $format);
531  // first letter of month - no php equivalent
532  $format = str_replace('mmmmm', 'M', $format);
533  // full month name
534  $format = str_replace('mmmm', 'F', $format);
535  // short month name
536  $format = str_replace('mmm', 'M', $format);
537  // mm is minutes if time or month w/leading zero
538  $format = str_replace(':mm', ':i', $format);
539  // tmp place holder
540  $format = str_replace('mm', 'x', $format);
541  // month no leading zero
542  $format = str_replace('m', 'n', $format);
543  // month leading zero
544  $format = str_replace('x', 'm', $format);
545  // 12-hour suffix
546  $format = str_replace('am/pm', 'A', $format);
547  // full day of week name
548  $format = str_replace('dddd', 'l', $format);
549  // short day of week name
550  $format = str_replace('ddd', 'D', $format);
551  // tmp place holder
552  $format = str_replace('dd', 'x', $format);
553  // days no leading zero
554  $format = str_replace('d', 'j', $format);
555  // days leading zero
556  $format = str_replace('x', 'd', $format);
557  // seconds
558  $format = str_replace('ss', 's', $format);
559  // fractional seconds - no php equivalent
560  $format = str_replace('.s', '', $format);
561 
562  if (!strpos($format,'A')) { // 24-hour format
563  $format = str_replace('h', 'H', $format);
564  }
565 
566  return gmdate($format, PHPExcel_Shared_Date::ExcelToPHP($value));
567 
568  } else if (preg_match('/%$/', $format)) { // % number format
569  if ($format === self::FORMAT_PERCENTAGE) {
570  return round( (100 * $value), 0) . '%';
571  }
572  if (preg_match('/\.[#0]+/i', $format, $m)) {
573  $s = substr($m[0], 0, 1) . (strlen($m[0]) - 1);
574  $format = str_replace($m[0], $s, $format);
575  }
576  if (preg_match('/^[#0]+/', $format, $m)) {
577  $format = str_replace($m[0], strlen($m[0]), $format);
578  }
579  $format = '%' . str_replace('%', 'f%%', $format);
580 
581  return sprintf($format, 100 * $value);
582 
583  } else {
584  if (preg_match ("/^([0-9.,-]+)$/", $value)) {
585  if ($format === self::FORMAT_CURRENCY_EUR_SIMPLE) {
586  return 'EUR ' . sprintf('%1.2f', $value);
587 
588  } else {
589  // In Excel formats, "_" is used to add spacing, which we can't do in HTML
590  $format = preg_replace('/_./', '', $format);
591 
592  // Some non-number characters are escaped with \, which we don't need
593  $format = preg_replace("/\\\\/", '', $format);
594 
595  // Some non-number strings are quoted, so we'll get rid of the quotes
596  $format = preg_replace('/"/', '', $format);
597 
598  // TEMPORARY - Convert # to 0
599  $format = preg_replace('/\\#/', '0', $format);
600 
601  // Find out if we need thousands separator
602  $useThousands = preg_match('/,/', $format);
603  if ($useThousands) {
604  $format = preg_replace('/,/', '', $format);
605  }
606 
607  if (preg_match('/0?.*\?\/\?/', $format, $m)) {
608  //echo 'Format mask is fractional '.$format.' <br />';
609  $sign = ($value < 0) ? '-' : '';
610 
611  $integerPart = floor(abs($value));
612  $decimalPart = trim(fmod(abs($value),1),'0.');
613  $decimalLength = strlen($decimalPart);
614  $decimalDivisor = pow(10,$decimalLength);
615 
616  $GCD = PHPExcel_Calculation_Functions::GCD($decimalPart,$decimalDivisor);
617 
618  $adjustedDecimalPart = $decimalPart/$GCD;
619  $adjustedDecimalDivisor = $decimalDivisor/$GCD;
620 
621  if (strpos($format,'0') !== false) {
622  $value = "$sign$integerPart $adjustedDecimalPart/$adjustedDecimalDivisor";
623  } else {
624  $adjustedDecimalPart += $integerPart * $adjustedDecimalDivisor;
625  $value = "$sign$adjustedDecimalPart/$adjustedDecimalDivisor";
626  }
627 
628  } else {
629  // Handle the number itself
630  $number_regex = "/(\d+)(\.?)(\d*)/";
631  if (preg_match($number_regex, $format, $matches)) {
632  $left = $matches[1];
633  $dec = $matches[2];
634  $right = $matches[3];
635  if ($useThousands) {
636  $localeconv = localeconv();
637  if (($localeconv['thousands_sep'] == '') || ($localeconv['decimal_point'] == '')) {
638  $value = number_format($value, strlen($right), $localeconv['mon_decimal_point'], $localeconv['mon_thousands_sep']);
639  } else {
640  $value = number_format($value, strlen($right), $localeconv['decimal_point'], $localeconv['thousands_sep']);
641  }
642  } else {
643  $sprintf_pattern = "%1." . strlen($right) . "f";
644  $value = sprintf($sprintf_pattern, $value);
645  }
646  $value = preg_replace($number_regex, $value, $format);
647  }
648  }
649 
650  return $value;
651 
652  }
653  }
654 
655  return $value;
656  }
657  }
658 }