tablepress / libraries / vendor / PhpSpreadsheet / Calculation / MathTrig / Subtotal.php

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

165 lines 4.6 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\MathTrig;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Exception;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
8	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Statistical;
9	use TablePress\PhpOffice\PhpSpreadsheet\Cell\Cell;
10
11	class Subtotal
12	{
13	/**
14	* @param mixed[] $args
15	*
16	* @return mixed[]
17	*/
18	protected static function filterHiddenArgs(Cell $cellReference, array $args): array
19	{
20	return array_filter(
21	$args,
22	function ($index) use ($cellReference) {
23	$explodeArray = explode('.', $index);
24	$row = $explodeArray[1] ?? '';
25	if (!is_numeric($row)) {
26	return true;
27	}
28
29	return $cellReference->getWorksheet()->getRowDimension((int) $row)->getVisible();
30	},
31	ARRAY_FILTER_USE_KEY
32	);
33	}
34
35	/**
36	* @param mixed[] $args
37	*
38	* @return mixed[]
39	*/
40	protected static function filterFilteredArgs(Cell $cellReference, array $args): array
41	{
42	return array_filter(
43	$args,
44	function ($index) use ($cellReference) {
45	$explodeArray = explode('.', $index);
46	$row = $explodeArray[1] ?? '';
47
48	return is_numeric($row) ? ($cellReference->getWorksheet()->getRowDimension((int) $row)->getVisibleAfterFilter()) : true;
49	},
50	ARRAY_FILTER_USE_KEY
51	);
52	}
53
54	/**
55	* @param mixed[] $args
56	*
57	* @return mixed[]
58	*/
59	protected static function filterFormulaArgs(Cell $cellReference, array $args): array
60	{
61	return array_filter(
62	$args,
63	function ($index) use ($cellReference): bool {
64	$explodeArray = explode('.', $index);
65	$row = $explodeArray[1] ?? '';
66	$column = $explodeArray[2] ?? '';
67	$retVal = true;
68	if ($cellReference->getWorksheet()->cellExists($column . $row)) {
69	//take this cell out if it contains the SUBTOTAL or AGGREGATE functions in a formula
70	$isFormula = $cellReference->getWorksheet()->getCell($column . $row)->isFormula();
71	$cellFormula = !preg_match(
72	'/^=.\b(SUBTOTAL\|AGGREGATE)\s\(/i',
73	$cellReference->getWorksheet()->getCell($column . $row)->getValueString()
74	);
75
76	$retVal = !$isFormula \|\| $cellFormula;
77	}
78
79	return $retVal;
80	},
81	ARRAY_FILTER_USE_KEY
82	);
83	}
84
85	/**
86	* @var array<int, callable>
87	*/
88	private const CALL_FUNCTIONS = [
89	1 => [Statistical\Averages::class, 'average'], // 1 and 101
90	[Statistical\Counts::class, 'COUNT'], // 2 and 102
91	[Statistical\Counts::class, 'COUNTA'], // 3 and 103
92	[Statistical\Maximum::class, 'max'], // 4 and 104
93	[Statistical\Minimum::class, 'min'], // 5 and 105
94	[Operations::class, 'product'], // 6 and 106
95	[Statistical\StandardDeviations::class, 'STDEV'], // 7 and 107
96	[Statistical\StandardDeviations::class, 'STDEVP'], // 8 and 108
97	[Sum::class, 'sumIgnoringStrings'], // 9 and 109
98	[Statistical\Variances::class, 'VAR'], // 10 and 110
99	[Statistical\Variances::class, 'VARP'], // 111 and 111
100	];
101
102	/**
103	* SUBTOTAL.
104	*
105	* Returns a subtotal in a list or database.
106	*
107	* @param mixed $functionType
108	* A number 1 to 11 that specifies which function to
109	* use in calculating subtotals within a range
110	* list
111	* Numbers 101 to 111 shadow the functions of 1 to 11
112	* but ignore any values in the range that are
113	* in hidden rows
114	* @param mixed[] $args A mixed data series of values
115	* @return float\|int\|string
116	*/
117	public static function evaluate($functionType, ...$args)
118	{
119	/** @var Cell */
120	$cellReference = array_pop($args);
121	$bArgs = Functions::flattenArrayIndexed($args);
122	$aArgs = [];
123	// int keys must come before string keys for PHP 8.0+
124	// Otherwise, PHP thinks positional args follow keyword
125	// in the subsequent call to call_user_func_array.
126	// Fortunately, order of args is unimportant to Subtotal.
127	foreach ($bArgs as $key => $value) {
128	if (is_int($key)) {
129	$aArgs[$key] = $value;
130	}
131	}
132	foreach ($bArgs as $key => $value) {
133	if (!is_int($key)) {
134	$aArgs[$key] = $value;
135	}
136	}
137
138	try {
139	$subtotal = (int) Helpers::validateNumericNullBool($functionType);
140	} catch (Exception $e) {
141	return $e->getMessage();
142	}
143
144	// Calculate
145	if ($subtotal > 100) {
146	$aArgs = self::filterHiddenArgs($cellReference, $aArgs);
147	$subtotal -= 100;
148	} else {
149	$aArgs = self::filterFilteredArgs($cellReference, $aArgs);
150	}
151
152	$aArgs = self::filterFormulaArgs($cellReference, $aArgs);
153	if (array_key_exists($subtotal, self::CALL_FUNCTIONS)) {
154	$call = self::CALL_FUNCTIONS[$subtotal];
155
156	/** @var float\|int\|string */
157	$temp = call_user_func_array($call, $aArgs);
158
159	return $temp;
160	}
161
162	return ExcelError::VALUE();
163	}
164	}
165