[Summary view] [Print] [Text view]

   1  <?php
   2  
   3  namespace Box\Spout\Reader\XLSX\Helper;
   4  
   5  use Box\Spout\Reader\Wrapper\SimpleXMLElement;
   6  use Box\Spout\Reader\Wrapper\XMLReader;
   7  
   8  /**
   9   * Class StyleHelper
  10   * This class provides helper functions related to XLSX styles
  11   *
  12   * @package Box\Spout\Reader\XLSX\Helper
  13   */
  14  class StyleHelper
  15  {
  16      /** Paths of XML files relative to the XLSX file root */
  17      const STYLES_XML_FILE_PATH = 'xl/styles.xml';
  18  
  19      /** Nodes used to find relevant information in the styles XML file */
  20      const XML_NODE_NUM_FMTS = 'numFmts';
  21      const XML_NODE_NUM_FMT = 'numFmt';
  22      const XML_NODE_CELL_XFS = 'cellXfs';
  23      const XML_NODE_XF = 'xf';
  24  
  25      /** Attributes used to find relevant information in the styles XML file */
  26      const XML_ATTRIBUTE_NUM_FMT_ID = 'numFmtId';
  27      const XML_ATTRIBUTE_FORMAT_CODE = 'formatCode';
  28      const XML_ATTRIBUTE_APPLY_NUMBER_FORMAT = 'applyNumberFormat';
  29  
  30      /** By convention, default style ID is 0 */
  31      const DEFAULT_STYLE_ID = 0;
  32  
  33      /** @var string Path of the XLSX file being read */
  34      protected $filePath;
  35  
  36      /** @var array Array containing a mapping NUM_FMT_ID => FORMAT_CODE */
  37      protected $customNumberFormats;
  38  
  39      /** @var array Array containing a mapping STYLE_ID => [STYLE_ATTRIBUTES] */
  40      protected $stylesAttributes;
  41  
  42      /**
  43       * @param string $filePath Path of the XLSX file being read
  44       */
  45      public function __construct($filePath)
  46      {
  47          $this->filePath = $filePath;
  48      }
  49  
  50      /**
  51       * Reads the styles.xml file and extract the relevant information from the file.
  52       *
  53       * @return void
  54       */
  55      protected function extractRelevantInfo()
  56      {
  57          $this->customNumberFormats = [];
  58          $this->stylesAttributes = [];
  59  
  60          $stylesXmlFilePath = $this->filePath .'#' . self::STYLES_XML_FILE_PATH;
  61          $xmlReader = new XMLReader();
  62  
  63          if ($xmlReader->open('zip://' . $stylesXmlFilePath)) {
  64              while ($xmlReader->read()) {
  65                  if ($xmlReader->isPositionedOnStartingNode(self::XML_NODE_NUM_FMTS)) {
  66                      $numFmtsNode = new SimpleXMLElement($xmlReader->readOuterXml());
  67                      $this->extractNumberFormats($numFmtsNode);
  68  
  69                  } else if ($xmlReader->isPositionedOnStartingNode(self::XML_NODE_CELL_XFS)) {
  70                      $cellXfsNode = new SimpleXMLElement($xmlReader->readOuterXml());
  71                      $this->extractStyleAttributes($cellXfsNode);
  72                  }
  73              }
  74  
  75              $xmlReader->close();
  76          }
  77      }
  78  
  79      /**
  80       * Extracts number formats from the "numFmt" nodes.
  81       * For simplicity, the styles attributes are kept in memory. This is possible thanks
  82       * to the reuse of formats. So 1 million cells should not use 1 million formats.
  83       *
  84       * @param SimpleXMLElement $numFmtsNode The "numFmts" node
  85       * @return void
  86       */
  87      protected function extractNumberFormats($numFmtsNode)
  88      {
  89          foreach ($numFmtsNode->children() as $numFmtNode) {
  90              $numFmtId = intval($numFmtNode->getAttribute(self::XML_ATTRIBUTE_NUM_FMT_ID));
  91              $formatCode = $numFmtNode->getAttribute(self::XML_ATTRIBUTE_FORMAT_CODE);
  92              $this->customNumberFormats[$numFmtId] = $formatCode;
  93          }
  94      }
  95  
  96      /**
  97       * Extracts style attributes from the "xf" nodes, inside the "cellXfs" section.
  98       * For simplicity, the styles attributes are kept in memory. This is possible thanks
  99       * to the reuse of styles. So 1 million cells should not use 1 million styles.
 100       *
 101       * @param SimpleXMLElement $cellXfsNode The "cellXfs" node
 102       * @return void
 103       */
 104      protected function extractStyleAttributes($cellXfsNode)
 105      {
 106          foreach ($cellXfsNode->children() as $xfNode) {
 107              $this->stylesAttributes[] = [
 108                  self::XML_ATTRIBUTE_NUM_FMT_ID => intval($xfNode->getAttribute(self::XML_ATTRIBUTE_NUM_FMT_ID)),
 109                  self::XML_ATTRIBUTE_APPLY_NUMBER_FORMAT => !!($xfNode->getAttribute(self::XML_ATTRIBUTE_APPLY_NUMBER_FORMAT)),
 110              ];
 111          }
 112      }
 113  
 114      /**
 115       * @return array The custom number formats
 116       */
 117      protected function getCustomNumberFormats()
 118      {
 119          if (!isset($this->customNumberFormats)) {
 120              $this->extractRelevantInfo();
 121          }
 122  
 123          return $this->customNumberFormats;
 124      }
 125  
 126      /**
 127       * @return array The styles attributes
 128       */
 129      protected function getStylesAttributes()
 130      {
 131          if (!isset($this->stylesAttributes)) {
 132              $this->extractRelevantInfo();
 133          }
 134  
 135          return $this->stylesAttributes;
 136      }
 137  
 138      /**
 139       * Returns whether the style with the given ID should consider
 140       * numeric values as timestamps and format the cell as a date.
 141       *
 142       * @param int $styleId Zero-based style ID
 143       * @return bool Whether the cell with the given cell should display a date instead of a numeric value
 144       */
 145      public function shouldFormatNumericValueAsDate($styleId)
 146      {
 147          $stylesAttributes = $this->getStylesAttributes();
 148  
 149          // Default style (0) does not format numeric values as timestamps. Only custom styles do.
 150          // Also if the style ID does not exist in the styles.xml file, format as numeric value.
 151          // Using isset here because it is way faster than array_key_exists...
 152          if ($styleId === self::DEFAULT_STYLE_ID || !isset($stylesAttributes[$styleId])) {
 153              return false;
 154          }
 155  
 156          $styleAttributes = $stylesAttributes[$styleId];
 157  
 158          $applyNumberFormat = $styleAttributes[self::XML_ATTRIBUTE_APPLY_NUMBER_FORMAT];
 159          if (!$applyNumberFormat) {
 160              return false;
 161          }
 162  
 163          $numFmtId = $styleAttributes[self::XML_ATTRIBUTE_NUM_FMT_ID];
 164          return $this->doesNumFmtIdIndicateDate($numFmtId);
 165      }
 166  
 167      /**
 168       * @param int $numFmtId
 169       * @return bool Whether the number format ID indicates that the number is a timestamp
 170       */
 171      protected function doesNumFmtIdIndicateDate($numFmtId)
 172      {
 173          return (
 174              $this->isNumFmtIdBuiltInDateFormat($numFmtId) ||
 175              $this->isNumFmtIdCustomDateFormat($numFmtId)
 176          );
 177      }
 178  
 179      /**
 180       * @param int $numFmtId
 181       * @return bool Whether the number format ID indicates that the number is a timestamp
 182       */
 183      protected function isNumFmtIdBuiltInDateFormat($numFmtId)
 184      {
 185          $builtInDateFormatIds = [14, 15, 16, 17, 18, 19, 20, 21, 22, 45, 46, 47];
 186          return in_array($numFmtId, $builtInDateFormatIds);
 187      }
 188  
 189      /**
 190       * @param int $numFmtId
 191       * @return bool Whether the number format ID indicates that the number is a timestamp
 192       */
 193      protected function isNumFmtIdCustomDateFormat($numFmtId)
 194      {
 195          $customNumberFormats = $this->getCustomNumberFormats();
 196  
 197          // Using isset here because it is way faster than array_key_exists...
 198          if (!isset($customNumberFormats[$numFmtId])) {
 199              return false;
 200          }
 201  
 202          $customNumberFormat = $customNumberFormats[$numFmtId];
 203  
 204          // Remove extra formatting (what's between [ ], the brackets should not be preceded by a "\")
 205          $pattern = '((?<!\\\)\[.+?(?<!\\\)\])';
 206          $customNumberFormat = preg_replace($pattern, '', $customNumberFormat);
 207  
 208          // custom date formats contain specific characters to represent the date:
 209          // e - yy - m - d - h - s
 210          // and all of their variants (yyyy - mm - dd...)
 211          $dateFormatCharacters = ['e', 'yy', 'm', 'd', 'h', 's'];
 212  
 213          $hasFoundDateFormatCharacter = false;
 214          foreach ($dateFormatCharacters as $dateFormatCharacter) {
 215              // character not preceded by "\"
 216              $pattern = '/(?<!\\\)' . $dateFormatCharacter . '/';
 217  
 218              if (preg_match($pattern, $customNumberFormat)) {
 219                  $hasFoundDateFormatCharacter = true;
 220                  break;
 221              }
 222          }
 223  
 224          return $hasFoundDateFormatCharacter;
 225      }
 226  }