fluent-cart / vendor / openspout / openspout / src / Reader / XLSX / Helper / CellValueFormatter.php

CellValueFormatter.php in FluentCart A New Era of eCommerce – Faster, Lighter, and Simpler 1.5.1, at vendor/openspout/openspout/src/Reader/XLSX/Helper/CellValueFormatter.php

271 lines 11.3 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\OpenSpout\Reader\XLSX\Helper;
4
5	use FluentCart\OpenSpout\Reader\Exception\InvalidValueException;
6	use FluentCart\OpenSpout\Reader\XLSX\Manager\SharedStringsManager;
7	use FluentCart\OpenSpout\Reader\XLSX\Manager\StyleManager;
8	/**
9	* This class provides helper functions to format cell values.
10	*/
11	class CellValueFormatter
12	{
13	/** Definition of all possible cell types */
14	public const CELL_TYPE_INLINE_STRING = 'inlineStr';
15	public const CELL_TYPE_STR = 'str';
16	public const CELL_TYPE_SHARED_STRING = 's';
17	public const CELL_TYPE_BOOLEAN = 'b';
18	public const CELL_TYPE_NUMERIC = 'n';
19	public const CELL_TYPE_DATE = 'd';
20	public const CELL_TYPE_ERROR = 'e';
21	/** Definition of XML nodes names used to parse data */
22	public const XML_NODE_VALUE = 'v';
23	public const XML_NODE_INLINE_STRING_VALUE = 't';
24	/** Definition of XML attributes used to parse data */
25	public const XML_ATTRIBUTE_TYPE = 't';
26	public const XML_ATTRIBUTE_STYLE_ID = 's';
27	/** Constants used for date formatting */
28	public const NUM_SECONDS_IN_ONE_DAY = 86400;
29	/** @var SharedStringsManager Manages shared strings */
30	protected $sharedStringsManager;
31	/** @var StyleManager Manages styles */
32	protected $styleManager;
33	/** @var bool Whether date/time values should be returned as PHP objects or be formatted as strings */
34	protected $shouldFormatDates;
35	/** @var bool Whether date/time values should use a calendar starting in 1904 instead of 1900 */
36	protected $shouldUse1904Dates;
37	/** @var \OpenSpout\Common\Helper\Escaper\XLSX Used to unescape XML data */
38	protected $escaper;
39	/**
40	* @param SharedStringsManager $sharedStringsManager Manages shared strings
41	* @param StyleManager $styleManager Manages styles
42	* @param bool $shouldFormatDates Whether date/time values should be returned as PHP objects or be formatted as strings
43	* @param bool $shouldUse1904Dates Whether date/time values should use a calendar starting in 1904 instead of 1900
44	* @param \OpenSpout\Common\Helper\Escaper\XLSX $escaper Used to unescape XML data
45	*/
46	public function __construct($sharedStringsManager, $styleManager, $shouldFormatDates, $shouldUse1904Dates, $escaper)
47	{
48	$this->sharedStringsManager = $sharedStringsManager;
49	$this->styleManager = $styleManager;
50	$this->shouldFormatDates = $shouldFormatDates;
51	$this->shouldUse1904Dates = $shouldUse1904Dates;
52	$this->escaper = $escaper;
53	}
54	/**
55	* Returns the (unescaped) correctly marshalled, cell value associated to the given XML node.
56	*
57	* @param \DOMElement $node
58	*
59	* @throws InvalidValueException If the value is not valid
60	*
61	* @return bool\|\DateTime\|float\|int\|string The value associated with the cell
62	*/
63	public function extractAndFormatNodeValue($node)
64	{
65	// Default cell type is "n"
66	$cellType = $node->getAttribute(self::XML_ATTRIBUTE_TYPE) ?: self::CELL_TYPE_NUMERIC;
67	$cellStyleId = (int) $node->getAttribute(self::XML_ATTRIBUTE_STYLE_ID);
68	$vNodeValue = $this->getVNodeValue($node);
69	if ('' === $vNodeValue && self::CELL_TYPE_INLINE_STRING !== $cellType) {
70	return $vNodeValue;
71	}
72	switch ($cellType) {
73	case self::CELL_TYPE_INLINE_STRING:
74	return $this->formatInlineStringCellValue($node);
75	case self::CELL_TYPE_SHARED_STRING:
76	return $this->formatSharedStringCellValue($vNodeValue);
77	case self::CELL_TYPE_STR:
78	return $this->formatStrCellValue($vNodeValue);
79	case self::CELL_TYPE_BOOLEAN:
80	return $this->formatBooleanCellValue($vNodeValue);
81	case self::CELL_TYPE_NUMERIC:
82	return $this->formatNumericCellValue($vNodeValue, $cellStyleId);
83	case self::CELL_TYPE_DATE:
84	return $this->formatDateCellValue($vNodeValue);
85	default:
86	throw new InvalidValueException($vNodeValue);
87	}
88	}
89	/**
90	* Returns the cell's string value from a node's nested value node.
91	*
92	* @param \DOMElement $node
93	*
94	* @return string The value associated with the cell
95	*/
96	protected function getVNodeValue($node)
97	{
98	// for cell types having a "v" tag containing the value.
99	// if not, the returned value should be empty string.
100	$vNode = $node->getElementsByTagName(self::XML_NODE_VALUE)->item(0);
101	return null !== $vNode ? $vNode->nodeValue : '';
102	}
103	/**
104	* Returns the cell String value where string is inline.
105	*
106	* @param \DOMElement $node
107	*
108	* @return string The value associated with the cell
109	*/
110	protected function formatInlineStringCellValue($node)
111	{
112	// inline strings are formatted this way (they can contain any number of <t> nodes):
113	// <c r="A1" t="inlineStr"><is><t>[INLINE_STRING]</t><t>[INLINE_STRING_2]</t></is></c>
114	$tNodes = $node->getElementsByTagName(self::XML_NODE_INLINE_STRING_VALUE);
115	$cellValue = '';
116	for ($i = 0; $i < $tNodes->count(); ++$i) {
117	$tNode = $tNodes->item($i);
118	$cellValue .= $this->escaper->unescape($tNode->nodeValue);
119	}
120	return $cellValue;
121	}
122	/**
123	* Returns the cell String value from shared-strings file using nodeValue index.
124	*
125	* @param string $nodeValue
126	*
127	* @return string The value associated with the cell
128	*/
129	protected function formatSharedStringCellValue($nodeValue)
130	{
131	// shared strings are formatted this way:
132	// <c r="A1" t="s"><v>[SHARED_STRING_INDEX]</v></c>
133	$sharedStringIndex = (int) $nodeValue;
134	$escapedCellValue = $this->sharedStringsManager->getStringAtIndex($sharedStringIndex);
135	return $this->escaper->unescape($escapedCellValue);
136	}
137	/**
138	* Returns the cell String value, where string is stored in value node.
139	*
140	* @param string $nodeValue
141	*
142	* @return string The value associated with the cell
143	*/
144	protected function formatStrCellValue($nodeValue)
145	{
146	$escapedCellValue = \trim($nodeValue);
147	return $this->escaper->unescape($escapedCellValue);
148	}
149	/**
150	* Returns the cell Numeric value from string of nodeValue.
151	* The value can also represent a timestamp and a DateTime will be returned.
152	*
153	* @param string $nodeValue
154	* @param int $cellStyleId 0 being the default style
155	*
156	* @return \DateTime\|float\|int The value associated with the cell
157	*/
158	protected function formatNumericCellValue($nodeValue, $cellStyleId)
159	{
160	// Numeric values can represent numbers as well as timestamps.
161	// We need to look at the style of the cell to determine whether it is one or the other.
162	$shouldFormatAsDate = $this->styleManager->shouldFormatNumericValueAsDate($cellStyleId);
163	if ($shouldFormatAsDate) {
164	$cellValue = $this->formatExcelTimestampValue((float) $nodeValue, $cellStyleId);
165	} else {
166	$nodeIntValue = (int) $nodeValue;
167	$nodeFloatValue = (float) $nodeValue;
168	$cellValue = (float) $nodeIntValue === $nodeFloatValue ? $nodeIntValue : $nodeFloatValue;
169	}
170	return $cellValue;
171	}
172	/**
173	* Returns a cell's PHP Date value, associated to the given timestamp.
174	* NOTE: The timestamp is a float representing the number of days since the base Excel date:
175	* Dec 30th 1899, 1900 or Jan 1st, 1904, depending on the Workbook setting.
176	* NOTE: The timestamp can also represent a time, if it is a value between 0 and 1.
177	*
178	* @see ECMA-376 Part 1 - §18.17.4
179	*
180	* @param float $nodeValue
181	* @param int $cellStyleId 0 being the default style
182	*
183	* @throws InvalidValueException If the value is not a valid timestamp
184	*
185	* @return \DateTime The value associated with the cell
186	*/
187	protected function formatExcelTimestampValue($nodeValue, $cellStyleId)
188	{
189	if ($this->isValidTimestampValue($nodeValue)) {
190	$cellValue = $this->formatExcelTimestampValueAsDateTimeValue($nodeValue, $cellStyleId);
191	} else {
192	throw new InvalidValueException($nodeValue);
193	}
194	return $cellValue;
195	}
196	/**
197	* Returns whether the given timestamp is supported by SpreadsheetML.
198	*
199	* @see ECMA-376 Part 1 - §18.17.4 - this specifies the timestamp boundaries.
200	*
201	* @param float $timestampValue
202	*
203	* @return bool
204	*/
205	protected function isValidTimestampValue($timestampValue)
206	{
207	// @NOTE: some versions of Excel don't support negative dates (e.g. Excel for Mac 2011)
208	return $this->shouldUse1904Dates && $timestampValue >= -695055 && $timestampValue <= 2957003.9999884 \|\| !$this->shouldUse1904Dates && $timestampValue >= -693593 && $timestampValue <= 2958465.9999884;
209	}
210	/**
211	* Returns a cell's PHP DateTime value, associated to the given timestamp.
212	* Only the time value matters. The date part is set to the base Excel date:
213	* Dec 30th 1899, 1900 or Jan 1st, 1904, depending on the Workbook setting.
214	*
215	* @param float $nodeValue
216	* @param int $cellStyleId 0 being the default style
217	*
218	* @return \DateTime\|string The value associated with the cell
219	*/
220	protected function formatExcelTimestampValueAsDateTimeValue($nodeValue, $cellStyleId)
221	{
222	$baseDate = $this->shouldUse1904Dates ? '1904-01-01' : '1899-12-30';
223	$daysSinceBaseDate = (int) $nodeValue;
224	$timeRemainder = \fmod($nodeValue, 1);
225	$secondsRemainder = \round($timeRemainder * self::NUM_SECONDS_IN_ONE_DAY, 0);
226	$dateObj = \DateTime::createFromFormat('\|Y-m-d', $baseDate);
227	$dateObj->modify('+' . $daysSinceBaseDate . 'days');
228	$dateObj->modify('+' . $secondsRemainder . 'seconds');
229	if ($this->shouldFormatDates) {
230	$styleNumberFormatCode = $this->styleManager->getNumberFormatCode($cellStyleId);
231	$phpDateFormat = DateFormatHelper::toPHPDateFormat($styleNumberFormatCode);
232	$cellValue = $dateObj->format($phpDateFormat);
233	} else {
234	$cellValue = $dateObj;
235	}
236	return $cellValue;
237	}
238	/**
239	* Returns the cell Boolean value from a specific node's Value.
240	*
241	* @param string $nodeValue
242	*
243	* @return bool The value associated with the cell
244	*/
245	protected function formatBooleanCellValue($nodeValue)
246	{
247	return (bool) $nodeValue;
248	}
249	/**
250	* Returns a cell's PHP Date value, associated to the given stored nodeValue.
251	*
252	* @see ECMA-376 Part 1 - §18.17.4
253	*
254	* @param string $nodeValue ISO 8601 Date string
255	*
256	* @throws InvalidValueException If the value is not a valid date
257	*
258	* @return \DateTime\|string The value associated with the cell
259	*/
260	protected function formatDateCellValue($nodeValue)
261	{
262	// Mitigate thrown Exception on invalid date-time format (http://php.net/manual/en/datetime.construct.php)
263	try {
264	$cellValue = $this->shouldFormatDates ? $nodeValue : new \DateTime($nodeValue);
265	} catch (\Exception $e) {
266	throw new InvalidValueException($nodeValue);
267	}
268	return $cellValue;
269	}
270	}
271