CellValueFormatter::formatDateCellValue() - Code Metrics - Inspection of "Add support for 1904 dates" - box/spout - Measure and Improve Code Quality continuously with Scrutinizer

Test Failed

Pull Request — develop_3.0 (#483)

by Adrien

created 2017-11-04 11:51 UTC

CellValueFormatter::formatDateCellValue() A

↳ Parent: CellValueFormatter

Complexity

Conditions	3
Paths	4

Size

Total Lines	11
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	2
CRAP Score	3

Importance

Changes

Metric	Value
dl	0
loc	11
c	0
b	0
f	0
ccs	2
cts	2
cp	1
rs	9.4285
cc	3
eloc	6
nc	4
nop	1
crap	3

<?php

namespace Box\Spout\Reader\XLSX\Helper;

use Box\Spout\Reader\XLSX\Manager\SharedStringsManager;
use Box\Spout\Reader\XLSX\Manager\StyleManager;

/**
 * Class CellValueFormatter
 * This class provides helper functions to format cell values
 */
class CellValueFormatter
{
    /** Definition of all possible cell types */
    const CELL_TYPE_INLINE_STRING = 'inlineStr';
    const CELL_TYPE_STR = 'str';
    const CELL_TYPE_SHARED_STRING = 's';
    const CELL_TYPE_BOOLEAN = 'b';
    const CELL_TYPE_NUMERIC = 'n';
    const CELL_TYPE_DATE = 'd';
    const CELL_TYPE_ERROR = 'e';

    /** Definition of XML nodes names used to parse data */
    const XML_NODE_VALUE = 'v';
    const XML_NODE_INLINE_STRING_VALUE = 't';

    /** Definition of XML attributes used to parse data */
    const XML_ATTRIBUTE_TYPE = 't';
    const XML_ATTRIBUTE_STYLE_ID = 's';

    /** Constants used for date formatting */
    const NUM_SECONDS_IN_ONE_DAY = 86400;
    const NUM_SECONDS_IN_ONE_HOUR = 3600;
    const NUM_SECONDS_IN_ONE_MINUTE = 60;

    /**
     * February 29th, 1900 is NOT a leap year but Excel thinks it is...
     * @see https://en.wikipedia.org/wiki/Year_1900_problem#Microsoft_Excel
     */
    const ERRONEOUS_EXCEL_LEAP_YEAR_DAY = 60;

    /** @var SharedStringsManager Manages shared strings */
    protected $sharedStringsManager;

    /** @var StyleManager Manages styles */
    protected $styleManager;

    /** @var bool Whether date/time values should be returned as PHP objects or be formatted as strings */
    protected $shouldFormatDates;

    /** @var bool Whether date/time values should use a calendar starting in 1904 instead of 1900 */
    protected $shouldUse1904Dates;

    /** @var \Box\Spout\Common\Helper\Escaper\XLSX Used to unescape XML data */
    protected $escaper;

    /**
     * @param SharedStringsManager $sharedStringsManager Manages shared strings
     * @param StyleManager $styleManager Manages styles
     * @param bool $shouldFormatDates Whether date/time values should be returned as PHP objects or be formatted as strings
     * @param bool $shouldUse1904Dates Whether date/time values should use a calendar starting in 1904 instead of 1900
     * @param \Box\Spout\Common\Helper\Escaper\XLSX $escaper Used to unescape XML data
     */
    public function __construct($sharedStringsManager, $styleManager, $shouldFormatDates, $shouldUse1904Dates, $escaper)
    {
        $this->sharedStringsManager = $sharedStringsManager;
        $this->styleManager = $styleManager;
        $this->shouldFormatDates = $shouldFormatDates;
        $this->shouldUse1904Dates = $shouldUse1904Dates;
        $this->escaper = $escaper;
    }

    /**
     * Returns the (unescaped) correctly marshalled, cell value associated to the given XML node.
     *
     * @param \DOMNode $node
     * @return string|int|float|bool|\DateTime|null The value associated with the cell (null when the cell has an error)
     */
    public function extractAndFormatNodeValue($node)
    {
        // Default cell type is "n"
        $cellType = $node->getAttribute(self::XML_ATTRIBUTE_TYPE) ?: self::CELL_TYPE_NUMERIC;
        $cellStyleId = (int) $node->getAttribute(self::XML_ATTRIBUTE_STYLE_ID);
        $vNodeValue = $this->getVNodeValue($node);

        if (($vNodeValue === '') && ($cellType !== self::CELL_TYPE_INLINE_STRING)) {
            return $vNodeValue;
        }

        switch ($cellType) {
            case self::CELL_TYPE_INLINE_STRING:
                return $this->formatInlineStringCellValue($node);
            case self::CELL_TYPE_SHARED_STRING:
                return $this->formatSharedStringCellValue($vNodeValue);
            case self::CELL_TYPE_STR:
                return $this->formatStrCellValue($vNodeValue);
            case self::CELL_TYPE_BOOLEAN:
                return $this->formatBooleanCellValue($vNodeValue);
            case self::CELL_TYPE_NUMERIC:
                return $this->formatNumericCellValue($vNodeValue, $cellStyleId);
            case self::CELL_TYPE_DATE:
                return $this->formatDateCellValue($vNodeValue);
            default:
                return null;
        }
    }

    /**
     * Returns the cell's string value from a node's nested value node
     *
     * @param \DOMNode $node
     * @return string The value associated with the cell
     */
    protected function getVNodeValue($node)
    {
        // for cell types having a "v" tag containing the value.
        // if not, the returned value should be empty string.
        $vNode = $node->getElementsByTagName(self::XML_NODE_VALUE)->item(0);

        return ($vNode !== null) ? $vNode->nodeValue : '';
    }

    /**
     * Returns the cell String value where string is inline.
     *
     * @param \DOMNode $node
     * @return string The value associated with the cell (null when the cell has an error)
     */
    protected function formatInlineStringCellValue($node)
    {
        // inline strings are formatted this way:
        // <c r="A1" t="inlineStr"><is><t>[INLINE_STRING]</t></is></c>
        $tNode = $node->getElementsByTagName(self::XML_NODE_INLINE_STRING_VALUE)->item(0);
        $cellValue = $this->escaper->unescape($tNode->nodeValue);

        return $cellValue;
    }

    /**
     * Returns the cell String value from shared-strings file using nodeValue index.
     *
     * @param string $nodeValue
     * @return string The value associated with the cell (null when the cell has an error)
     */
    protected function formatSharedStringCellValue($nodeValue)
    {
        // shared strings are formatted this way:
        // <c r="A1" t="s"><v>[SHARED_STRING_INDEX]</v></c>
        $sharedStringIndex = (int) $nodeValue;
        $escapedCellValue = $this->sharedStringsManager->getStringAtIndex($sharedStringIndex);
        $cellValue = $this->escaper->unescape($escapedCellValue);

        return $cellValue;
    }

    /**
     * Returns the cell String value, where string is stored in value node.
     *
     * @param string $nodeValue
     * @return string The value associated with the cell (null when the cell has an error)
     */
    protected function formatStrCellValue($nodeValue)
    {
        $escapedCellValue = trim($nodeValue);
        $cellValue = $this->escaper->unescape($escapedCellValue);

        return $cellValue;
    }

    /**
     * Returns the cell Numeric value from string of nodeValue.
     * The value can also represent a timestamp and a DateTime will be returned.
     *
     * @param string $nodeValue
     * @param int $cellStyleId 0 being the default style
     * @return int|float|\DateTime|null The value associated with the cell
     */
    protected function formatNumericCellValue($nodeValue, $cellStyleId)
    {
        // Numeric values can represent numbers as well as timestamps.
        // We need to look at the style of the cell to determine whether it is one or the other.
        $shouldFormatAsDate = $this->styleManager->shouldFormatNumericValueAsDate($cellStyleId);

        if ($shouldFormatAsDate) {
            $cellValue = $this->formatExcelTimestampValue((float) $nodeValue, $cellStyleId);
        } else {
            $nodeIntValue = (int) $nodeValue;
            $nodeFloatValue = (float) $nodeValue;
            $cellValue = ((float) $nodeIntValue === $nodeFloatValue) ? $nodeIntValue : $nodeFloatValue;
        }

        return $cellValue;
    }

    /**
     * Returns a cell's PHP Date value, associated to the given timestamp.
     * NOTE: The timestamp is a float representing the number of days since the base Excel date:
     *       Dec 30th 1899, 1900 or Jan 1st, 1904, depending on the Workbook setting.
     * NOTE: The timestamp can also represent a time, if it is a value between 0 and 1.
     *
     * @see ECMA-376 Part 1 - §18.17.4
     *
     * @param float $nodeValue
     * @param int $cellStyleId 0 being the default style
     * @return \DateTime|null The value associated with the cell or NULL if invalid date value
     */
    protected function formatExcelTimestampValue($nodeValue, $cellStyleId)
    {
        if ($this->isValidTimestampValue($nodeValue)) {
            $cellValue = $this->formatExcelTimestampValueAsDateTimeValue($nodeValue, $cellStyleId);
        } else {
            // invalid date
            $cellValue = null;
        }

        return $cellValue;
    }

    /**
     * Returns whether the given timestamp is supported by SpreadsheetML
     * @see ECMA-376 Part 1 - §18.17.4 - this specifies the timestamp boundaries.
     *
     * @param $timestampValue
     * @return bool
     */
    protected function isValidTimestampValue($timestampValue)
    {
        // @NOTE: some versions of Excel don't support negative dates (e.g. Excel for Mac 2011)
        return (
            $this->shouldUse1904Dates && $timestampValue >= -695055 && $timestampValue <= 2957003.9999884 ||
            !$this->shouldUse1904Dates && $timestampValue >= -693593 && $timestampValue <= 2958465.9999884
        );
    }

    /**
     * Returns a cell's PHP DateTime value, associated to the given timestamp.
     * Only the time value matters. The date part is set to the base Excel date:
     * Dec 30th 1899, 1900 or Jan 1st, 1904, depending on the Workbook setting.
     *
     * @param float $nodeValue
     * @param int $cellStyleId 0 being the default style
     * @return \DateTime|string The value associated with the cell
     */
    protected function formatExcelTimestampValueAsDateTimeValue($nodeValue, $cellStyleId)
    {
        $baseDate = $this->shouldUse1904Dates ? '1904-01-01' : '1899-12-30';

        $daysSinceBaseDate = (int) $nodeValue;
        $timeRemainder = fmod($nodeValue, 1);
        $secondsRemainder = round($timeRemainder * self::NUM_SECONDS_IN_ONE_DAY, 0);

        $dateObj = \DateTime::createFromFormat('|Y-m-d', $baseDate);
        $dateObj->modify('+' . $daysSinceBaseDate . 'days');
        $dateObj->modify('+' . $secondsRemainder . 'seconds');

        if ($this->shouldFormatDates) {
            $styleNumberFormatCode = $this->styleManager->getNumberFormatCode($cellStyleId);
            $phpDateFormat = DateFormatHelper::toPHPDateFormat($styleNumberFormatCode);
            $cellValue = $dateObj->format($phpDateFormat);
        } else {
            $cellValue = $dateObj;
        }

        return $cellValue;
    }

    /**
     * Returns the cell Boolean value from a specific node's Value.
     *
     * @param string $nodeValue
     * @return bool The value associated with the cell
     */
    protected function formatBooleanCellValue($nodeValue)
    {
        return (bool) $nodeValue;
    }

    /**
     * Returns a cell's PHP Date value, associated to the given stored nodeValue.
     * @see ECMA-376 Part 1 - §18.17.4
     *
     * @param string $nodeValue ISO 8601 Date string
     * @return \DateTime|string|null The value associated with the cell or NULL if invalid date value
     */
    protected function formatDateCellValue($nodeValue)
    {
        // Mitigate thrown Exception on invalid date-time format (http://php.net/manual/en/datetime.construct.php)
        try {
            $cellValue = ($this->shouldFormatDates) ? $nodeValue : new \DateTime($nodeValue);
        } catch (\Exception $e) {
            $cellValue = null;
        }

        return $cellValue;
    }
}


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

box / spout

Pull Request — develop_3.0 (#483)

CellValueFormatter::formatDateCellValue() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like