wpdatatables / lib / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Cell / AdvancedValueBinder.php

AdvancedValueBinder.php in wpDataTables – WordPress Data Table, Dynamic Tables & Table Charts Plugin 6.5.1.7, at lib/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/AdvancedValueBinder.php

212 lines 8.3 KB

Raw Download Zip

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