mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-08-22 01:43:42 +00:00
oleibman
e1bcab6ca9
Fix #3875. Even better, fix #2146, which has been open for 2.5 years. Empty arguments are improperly placed on the stack; in particular, they are added without `onlyIf` and `onlyIfNot` attributes.This results in problems described in 3875. IF has a somewhat unexpected design. In Excel, `IF(false, valueIfTrue)` evaluates as `false`, but `IF(false, valueIfTrue,)` evaluates as 0. This means that IF empty arguments should be handled in the same manner as MIN/MAX/MINA/MAXA, but you need to be careful to distinguish empty from omitted. Also note that IF requires 2 operands - `IF(true)` is an error, but `IF(true,)` evaluates to 0.
63 lines
2.7 KiB
PHP
63 lines
2.7 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Calculation;
|
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcExp;
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PHPUnit\Framework\TestCase;
|
|
|
|
class MissingArgumentsTest extends TestCase
|
|
{
|
|
/**
|
|
* @dataProvider providerMissingArguments
|
|
*/
|
|
public function testMissingArguments(mixed $expected, string $formula): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$sheet->getCell('A1')->setValue($formula);
|
|
$sheet->getCell('B1')->setValue(1);
|
|
|
|
try {
|
|
self::assertSame($expected, $sheet->getCell('A1')->getCalculatedValue());
|
|
} catch (CalcExp $e) {
|
|
self::assertSame('exception', $expected);
|
|
}
|
|
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public static function providerMissingArguments(): array
|
|
{
|
|
return [
|
|
'argument missing at end' => [0, '=min(3,2,)'],
|
|
'argument missing at beginning' => [0, '=mina(,3,2)'],
|
|
'argument missing in middle' => [0, '=min(3,,2)'],
|
|
'missing argument is not result' => [-2, '=min(3,-2,)'],
|
|
'max with missing argument' => [0, '=max(-3,-2,)'],
|
|
'maxa with missing argument' => [0, '=maxa(-3,-2,)'],
|
|
'max with null cell' => [-2, '=max(-3,-2,Z1)'],
|
|
'min with null cell' => [2, '=min(3,2,Z1)'],
|
|
'product ignores null argument' => [6.0, '=product(3,2,)'],
|
|
'embedded function' => [5, '=sum(3,2,min(3,2,))'],
|
|
'unaffected embedded function' => [8, '=sum(3,2,max(3,2,))'],
|
|
'if true missing at end' => [0, '=if(b1=1,min(3,2,),product(3,2,))'],
|
|
'if false missing at end' => [6.0, '=if(b1=2,min(3,2,),product(3,2,))'],
|
|
'if true missing in middle' => [0, '=if(b1=1,min(3,,2),product(3,,2))'],
|
|
'if false missing in middle' => [6.0, '=if(b1=2,min(3,,2),product(3,,2))'],
|
|
'if true missing at beginning' => [0, '=if(b1=1,min(,3,2),product(,3,2))'],
|
|
'if false missing at beginning' => [6.0, '=if(b1=2,min(,3,2),product(,3,2))'],
|
|
'if true nothing missing' => [2, '=if(b1=1,min(3,2),product(3,2))'],
|
|
'if false nothing missing' => [6.0, '=if(b1=2,min(3,2),product(3,2))'],
|
|
'if true empty arg' => [0, '=if(b1=1,)'],
|
|
'if true omitted args' => ['exception', '=if(b1=1)'],
|
|
'if true missing arg' => [0, '=if(b1=1,,6)'],
|
|
'if false missing arg' => [0, '=if(b1=2,6,)'],
|
|
'if false omitted arg' => [false, '=if(b1=2,6)'],
|
|
'multiple ifs and omissions' => [0, '=IF(0<9,,IF(0=0,,1))'],
|
|
];
|
|
}
|
|
}
|