mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-08-31 04:28:51 +00:00
oleibman
f3c3aba618
TOCOL and TOROW were introduced to Excel in 2024, and will now be supported by PhpSpreadsheet. The documentation says that, under certain circumstances, "blanks" will be ignored. This seems demonstrably wrong. In the right circumstance, Excel will ignore nulls, not blanks. Further, when it decides to not ignore the nulls, it changes them to 0, which also seems insufficiently documented. PhpSpreadsheet will behave as Excel does. I discovered some minor problems and some missing test conditions for the TRANSPOSE function while testing these. Those are now fixed.
60 lines
2.8 KiB
PHP
60 lines
2.8 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\LookupRef;
|
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
|
|
use PHPUnit\Framework\Attributes\DataProvider;
|
|
|
|
class TocolTest extends AllSetupTeardown
|
|
{
|
|
#[DataProvider('providerTocol')]
|
|
public function testTorow(mixed $expectedResult, mixed $ignore = 'omitted', mixed $byColumn = 'omitted'): void
|
|
{
|
|
$this->mightHaveException($expectedResult);
|
|
$sheet = $this->getSheet();
|
|
$this->setArrayAsArray();
|
|
if (is_string($ignore) && $ignore !== 'omitted') {
|
|
$ignore = '"' . $ignore . '"';
|
|
}
|
|
if (is_string($byColumn) && $byColumn !== 'omitted') {
|
|
$byColumn = '"' . $byColumn . '"';
|
|
}
|
|
$ignore = StringHelper::convertToString($ignore);
|
|
$byColumn = StringHelper::convertToString($byColumn, convertBool: true);
|
|
if ($ignore === 'omitted') {
|
|
$formula = '=TOCOL(A1:D3)';
|
|
} elseif ($byColumn === 'omitted') {
|
|
$formula = "=TOCOL(A1:D3,$ignore)";
|
|
} else {
|
|
$formula = "=TOCOL(A1:D3,$ignore,$byColumn)";
|
|
}
|
|
|
|
$data = [
|
|
['a-one', 'b-one', 'c-one', 'd-one'],
|
|
[null, 'b-two', 'c-two', '=2/0'],
|
|
[' ', 'b-three', 'c-three', 'd-three'],
|
|
];
|
|
$sheet->fromArray($data, null, 'A1', true);
|
|
$sheet->setCellValue('A5', $formula);
|
|
$result = $sheet->getCell('A5')->getCalculatedValue();
|
|
self::assertSame($expectedResult, $result);
|
|
}
|
|
|
|
public static function providerTocol(): array
|
|
{
|
|
return [
|
|
'defaults' => [[['a-one'], ['b-one'], ['c-one'], ['d-one'], [0], ['b-two'], ['c-two'], ['#DIV/0!'], [' '], ['b-three'], ['c-three'], ['d-three']]],
|
|
'ignore=0' => [[['a-one'], ['b-one'], ['c-one'], ['d-one'], [0], ['b-two'], ['c-two'], ['#DIV/0!'], [' '], ['b-three'], ['c-three'], ['d-three']], 0],
|
|
'ignore=1 supplied as 1.1' => [[['a-one'], ['b-one'], ['c-one'], ['d-one'], ['b-two'], ['c-two'], ['#DIV/0!'], [' '], ['b-three'], ['c-three'], ['d-three']], 1.1],
|
|
'ignore=2' => [[['a-one'], ['b-one'], ['c-one'], ['d-one'], [0], ['b-two'], ['c-two'], [' '], ['b-three'], ['c-three'], ['d-three']], 2],
|
|
'ignore=3' => [[['a-one'], ['b-one'], ['c-one'], ['d-one'], ['b-two'], ['c-two'], [' '], ['b-three'], ['c-three'], ['d-three']], 3],
|
|
'ignore=4 invalid' => ['#VALUE!', 4],
|
|
'ignore=string invalid' => ['#VALUE!', 'x'],
|
|
'by column' => [[['a-one'], [0], [' '], ['b-one'], ['b-two'], ['b-three'], ['c-one'], ['c-two'], ['c-three'], ['d-one'], ['#DIV/0!'], ['d-three']], 0, true],
|
|
'by column using float rather than bool, ignore=2' => [[['a-one'], [0], [' '], ['b-one'], ['b-two'], ['b-three'], ['c-one'], ['c-two'], ['c-three'], ['d-one'], ['d-three']], 2, 29.7],
|
|
];
|
|
}
|
|
}
|