tablepress / libraries / vendor / PhpSpreadsheet / Cell / AdvancedValueBinder.php

AdvancedValueBinder.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Cell/AdvancedValueBinder.php

213 lines 7.4 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Cell;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Engine\FormattedNumber;
7	use TablePress\PhpOffice\PhpSpreadsheet\Shared\Date;
8	use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper;
9	use TablePress\PhpOffice\PhpSpreadsheet\Style\NumberFormat;
10
11	class AdvancedValueBinder extends DefaultValueBinder implements IValueBinder
12	{
13	/**
14	* Bind value to a cell.
15	*
16	* @param Cell $cell Cell to bind value to
17	* @param mixed $value Value to bind in cell
18	*/
19	public function bindValue(Cell $cell, $value = null): bool
20	{
21	if ($value === null) {
22	return parent::bindValue($cell, $value);
23	} elseif (is_string($value)) {
24	// sanitize UTF-8 strings
25	$value = StringHelper::sanitizeUTF8($value);
26	}
27
28	// Find out data type
29	$dataType = parent::dataTypeForValue($value);
30
31	// Style logic - strings
32	if ($dataType === DataType::TYPE_STRING && is_string($value)) {
33	// Test for booleans using locale-setting
34	if (StringHelper::strToUpper($value) === Calculation::getTRUE()) {
35	$cell->setValueExplicit(true, DataType::TYPE_BOOL);
36
37	return true;
38	} elseif (StringHelper::strToUpper($value) === Calculation::getFALSE()) {
39	$cell->setValueExplicit(false, DataType::TYPE_BOOL);
40
41	return true;
42	}
43
44	// Check for fractions
45	if (preg_match('~^([+-]?)\s(\d+)\s/\s*(\d+)$~', $value, $matches)) {
46	return $this->setProperFraction($matches, $cell);
47	} elseif (preg_match('~^([+-]?)(\d+)\s+(\d+)\s/\s(\d+)$~', $value, $matches)) {
48	return $this->setImproperFraction($matches, $cell);
49	}
50
51	$decimalSeparatorNoPreg = StringHelper::getDecimalSeparator();
52	$decimalSeparator = preg_quote($decimalSeparatorNoPreg, '/');
53	$thousandsSeparator = preg_quote(StringHelper::getThousandsSeparator(), '/');
54
55	// Check for percentage
56	if (preg_match('/^\-?\d' . $decimalSeparator . '?\d\s?\%$/', (string) preg_replace('/(\d)' . $thousandsSeparator . '(\d)/u', '$1$2', $value))) {
57	return $this->setPercentage((string) preg_replace('/(\d)' . $thousandsSeparator . '(\d)/u', '$1$2', $value), $cell);
58	}
59
60	// Check for currency
61	if (preg_match(FormattedNumber::currencyMatcherRegexp(), (string) preg_replace('/(\d)' . $thousandsSeparator . '(\d)/u', '$1$2', $value), $matches, PREG_UNMATCHED_AS_NULL)) {
62	// Convert value to number
63	$sign = ($matches['PrefixedSign'] ?? $matches['PrefixedSign2'] ?? $matches['PostfixedSign']) ?? null; //* @phpstan-ignore nullCoalesce.unnecessary (Not sure whether Phpstan is correct)
64	$currencyCode = $matches['PrefixedCurrency'] ?? $matches['PostfixedCurrency'] ?? '';
65	/** @var string */
66	$temp = str_replace([$decimalSeparatorNoPreg, $currencyCode, ' ', '-'], ['.', '', '', ''], (string) preg_replace('/(\d)' . $thousandsSeparator . '(\d)/u', '$1$2', $value));
67	$value = (float) ($sign . trim($temp));
68
69	return $this->setCurrency($value, $cell, $currencyCode);
70	}
71
72	// Check for time without seconds e.g. '9:45', '09:45'
73	if (preg_match('/^(\d\|[0-1]\d\|2[0-3]):[0-5]\d$/', $value)) {
74	return $this->setTimeHoursMinutes($value, $cell);
75	}
76
77	// Check for time with seconds '9:45:59', '09:45:59'
78	if (preg_match('/^(\d\|[0-1]\d\|2[0-3]):[0-5]\d:[0-5]\d$/', $value)) {
79	return $this->setTimeHoursMinutesSeconds($value, $cell);
80	}
81
82	// Check for datetime, e.g. '2008-12-31', '2008-12-31 15:59', '2008-12-31 15:59:10'
83	if (($d = Date::stringToExcel($value)) !== false) {
84	// Convert value to number
85	$cell->setValueExplicit($d, DataType::TYPE_NUMERIC);
86	// Determine style. Either there is a time part or not. Look for ':'
87	if (str_contains($value, ':')) {
88	$formatCode = 'yyyy-mm-dd h:mm';
89	} else {
90	$formatCode = 'yyyy-mm-dd';
91	}
92	$cell->getWorksheet()->getStyle($cell->getCoordinate())
93	->getNumberFormat()->setFormatCode($formatCode);
94
95	return true;
96	}
97
98	// Check for newline character "\n"
99	if (str_contains($value, "\n")) {
100	$cell->setValueExplicit($value, DataType::TYPE_STRING);
101	// Set style
102	$cell->getWorksheet()->getStyle($cell->getCoordinate())
103	->getAlignment()->setWrapText(true);
104
105	return true;
106	}
107	}
108
109	// Not bound yet? Use parent...
110	return parent::bindValue($cell, $value);
111	}
112
113	/** @param array{0: string, 1: ?string, 2: numeric-string, 3: numeric-string, 4: numeric-string} $matches */
114	protected function setImproperFraction(array $matches, Cell $cell): bool
115	{
116	// Convert value to number
117	$value = $matches[2] + ($matches[3] / $matches[4]);
118	if ($matches[1] === '-') {
119	$value = 0 - $value;
120	}
121	$cell->setValueExplicit((float) $value, DataType::TYPE_NUMERIC);
122
123	// Build the number format mask based on the size of the matched values
124	$dividend = str_repeat('?', strlen($matches[3]));
125	$divisor = str_repeat('?', strlen($matches[4]));
126	$fractionMask = "# {$dividend}/{$divisor}";
127	// Set style
128	$cell->getWorksheet()->getStyle($cell->getCoordinate())
129	->getNumberFormat()->setFormatCode($fractionMask);
130
131	return true;
132	}
133
134	/** @param array{0: string, 1: ?string, 2: numeric-string, 3: numeric-string} $matches */
135	protected function setProperFraction(array $matches, Cell $cell): bool
136	{
137	// Convert value to number
138	$value = $matches[2] / $matches[3];
139	if ($matches[1] === '-') {
140	$value = 0 - $value;
141	}
142	$cell->setValueExplicit((float) $value, DataType::TYPE_NUMERIC);
143
144	// Build the number format mask based on the size of the matched values
145	$dividend = str_repeat('?', strlen($matches[2]));
146	$divisor = str_repeat('?', strlen($matches[3]));
147	$fractionMask = "{$dividend}/{$divisor}";
148	// Set style
149	$cell->getWorksheet()->getStyle($cell->getCoordinate())
150	->getNumberFormat()->setFormatCode($fractionMask);
151
152	return true;
153	}
154
155	protected function setPercentage(string $value, Cell $cell): bool
156	{
157	// Convert value to number
158	$value = ((float) str_replace('%', '', $value)) / 100;
159	$cell->setValueExplicit($value, DataType::TYPE_NUMERIC);
160
161	// Set style
162	$cell->getWorksheet()->getStyle($cell->getCoordinate())
163	->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00);
164
165	return true;
166	}
167
168	protected function setCurrency(float $value, Cell $cell, string $currencyCode): bool
169	{
170	$cell->setValueExplicit($value, DataType::TYPE_NUMERIC);
171	// Set style
172	$cell->getWorksheet()->getStyle($cell->getCoordinate())
173	->getNumberFormat()->setFormatCode(
174	str_replace('$', '[$' . $currencyCode . ']', NumberFormat::FORMAT_CURRENCY_USD)
175	);
176
177	return true;
178	}
179
180	protected function setTimeHoursMinutes(string $value, Cell $cell): bool
181	{
182	// Convert value to number
183	[$hours, $minutes] = explode(':', $value);
184	$hours = (int) $hours;
185	$minutes = (int) $minutes;
186	$days = ($hours / 24) + ($minutes / 1440);
187	$cell->setValueExplicit($days, DataType::TYPE_NUMERIC);
188
189	// Set style
190	$cell->getWorksheet()->getStyle($cell->getCoordinate())
191	->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_TIME3);
192
193	return true;
194	}
195
196	protected function setTimeHoursMinutesSeconds(string $value, Cell $cell): bool
197	{
198	// Convert value to number
199	[$hours, $minutes, $seconds] = explode(':', $value);
200	$hours = (int) $hours;
201	$minutes = (int) $minutes;
202	$seconds = (int) $seconds;
203	$days = ($hours / 24) + ($minutes / 1440) + ($seconds / 86400);
204	$cell->setValueExplicit($days, DataType::TYPE_NUMERIC);
205
206	// Set style
207	$cell->getWorksheet()->getStyle($cell->getCoordinate())
208	->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_TIME4);
209
210	return true;
211	}
212	}
213