visualizer / vendor / phpoffice / phpspreadsheet / src / PhpSpreadsheet / Cell / AdvancedValueBinder.php

AdvancedValueBinder.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.5.1, at vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/AdvancedValueBinder.php

177 lines 7.2 KB

Raw Download Zip

1	<?php
2
3	namespace PhpOffice\PhpSpreadsheet\Cell;
4
5	use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6	use PhpOffice\PhpSpreadsheet\RichText\RichText;
7	use PhpOffice\PhpSpreadsheet\Shared\Date;
8	use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
9	use 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	* @throws \PhpOffice\PhpSpreadsheet\Exception
20	*
21	* @return bool
22	*/
23	public function bindValue(Cell $cell, $value = null)
24	{
25	// sanitize UTF-8 strings
26	if (is_string($value)) {
27	$value = StringHelper::sanitizeUTF8($value);
28	}
29
30	// Find out data type
31	$dataType = parent::dataTypeForValue($value);
32
33	// Style logic - strings
34	if ($dataType === DataType::TYPE_STRING && !$value instanceof RichText) {
35	// Test for booleans using locale-setting
36	if ($value == Calculation::getTRUE()) {
37	$cell->setValueExplicit(true, DataType::TYPE_BOOL);
38
39	return true;
40	} elseif ($value == Calculation::getFALSE()) {
41	$cell->setValueExplicit(false, DataType::TYPE_BOOL);
42
43	return true;
44	}
45
46	// Check for number in scientific format
47	if (preg_match('/^' . Calculation::CALCULATION_REGEXP_NUMBER . '$/', $value)) {
48	$cell->setValueExplicit((float) $value, DataType::TYPE_NUMERIC);
49
50	return true;
51	}
52
53	// Check for fraction
54	if (preg_match('/^([+-]?)\s(\d+)\s?\/\s(\d+)$/', $value, $matches)) {
55	// Convert value to number
56	$value = $matches[2] / $matches[3];
57	if ($matches[1] == '-') {
58	$value = 0 - $value;
59	}
60	$cell->setValueExplicit((float) $value, DataType::TYPE_NUMERIC);
61	// Set style
62	$cell->getWorksheet()->getStyle($cell->getCoordinate())
63	->getNumberFormat()->setFormatCode('??/??');
64
65	return true;
66	} elseif (preg_match('/^([+-]?)(\d) +(\d)\s?\/\s(\d)$/', $value, $matches)) {
67	// Convert value to number
68	$value = $matches[2] + ($matches[3] / $matches[4]);
69	if ($matches[1] == '-') {
70	$value = 0 - $value;
71	}
72	$cell->setValueExplicit((float) $value, DataType::TYPE_NUMERIC);
73	// Set style
74	$cell->getWorksheet()->getStyle($cell->getCoordinate())
75	->getNumberFormat()->setFormatCode('# ??/??');
76
77	return true;
78	}
79
80	// Check for percentage
81	if (preg_match('/^\-?\d\.?\d\s?\%$/', $value)) {
82	// Convert value to number
83	$value = (float) str_replace('%', '', $value) / 100;
84	$cell->setValueExplicit($value, DataType::TYPE_NUMERIC);
85	// Set style
86	$cell->getWorksheet()->getStyle($cell->getCoordinate())
87	->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00);
88
89	return true;
90	}
91
92	// Check for currency
93	$currencyCode = StringHelper::getCurrencyCode();
94	$decimalSeparator = StringHelper::getDecimalSeparator();
95	$thousandsSeparator = StringHelper::getThousandsSeparator();
96	if (preg_match('/^' . preg_quote($currencyCode, '/') . ' (\d{1,3}(' . preg_quote($thousandsSeparator, '/') . '\d{3})\|(\d+))(' . preg_quote($decimalSeparator, '/') . '\d{2})?$/', $value)) {
97	// Convert value to number
98	$value = (float) trim(str_replace([$currencyCode, $thousandsSeparator, $decimalSeparator], ['', '', '.'], $value));
99	$cell->setValueExplicit($value, DataType::TYPE_NUMERIC);
100	// Set style
101	$cell->getWorksheet()->getStyle($cell->getCoordinate())
102	->getNumberFormat()->setFormatCode(
103	str_replace('$', $currencyCode, NumberFormat::FORMAT_CURRENCY_USD_SIMPLE)
104	);
105
106	return true;
107	} elseif (preg_match('/^\$ (\d{1,3}(\,\d{3})\|(\d+))(\.\d{2})?$/', $value)) {
108	// Convert value to number
109	$value = (float) trim(str_replace(['$', ','], '', $value));
110	$cell->setValueExplicit($value, DataType::TYPE_NUMERIC);
111	// Set style
112	$cell->getWorksheet()->getStyle($cell->getCoordinate())
113	->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
114
115	return true;
116	}
117
118	// Check for time without seconds e.g. '9:45', '09:45'
119	if (preg_match('/^(\d\|[0-1]\d\|2[0-3]):[0-5]\d$/', $value)) {
120	// Convert value to number
121	list($h, $m) = explode(':', $value);
122	$days = $h / 24 + $m / 1440;
123	$cell->setValueExplicit($days, DataType::TYPE_NUMERIC);
124	// Set style
125	$cell->getWorksheet()->getStyle($cell->getCoordinate())
126	->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_TIME3);
127
128	return true;
129	}
130
131	// Check for time with seconds '9:45:59', '09:45:59'
132	if (preg_match('/^(\d\|[0-1]\d\|2[0-3]):[0-5]\d:[0-5]\d$/', $value)) {
133	// Convert value to number
134	list($h, $m, $s) = explode(':', $value);
135	$days = $h / 24 + $m / 1440 + $s / 86400;
136	// Convert value to number
137	$cell->setValueExplicit($days, DataType::TYPE_NUMERIC);
138	// Set style
139	$cell->getWorksheet()->getStyle($cell->getCoordinate())
140	->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_TIME4);
141
142	return true;
143	}
144
145	// Check for datetime, e.g. '2008-12-31', '2008-12-31 15:59', '2008-12-31 15:59:10'
146	if (($d = Date::stringToExcel($value)) !== false) {
147	// Convert value to number
148	$cell->setValueExplicit($d, DataType::TYPE_NUMERIC);
149	// Determine style. Either there is a time part or not. Look for ':'
150	if (strpos($value, ':') !== false) {
151	$formatCode = 'yyyy-mm-dd h:mm';
152	} else {
153	$formatCode = 'yyyy-mm-dd';
154	}
155	$cell->getWorksheet()->getStyle($cell->getCoordinate())
156	->getNumberFormat()->setFormatCode($formatCode);
157
158	return true;
159	}
160
161	// Check for newline character "\n"
162	if (strpos($value, "\n") !== false) {
163	$value = StringHelper::sanitizeUTF8($value);
164	$cell->setValueExplicit($value, DataType::TYPE_STRING);
165	// Set style
166	$cell->getWorksheet()->getStyle($cell->getCoordinate())
167	->getAlignment()->setWrapText(true);
168
169	return true;
170	}
171	}
172
173	// Not bound yet? Use parent...
174	return parent::bindValue($cell, $value);
175	}
176	}
177