mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-08-31 04:28:51 +00:00
oleibman
150b3849a8
Fix #4558. Fix #4530. 4558 deals with a problem with COUNTA. Excel does not count empty cells, but it does count literal nulls (usually empty parameters) in the argument list. PhpSpreadsheet has till now not created empty cells during calculation, and so winds up treating them as literals. It is changed to create the cell when appropriate. 4530 has a similar problem. When INDIRECT winds up evaluating an uninitialized cell, it treats it as a null literal, leading to incorrect results. It is now changed to create the missing cell. A number of functions, almost all of them Financial, have a related problem. They test for a null literal when initializing some of their parameters, but they should be testing for null literal or cell containing null. They are changed to do the right thing. SUMIF and related functions are slightly affected by this change, and are changed so that they are no longer affected. New tests have been added. Only one existing test had to change. MergeBehaviorTest for `[12, '=5+1', '=A1/A2']`, where cell A2 was uninitialized formerly returned one row, but now returns a second all-null row because A2, by virtue of being used in a calculation, now winds up defined. I do not consider this a significant difference.
35 lines
1.2 KiB
PHP
35 lines
1.2 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\Statistical;
|
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
|
|
class CountATest extends AllSetupTeardown
|
|
{
|
|
#[\PHPUnit\Framework\Attributes\DataProvider('providerCOUNTA')]
|
|
public function testCOUNTA(mixed $expectedResult, mixed ...$args): void
|
|
{
|
|
$this->runTestCases('COUNTA', $expectedResult, ...$args);
|
|
}
|
|
|
|
public static function providerCOUNTA(): array
|
|
{
|
|
return require 'tests/data/Calculation/Statistical/COUNTA.php';
|
|
}
|
|
|
|
public function testNull(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$sheet->setCellValue('A1', '=COUNTA(B1,B2,B3,B4)');
|
|
$sheet->setCellValue('A2', '=COUNTA(B1,,B3,B4)');
|
|
$sheet->setCellValue('A3', '=COUNTA(B1,B2,B3,B4)');
|
|
self::assertSame(0, $sheet->getCell('A1')->getCalculatedValue(), 'empty cells not counted');
|
|
self::assertSame(1, $sheet->getCell('A2')->getCalculatedValue(), 'null argument is counted');
|
|
self::assertSame(0, $sheet->getCell('A3')->getCalculatedValue(), 'empty cells still not counted');
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
}
|