[Summary view]

Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Style;
   4  
   5  class NumberFormat extends Supervisor
   6  {
   7      // Pre-defined formats
   8      const FORMAT_GENERAL = 'General';
   9  
  10      const FORMAT_TEXT = '@';
  11  
  12      const FORMAT_NUMBER = '0';
  13      const FORMAT_NUMBER_0 = '0.0';
  14      const FORMAT_NUMBER_00 = '0.00';
  15      const FORMAT_NUMBER_COMMA_SEPARATED1 = '#,##0.00';
  16      const FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-';
  17  
  18      const FORMAT_PERCENTAGE = '0%';
  19      const FORMAT_PERCENTAGE_0 = '0.0%';
  20      const FORMAT_PERCENTAGE_00 = '0.00%';
  21  
  22      const FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd';
  23      const FORMAT_DATE_YYYYMMDD = 'yyyy-mm-dd';
  24      const FORMAT_DATE_DDMMYYYY = 'dd/mm/yyyy';
  25      const FORMAT_DATE_DMYSLASH = 'd/m/yy';
  26      const FORMAT_DATE_DMYMINUS = 'd-m-yy';
  27      const FORMAT_DATE_DMMINUS = 'd-m';
  28      const FORMAT_DATE_MYMINUS = 'm-yy';
  29      const FORMAT_DATE_XLSX14 = 'mm-dd-yy';
  30      const FORMAT_DATE_XLSX15 = 'd-mmm-yy';
  31      const FORMAT_DATE_XLSX16 = 'd-mmm';
  32      const FORMAT_DATE_XLSX17 = 'mmm-yy';
  33      const FORMAT_DATE_XLSX22 = 'm/d/yy h:mm';
  34      const FORMAT_DATE_DATETIME = 'd/m/yy h:mm';
  35      const FORMAT_DATE_TIME1 = 'h:mm AM/PM';
  36      const FORMAT_DATE_TIME2 = 'h:mm:ss AM/PM';
  37      const FORMAT_DATE_TIME3 = 'h:mm';
  38      const FORMAT_DATE_TIME4 = 'h:mm:ss';
  39      const FORMAT_DATE_TIME5 = 'mm:ss';
  40      const FORMAT_DATE_TIME6 = 'h:mm:ss';
  41      const FORMAT_DATE_TIME7 = 'i:s.S';
  42      const FORMAT_DATE_TIME8 = 'h:mm:ss;@';
  43      const FORMAT_DATE_YYYYMMDDSLASH = 'yyyy/mm/dd;@';
  44  
  45      const FORMAT_CURRENCY_USD_SIMPLE = '"$"#,##0.00_-';
  46      const FORMAT_CURRENCY_USD = '$#,##0_-';
  47      const FORMAT_CURRENCY_EUR_SIMPLE = '#,##0.00_-"€"';
  48      const FORMAT_CURRENCY_EUR = '#,##0_-"€"';
  49      const FORMAT_ACCOUNTING_USD = '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)';
  50      const FORMAT_ACCOUNTING_EUR = '_("€"* #,##0.00_);_("€"* \(#,##0.00\);_("€"* "-"??_);_(@_)';
  51  
  52      /**
  53       * Excel built-in number formats.
  54       *
  55       * @var array
  56       */
  57      protected static $builtInFormats;
  58  
  59      /**
  60       * Excel built-in number formats (flipped, for faster lookups).
  61       *
  62       * @var array
  63       */
  64      protected static $flippedBuiltInFormats;
  65  
  66      /**
  67       * Format Code.
  68       *
  69       * @var null|string
  70       */
  71      protected $formatCode = self::FORMAT_GENERAL;
  72  
  73      /**
  74       * Built-in format Code.
  75       *
  76       * @var false|int
  77       */
  78      protected $builtInFormatCode = 0;
  79  
  80      /**
  81       * Create a new NumberFormat.
  82       *
  83       * @param bool $isSupervisor Flag indicating if this is a supervisor or not
  84       *                                    Leave this value at default unless you understand exactly what
  85       *                                        its ramifications are
  86       * @param bool $isConditional Flag indicating if this is a conditional style or not
  87       *                                    Leave this value at default unless you understand exactly what
  88       *                                        its ramifications are
  89       */
  90      public function __construct($isSupervisor = false, $isConditional = false)
  91      {
  92          // Supervisor?
  93          parent::__construct($isSupervisor);
  94  
  95          if ($isConditional) {
  96              $this->formatCode = null;
  97              $this->builtInFormatCode = false;
  98          }
  99      }
 100  
 101      /**
 102       * Get the shared style component for the currently active cell in currently active sheet.
 103       * Only used for style supervisor.
 104       *
 105       * @return NumberFormat
 106       */
 107      public function getSharedComponent()
 108      {
 109          /** @var Style */
 110          $parent = $this->parent;
 111  
 112          return $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 $styleArray Array containing style information
 139       *
 140       * @return $this
 141       */
 142      public function applyFromArray(array $styleArray)
 143      {
 144          if ($this->isSupervisor) {
 145              $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($this->getStyleArray($styleArray));
 146          } else {
 147              if (isset($styleArray['formatCode'])) {
 148                  $this->setFormatCode($styleArray['formatCode']);
 149              }
 150          }
 151  
 152          return $this;
 153      }
 154  
 155      /**
 156       * Get Format Code.
 157       *
 158       * @return null|string
 159       */
 160      public function getFormatCode()
 161      {
 162          if ($this->isSupervisor) {
 163              return $this->getSharedComponent()->getFormatCode();
 164          }
 165          if (is_int($this->builtInFormatCode)) {
 166              return self::builtInFormatCode($this->builtInFormatCode);
 167          }
 168  
 169          return $this->formatCode;
 170      }
 171  
 172      /**
 173       * Set Format Code.
 174       *
 175       * @param string $formatCode see self::FORMAT_*
 176       *
 177       * @return $this
 178       */
 179      public function setFormatCode($formatCode)
 180      {
 181          if ($formatCode == '') {
 182              $formatCode = self::FORMAT_GENERAL;
 183          }
 184          if ($this->isSupervisor) {
 185              $styleArray = $this->getStyleArray(['formatCode' => $formatCode]);
 186              $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray);
 187          } else {
 188              $this->formatCode = $formatCode;
 189              $this->builtInFormatCode = self::builtInFormatCodeIndex($formatCode);
 190          }
 191  
 192          return $this;
 193      }
 194  
 195      /**
 196       * Get Built-In Format Code.
 197       *
 198       * @return false|int
 199       */
 200      public function getBuiltInFormatCode()
 201      {
 202          if ($this->isSupervisor) {
 203              return $this->getSharedComponent()->getBuiltInFormatCode();
 204          }
 205  
 206          return $this->builtInFormatCode;
 207      }
 208  
 209      /**
 210       * Set Built-In Format Code.
 211       *
 212       * @param int $formatCodeIndex
 213       *
 214       * @return $this
 215       */
 216      public function setBuiltInFormatCode($formatCodeIndex)
 217      {
 218          if ($this->isSupervisor) {
 219              $styleArray = $this->getStyleArray(['formatCode' => self::builtInFormatCode($formatCodeIndex)]);
 220              $this->getActiveSheet()->getStyle($this->getSelectedCells())->applyFromArray($styleArray);
 221          } else {
 222              $this->builtInFormatCode = $formatCodeIndex;
 223              $this->formatCode = self::builtInFormatCode($formatCodeIndex);
 224          }
 225  
 226          return $this;
 227      }
 228  
 229      /**
 230       * Fill built-in format codes.
 231       */
 232      private static function fillBuiltInFormatCodes(): void
 233      {
 234          //  [MS-OI29500: Microsoft Office Implementation Information for ISO/IEC-29500 Standard Compliance]
 235          //  18.8.30. numFmt (Number Format)
 236          //
 237          //  The ECMA standard defines built-in format IDs
 238          //      14: "mm-dd-yy"
 239          //      22: "m/d/yy h:mm"
 240          //      37: "#,##0 ;(#,##0)"
 241          //      38: "#,##0 ;[Red](#,##0)"
 242          //      39: "#,##0.00;(#,##0.00)"
 243          //      40: "#,##0.00;[Red](#,##0.00)"
 244          //      47: "mmss.0"
 245          //      KOR fmt 55: "yyyy-mm-dd"
 246          //  Excel defines built-in format IDs
 247          //      14: "m/d/yyyy"
 248          //      22: "m/d/yyyy h:mm"
 249          //      37: "#,##0_);(#,##0)"
 250          //      38: "#,##0_);[Red](#,##0)"
 251          //      39: "#,##0.00_);(#,##0.00)"
 252          //      40: "#,##0.00_);[Red](#,##0.00)"
 253          //      47: "mm:ss.0"
 254          //      KOR fmt 55: "yyyy/mm/dd"
 255  
 256          // Built-in format codes
 257          if (self::$builtInFormats === null) {
 258              self::$builtInFormats = [];
 259  
 260              // General
 261              self::$builtInFormats[0] = self::FORMAT_GENERAL;
 262              self::$builtInFormats[1] = '0';
 263              self::$builtInFormats[2] = '0.00';
 264              self::$builtInFormats[3] = '#,##0';
 265              self::$builtInFormats[4] = '#,##0.00';
 266  
 267              self::$builtInFormats[9] = '0%';
 268              self::$builtInFormats[10] = '0.00%';
 269              self::$builtInFormats[11] = '0.00E+00';
 270              self::$builtInFormats[12] = '# ?/?';
 271              self::$builtInFormats[13] = '# ??/??';
 272              self::$builtInFormats[14] = 'm/d/yyyy'; // Despite ECMA 'mm-dd-yy';
 273              self::$builtInFormats[15] = 'd-mmm-yy';
 274              self::$builtInFormats[16] = 'd-mmm';
 275              self::$builtInFormats[17] = 'mmm-yy';
 276              self::$builtInFormats[18] = 'h:mm AM/PM';
 277              self::$builtInFormats[19] = 'h:mm:ss AM/PM';
 278              self::$builtInFormats[20] = 'h:mm';
 279              self::$builtInFormats[21] = 'h:mm:ss';
 280              self::$builtInFormats[22] = 'm/d/yyyy h:mm'; // Despite ECMA 'm/d/yy h:mm';
 281  
 282              self::$builtInFormats[37] = '#,##0_);(#,##0)'; //  Despite ECMA '#,##0 ;(#,##0)';
 283              self::$builtInFormats[38] = '#,##0_);[Red](#,##0)'; //  Despite ECMA '#,##0 ;[Red](#,##0)';
 284              self::$builtInFormats[39] = '#,##0.00_);(#,##0.00)'; //  Despite ECMA '#,##0.00;(#,##0.00)';
 285              self::$builtInFormats[40] = '#,##0.00_);[Red](#,##0.00)'; //  Despite ECMA '#,##0.00;[Red](#,##0.00)';
 286  
 287              self::$builtInFormats[44] = '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)';
 288              self::$builtInFormats[45] = 'mm:ss';
 289              self::$builtInFormats[46] = '[h]:mm:ss';
 290              self::$builtInFormats[47] = 'mm:ss.0'; //  Despite ECMA 'mmss.0';
 291              self::$builtInFormats[48] = '##0.0E+0';
 292              self::$builtInFormats[49] = '@';
 293  
 294              // CHT
 295              self::$builtInFormats[27] = '[$-404]e/m/d';
 296              self::$builtInFormats[30] = 'm/d/yy';
 297              self::$builtInFormats[36] = '[$-404]e/m/d';
 298              self::$builtInFormats[50] = '[$-404]e/m/d';
 299              self::$builtInFormats[57] = '[$-404]e/m/d';
 300  
 301              // THA
 302              self::$builtInFormats[59] = 't0';
 303              self::$builtInFormats[60] = 't0.00';
 304              self::$builtInFormats[61] = 't#,##0';
 305              self::$builtInFormats[62] = 't#,##0.00';
 306              self::$builtInFormats[67] = 't0%';
 307              self::$builtInFormats[68] = 't0.00%';
 308              self::$builtInFormats[69] = 't# ?/?';
 309              self::$builtInFormats[70] = 't# ??/??';
 310  
 311              // JPN
 312              self::$builtInFormats[28] = '[$-411]ggge"年"m"月"d"日"';
 313              self::$builtInFormats[29] = '[$-411]ggge"年"m"月"d"日"';
 314              self::$builtInFormats[31] = 'yyyy"年"m"月"d"日"';
 315              self::$builtInFormats[32] = 'h"時"mm"分"';
 316              self::$builtInFormats[33] = 'h"時"mm"分"ss"秒"';
 317              self::$builtInFormats[34] = 'yyyy"年"m"月"';
 318              self::$builtInFormats[35] = 'm"月"d"日"';
 319              self::$builtInFormats[51] = '[$-411]ggge"年"m"月"d"日"';
 320              self::$builtInFormats[52] = 'yyyy"年"m"月"';
 321              self::$builtInFormats[53] = 'm"月"d"日"';
 322              self::$builtInFormats[54] = '[$-411]ggge"年"m"月"d"日"';
 323              self::$builtInFormats[55] = 'yyyy"年"m"月"';
 324              self::$builtInFormats[56] = 'm"月"d"日"';
 325              self::$builtInFormats[58] = '[$-411]ggge"年"m"月"d"日"';
 326  
 327              // Flip array (for faster lookups)
 328              self::$flippedBuiltInFormats = array_flip(self::$builtInFormats);
 329          }
 330      }
 331  
 332      /**
 333       * Get built-in format code.
 334       *
 335       * @param int $index
 336       *
 337       * @return string
 338       */
 339      public static function builtInFormatCode($index)
 340      {
 341          // Clean parameter
 342          $index = (int) $index;
 343  
 344          // Ensure built-in format codes are available
 345          self::fillBuiltInFormatCodes();
 346  
 347          // Lookup format code
 348          if (isset(self::$builtInFormats[$index])) {
 349              return self::$builtInFormats[$index];
 350          }
 351  
 352          return '';
 353      }
 354  
 355      /**
 356       * Get built-in format code index.
 357       *
 358       * @param string $formatCodeIndex
 359       *
 360       * @return false|int
 361       */
 362      public static function builtInFormatCodeIndex($formatCodeIndex)
 363      {
 364          // Ensure built-in format codes are available
 365          self::fillBuiltInFormatCodes();
 366  
 367          // Lookup format code
 368          if (array_key_exists($formatCodeIndex, self::$flippedBuiltInFormats)) {
 369              return self::$flippedBuiltInFormats[$formatCodeIndex];
 370          }
 371  
 372          return false;
 373      }
 374  
 375      /**
 376       * Get hash code.
 377       *
 378       * @return string Hash code
 379       */
 380      public function getHashCode()
 381      {
 382          if ($this->isSupervisor) {
 383              return $this->getSharedComponent()->getHashCode();
 384          }
 385  
 386          return md5(
 387              $this->formatCode .
 388              $this->builtInFormatCode .
 389              __CLASS__
 390          );
 391      }
 392  
 393      /**
 394       * Convert a value in a pre-defined format to a PHP string.
 395       *
 396       * @param mixed $value Value to format
 397       * @param string $format Format code, see = self::FORMAT_*
 398       * @param array $callBack Callback function for additional formatting of string
 399       *
 400       * @return string Formatted string
 401       */
 402      public static function toFormattedString($value, $format, $callBack = null)
 403      {
 404          return NumberFormat\Formatter::toFormattedString($value, $format, $callBack);
 405      }
 406  
 407      protected function exportArray1(): array
 408      {
 409          $exportedArray = [];
 410          $this->exportArray2($exportedArray, 'formatCode', $this->getFormatCode());
 411  
 412          return $exportedArray;
 413      }
 414  }