[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\Manager;
   4  
   5  use Box\Spout\Reader\XLSX\Creator\InternalEntityFactory;
   6  
   7  /**
   8   * Class StyleManager
   9   * This class manages XLSX styles
  10   */
  11  class StyleManager
  12  {
  13      /** Nodes used to find relevant information in the styles XML file */
  14      const XML_NODE_NUM_FMTS = 'numFmts';
  15      const XML_NODE_NUM_FMT = 'numFmt';
  16      const XML_NODE_CELL_XFS = 'cellXfs';
  17      const XML_NODE_XF = 'xf';
  18  
  19      /** Attributes used to find relevant information in the styles XML file */
  20      const XML_ATTRIBUTE_NUM_FMT_ID = 'numFmtId';
  21      const XML_ATTRIBUTE_FORMAT_CODE = 'formatCode';
  22      const XML_ATTRIBUTE_APPLY_NUMBER_FORMAT = 'applyNumberFormat';
  23  
  24      /** By convention, default style ID is 0 */
  25      const DEFAULT_STYLE_ID = 0;
  26  
  27      const NUMBER_FORMAT_GENERAL = 'General';
  28  
  29      /**
  30       * @see https://msdn.microsoft.com/en-us/library/ff529597(v=office.12).aspx
  31       * @var array Mapping between built-in numFmtId and the associated format - for dates only
  32       */
  33      protected static $builtinNumFmtIdToNumFormatMapping = [
  34          14 => 'm/d/yyyy', // @NOTE: ECMA spec is 'mm-dd-yy'
  35          15 => 'd-mmm-yy',
  36          16 => 'd-mmm',
  37          17 => 'mmm-yy',
  38          18 => 'h:mm AM/PM',
  39          19 => 'h:mm:ss AM/PM',
  40          20 => 'h:mm',
  41          21 => 'h:mm:ss',
  42          22 => 'm/d/yyyy h:mm', // @NOTE: ECMA spec is 'm/d/yy h:mm',
  43          45 => 'mm:ss',
  44          46 => '[h]:mm:ss',
  45          47 => 'mm:ss.0',  // @NOTE: ECMA spec is 'mmss.0',
  46      ];
  47  
  48      /** @var string Path of the XLSX file being read */
  49      protected $filePath;
  50  
  51      /** @var string Path of the styles XML file */
  52      protected $stylesXMLFilePath;
  53  
  54      /** @var InternalEntityFactory Factory to create entities */
  55      protected $entityFactory;
  56  
  57      /** @var array Array containing the IDs of built-in number formats indicating a date */
  58      protected $builtinNumFmtIdIndicatingDates;
  59  
  60      /** @var array Array containing a mapping NUM_FMT_ID => FORMAT_CODE */
  61      protected $customNumberFormats;
  62  
  63      /** @var array Array containing a mapping STYLE_ID => [STYLE_ATTRIBUTES] */
  64      protected $stylesAttributes;
  65  
  66      /** @var array Cache containing a mapping NUM_FMT_ID => IS_DATE_FORMAT. Used to avoid lots of recalculations */
  67      protected $numFmtIdToIsDateFormatCache = [];
  68  
  69      /**
  70       * @param string $filePath Path of the XLSX file being read
  71       * @param WorkbookRelationshipsManager $workbookRelationshipsManager Helps retrieving workbook relationships
  72       * @param InternalEntityFactory $entityFactory Factory to create entities
  73       */
  74      public function __construct($filePath, $workbookRelationshipsManager, $entityFactory)
  75      {
  76          $this->filePath = $filePath;
  77          $this->entityFactory = $entityFactory;
  78          $this->builtinNumFmtIdIndicatingDates = array_keys(self::$builtinNumFmtIdToNumFormatMapping);
  79          $this->stylesXMLFilePath = $workbookRelationshipsManager->getStylesXMLFilePath();
  80      }
  81  
  82      /**
  83       * Returns whether the style with the given ID should consider
  84       * numeric values as timestamps and format the cell as a date.
  85       *
  86       * @param int $styleId Zero-based style ID
  87       * @return bool Whether the cell with the given cell should display a date instead of a numeric value
  88       */
  89      public function shouldFormatNumericValueAsDate($styleId)
  90      {
  91          $stylesAttributes = $this->getStylesAttributes();
  92  
  93          // Default style (0) does not format numeric values as timestamps. Only custom styles do.
  94          // Also if the style ID does not exist in the styles.xml file, format as numeric value.
  95          // Using isset here because it is way faster than array_key_exists...
  96          if ($styleId === self::DEFAULT_STYLE_ID || !isset($stylesAttributes[$styleId])) {
  97              return false;
  98          }
  99  
 100          $styleAttributes = $stylesAttributes[$styleId];
 101  
 102          return $this->doesStyleIndicateDate($styleAttributes);
 103      }
 104  
 105      /**
 106       * Reads the styles.xml file and extract the relevant information from the file.
 107       *
 108       * @return void
 109       */
 110      protected function extractRelevantInfo()
 111      {
 112          $this->customNumberFormats = [];
 113          $this->stylesAttributes = [];
 114  
 115          $xmlReader = $this->entityFactory->createXMLReader();
 116  
 117          if ($xmlReader->openFileInZip($this->filePath, $this->stylesXMLFilePath)) {
 118              while ($xmlReader->read()) {
 119                  if ($xmlReader->isPositionedOnStartingNode(self::XML_NODE_NUM_FMTS)) {
 120                      $this->extractNumberFormats($xmlReader);
 121                  } elseif ($xmlReader->isPositionedOnStartingNode(self::XML_NODE_CELL_XFS)) {
 122                      $this->extractStyleAttributes($xmlReader);
 123                  }
 124              }
 125  
 126              $xmlReader->close();
 127          }
 128      }
 129  
 130      /**
 131       * Extracts number formats from the "numFmt" nodes.
 132       * For simplicity, the styles attributes are kept in memory. This is possible thanks
 133       * to the reuse of formats. So 1 million cells should not use 1 million formats.
 134       *
 135       * @param \Box\Spout\Reader\Wrapper\XMLReader $xmlReader XML Reader positioned on the "numFmts" node
 136       * @return void
 137       */
 138      protected function extractNumberFormats($xmlReader)
 139      {
 140          while ($xmlReader->read()) {
 141              if ($xmlReader->isPositionedOnStartingNode(self::XML_NODE_NUM_FMT)) {
 142                  $numFmtId = (int) ($xmlReader->getAttribute(self::XML_ATTRIBUTE_NUM_FMT_ID));
 143                  $formatCode = $xmlReader->getAttribute(self::XML_ATTRIBUTE_FORMAT_CODE);
 144                  $this->customNumberFormats[$numFmtId] = $formatCode;
 145              } elseif ($xmlReader->isPositionedOnEndingNode(self::XML_NODE_NUM_FMTS)) {
 146                  // Once done reading "numFmts" node's children
 147                  break;
 148              }
 149          }
 150      }
 151  
 152      /**
 153       * Extracts style attributes from the "xf" nodes, inside the "cellXfs" section.
 154       * For simplicity, the styles attributes are kept in memory. This is possible thanks
 155       * to the reuse of styles. So 1 million cells should not use 1 million styles.
 156       *
 157       * @param \Box\Spout\Reader\Wrapper\XMLReader $xmlReader XML Reader positioned on the "cellXfs" node
 158       * @return void
 159       */
 160      protected function extractStyleAttributes($xmlReader)
 161      {
 162          while ($xmlReader->read()) {
 163              if ($xmlReader->isPositionedOnStartingNode(self::XML_NODE_XF)) {
 164                  $numFmtId = $xmlReader->getAttribute(self::XML_ATTRIBUTE_NUM_FMT_ID);
 165                  $normalizedNumFmtId = ($numFmtId !== null) ? (int) $numFmtId : null;
 166  
 167                  $applyNumberFormat = $xmlReader->getAttribute(self::XML_ATTRIBUTE_APPLY_NUMBER_FORMAT);
 168                  $normalizedApplyNumberFormat = ($applyNumberFormat !== null) ? (bool) $applyNumberFormat : null;
 169  
 170                  $this->stylesAttributes[] = [
 171                      self::XML_ATTRIBUTE_NUM_FMT_ID          => $normalizedNumFmtId,
 172                      self::XML_ATTRIBUTE_APPLY_NUMBER_FORMAT => $normalizedApplyNumberFormat,
 173                  ];
 174              } elseif ($xmlReader->isPositionedOnEndingNode(self::XML_NODE_CELL_XFS)) {
 175                  // Once done reading "cellXfs" node's children
 176                  break;
 177              }
 178          }
 179      }
 180  
 181      /**
 182       * @return array The custom number formats
 183       */
 184      protected function getCustomNumberFormats()
 185      {
 186          if (!isset($this->customNumberFormats)) {
 187              $this->extractRelevantInfo();
 188          }
 189  
 190          return $this->customNumberFormats;
 191      }
 192  
 193      /**
 194       * @return array The styles attributes
 195       */
 196      protected function getStylesAttributes()
 197      {
 198          if (!isset($this->stylesAttributes)) {
 199              $this->extractRelevantInfo();
 200          }
 201  
 202          return $this->stylesAttributes;
 203      }
 204  
 205      /**
 206       * @param array $styleAttributes Array containing the style attributes (2 keys: "applyNumberFormat" and "numFmtId")
 207       * @return bool Whether the style with the given attributes indicates that the number is a date
 208       */
 209      protected function doesStyleIndicateDate($styleAttributes)
 210      {
 211          $applyNumberFormat = $styleAttributes[self::XML_ATTRIBUTE_APPLY_NUMBER_FORMAT];
 212          $numFmtId = $styleAttributes[self::XML_ATTRIBUTE_NUM_FMT_ID];
 213  
 214          // A style may apply a date format if it has:
 215          //  - "applyNumberFormat" attribute not set to "false"
 216          //  - "numFmtId" attribute set
 217          // This is a preliminary check, as having "numFmtId" set just means the style should apply a specific number format,
 218          // but this is not necessarily a date.
 219          if ($applyNumberFormat === false || $numFmtId === null) {
 220              return false;
 221          }
 222  
 223          return $this->doesNumFmtIdIndicateDate($numFmtId);
 224      }
 225  
 226      /**
 227       * Returns whether the number format ID indicates that the number is a date.
 228       * The result is cached to avoid recomputing the same thing over and over, as
 229       * "numFmtId" attributes can be shared between multiple styles.
 230       *
 231       * @param int $numFmtId
 232       * @return bool Whether the number format ID indicates that the number is a date
 233       */
 234      protected function doesNumFmtIdIndicateDate($numFmtId)
 235      {
 236          if (!isset($this->numFmtIdToIsDateFormatCache[$numFmtId])) {
 237              $formatCode = $this->getFormatCodeForNumFmtId($numFmtId);
 238  
 239              $this->numFmtIdToIsDateFormatCache[$numFmtId] = (
 240                  $this->isNumFmtIdBuiltInDateFormat($numFmtId) ||
 241                  $this->isFormatCodeCustomDateFormat($formatCode)
 242              );
 243          }
 244  
 245          return $this->numFmtIdToIsDateFormatCache[$numFmtId];
 246      }
 247  
 248      /**
 249       * @param int $numFmtId
 250       * @return string|null The custom number format or NULL if none defined for the given numFmtId
 251       */
 252      protected function getFormatCodeForNumFmtId($numFmtId)
 253      {
 254          $customNumberFormats = $this->getCustomNumberFormats();
 255  
 256          // Using isset here because it is way faster than array_key_exists...
 257          return (isset($customNumberFormats[$numFmtId])) ? $customNumberFormats[$numFmtId] : null;
 258      }
 259  
 260      /**
 261       * @param int $numFmtId
 262       * @return bool Whether the number format ID indicates that the number is a date
 263       */
 264      protected function isNumFmtIdBuiltInDateFormat($numFmtId)
 265      {
 266          return in_array($numFmtId, $this->builtinNumFmtIdIndicatingDates);
 267      }
 268  
 269      /**
 270       * @param string|null $formatCode
 271       * @return bool Whether the given format code indicates that the number is a date
 272       */
 273      protected function isFormatCodeCustomDateFormat($formatCode)
 274      {
 275          // if no associated format code or if using the default "General" format
 276          if ($formatCode === null || strcasecmp($formatCode, self::NUMBER_FORMAT_GENERAL) === 0) {
 277              return false;
 278          }
 279  
 280          return $this->isFormatCodeMatchingDateFormatPattern($formatCode);
 281      }
 282  
 283      /**
 284       * @param string $formatCode
 285       * @return bool Whether the given format code matches a date format pattern
 286       */
 287      protected function isFormatCodeMatchingDateFormatPattern($formatCode)
 288      {
 289          // Remove extra formatting (what's between [ ], the brackets should not be preceded by a "\")
 290          $pattern = '((?<!\\\)\[.+?(?<!\\\)\])';
 291          $formatCode = preg_replace($pattern, '', $formatCode);
 292  
 293          // custom date formats contain specific characters to represent the date:
 294          // e - yy - m - d - h - s
 295          // and all of their variants (yyyy - mm - dd...)
 296          $dateFormatCharacters = ['e', 'yy', 'm', 'd', 'h', 's'];
 297  
 298          $hasFoundDateFormatCharacter = false;
 299          foreach ($dateFormatCharacters as $dateFormatCharacter) {
 300              // character not preceded by "\" (case insensitive)
 301              $pattern = '/(?<!\\\)' . $dateFormatCharacter . '/i';
 302  
 303              if (preg_match($pattern, $formatCode)) {
 304                  $hasFoundDateFormatCharacter = true;
 305                  break;
 306              }
 307          }
 308  
 309          return $hasFoundDateFormatCharacter;
 310      }
 311  
 312      /**
 313       * Returns the format as defined in "styles.xml" of the given style.
 314       * NOTE: It is assumed that the style DOES have a number format associated to it.
 315       *
 316       * @param int $styleId Zero-based style ID
 317       * @return string The number format code associated with the given style
 318       */
 319      public function getNumberFormatCode($styleId)
 320      {
 321          $stylesAttributes = $this->getStylesAttributes();
 322          $styleAttributes = $stylesAttributes[$styleId];
 323          $numFmtId = $styleAttributes[self::XML_ATTRIBUTE_NUM_FMT_ID];
 324  
 325          if ($this->isNumFmtIdBuiltInDateFormat($numFmtId)) {
 326              $numberFormatCode = self::$builtinNumFmtIdToNumFormatMapping[$numFmtId];
 327          } else {
 328              $customNumberFormats = $this->getCustomNumberFormats();
 329              $numberFormatCode = $customNumberFormats[$numFmtId];
 330          }
 331  
 332          return $numberFormatCode;
 333      }
 334  }