Conditional::databaseFromRangeAndValue() - Code Metrics - Inspection of "Merge pull request #4565 from oleibman/issue4588" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Push — master ( ea97af...216db0 )

unknown

created 2025-08-07 05:03 UTC

Conditional::databaseFromRangeAndValue() A

↳ Parent: Conditional

Complexity

Conditions	2
Paths	2

Size

Total Lines	12
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	7
CRAP Score	2

Importance

Changes

Metric	Value
eloc	6
dl	0
loc	12
ccs	7
cts	7
cp	1
rs	10
c	0
b	0
f	0
cc	2
nc	2
nop	2
crap	2

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation\Statistical;

use PhpOffice\PhpSpreadsheet\Calculation\Database\DAverage;
use PhpOffice\PhpSpreadsheet\Calculation\Database\DCount;
use PhpOffice\PhpSpreadsheet\Calculation\Database\DMax;
use PhpOffice\PhpSpreadsheet\Calculation\Database\DMin;
use PhpOffice\PhpSpreadsheet\Calculation\Database\DSum;
use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcException;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;

class Conditional
{
    private const CONDITION_COLUMN_NAME = 'CONDITION';
    private const VALUE_COLUMN_NAME = 'VALUE';
    private const CONDITIONAL_COLUMN_NAME = 'CONDITIONAL %d';

    /**
     * AVERAGEIF.
     *
     * Returns the average value from a range of cells that contain numbers within the list of arguments
     *
     * Excel Function:
     *        AVERAGEIF(range,condition[, average_range])
     *
     * @param mixed $range Data values, expect array
     * @param null|mixed[]|string $condition the criteria that defines which cells will be checked
     * @param mixed $averageRange Data values
     */
    public static function AVERAGEIF(mixed $range, null|array|string $condition, mixed $averageRange = []): null|int|float|string
    {
        if (!is_array($range) || !is_array($averageRange) || array_key_exists(0, $range) || array_key_exists(0, $averageRange)) {
            $refError = ExcelError::REF();
            if (in_array($refError, [$range, $averageRange], true)) {
                return $refError;
            }

            throw new CalcException('Must specify range of cells, not any kind of literal');
        }
        $database = self::databaseFromRangeAndValue($range, $averageRange);
        $condition = Functions::flattenSingleValue($condition);
        $condition = [[self::CONDITION_COLUMN_NAME, self::VALUE_COLUMN_NAME], [$condition, null]];

        return DAverage::evaluate($database, self::VALUE_COLUMN_NAME, $condition);
    }

    /**
     * AVERAGEIFS.
     *
     * Counts the number of cells that contain numbers within the list of arguments
     *
     * Excel Function:
     *        AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
     *
     * @param mixed $args Pairs of Ranges and Criteria
     */
    public static function AVERAGEIFS(mixed ...$args): null|int|float|string
    {
        if (empty($args)) {
            return 0.0;
        }
        if (count($args) === 3) {
            return self::AVERAGEIF($args[1], $args[2], $args[0]); //* @phpstan-ignore-line
        }
        foreach ($args as $arg) {
            if (is_array($arg) && array_key_exists(0, $arg)) {
                throw new CalcException('Must specify range of cells, not any kind of literal');
            }
        }

        $conditions = self::buildConditionSetForValueRange(...$args);
        $database = self::buildDatabaseWithValueRange(...$args);

        return DAverage::evaluate($database, self::VALUE_COLUMN_NAME, $conditions);
    }

    /**
     * COUNTIF.
     *
     * Counts the number of cells that contain numbers within the list of arguments
     *
     * Excel Function:
     *        COUNTIF(range,condition)
     *
     * @param mixed $range Data values, expect array
     * @param null|mixed[]|string $condition the criteria that defines which cells will be counted
     */
    public static function COUNTIF(mixed $range, null|array|string $condition): string|int
    {
        if (
            !is_array($range)
            || array_key_exists(0, $range)
        ) {
            if ($range === ExcelError::REF()) {
                return $range;
            }

            throw new CalcException('Must specify range of cells, not any kind of literal');
        }
        // Filter out any empty values that shouldn't be included in a COUNT
        $range = array_filter(
            Functions::flattenArray($range),
            fn ($value): bool => $value !== null && $value !== ''
        );

        $range = array_merge([[self::CONDITION_COLUMN_NAME]], array_chunk($range, 1));
        $condition = Functions::flattenSingleValue($condition);
        $condition = array_merge([[self::CONDITION_COLUMN_NAME]], [[$condition]]);

        return DCount::evaluate($range, null, $condition, false);
    }

    /**
     * COUNTIFS.
     *
     * Counts the number of cells that contain numbers within the list of arguments
     *
     * Excel Function:
     *        COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
     *
     * @param mixed $args Pairs of Ranges and Criteria
     */
    public static function COUNTIFS(mixed ...$args): int|string
    {
        if (empty($args)) {
            return 0;
        } elseif (count($args) === 2) {
            return self::COUNTIF(...$args);
        }

        $database = self::buildDatabase(...$args);
        $conditions = self::buildConditionSet(...$args);

        return DCount::evaluate($database, null, $conditions, false);
    }

    /**
     * MAXIFS.
     *
     * Returns the maximum value within a range of cells that contain numbers within the list of arguments
     *
     * Excel Function:
     *        MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
     *
     * @param mixed $args Pairs of Ranges and Criteria
     */
    public static function MAXIFS(mixed ...$args): null|float|string
    {
        if (empty($args)) {
            return 0.0;
        }

        $conditions = self::buildConditionSetForValueRange(...$args);
        $database = self::buildDatabaseWithValueRange(...$args);

        return DMax::evaluate($database, self::VALUE_COLUMN_NAME, $conditions, false);
    }

    /**
     * MINIFS.
     *
     * Returns the minimum value within a range of cells that contain numbers within the list of arguments
     *
     * Excel Function:
     *        MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
     *
     * @param mixed $args Pairs of Ranges and Criteria
     */
    public static function MINIFS(mixed ...$args): null|float|string
    {
        if (empty($args)) {
            return 0.0;
        }

        $conditions = self::buildConditionSetForValueRange(...$args);
        $database = self::buildDatabaseWithValueRange(...$args);

        return DMin::evaluate($database, self::VALUE_COLUMN_NAME, $conditions, false);
    }

    /**
     * SUMIF.
     *
     * Totals the values of cells that contain numbers within the list of arguments
     *
     * Excel Function:
     *        SUMIF(range, criteria, [sum_range])
     *
     * @param mixed $range Data values, expecting array
     * @param mixed $sumRange Data values, expecting array
     */
    public static function SUMIF(mixed $range, mixed $condition, mixed $sumRange = []): null|float|string
    {
        if (
            !is_array($range)
            || array_key_exists(0, $range)
            || !is_array($sumRange)
            || array_key_exists(0, $sumRange)
        ) {
            $refError = ExcelError::REF();
            if (in_array($refError, [$range, $sumRange], true)) {
                return $refError;
            }

            throw new CalcException('Must specify range of cells, not any kind of literal');
        }
        $database = self::databaseFromRangeAndValue($range, $sumRange);
        $condition = Functions::flattenSingleValue($condition);
        $condition = [[self::CONDITION_COLUMN_NAME, self::VALUE_COLUMN_NAME], [$condition, null]];

        return DSum::evaluate($database, self::VALUE_COLUMN_NAME, $condition);
    }

    /**
     * SUMIFS.
     *
     * Counts the number of cells that contain numbers within the list of arguments
     *
     * Excel Function:
     *        SUMIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
     *
     * @param mixed $args Pairs of Ranges and Criteria
     */
    public static function SUMIFS(mixed ...$args): null|float|string
    {
        if (empty($args)) {
            return 0.0;
        } elseif (count($args) === 3) {
            return self::SUMIF($args[1], $args[2], $args[0]);
        }

        $conditions = self::buildConditionSetForValueRange(...$args);
        $database = self::buildDatabaseWithValueRange(...$args);

        return DSum::evaluate($database, self::VALUE_COLUMN_NAME, $conditions);
    }

    /**
     * @param mixed[] $args
     *
     * @return mixed[][]
     */
    private static function buildConditionSet(...$args): array
    {
        $conditions = self::buildConditions(1, ...$args);

        return array_map(null, ...$conditions);
    }

    /**
     * @param mixed[] $args
     *
     * @return mixed[][]
     */
    private static function buildConditionSetForValueRange(...$args): array
    {
        $conditions = self::buildConditions(2, ...$args);

        if (count($conditions) === 1) {
            return array_map(
                fn ($value): array => [$value],
                $conditions[0]
            );
        }

        return array_map(null, ...$conditions);
    }

    /**
     * @param mixed[] $args
     *
     * @return mixed[][]
     */
    private static function buildConditions(int $startOffset, ...$args): array
    {
        $conditions = [];

        $pairCount = 1;
        $argumentCount = count($args);
        for ($argument = $startOffset; $argument < $argumentCount; $argument += 2) {
            $conditions[] = array_merge([sprintf(self::CONDITIONAL_COLUMN_NAME, $pairCount)], [$args[$argument]]);
            ++$pairCount;
        }

        return $conditions;
    }

    /**
     * @param mixed[] $args
     *
     * @return mixed[]
     */
    private static function buildDatabase(...$args): array
    {
        $database = [];

        return self::buildDataSet(0, $database, ...$args);
    }

    /**
     * @param mixed[] $args
     *
     * @return mixed[]
     */
    private static function buildDatabaseWithValueRange(...$args): array
    {
        $database = [];
        $database[] = array_merge(
            [self::VALUE_COLUMN_NAME],
            Functions::flattenArray($args[0])
        );

        return self::buildDataSet(1, $database, ...$args);
    }

    /**
     * @param mixed[][] $database
     * @param mixed[] $args
     *
     * @return mixed[]
     */
    private static function buildDataSet(int $startOffset, array $database, ...$args): array
    {
        $pairCount = 1;
        $argumentCount = count($args);
        for ($argument = $startOffset; $argument < $argumentCount; $argument += 2) {
            $database[] = array_merge(
                [sprintf(self::CONDITIONAL_COLUMN_NAME, $pairCount)],
                Functions::flattenArray($args[$argument])
            );
            ++$pairCount;
        }

        return array_map(null, ...$database);
    }

    /**
     * @param mixed[] $range
     * @param mixed[] $valueRange
     *
     * @return mixed[]
     */
    private static function databaseFromRangeAndValue(array $range, array $valueRange = []): array
    {
        $range = Functions::flattenArray($range);

        $valueRange = Functions::flattenArray($valueRange);
        if (empty($valueRange)) {
            $valueRange = $range;
        }

        $database = array_map(null, array_merge([self::CONDITION_COLUMN_NAME], $range), array_merge([self::VALUE_COLUMN_NAME], $valueRange));

        return $database;
    }
}


1		<?php
2
3		namespace PhpOffice\PhpSpreadsheet\Calculation\Statistical;
4
5		use PhpOffice\PhpSpreadsheet\Calculation\Database\DAverage;
6		use PhpOffice\PhpSpreadsheet\Calculation\Database\DCount;
7		use PhpOffice\PhpSpreadsheet\Calculation\Database\DMax;
8		use PhpOffice\PhpSpreadsheet\Calculation\Database\DMin;
9		use PhpOffice\PhpSpreadsheet\Calculation\Database\DSum;
10		use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcException;
11		use PhpOffice\PhpSpreadsheet\Calculation\Functions;
12		use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
13
14		class Conditional
15		{
16		private const CONDITION_COLUMN_NAME = 'CONDITION';
17		private const VALUE_COLUMN_NAME = 'VALUE';
18		private const CONDITIONAL_COLUMN_NAME = 'CONDITIONAL %d';
19
20		/**
21		* AVERAGEIF.
22		*
23		* Returns the average value from a range of cells that contain numbers within the list of arguments
24		*
25		* Excel Function:
26		* AVERAGEIF(range,condition[, average_range])
27		*
28		* @param mixed $range Data values, expect array
29		* @param null\|mixed[]\|string $condition the criteria that defines which cells will be checked
30		* @param mixed $averageRange Data values
31		*/
32	18	public static function AVERAGEIF(mixed $range, null\|array\|string $condition, mixed $averageRange = []): null\|int\|float\|string
33		{
34	18	if (!is_array($range) \|\| !is_array($averageRange) \|\| array_key_exists(0, $range) \|\| array_key_exists(0, $averageRange)) {
35	2	$refError = ExcelError::REF();
36	2	if (in_array($refError, [$range, $averageRange], true)) {
37	1	return $refError;
38		}
39
40	2	throw new CalcException('Must specify range of cells, not any kind of literal');
41		}
42	18	$database = self::databaseFromRangeAndValue($range, $averageRange);
43	18	$condition = Functions::flattenSingleValue($condition);
44	18	$condition = [[self::CONDITION_COLUMN_NAME, self::VALUE_COLUMN_NAME], [$condition, null]];
45
46	18	return DAverage::evaluate($database, self::VALUE_COLUMN_NAME, $condition);
47		}
48
49		/**
50		* AVERAGEIFS.
51		*
52		* Counts the number of cells that contain numbers within the list of arguments
53		*
54		* Excel Function:
55		* AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
56		*
57		* @param mixed $args Pairs of Ranges and Criteria
58		*/
59	6	public static function AVERAGEIFS(mixed ...$args): null\|int\|float\|string
60		{
61	6	if (empty($args)) {
62		return 0.0;
63		}
64	6	if (count($args) === 3) {
65	2	return self::AVERAGEIF($args[1], $args[2], $args[0]); //* @phpstan-ignore-line
66		}
67	4	foreach ($args as $arg) {
68	4	if (is_array($arg) && array_key_exists(0, $arg)) {
69		throw new CalcException('Must specify range of cells, not any kind of literal');
70		}
71		}
72
73	4	$conditions = self::buildConditionSetForValueRange(...$args);
74	4	$database = self::buildDatabaseWithValueRange(...$args);
75
76	4	return DAverage::evaluate($database, self::VALUE_COLUMN_NAME, $conditions);
77		}
78
79		/**
80		* COUNTIF.
81		*
82		* Counts the number of cells that contain numbers within the list of arguments
83		*
84		* Excel Function:
85		* COUNTIF(range,condition)
86		*
87		* @param mixed $range Data values, expect array
88		* @param null\|mixed[]\|string $condition the criteria that defines which cells will be counted
89		*/
90	30	public static function COUNTIF(mixed $range, null\|array\|string $condition): string\|int
91		{
92		if (
93	30	!is_array($range)
94	30	\|\| array_key_exists(0, $range)
95		) {
96	1	if ($range === ExcelError::REF()) {
97	1	return $range;
98		}
99
100	1	throw new CalcException('Must specify range of cells, not any kind of literal');
101		}
102		// Filter out any empty values that shouldn't be included in a COUNT
103	29	$range = array_filter(
104	29	Functions::flattenArray($range),
105	29	fn ($value): bool => $value !== null && $value !== ''
106	29	);
107
108	29	$range = array_merge([[self::CONDITION_COLUMN_NAME]], array_chunk($range, 1));
109	29	$condition = Functions::flattenSingleValue($condition);
110	29	$condition = array_merge([[self::CONDITION_COLUMN_NAME]], [[$condition]]);
111
112	29	return DCount::evaluate($range, null, $condition, false);
113		}
114
115		/**
116		* COUNTIFS.
117		*
118		* Counts the number of cells that contain numbers within the list of arguments
119		*
120		* Excel Function:
121		* COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
122		*
123		* @param mixed $args Pairs of Ranges and Criteria
124		*/
125	6	public static function COUNTIFS(mixed ...$args): int\|string
126		{
127	6	if (empty($args)) {
128		return 0;
129	6	} elseif (count($args) === 2) {
130	3	return self::COUNTIF(...$args);
131		}
132
133	3	$database = self::buildDatabase(...$args);
134	3	$conditions = self::buildConditionSet(...$args);
135
136	3	return DCount::evaluate($database, null, $conditions, false);
137		}
138
139		/**
140		* MAXIFS.
141		*
142		* Returns the maximum value within a range of cells that contain numbers within the list of arguments
143		*
144		* Excel Function:
145		* MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
146		*
147		* @param mixed $args Pairs of Ranges and Criteria
148		*/
149	5	public static function MAXIFS(mixed ...$args): null\|float\|string
150		{
151	5	if (empty($args)) {
152		return 0.0;
153		}
154
155	5	$conditions = self::buildConditionSetForValueRange(...$args);
156	5	$database = self::buildDatabaseWithValueRange(...$args);
157
158	5	return DMax::evaluate($database, self::VALUE_COLUMN_NAME, $conditions, false);
159		}
160
161		/**
162		* MINIFS.
163		*
164		* Returns the minimum value within a range of cells that contain numbers within the list of arguments
165		*
166		* Excel Function:
167		* MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
168		*
169		* @param mixed $args Pairs of Ranges and Criteria
170		*/
171	5	public static function MINIFS(mixed ...$args): null\|float\|string
172		{
173	5	if (empty($args)) {
174		return 0.0;
175		}
176
177	5	$conditions = self::buildConditionSetForValueRange(...$args);
178	5	$database = self::buildDatabaseWithValueRange(...$args);
179
180	5	return DMin::evaluate($database, self::VALUE_COLUMN_NAME, $conditions, false);
181		}
182
183		/**
184		* SUMIF.
185		*
186		* Totals the values of cells that contain numbers within the list of arguments
187		*
188		* Excel Function:
189		* SUMIF(range, criteria, [sum_range])
190		*
191		* @param mixed $range Data values, expecting array
192		* @param mixed $sumRange Data values, expecting array
193		*/
194	23	public static function SUMIF(mixed $range, mixed $condition, mixed $sumRange = []): null\|float\|string
195		{
196		if (
197	23	!is_array($range)
198	23	\|\| array_key_exists(0, $range)
199	23	\|\| !is_array($sumRange)
200	23	\|\| array_key_exists(0, $sumRange)
201		) {
202	1	$refError = ExcelError::REF();
203	1	if (in_array($refError, [$range, $sumRange], true)) {
204	1	return $refError;
205		}
206
207	1	throw new CalcException('Must specify range of cells, not any kind of literal');
208		}
209	22	$database = self::databaseFromRangeAndValue($range, $sumRange);
210	22	$condition = Functions::flattenSingleValue($condition);
211	22	$condition = [[self::CONDITION_COLUMN_NAME, self::VALUE_COLUMN_NAME], [$condition, null]];
212
213	22	return DSum::evaluate($database, self::VALUE_COLUMN_NAME, $condition);
214		}
215
216		/**
217		* SUMIFS.
218		*
219		* Counts the number of cells that contain numbers within the list of arguments
220		*
221		* Excel Function:
222		* SUMIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
223		*
224		* @param mixed $args Pairs of Ranges and Criteria
225		*/
226	6	public static function SUMIFS(mixed ...$args): null\|float\|string
227		{
228	6	if (empty($args)) {
229	1	return 0.0;
230	5	} elseif (count($args) === 3) {
231	1	return self::SUMIF($args[1], $args[2], $args[0]);
232		}
233
234	4	$conditions = self::buildConditionSetForValueRange(...$args);
235	4	$database = self::buildDatabaseWithValueRange(...$args);
236
237	4	return DSum::evaluate($database, self::VALUE_COLUMN_NAME, $conditions);
238		}
239
240		/**
241		* @param mixed[] $args
242		*
243		* @return mixed[][]
244		*/
245	3	private static function buildConditionSet(...$args): array
246		{
247	3	$conditions = self::buildConditions(1, ...$args);
248
249	3	return array_map(null, ...$conditions);
250		}
251
252		/**
253		* @param mixed[] $args
254		*
255		* @return mixed[][]
256		*/
257	18	private static function buildConditionSetForValueRange(...$args): array
258		{
259	18	$conditions = self::buildConditions(2, ...$args);
260
261	18	if (count($conditions) === 1) {
262	4	return array_map(
263	4	fn ($value): array => [$value],
264	4	$conditions[0]
265	4	);
266		}
267
268	14	return array_map(null, ...$conditions);
269		}
270
271		/**
272		* @param mixed[] $args
273		*
274		* @return mixed[][]
275		*/
276	21	private static function buildConditions(int $startOffset, ...$args): array
277		{
278	21	$conditions = [];
279
280	21	$pairCount = 1;
281	21	$argumentCount = count($args);
282	21	for ($argument = $startOffset; $argument < $argumentCount; $argument += 2) {
283	21	$conditions[] = array_merge([sprintf(self::CONDITIONAL_COLUMN_NAME, $pairCount)], [$args[$argument]]);
284	21	++$pairCount;
285		}
286
287	21	return $conditions;
288		}
289
290		/**
291		* @param mixed[] $args
292		*
293		* @return mixed[]
294		*/
295	3	private static function buildDatabase(...$args): array
296		{
297	3	$database = [];
298
299	3	return self::buildDataSet(0, $database, ...$args);
300		}
301
302		/**
303		* @param mixed[] $args
304		*
305		* @return mixed[]
306		*/
307	18	private static function buildDatabaseWithValueRange(...$args): array
308		{
309	18	$database = [];
310	18	$database[] = array_merge(
311	18	[self::VALUE_COLUMN_NAME],
312	18	Functions::flattenArray($args[0])
313	18	);
314
315	18	return self::buildDataSet(1, $database, ...$args);
316		}
317
318		/**
319		* @param mixed[][] $database
320		* @param mixed[] $args
321		*
322		* @return mixed[]
323		*/
324	21	private static function buildDataSet(int $startOffset, array $database, ...$args): array
325		{
326	21	$pairCount = 1;
327	21	$argumentCount = count($args);
328	21	for ($argument = $startOffset; $argument < $argumentCount; $argument += 2) {
329	21	$database[] = array_merge(
330	21	[sprintf(self::CONDITIONAL_COLUMN_NAME, $pairCount)],
331	21	Functions::flattenArray($args[$argument])
332	21	);
333	21	++$pairCount;
334		}
335
336	21	return array_map(null, ...$database);
337		}
338
339		/**
340		* @param mixed[] $range
341		* @param mixed[] $valueRange
342		*
343		* @return mixed[]
344		*/
345	40	private static function databaseFromRangeAndValue(array $range, array $valueRange = []): array
346		{
347	40	$range = Functions::flattenArray($range);
348
349	40	$valueRange = Functions::flattenArray($valueRange);
350	40	if (empty($valueRange)) {
351	7	$valueRange = $range;
352		}
353
354	40	$database = array_map(null, array_merge([self::CONDITION_COLUMN_NAME], $range), array_merge([self::VALUE_COLUMN_NAME], $valueRange));
355
356	40	return $database;
357		}
358		}
359

PHPOffice / PhpSpreadsheet

Push — master ( ea97af...216db0 )

Conditional::databaseFromRangeAndValue() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like