Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 3.9.x will end* 10 May 2021 (12 months).
  • Bug fixes for security issues in 3.9.x will end* 8 May 2023 (36 months).
  • PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.

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

   1  <?php
   2  
   3  namespace Box\Spout\Reader\XLSX\Manager;
   4  
   5  use Box\Spout\Reader\Common\Entity\Options;
   6  use Box\Spout\Reader\Common\XMLProcessor;
   7  use Box\Spout\Reader\XLSX\Creator\InternalEntityFactory;
   8  use Box\Spout\Reader\XLSX\Sheet;
   9  
  10  /**
  11   * Class SheetManager
  12   * This class manages XLSX sheets
  13   */
  14  class SheetManager
  15  {
  16      /** Paths of XML files relative to the XLSX file root */
  17      const WORKBOOK_XML_RELS_FILE_PATH = 'xl/_rels/workbook.xml.rels';
  18      const WORKBOOK_XML_FILE_PATH = 'xl/workbook.xml';
  19  
  20      /** Definition of XML node names used to parse data */
  21      const XML_NODE_WORKBOOK_PROPERTIES = 'workbookPr';
  22      const XML_NODE_WORKBOOK_VIEW = 'workbookView';
  23      const XML_NODE_SHEET = 'sheet';
  24      const XML_NODE_SHEETS = 'sheets';
  25      const XML_NODE_RELATIONSHIP = 'Relationship';
  26  
  27      /** Definition of XML attributes used to parse data */
  28      const XML_ATTRIBUTE_DATE_1904 = 'date1904';
  29      const XML_ATTRIBUTE_ACTIVE_TAB = 'activeTab';
  30      const XML_ATTRIBUTE_R_ID = 'r:id';
  31      const XML_ATTRIBUTE_NAME = 'name';
  32      const XML_ATTRIBUTE_STATE = 'state';
  33      const XML_ATTRIBUTE_ID = 'Id';
  34      const XML_ATTRIBUTE_TARGET = 'Target';
  35  
  36      /** State value to represent a hidden sheet */
  37      const SHEET_STATE_HIDDEN = 'hidden';
  38  
  39      /** @var string Path of the XLSX file being read */
  40      protected $filePath;
  41  
  42      /** @var \Box\Spout\Common\Manager\OptionsManagerInterface Reader's options manager */
  43      protected $optionsManager;
  44  
  45      /** @var \Box\Spout\Reader\XLSX\Manager\SharedStringsManager Manages shared strings */
  46      protected $sharedStringsManager;
  47  
  48      /** @var \Box\Spout\Common\Helper\GlobalFunctionsHelper Helper to work with global functions */
  49      protected $globalFunctionsHelper;
  50  
  51      /** @var InternalEntityFactory Factory to create entities */
  52      protected $entityFactory;
  53  
  54      /** @var \Box\Spout\Common\Helper\Escaper\XLSX Used to unescape XML data */
  55      protected $escaper;
  56  
  57      /** @var array List of sheets */
  58      protected $sheets;
  59  
  60      /** @var int Index of the sheet currently read */
  61      protected $currentSheetIndex;
  62  
  63      /** @var int Index of the active sheet (0 by default) */
  64      protected $activeSheetIndex;
  65  
  66      /**
  67       * @param string $filePath Path of the XLSX file being read
  68       * @param \Box\Spout\Common\Manager\OptionsManagerInterface $optionsManager Reader's options manager
  69       * @param \Box\Spout\Reader\XLSX\Manager\SharedStringsManager $sharedStringsManager Manages shared strings
  70       * @param \Box\Spout\Common\Helper\Escaper\XLSX $escaper Used to unescape XML data
  71       * @param InternalEntityFactory $entityFactory Factory to create entities
  72       * @param mixed $sharedStringsManager
  73       */
  74      public function __construct($filePath, $optionsManager, $sharedStringsManager, $escaper, $entityFactory)
  75      {
  76          $this->filePath = $filePath;
  77          $this->optionsManager = $optionsManager;
  78          $this->sharedStringsManager = $sharedStringsManager;
  79          $this->escaper = $escaper;
  80          $this->entityFactory = $entityFactory;
  81      }
  82  
  83      /**
  84       * Returns the sheets metadata of the file located at the previously given file path.
  85       * The paths to the sheets' data are read from the [Content_Types].xml file.
  86       *
  87       * @return Sheet[] Sheets within the XLSX file
  88       */
  89      public function getSheets()
  90      {
  91          $this->sheets = [];
  92          $this->currentSheetIndex = 0;
  93          $this->activeSheetIndex = 0; // By default, the first sheet is active
  94  
  95          $xmlReader = $this->entityFactory->createXMLReader();
  96          $xmlProcessor = $this->entityFactory->createXMLProcessor($xmlReader);
  97  
  98          $xmlProcessor->registerCallback(self::XML_NODE_WORKBOOK_PROPERTIES, XMLProcessor::NODE_TYPE_START, [$this, 'processWorkbookPropertiesStartingNode']);
  99          $xmlProcessor->registerCallback(self::XML_NODE_WORKBOOK_VIEW, XMLProcessor::NODE_TYPE_START, [$this, 'processWorkbookViewStartingNode']);
 100          $xmlProcessor->registerCallback(self::XML_NODE_SHEET, XMLProcessor::NODE_TYPE_START, [$this, 'processSheetStartingNode']);
 101          $xmlProcessor->registerCallback(self::XML_NODE_SHEETS, XMLProcessor::NODE_TYPE_END, [$this, 'processSheetsEndingNode']);
 102  
 103          if ($xmlReader->openFileInZip($this->filePath, self::WORKBOOK_XML_FILE_PATH)) {
 104              $xmlProcessor->readUntilStopped();
 105              $xmlReader->close();
 106          }
 107  
 108          return $this->sheets;
 109      }
 110  
 111      /**
 112       * @param \Box\Spout\Reader\Wrapper\XMLReader $xmlReader XMLReader object, positioned on a "<workbookPr>" starting node
 113       * @return int A return code that indicates what action should the processor take next
 114       */
 115      protected function processWorkbookPropertiesStartingNode($xmlReader)
 116      {
 117          // Using "filter_var($x, FILTER_VALIDATE_BOOLEAN)" here because the value of the "date1904" attribute
 118          // may be the string "false", that is not mapped to the boolean "false" by default...
 119          $shouldUse1904Dates = filter_var($xmlReader->getAttribute(self::XML_ATTRIBUTE_DATE_1904), FILTER_VALIDATE_BOOLEAN);
 120          $this->optionsManager->setOption(Options::SHOULD_USE_1904_DATES, $shouldUse1904Dates);
 121  
 122          return XMLProcessor::PROCESSING_CONTINUE;
 123      }
 124  
 125      /**
 126       * @param \Box\Spout\Reader\Wrapper\XMLReader $xmlReader XMLReader object, positioned on a "<workbookView>" starting node
 127       * @return int A return code that indicates what action should the processor take next
 128       */
 129      protected function processWorkbookViewStartingNode($xmlReader)
 130      {
 131          // The "workbookView" node is located before "sheet" nodes, ensuring that
 132          // the active sheet is known before parsing sheets data.
 133          $this->activeSheetIndex = (int) $xmlReader->getAttribute(self::XML_ATTRIBUTE_ACTIVE_TAB);
 134  
 135          return XMLProcessor::PROCESSING_CONTINUE;
 136      }
 137  
 138      /**
 139       * @param \Box\Spout\Reader\Wrapper\XMLReader $xmlReader XMLReader object, positioned on a "<sheet>" starting node
 140       * @return int A return code that indicates what action should the processor take next
 141       */
 142      protected function processSheetStartingNode($xmlReader)
 143      {
 144          $isSheetActive = ($this->currentSheetIndex === $this->activeSheetIndex);
 145          $this->sheets[] = $this->getSheetFromSheetXMLNode($xmlReader, $this->currentSheetIndex, $isSheetActive);
 146          $this->currentSheetIndex++;
 147  
 148          return XMLProcessor::PROCESSING_CONTINUE;
 149      }
 150  
 151      /**
 152       * @return int A return code that indicates what action should the processor take next
 153       */
 154      protected function processSheetsEndingNode()
 155      {
 156          return XMLProcessor::PROCESSING_STOP;
 157      }
 158  
 159      /**
 160       * Returns an instance of a sheet, given the XML node describing the sheet - from "workbook.xml".
 161       * We can find the XML file path describing the sheet inside "workbook.xml.res", by mapping with the sheet ID
 162       * ("r:id" in "workbook.xml", "Id" in "workbook.xml.res").
 163       *
 164       * @param \Box\Spout\Reader\Wrapper\XMLReader $xmlReaderOnSheetNode XML Reader instance, pointing on the node describing the sheet, as defined in "workbook.xml"
 165       * @param int $sheetIndexZeroBased Index of the sheet, based on order of appearance in the workbook (zero-based)
 166       * @param bool $isSheetActive Whether this sheet was defined as active
 167       * @return \Box\Spout\Reader\XLSX\Sheet Sheet instance
 168       */
 169      protected function getSheetFromSheetXMLNode($xmlReaderOnSheetNode, $sheetIndexZeroBased, $isSheetActive)
 170      {
 171          $sheetId = $xmlReaderOnSheetNode->getAttribute(self::XML_ATTRIBUTE_R_ID);
 172  
 173          $sheetState = $xmlReaderOnSheetNode->getAttribute(self::XML_ATTRIBUTE_STATE);
 174          $isSheetVisible = ($sheetState !== self::SHEET_STATE_HIDDEN);
 175  
 176          $escapedSheetName = $xmlReaderOnSheetNode->getAttribute(self::XML_ATTRIBUTE_NAME);
 177          $sheetName = $this->escaper->unescape($escapedSheetName);
 178  
 179          $sheetDataXMLFilePath = $this->getSheetDataXMLFilePathForSheetId($sheetId);
 180  
 181          return $this->entityFactory->createSheet(
 182              $this->filePath,
 183              $sheetDataXMLFilePath,
 184              $sheetIndexZeroBased,
 185              $sheetName,
 186              $isSheetActive,
 187              $isSheetVisible,
 188              $this->optionsManager,
 189              $this->sharedStringsManager
 190          );
 191      }
 192  
 193      /**
 194       * @param string $sheetId The sheet ID, as defined in "workbook.xml"
 195       * @return string The XML file path describing the sheet inside "workbook.xml.res", for the given sheet ID
 196       */
 197      protected function getSheetDataXMLFilePathForSheetId($sheetId)
 198      {
 199          $sheetDataXMLFilePath = '';
 200  
 201          // find the file path of the sheet, by looking at the "workbook.xml.res" file
 202          $xmlReader = $this->entityFactory->createXMLReader();
 203          if ($xmlReader->openFileInZip($this->filePath, self::WORKBOOK_XML_RELS_FILE_PATH)) {
 204              while ($xmlReader->read()) {
 205                  if ($xmlReader->isPositionedOnStartingNode(self::XML_NODE_RELATIONSHIP)) {
 206                      $relationshipSheetId = $xmlReader->getAttribute(self::XML_ATTRIBUTE_ID);
 207  
 208                      if ($relationshipSheetId === $sheetId) {
 209                          // In workbook.xml.rels, it is only "worksheets/sheet1.xml"
 210                          // In [Content_Types].xml, the path is "/xl/worksheets/sheet1.xml"
 211                          $sheetDataXMLFilePath = $xmlReader->getAttribute(self::XML_ATTRIBUTE_TARGET);
 212  
 213                          // sometimes, the sheet data file path already contains "/xl/"...
 214                          if (strpos($sheetDataXMLFilePath, '/xl/') !== 0) {
 215                              $sheetDataXMLFilePath = '/xl/' . $sheetDataXMLFilePath;
 216                              break;
 217                          }
 218                      }
 219                  }
 220              }
 221  
 222              $xmlReader->close();
 223          }
 224  
 225          return $sheetDataXMLFilePath;
 226      }
 227  }