[Summary view]

   1  <?php
   2  
   3  declare(strict_types=1);
   4  
   5  namespace OpenSpout\Reader\XLSX\Helper;
   6  
   7  /**
   8   * @internal
   9   */
  10  final class DateFormatHelper
  11  {
  12      public const KEY_GENERAL = 'general';
  13      public const KEY_HOUR_12 = '12h';
  14      public const KEY_HOUR_24 = '24h';
  15  
  16      /**
  17       * This map is used to replace Excel format characters by their PHP equivalent.
  18       * Keys should be ordered from longest to smallest.
  19       * Mapping between Excel format characters and PHP format characters.
  20       */
  21      private const excelDateFormatToPHPDateFormatMapping = [
  22          self::KEY_GENERAL => [
  23              // Time
  24              'am/pm' => 'A',  // Uppercase Ante meridiem and Post meridiem
  25              ':mm' => ':i', // Minutes with leading zeros - if preceded by a ":" (otherwise month)
  26              'mm:' => 'i:', // Minutes with leading zeros - if followed by a ":" (otherwise month)
  27              'ss' => 's',  // Seconds, with leading zeros
  28              '.s' => '',   // Ignore (fractional seconds format does not exist in PHP)
  29  
  30              // Date
  31              'e' => 'Y',  // Full numeric representation of a year, 4 digits
  32              'yyyy' => 'Y',  // Full numeric representation of a year, 4 digits
  33              'yy' => 'y',  // Two digit representation of a year
  34              'mmmmm' => 'M',  // Short textual representation of a month, three letters ("mmmmm" should only contain the 1st letter...)
  35              'mmmm' => 'F',  // Full textual representation of a month
  36              'mmm' => 'M',  // Short textual representation of a month, three letters
  37              'mm' => 'm',  // Numeric representation of a month, with leading zeros
  38              'm' => 'n',  // Numeric representation of a month, without leading zeros
  39              'dddd' => 'l',  // Full textual representation of the day of the week
  40              'ddd' => 'D',  // Textual representation of a day, three letters
  41              'dd' => 'd',  // Day of the month, 2 digits with leading zeros
  42              'd' => 'j',  // Day of the month without leading zeros
  43          ],
  44          self::KEY_HOUR_12 => [
  45              'hh' => 'h',  // 12-hour format of an hour without leading zeros
  46              'h' => 'g',  // 12-hour format of an hour without leading zeros
  47          ],
  48          self::KEY_HOUR_24 => [
  49              'hh' => 'H',  // 24-hour hours with leading zero
  50              'h' => 'G',  // 24-hour format of an hour without leading zeros
  51          ],
  52      ];
  53  
  54      /**
  55       * Converts the given Excel date format to a format understandable by the PHP date function.
  56       *
  57       * @param string $excelDateFormat Excel date format
  58       *
  59       * @return string PHP date format (as defined here: http://php.net/manual/en/function.date.php)
  60       */
  61      public static function toPHPDateFormat(string $excelDateFormat): string
  62      {
  63          // Remove brackets potentially present at the beginning of the format string
  64          // and text portion of the format at the end of it (starting with ";")
  65          // See §18.8.31 of ECMA-376 for more detail.
  66          $dateFormat = preg_replace('/^(?:\[\$[^\]]+?\])?([^;]*).*/', '$1', $excelDateFormat);
  67          \assert(null !== $dateFormat);
  68  
  69          // Double quotes are used to escape characters that must not be interpreted.
  70          // For instance, ["Day " dd] should result in "Day 13" and we should not try to interpret "D", "a", "y"
  71          // By exploding the format string using double quote as a delimiter, we can get all parts
  72          // that must be transformed (even indexes) and all parts that must not be (odd indexes).
  73          $dateFormatParts = explode('"', $dateFormat);
  74  
  75          foreach ($dateFormatParts as $partIndex => $dateFormatPart) {
  76              // do not look at odd indexes
  77              if (1 === $partIndex % 2) {
  78                  continue;
  79              }
  80  
  81              // Make sure all characters are lowercase, as the mapping table is using lowercase characters
  82              $transformedPart = strtolower($dateFormatPart);
  83  
  84              // Remove escapes related to non-format characters
  85              $transformedPart = str_replace('\\', '', $transformedPart);
  86  
  87              // Apply general transformation first...
  88              $transformedPart = strtr($transformedPart, self::excelDateFormatToPHPDateFormatMapping[self::KEY_GENERAL]);
  89  
  90              // ... then apply hour transformation, for 12-hour or 24-hour format
  91              if (self::has12HourFormatMarker($dateFormatPart)) {
  92                  $transformedPart = strtr($transformedPart, self::excelDateFormatToPHPDateFormatMapping[self::KEY_HOUR_12]);
  93              } else {
  94                  $transformedPart = strtr($transformedPart, self::excelDateFormatToPHPDateFormatMapping[self::KEY_HOUR_24]);
  95              }
  96  
  97              // overwrite the parts array with the new transformed part
  98              $dateFormatParts[$partIndex] = $transformedPart;
  99          }
 100  
 101          // Merge all transformed parts back together
 102          $phpDateFormat = implode('"', $dateFormatParts);
 103  
 104          // Finally, to have the date format compatible with the DateTime::format() function, we need to escape
 105          // all characters that are inside double quotes (and double quotes must be removed).
 106          // For instance, ["Day " dd] should become [\D\a\y\ dd]
 107          return preg_replace_callback('/"(.+?)"/', static function ($matches): string {
 108              $stringToEscape = $matches[1];
 109              $letters = preg_split('//u', $stringToEscape, -1, PREG_SPLIT_NO_EMPTY);
 110              \assert(false !== $letters);
 111  
 112              return '\\'.implode('\\', $letters);
 113          }, $phpDateFormat);
 114      }
 115  
 116      /**
 117       * @param string $excelDateFormat Date format as defined by Excel
 118       *
 119       * @return bool Whether the given date format has the 12-hour format marker
 120       */
 121      private static function has12HourFormatMarker(string $excelDateFormat): bool
 122      {
 123          return false !== stripos($excelDateFormat, 'am/pm');
 124      }
 125  }