tablepress / libraries / vendor / PhpSpreadsheet / Calculation / Functions.php

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

429 lines 11.2 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Cell\Cell;
6	use TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate;
7	use TablePress\PhpOffice\PhpSpreadsheet\Shared\Date;
8	use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper;
9
10	class Functions
11	{
12	const PRECISION = 8.88E-016;
13
14	/**
15	* 2 / PI.
16	*/
17	const M_2DIVPI = 0.63661977236758134307553505349006;
18
19	const COMPATIBILITY_EXCEL = 'Excel';
20	const COMPATIBILITY_GNUMERIC = 'Gnumeric';
21	const COMPATIBILITY_OPENOFFICE = 'OpenOfficeCalc';
22
23	/** Use of RETURNDATE_PHP_NUMERIC is discouraged - not 32-bit Y2038-safe, no timezone. */
24	const RETURNDATE_PHP_NUMERIC = 'P';
25	/** Use of RETURNDATE_UNIX_TIMESTAMP is discouraged - not 32-bit Y2038-safe, no timezone. */
26	const RETURNDATE_UNIX_TIMESTAMP = 'P';
27	const RETURNDATE_PHP_OBJECT = 'O';
28	const RETURNDATE_PHP_DATETIME_OBJECT = 'O';
29	const RETURNDATE_EXCEL = 'E';
30
31	public const NOT_YET_IMPLEMENTED = '#Not Yet Implemented';
32
33	/**
34	* Compatibility mode to use for error checking and responses.
35	*/
36	protected static string $compatibilityMode = self::COMPATIBILITY_EXCEL;
37
38	/**
39	* Data Type to use when returning date values.
40	*/
41	protected static string $returnDateType = self::RETURNDATE_EXCEL;
42
43	/**
44	* Set the Compatibility Mode.
45	*
46	* @param string $compatibilityMode Compatibility Mode
47	* Permitted values are:
48	* Functions::COMPATIBILITY_EXCEL 'Excel'
49	* Functions::COMPATIBILITY_GNUMERIC 'Gnumeric'
50	* Functions::COMPATIBILITY_OPENOFFICE 'OpenOfficeCalc'
51	*
52	* @return bool (Success or Failure)
53	*/
54	public static function setCompatibilityMode(string $compatibilityMode): bool
55	{
56	if (
57	($compatibilityMode == self::COMPATIBILITY_EXCEL)
58	\|\| ($compatibilityMode == self::COMPATIBILITY_GNUMERIC)
59	\|\| ($compatibilityMode == self::COMPATIBILITY_OPENOFFICE)
60	) {
61	self::$compatibilityMode = $compatibilityMode;
62
63	return true;
64	}
65
66	return false;
67	}
68
69	/**
70	* Return the current Compatibility Mode.
71	*
72	* @return string Compatibility Mode
73	* Possible Return values are:
74	* Functions::COMPATIBILITY_EXCEL 'Excel'
75	* Functions::COMPATIBILITY_GNUMERIC 'Gnumeric'
76	* Functions::COMPATIBILITY_OPENOFFICE 'OpenOfficeCalc'
77	*/
78	public static function getCompatibilityMode(): string
79	{
80	return self::$compatibilityMode;
81	}
82
83	/**
84	* Set the Return Date Format used by functions that return a date/time (Excel, PHP Serialized Numeric or PHP DateTime Object).
85	*
86	* @param string $returnDateType Return Date Format
87	* Permitted values are:
88	* Functions::RETURNDATE_UNIX_TIMESTAMP 'P'
89	* Functions::RETURNDATE_PHP_DATETIME_OBJECT 'O'
90	* Functions::RETURNDATE_EXCEL 'E'
91	*
92	* @return bool Success or failure
93	*/
94	public static function setReturnDateType(string $returnDateType): bool
95	{
96	if (
97	($returnDateType == self::RETURNDATE_UNIX_TIMESTAMP)
98	\|\| ($returnDateType == self::RETURNDATE_PHP_DATETIME_OBJECT)
99	\|\| ($returnDateType == self::RETURNDATE_EXCEL)
100	) {
101	self::$returnDateType = $returnDateType;
102
103	return true;
104	}
105
106	return false;
107	}
108
109	/**
110	* Return the current Return Date Format for functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object).
111	*
112	* @return string Return Date Format
113	* Possible Return values are:
114	* Functions::RETURNDATE_UNIX_TIMESTAMP 'P'
115	* Functions::RETURNDATE_PHP_DATETIME_OBJECT 'O'
116	* Functions::RETURNDATE_EXCEL ' 'E'
117	*/
118	public static function getReturnDateType(): string
119	{
120	return self::$returnDateType;
121	}
122
123	/**
124	* DUMMY.
125	*
126	* @return string #Not Yet Implemented
127	*/
128	public static function DUMMY(): string
129	{
130	return self::NOT_YET_IMPLEMENTED;
131	}
132
133	/**
134	* @param mixed $idx
135	*/
136	public static function isMatrixValue($idx): bool
137	{
138	$idx = StringHelper::convertToString($idx);
139
140	return (substr_count($idx, '.') <= 1) \|\| (preg_match('/\.[A-Z]/', $idx) > 0);
141	}
142
143	/**
144	* @param mixed $idx
145	*/
146	public static function isValue($idx): bool
147	{
148	$idx = StringHelper::convertToString($idx);
149
150	return substr_count($idx, '.') === 0;
151	}
152
153	/**
154	* @param mixed $idx
155	*/
156	public static function isCellValue($idx): bool
157	{
158	$idx = StringHelper::convertToString($idx);
159
160	return substr_count($idx, '.') > 1;
161	}
162
163	/**
164	* @param mixed $condition
165	*/
166	public static function ifCondition($condition): string
167	{
168	$condition = self::flattenSingleValue($condition);
169
170	if ($condition === '' \|\| $condition === null) {
171	return '=""';
172	}
173	if (!is_string($condition) \|\| !in_array($condition[0], ['>', '<', '='], true)) {
174	$condition = self::operandSpecialHandling($condition);
175	if (is_bool($condition)) {
176	return '=' . ($condition ? 'TRUE' : 'FALSE');
177	}
178	if (!is_numeric($condition)) {
179	if ($condition !== '""') { // Not an empty string
180	// Escape any quotes in the string value
181	$condition = (string) preg_replace('/"/ui', '""', $condition);
182	}
183	$condition = Calculation::wrapResult(strtoupper($condition));
184	}
185
186	return str_replace('""""', '""', '=' . StringHelper::convertToString($condition));
187	}
188	$operator = $operand = '';
189	if (1 === preg_match('/(=\|<[>=]?\|>=?)(.*)/', $condition, $matches)) {
190	[, $operator, $operand] = $matches;
191	}
192
193	$operand = (string) self::operandSpecialHandling($operand);
194	if (is_numeric(trim($operand, '"'))) {
195	$operand = trim($operand, '"');
196	} elseif (!is_numeric($operand) && $operand !== 'FALSE' && $operand !== 'TRUE') {
197	$operand = str_replace('"', '""', $operand);
198	$operand = Calculation::wrapResult(strtoupper($operand));
199	$operand = StringHelper::convertToString($operand);
200	}
201
202	return str_replace('""""', '""', $operator . $operand);
203	}
204
205	/**
206	* @return bool\|float\|int\|string
207	* @param mixed $operand
208	*/
209	private static function operandSpecialHandling($operand)
210	{
211	if (is_numeric($operand) \|\| is_bool($operand)) {
212	return $operand;
213	}
214	$operand = StringHelper::convertToString($operand);
215	if (strtoupper($operand) === Calculation::getTRUE() \|\| strtoupper($operand) === Calculation::getFALSE()) {
216	return strtoupper($operand);
217	}
218
219	// Check for percentage
220	if (preg_match('/^\-?\d\.?\d\s?\%$/', $operand)) {
221	return ((float) rtrim($operand, '%')) / 100;
222	}
223
224	// Check for dates
225	if (($dateValueOperand = Date::stringToExcel($operand)) !== false) {
226	return $dateValueOperand;
227	}
228
229	return $operand;
230	}
231
232	/**
233	* Convert a multi-dimensional array to a simple 1-dimensional array.
234	*
235	* @param mixed $array Array to be flattened
236	*
237	* @return array<mixed> Flattened array
238	*/
239	public static function flattenArray($array): array
240	{
241	if (!is_array($array)) {
242	return (array) $array;
243	}
244
245	$flattened = [];
246	$stack = array_values($array);
247
248	while (!empty($stack)) {
249	$value = array_shift($stack);
250
251	if (is_array($value)) {
252	array_unshift($stack, ...array_values($value));
253	} else {
254	$flattened[] = $value;
255	}
256	}
257
258	return $flattened;
259	}
260
261	/**
262	* Convert a multi-dimensional array to a simple 1-dimensional array.
263	* Same as above but argument is specified in ... format.
264	*
265	* @param mixed $array Array to be flattened
266	*
267	* @return array<mixed> Flattened array
268	*/
269	public static function flattenArray2(...$array): array
270	{
271	$flattened = [];
272	$stack = array_values($array);
273
274	while (!empty($stack)) {
275	$value = array_shift($stack);
276
277	if (is_array($value)) {
278	array_unshift($stack, ...array_values($value));
279	} else {
280	$flattened[] = $value;
281	}
282	}
283
284	return $flattened;
285	}
286
287	/**
288	* @param mixed $value
289	* @return mixed
290	*/
291	public static function scalar($value)
292	{
293	if (!is_array($value)) {
294	return $value;
295	}
296
297	do {
298	$value = array_pop($value);
299	} while (is_array($value));
300
301	return $value;
302	}
303
304	/**
305	* Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing.
306	*
307	* @param array\|mixed $array Array to be flattened
308	*
309	* @return array<mixed> Flattened array
310	*/
311	public static function flattenArrayIndexed($array): array
312	{
313	if (!is_array($array)) {
314	return (array) $array;
315	}
316
317	$arrayValues = [];
318	foreach ($array as $k1 => $value) {
319	if (is_array($value)) {
320	foreach ($value as $k2 => $val) {
321	if (is_array($val)) {
322	foreach ($val as $k3 => $v) {
323	$arrayValues[$k1 . '.' . $k2 . '.' . $k3] = $v;
324	}
325	} else {
326	$arrayValues[$k1 . '.' . $k2] = $val;
327	}
328	}
329	} else {
330	$arrayValues[$k1] = $value;
331	}
332	}
333
334	return $arrayValues;
335	}
336
337	/**
338	* Convert an array to a single scalar value by extracting the first element.
339	*
340	* @param mixed $value Array or scalar value
341	* @return mixed
342	*/
343	public static function flattenSingleValue($value)
344	{
345	while (is_array($value)) {
346	$value = array_shift($value);
347	}
348
349	return $value;
350	}
351
352	public static function expandDefinedName(string $coordinate, Cell $cell): string
353	{
354	$worksheet = $cell->getWorksheet();
355	$spreadsheet = $worksheet->getParentOrThrow();
356	// Uppercase coordinate
357	$pCoordinatex = strtoupper($coordinate);
358	// Eliminate leading equal sign
359	$pCoordinatex = (string) preg_replace('/^=/', '', $pCoordinatex);
360	$defined = $spreadsheet->getDefinedName($pCoordinatex, $worksheet);
361	if ($defined !== null) {
362	$worksheet2 = $defined->getWorkSheet();
363	if (!$defined->isFormula() && $worksheet2 !== null) {
364	$coordinate = "'" . $worksheet2->getTitle() . "'!"
365	. (string) preg_replace('/^=/', '', str_replace('$', '', $defined->getValue()));
366	}
367	}
368
369	return $coordinate;
370	}
371
372	public static function trimTrailingRange(string $coordinate): string
373	{
374	return (string) preg_replace('/:[\w\$]+$/', '', $coordinate);
375	}
376
377	public static function trimSheetFromCellReference(string $coordinate): string
378	{
379	if (str_contains($coordinate, '!')) {
380	$coordinate = (string) substr($coordinate, strrpos($coordinate, '!') + 1);
381	}
382
383	return $coordinate;
384	}
385
386	/** @param mixed[] $array */
387	public static function convertArrayToCellRange(array $array): string
388	{
389	$retVal = '';
390	$lastRow = $lastColumn = $firstRow = $firstColumn = 0;
391	foreach ($array as $rowkey => $row) {
392	if (!is_array($row) \|\| !is_int($rowkey) \|\| $rowkey < 1) {
393	$firstRow = 0;
394
395	break;
396	}
397	if ($firstRow > $rowkey \|\| $firstRow === 0) {
398	$firstRow = $rowkey;
399	}
400	if ($lastRow < $rowkey) {
401	$lastRow = $rowkey;
402	}
403	foreach ($row as $colkey => $cellValue) {
404	if (!preg_match('/^[A-Z]{1,3}$/', $colkey)) {
405	$firstRow = 0;
406
407	break 2;
408	}
409	$column = Coordinate::columnIndexFromString($colkey);
410	if ($firstColumn > $column \|\| $firstColumn === 0) {
411	$firstColumn = $column;
412	}
413	if ($lastColumn < $column) {
414	$lastColumn = $column;
415	}
416	}
417	}
418	if ($firstRow > 0 && $firstColumn > 0 && ($firstRow !== $lastRow \|\| $firstColumn !== $lastColumn)) {
419	$retVal = Coordinate::stringFromColumnIndex($firstColumn)
420	. $firstRow
421	. ':'
422	. Coordinate::stringFromColumnIndex($lastColumn)
423	. $lastRow;
424	}
425
426	return $retVal;
427	}
428	}
429