fluentform / app / Services / Spout / Reader / XLSX / Helper / SheetHelper.php

SheetHelper.php in Fluent Forms – Customizable Contact Forms, Survey, Quiz, & Conversational Form Builder 6.2.5, at app/Services/Spout/Reader/XLSX/Helper/SheetHelper.php

157 lines 6.5 KB

Raw Download Zip

1	<?php
2
3	namespace Box\Spout\Reader\XLSX\Helper;
4
5	use Box\Spout\Reader\Wrapper\XMLReader;
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 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_VIEW = 'workbookView';
22	const XML_NODE_SHEET = 'sheet';
23	const XML_NODE_SHEETS = 'sheets';
24	const XML_NODE_RELATIONSHIP = 'Relationship';
25
26	/** Definition of XML attributes used to parse data */
27	const XML_ATTRIBUTE_ACTIVE_TAB = 'activeTab';
28	const XML_ATTRIBUTE_R_ID = 'r:id';
29	const XML_ATTRIBUTE_NAME = 'name';
30	const XML_ATTRIBUTE_ID = 'Id';
31	const XML_ATTRIBUTE_TARGET = 'Target';
32
33	/** @var string Path of the XLSX file being read */
34	protected $filePath;
35
36	/** @var \Box\Spout\Reader\XLSX\ReaderOptions Reader's current options */
37	protected $options;
38
39	/** @var \Box\Spout\Reader\XLSX\Helper\SharedStringsHelper Helper to work with shared strings */
40	protected $sharedStringsHelper;
41
42	/** @var \Box\Spout\Common\Helper\GlobalFunctionsHelper Helper to work with global functions */
43	protected $globalFunctionsHelper;
44
45	/**
46	* @param string $filePath Path of the XLSX file being read
47	* @param \Box\Spout\Reader\XLSX\ReaderOptions $options Reader's current options
48	* @param \Box\Spout\Reader\XLSX\Helper\SharedStringsHelper Helper to work with shared strings
49	* @param \Box\Spout\Common\Helper\GlobalFunctionsHelper $globalFunctionsHelper
50	*/
51	public function __construct($filePath, $options, $sharedStringsHelper, $globalFunctionsHelper)
52	{
53	$this->filePath = $filePath;
54	$this->options = $options;
55	$this->sharedStringsHelper = $sharedStringsHelper;
56	$this->globalFunctionsHelper = $globalFunctionsHelper;
57	}
58
59	/**
60	* Returns the sheets metadata of the file located at the previously given file path.
61	* The paths to the sheets' data are read from the [Content_Types].xml file.
62	*
63	* @return Sheet[] Sheets within the XLSX file
64	*/
65	public function getSheets()
66	{
67	$sheets = [];
68	$sheetIndex = 0;
69	$activeSheetIndex = 0; // By default, the first sheet is active
70
71	$xmlReader = new XMLReader();
72	if ($xmlReader->openFileInZip($this->filePath, self::WORKBOOK_XML_FILE_PATH)) {
73	while ($xmlReader->read()) {
74	if ($xmlReader->isPositionedOnStartingNode(self::XML_NODE_WORKBOOK_VIEW)) {
75	// The "workbookView" node is located before "sheet" nodes, ensuring that
76	// the active sheet is known before parsing sheets data.
77	$activeSheetIndex = (int) $xmlReader->getAttribute(self::XML_ATTRIBUTE_ACTIVE_TAB);
78	} else if ($xmlReader->isPositionedOnStartingNode(self::XML_NODE_SHEET)) {
79	$isSheetActive = ($sheetIndex === $activeSheetIndex);
80	$sheets[] = $this->getSheetFromSheetXMLNode($xmlReader, $sheetIndex, $isSheetActive);
81	$sheetIndex++;
82	} else if ($xmlReader->isPositionedOnEndingNode(self::XML_NODE_SHEETS)) {
83	// stop reading once all sheets have been read
84	break;
85	}
86	}
87
88	$xmlReader->close();
89	}
90
91	return $sheets;
92	}
93
94	/**
95	* Returns an instance of a sheet, given the XML node describing the sheet - from "workbook.xml".
96	* We can find the XML file path describing the sheet inside "workbook.xml.res", by mapping with the sheet ID
97	* ("r:id" in "workbook.xml", "Id" in "workbook.xml.res").
98	*
99	* @param \Box\Spout\Reader\Wrapper\XMLReader $xmlReaderOnSheetNode XML Reader instance, pointing on the node describing the sheet, as defined in "workbook.xml"
100	* @param int $sheetIndexZeroBased Index of the sheet, based on order of appearance in the workbook (zero-based)
101	* @param bool $isSheetActive Whether this sheet was defined as active
102	* @return \Box\Spout\Reader\XLSX\Sheet Sheet instance
103	*/
104	protected function getSheetFromSheetXMLNode($xmlReaderOnSheetNode, $sheetIndexZeroBased, $isSheetActive)
105	{
106	$sheetId = $xmlReaderOnSheetNode->getAttribute(self::XML_ATTRIBUTE_R_ID);
107	$escapedSheetName = $xmlReaderOnSheetNode->getAttribute(self::XML_ATTRIBUTE_NAME);
108
109	/** @noinspection PhpUnnecessaryFullyQualifiedNameInspection */
110	$escaper = \Box\Spout\Common\Escaper\XLSX::getInstance();
111	$sheetName = $escaper->unescape($escapedSheetName);
112
113	$sheetDataXMLFilePath = $this->getSheetDataXMLFilePathForSheetId($sheetId);
114
115	return new Sheet(
116	$this->filePath, $sheetDataXMLFilePath,
117	$sheetIndexZeroBased, $sheetName, $isSheetActive,
118	$this->options, $this->sharedStringsHelper
119	);
120	}
121
122	/**
123	* @param string $sheetId The sheet ID, as defined in "workbook.xml"
124	* @return string The XML file path describing the sheet inside "workbook.xml.res", for the given sheet ID
125	*/
126	protected function getSheetDataXMLFilePathForSheetId($sheetId)
127	{
128	$sheetDataXMLFilePath = '';
129
130	// find the file path of the sheet, by looking at the "workbook.xml.res" file
131	$xmlReader = new XMLReader();
132	if ($xmlReader->openFileInZip($this->filePath, self::WORKBOOK_XML_RELS_FILE_PATH)) {
133	while ($xmlReader->read()) {
134	if ($xmlReader->isPositionedOnStartingNode(self::XML_NODE_RELATIONSHIP)) {
135	$relationshipSheetId = $xmlReader->getAttribute(self::XML_ATTRIBUTE_ID);
136
137	if ($relationshipSheetId === $sheetId) {
138	// In workbook.xml.rels, it is only "worksheets/sheet1.xml"
139	// In [Content_Types].xml, the path is "/xl/worksheets/sheet1.xml"
140	$sheetDataXMLFilePath = $xmlReader->getAttribute(self::XML_ATTRIBUTE_TARGET);
141
142	// sometimes, the sheet data file path already contains "/xl/"...
143	if (strpos($sheetDataXMLFilePath, '/xl/') !== 0) {
144	$sheetDataXMLFilePath = '/xl/' . $sheetDataXMLFilePath;
145	break;
146	}
147	}
148	}
149	}
150
151	$xmlReader->close();
152	}
153
154	return $sheetDataXMLFilePath;
155	}
156	}
157