mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-08-31 04:28:51 +00:00
oleibman
990e3ec3a3
Fix #4381. The report refers to COUNTIF, but AVERAGEIF and SUMIF, which are implemented in the same module, exhibit the same behavior. (There may be others, but, for now, I will just fix those 3.) Most methods which implement Excel functions should accept mixed arguments, so that they won't throw exceptions when calculated. Of course, MS often doesn't give much guidance as to how unexpected arguments should be handled. It at least seems clear that MS will often substitute #REF! for some arguments, and will return #REF! as the result in such cases. My test indicates that a formula using, say, #DIV/0! in lieu of #REF! will cause Excel to deem the spreadsheet corrupt. So, I think I am just going to deal with #REF! and let other unexpected values continue to throw exceptions.
50 lines
1.5 KiB
PHP
50 lines
1.5 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\Statistical;
|
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcException;
|
|
use PHPUnit\Framework\Attributes\DataProvider;
|
|
|
|
class CountIfTest extends AllSetupTeardown
|
|
{
|
|
#[DataProvider('providerCOUNTIF')]
|
|
public function testCOUNTIF(mixed $expectedResult, mixed ...$args): void
|
|
{
|
|
$this->runTestCaseNoBracket('COUNTIF', $expectedResult, ...$args);
|
|
}
|
|
|
|
public function testMultipleRows(): void
|
|
{
|
|
$sheet = $this->getSheet();
|
|
$sheet->fromArray([
|
|
['apples', 'oranges', 'peaches', 'apples'],
|
|
['bananas', 'mangoes', 'grapes', 'cherries'],
|
|
]);
|
|
$sheet->getCell('Z99')->setValue('=COUNTIF(A1:D2,"*p*e*")');
|
|
self::assertSame(4, $sheet->getCell('Z99')->getCalculatedValue());
|
|
}
|
|
|
|
public static function providerCOUNTIF(): array
|
|
{
|
|
return require 'tests/data/Calculation/Statistical/COUNTIF.php';
|
|
}
|
|
|
|
public function testOutliers(): void
|
|
{
|
|
$sheet = $this->getSheet();
|
|
$sheet->getCell('A1')->setValue('=COUNTIF(5,"<32")');
|
|
|
|
try {
|
|
$sheet->getCell('A1')->getCalculatedValue();
|
|
self::fail('Should receive exception for non-array arg');
|
|
} catch (CalcException $e) {
|
|
self::assertStringContainsString('Must specify range of cells', $e->getMessage());
|
|
}
|
|
|
|
$sheet->getCell('A4')->setValue('=COUNTIF(#REF!,1)');
|
|
self::assertSame('#REF!', $sheet->getCell('A4')->getCalculatedValue());
|
|
}
|
|
}
|