[Summary view]

Differences Between: [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Style\NumberFormat;
   4  
   5  use PhpOffice\PhpSpreadsheet\Shared\Date;
   6  
   7  class DateFormatter
   8  {
   9      /**
  10       * Search/replace values to convert Excel date/time format masks to PHP format masks.
  11       */
  12      private const DATE_FORMAT_REPLACEMENTS = [
  13          // first remove escapes related to non-format characters
  14          '\\' => '',
  15          //    12-hour suffix
  16          'am/pm' => 'A',
  17          //    4-digit year
  18          'e' => 'Y',
  19          'yyyy' => 'Y',
  20          //    2-digit year
  21          'yy' => 'y',
  22          //    first letter of month - no php equivalent
  23          'mmmmm' => 'M',
  24          //    full month name
  25          'mmmm' => 'F',
  26          //    short month name
  27          'mmm' => 'M',
  28          //    mm is minutes if time, but can also be month w/leading zero
  29          //    so we try to identify times be the inclusion of a : separator in the mask
  30          //    It isn't perfect, but the best way I know how
  31          ':mm' => ':i',
  32          'mm:' => 'i:',
  33          //    full day of week name
  34          'dddd' => 'l',
  35          //    short day of week name
  36          'ddd' => 'D',
  37          //    days leading zero
  38          'dd' => 'd',
  39          //    days no leading zero
  40          'd' => 'j',
  41          //    fractional seconds - no php equivalent
  42          '.s' => '',
  43      ];
  44  
  45      /**
  46       * Search/replace values to convert Excel date/time format masks hours to PHP format masks (24 hr clock).
  47       */
  48      private const DATE_FORMAT_REPLACEMENTS24 = [
  49          'hh' => 'H',
  50          'h' => 'G',
  51          //    month leading zero
  52          'mm' => 'm',
  53          //    month no leading zero
  54          'm' => 'n',
  55          //    seconds
  56          'ss' => 's',
  57      ];
  58  
  59      /**
  60       * Search/replace values to convert Excel date/time format masks hours to PHP format masks (12 hr clock).
  61       */
  62      private const DATE_FORMAT_REPLACEMENTS12 = [
  63          'hh' => 'h',
  64          'h' => 'g',
  65          //    month leading zero
  66          'mm' => 'm',
  67          //    month no leading zero
  68          'm' => 'n',
  69          //    seconds
  70          'ss' => 's',
  71      ];
  72  
  73      private const HOURS_IN_DAY = 24;
  74      private const MINUTES_IN_DAY = 60 * self::HOURS_IN_DAY;
  75      private const SECONDS_IN_DAY = 60 * self::MINUTES_IN_DAY;
  76      private const INTERVAL_PRECISION = 10;
  77      private const INTERVAL_LEADING_ZERO = [
  78          '[hh]',
  79          '[mm]',
  80          '[ss]',
  81      ];
  82      private const INTERVAL_ROUND_PRECISION = [
  83          // hours and minutes truncate
  84          '[h]' => self::INTERVAL_PRECISION,
  85          '[hh]' => self::INTERVAL_PRECISION,
  86          '[m]' => self::INTERVAL_PRECISION,
  87          '[mm]' => self::INTERVAL_PRECISION,
  88          // seconds round
  89          '[s]' => 0,
  90          '[ss]' => 0,
  91      ];
  92      private const INTERVAL_MULTIPLIER = [
  93          '[h]' => self::HOURS_IN_DAY,
  94          '[hh]' => self::HOURS_IN_DAY,
  95          '[m]' => self::MINUTES_IN_DAY,
  96          '[mm]' => self::MINUTES_IN_DAY,
  97          '[s]' => self::SECONDS_IN_DAY,
  98          '[ss]' => self::SECONDS_IN_DAY,
  99      ];
 100  
 101      /** @param mixed $value */
 102      private static function tryInterval(bool &$seekingBracket, string &$block, $value, string $format): void
 103      {
 104          if ($seekingBracket) {
 105              if (false !== strpos($block, $format)) {
 106                  $hours = (string) (int) round(
 107                      self::INTERVAL_MULTIPLIER[$format] * $value,
 108                      self::INTERVAL_ROUND_PRECISION[$format]
 109                  );
 110                  if (strlen($hours) === 1 && in_array($format, self::INTERVAL_LEADING_ZERO, true)) {
 111                      $hours = "0$hours";
 112                  }
 113                  $block = str_replace($format, $hours, $block);
 114                  $seekingBracket = false;
 115              }
 116          }
 117      }
 118  
 119      /** @param mixed $value */
 120      public static function format($value, string $format): string
 121      {
 122          // strip off first part containing e.g. [$-F800] or [$USD-409]
 123          // general syntax: [$<Currency string>-<language info>]
 124          // language info is in hexadecimal
 125          // strip off chinese part like [DBNum1][$-804]
 126          $format = (string) preg_replace('/^(\[DBNum\d\])*(\[\$[^\]]*\])/i', '', $format);
 127  
 128          // OpenOffice.org uses upper-case number formats, e.g. 'YYYY', convert to lower-case;
 129          //    but we don't want to change any quoted strings
 130          /** @var callable */
 131          $callable = [self::class, 'setLowercaseCallback'];
 132          $format = (string) preg_replace_callback('/(?:^|")([^"]*)(?:$|")/', $callable, $format);
 133  
 134          // Only process the non-quoted blocks for date format characters
 135  
 136          $blocks = explode('"', $format);
 137          foreach ($blocks as $key => &$block) {
 138              if ($key % 2 == 0) {
 139                  $block = strtr($block, self::DATE_FORMAT_REPLACEMENTS);
 140                  if (!strpos($block, 'A')) {
 141                      // 24-hour time format
 142                      // when [h]:mm format, the [h] should replace to the hours of the value * 24
 143                      $seekingBracket = true;
 144                      self::tryInterval($seekingBracket, $block, $value, '[h]');
 145                      self::tryInterval($seekingBracket, $block, $value, '[hh]');
 146                      self::tryInterval($seekingBracket, $block, $value, '[mm]');
 147                      self::tryInterval($seekingBracket, $block, $value, '[m]');
 148                      self::tryInterval($seekingBracket, $block, $value, '[s]');
 149                      self::tryInterval($seekingBracket, $block, $value, '[ss]');
 150                      $block = strtr($block, self::DATE_FORMAT_REPLACEMENTS24);
 151                  } else {
 152                      // 12-hour time format
 153                      $block = strtr($block, self::DATE_FORMAT_REPLACEMENTS12);
 154                  }
 155              }
 156          }
 157          $format = implode('"', $blocks);
 158  
 159          // escape any quoted characters so that DateTime format() will render them correctly
 160          /** @var callable */
 161          $callback = [self::class, 'escapeQuotesCallback'];
 162          $format = (string) preg_replace_callback('/"(.*)"/U', $callback, $format);
 163  
 164          $dateObj = Date::excelToDateTimeObject($value);
 165          // If the colon preceding minute had been quoted, as happens in
 166          // Excel 2003 XML formats, m will not have been changed to i above.
 167          // Change it now.
 168          $format = (string) \preg_replace('/\\\\:m/', ':i', $format);
 169  
 170          return $dateObj->format($format);
 171      }
 172  
 173      private static function setLowercaseCallback(array $matches): string
 174      {
 175          return mb_strtolower($matches[0]);
 176      }
 177  
 178      private static function escapeQuotesCallback(array $matches): string
 179      {
 180          return '\\' . implode('\\', str_split($matches[1]));
 181      }
 182  }