|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
declare(strict_types=1); |
|
4
|
|
|
|
|
5
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\Information; |
|
6
|
|
|
|
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\NamedRange; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheetTests\Calculation\Functions\LookupRef\AllSetupTeardown; |
|
9
|
|
|
use PHPUnit\Framework\Attributes\DataProvider; |
|
10
|
|
|
|
|
11
|
|
|
class IsRefTest extends AllSetupTeardown |
|
12
|
|
|
{ |
|
13
|
|
|
#[DataProvider('providerIsRef')] |
|
14
|
|
|
public function testIsRef(mixed $expected, string $ref): void |
|
15
|
|
|
{ |
|
16
|
|
|
if ($expected === 'incomplete') { |
|
17
|
|
|
self::markTestIncomplete('Calculation is too complicated'); |
|
18
|
|
|
} |
|
19
|
|
|
$sheet = $this->getSheet(); |
|
20
|
|
|
|
|
21
|
|
|
$sheet->getParentOrThrow()->addDefinedName(new NamedRange('NAMED_RANGE', $sheet, 'C1')); |
|
22
|
|
|
$sheet->getCell('A1')->setValue("=ISREF($ref)"); |
|
23
|
|
|
self::assertSame($expected, $sheet->getCell('A1')->getCalculatedValue()); |
|
24
|
|
|
} |
|
25
|
|
|
|
|
26
|
|
|
public static function providerIsRef(): array |
|
27
|
|
|
{ |
|
28
|
|
|
return [ |
|
29
|
|
|
'cell reference' => [true, 'B1'], |
|
30
|
|
|
'invalid cell reference' => [false, 'ZZZ1'], |
|
31
|
|
|
'cell range' => [true, 'B1:B2'], |
|
32
|
|
|
'complex cell range' => [true, 'B1:D4 C1:C5'], |
|
33
|
|
|
'text string' => [false, '"PHP"'], |
|
34
|
|
|
'math expression' => [false, 'B1*B2'], |
|
35
|
|
|
'unquoted sheet name' => [true, 'Worksheet2!B1'], |
|
36
|
|
|
'quoted sheet name' => [true, "'Worksheet2'!B1:B2"], |
|
37
|
|
|
'quoted sheet name with apostrophe' => [true, "'Work''sheet2'!B1:B2"], |
|
38
|
|
|
'named range' => [true, 'NAMED_RANGE'], |
|
39
|
|
|
'unknown named range' => ['#NAME?', 'xNAMED_RANGE'], |
|
40
|
|
|
'indirect to a cell reference' => [true, 'INDIRECT("A1")'], |
|
41
|
|
|
'indirect to a worksheet/cell reference' => [true, 'INDIRECT("\'Worksheet\'!A1")'], |
|
42
|
|
|
'indirect to invalid worksheet/cell reference' => [false, 'INDIRECT("\'Invalid Worksheet\'!A1")'], |
|
43
|
|
|
'returned cell reference' => ['incomplete', 'CHOOSE(2, A1, B1, C1)'], |
|
44
|
|
|
]; |
|
45
|
|
|
} |
|
46
|
|
|
} |
|
47
|
|
|
|
PHPOffice /
PhpSpreadsheet
