[Summary view]

Differences Between: [Versions 310 and 311] [Versions 310 and 400] [Versions 310 and 401]

   1  <?php
   2  
   3  namespace Box\Spout\Reader\XLSX\Helper;
   4  
   5  /**
   6   * Class DateFormatHelper
   7   * This class provides helper functions to format Excel dates
   8   */
   9  class DateFormatHelper
  10  {
  11      const KEY_GENERAL = 'general';
  12      const KEY_HOUR_12 = '12h';
  13      const KEY_HOUR_24 = '24h';
  14  
  15      /**
  16       * This map is used to replace Excel format characters by their PHP equivalent.
  17       * Keys should be ordered from longest to smallest.
  18       *
  19       * @var array Mapping between Excel format characters and PHP format characters
  20       */
  21      private static $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       * @return string PHP date format (as defined here: http://php.net/manual/en/function.date.php)
  59       */
  60      public static function toPHPDateFormat($excelDateFormat)
  61      {
  62          // Remove brackets potentially present at the beginning of the format string
  63          // and text portion of the format at the end of it (starting with ";")
  64          // See §18.8.31 of ECMA-376 for more detail.
  65          $dateFormat = preg_replace('/^(?:\[\$[^\]]+?\])?([^;]*).*/', '$1', $excelDateFormat);
  66  
  67          // Double quotes are used to escape characters that must not be interpreted.
  68          // For instance, ["Day " dd] should result in "Day 13" and we should not try to interpret "D", "a", "y"
  69          // By exploding the format string using double quote as a delimiter, we can get all parts
  70          // that must be transformed (even indexes) and all parts that must not be (odd indexes).
  71          $dateFormatParts = explode('"', $dateFormat);
  72  
  73          foreach ($dateFormatParts as $partIndex => $dateFormatPart) {
  74              // do not look at odd indexes
  75              if ($partIndex % 2 === 1) {
  76                  continue;
  77              }
  78  
  79              // Make sure all characters are lowercase, as the mapping table is using lowercase characters
  80              $transformedPart = strtolower($dateFormatPart);
  81  
  82              // Remove escapes related to non-format characters
  83              $transformedPart = str_replace('\\', '', $transformedPart);
  84  
  85              // Apply general transformation first...
  86              $transformedPart = strtr($transformedPart, self::$excelDateFormatToPHPDateFormatMapping[self::KEY_GENERAL]);
  87  
  88              // ... then apply hour transformation, for 12-hour or 24-hour format
  89              if (self::has12HourFormatMarker($dateFormatPart)) {
  90                  $transformedPart = strtr($transformedPart, self::$excelDateFormatToPHPDateFormatMapping[self::KEY_HOUR_12]);
  91              } else {
  92                  $transformedPart = strtr($transformedPart, self::$excelDateFormatToPHPDateFormatMapping[self::KEY_HOUR_24]);
  93              }
  94  
  95              // overwrite the parts array with the new transformed part
  96              $dateFormatParts[$partIndex] = $transformedPart;
  97          }
  98  
  99          // Merge all transformed parts back together
 100          $phpDateFormat = implode('"', $dateFormatParts);
 101  
 102          // Finally, to have the date format compatible with the DateTime::format() function, we need to escape
 103          // all characters that are inside double quotes (and double quotes must be removed).
 104          // For instance, ["Day " dd] should become [\D\a\y\ dd]
 105          $phpDateFormat = preg_replace_callback('/"(.+?)"/', function ($matches) {
 106              $stringToEscape = $matches[1];
 107              $letters = preg_split('//u', $stringToEscape, -1, PREG_SPLIT_NO_EMPTY);
 108  
 109              return '\\' . implode('\\', $letters);
 110          }, $phpDateFormat);
 111  
 112          return $phpDateFormat;
 113      }
 114  
 115      /**
 116       * @param string $excelDateFormat Date format as defined by Excel
 117       * @return bool Whether the given date format has the 12-hour format marker
 118       */
 119      private static function has12HourFormatMarker($excelDateFormat)
 120      {
 121          return (stripos($excelDateFormat, 'am/pm') !== false);
 122      }
 123  }