[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\XLSX\Sheet;
   7  
   8  /**
   9   * Class SheetHelper
  10   * This class provides helper functions related to XLSX sheets
  11   *
  12   * @package Box\Spout\Reader\XLSX\Helper
  13   */
  14  class SheetHelper
  15  {
  16      /** Paths of XML files relative to the XLSX file root */
  17      const CONTENT_TYPES_XML_FILE_PATH = '[Content_Types].xml';
  18      const WORKBOOK_XML_RELS_FILE_PATH = 'xl/_rels/workbook.xml.rels';
  19      const WORKBOOK_XML_FILE_PATH = 'xl/workbook.xml';
  20  
  21      /** Namespaces for the XML files */
  22      const MAIN_NAMESPACE_FOR_CONTENT_TYPES_XML = 'http://schemas.openxmlformats.org/package/2006/content-types';
  23      const MAIN_NAMESPACE_FOR_WORKBOOK_XML_RELS = 'http://schemas.openxmlformats.org/package/2006/relationships';
  24      const MAIN_NAMESPACE_FOR_WORKBOOK_XML = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
  25  
  26      /** Value of the Override attribute used in [Content_Types].xml to define sheets */
  27      const OVERRIDE_CONTENT_TYPES_ATTRIBUTE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml';
  28  
  29      /** @var string Path of the XLSX file being read */
  30      protected $filePath;
  31  
  32      /** @var \Box\Spout\Reader\XLSX\Helper\SharedStringsHelper Helper to work with shared strings */
  33      protected $sharedStringsHelper;
  34  
  35      /** @var \Box\Spout\Common\Helper\GlobalFunctionsHelper Helper to work with global functions */
  36      protected $globalFunctionsHelper;
  37  
  38      /** @var \Box\Spout\Reader\Wrapper\SimpleXMLElement XML element representing the workbook.xml.rels file */
  39      protected $workbookXMLRelsAsXMLElement;
  40  
  41      /** @var \Box\Spout\Reader\Wrapper\SimpleXMLElement XML element representing the workbook.xml file */
  42      protected $workbookXMLAsXMLElement;
  43  
  44      /**
  45       * @param string $filePath Path of the XLSX file being read
  46       * @param \Box\Spout\Reader\XLSX\Helper\SharedStringsHelper Helper to work with shared strings
  47       * @param \Box\Spout\Common\Helper\GlobalFunctionsHelper $globalFunctionsHelper
  48       */
  49      public function __construct($filePath, $sharedStringsHelper, $globalFunctionsHelper)
  50      {
  51          $this->filePath = $filePath;
  52          $this->sharedStringsHelper = $sharedStringsHelper;
  53          $this->globalFunctionsHelper = $globalFunctionsHelper;
  54      }
  55  
  56      /**
  57       * Returns the sheets metadata of the file located at the previously given file path.
  58       * The paths to the sheets' data are read from the [Content_Types].xml file.
  59       *
  60       * @return Sheet[] Sheets within the XLSX file
  61       */
  62      public function getSheets()
  63      {
  64          $sheets = [];
  65  
  66          $contentTypesAsXMLElement = $this->getFileAsXMLElementWithNamespace(
  67              self::CONTENT_TYPES_XML_FILE_PATH,
  68              self::MAIN_NAMESPACE_FOR_CONTENT_TYPES_XML
  69          );
  70  
  71          // find all nodes defining a sheet
  72          $sheetNodes = $contentTypesAsXMLElement->xpath('//ns:Override[@ContentType="' . self::OVERRIDE_CONTENT_TYPES_ATTRIBUTE . '"]');
  73          $numSheetNodes = count($sheetNodes);
  74  
  75          for ($i = 0; $i < $numSheetNodes; $i++) {
  76              $sheetNode = $sheetNodes[$i];
  77              $sheetDataXMLFilePath = $sheetNode->getAttribute('PartName');
  78  
  79              $sheets[] = $this->getSheetFromXML($sheetDataXMLFilePath);
  80          }
  81  
  82          // make sure the sheets are sorted by index
  83          // (as the sheets are not necessarily in this order in the XML file)
  84          usort($sheets, function ($sheet1, $sheet2) {
  85              return ($sheet1->getIndex() - $sheet2->getIndex());
  86          });
  87  
  88          return $sheets;
  89      }
  90  
  91      /**
  92       * Returns an instance of a sheet, given the path of its data XML file.
  93       * We first look at "xl/_rels/workbook.xml.rels" to find the relationship ID of the sheet.
  94       * Then we look at "xl/worbook.xml" to find the sheet entry associated to the found ID.
  95       * The entry contains the ID and name of the sheet.
  96       *
  97       * @param string $sheetDataXMLFilePath Path of the sheet data XML file as in [Content_Types].xml
  98       * @return \Box\Spout\Reader\XLSX\Sheet Sheet instance
  99       */
 100      protected function getSheetFromXML($sheetDataXMLFilePath)
 101      {
 102          // In [Content_Types].xml, the path is "/xl/worksheets/sheet1.xml"
 103          // In workbook.xml.rels, it is only "worksheets/sheet1.xml"
 104          $sheetDataXMLFilePathInWorkbookXMLRels = ltrim($sheetDataXMLFilePath, '/xl/');
 105  
 106          // find the node associated to the given file path
 107          $workbookXMLResElement = $this->getWorkbookXMLRelsAsXMLElement();
 108          $relationshipNodes = $workbookXMLResElement->xpath('//ns:Relationship[@Target="' . $sheetDataXMLFilePathInWorkbookXMLRels . '"]');
 109          $relationshipNode = $relationshipNodes[0];
 110  
 111          $relationshipSheetId = $relationshipNode->getAttribute('Id');
 112  
 113          $workbookXMLElement = $this->getWorkbookXMLAsXMLElement();
 114          $sheetNodes = $workbookXMLElement->xpath('//ns:sheet[@r:id="' . $relationshipSheetId . '"]');
 115          $sheetNode = $sheetNodes[0];
 116  
 117          $escapedSheetName = $sheetNode->getAttribute('name');
 118          $sheetIdOneBased = $sheetNode->getAttribute('sheetId');
 119          $sheetIndexZeroBased = $sheetIdOneBased - 1;
 120  
 121          /** @noinspection PhpUnnecessaryFullyQualifiedNameInspection */
 122          $escaper = new \Box\Spout\Common\Escaper\XLSX();
 123          $sheetName = $escaper->unescape($escapedSheetName);
 124  
 125          return new Sheet($this->filePath, $sheetDataXMLFilePath, $this->sharedStringsHelper, $sheetIndexZeroBased, $sheetName);
 126      }
 127  
 128      /**
 129       * Returns a representation of the workbook.xml.rels file, ready to be parsed.
 130       * The returned value is cached.
 131       *
 132       * @return \Box\Spout\Reader\Wrapper\SimpleXMLElement XML element representating the workbook.xml.rels file
 133       */
 134      protected function getWorkbookXMLRelsAsXMLElement()
 135      {
 136          if (!$this->workbookXMLRelsAsXMLElement) {
 137              $this->workbookXMLRelsAsXMLElement = $this->getFileAsXMLElementWithNamespace(
 138                  self::WORKBOOK_XML_RELS_FILE_PATH,
 139                  self::MAIN_NAMESPACE_FOR_WORKBOOK_XML_RELS
 140              );
 141          }
 142  
 143          return $this->workbookXMLRelsAsXMLElement;
 144      }
 145  
 146      /**
 147       * Returns a representation of the workbook.xml file, ready to be parsed.
 148       * The returned value is cached.
 149       *
 150       * @return \Box\Spout\Reader\Wrapper\SimpleXMLElement XML element representating the workbook.xml.rels file
 151       */
 152      protected function getWorkbookXMLAsXMLElement()
 153      {
 154          if (!$this->workbookXMLAsXMLElement) {
 155              $this->workbookXMLAsXMLElement = $this->getFileAsXMLElementWithNamespace(
 156                  self::WORKBOOK_XML_FILE_PATH,
 157                  self::MAIN_NAMESPACE_FOR_WORKBOOK_XML
 158              );
 159          }
 160  
 161          return $this->workbookXMLAsXMLElement;
 162      }
 163  
 164      /**
 165       * Loads the contents of the given file in an XML parser and register the given XPath namespace.
 166       *
 167       * @param string $xmlFilePath The path of the XML file inside the XLSX file
 168       * @param string $mainNamespace The main XPath namespace to register
 169       * @return \Box\Spout\Reader\Wrapper\SimpleXMLElement The XML element representing the file
 170       */
 171      protected function getFileAsXMLElementWithNamespace($xmlFilePath, $mainNamespace)
 172      {
 173          $xmlContents = $this->globalFunctionsHelper->file_get_contents('zip://' . $this->filePath . '#' . $xmlFilePath);
 174  
 175          $xmlElement = new SimpleXMLElement($xmlContents);
 176          $xmlElement->registerXPathNamespace('ns', $mainNamespace);
 177  
 178          return $xmlElement;
 179      }
 180  }