mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-09-16 21:16:57 +00:00
oleibman
b00dd47c28
Till now we have used a static variable/getter/setter to decide what type of result should be returned when a formula is evaluated and an array is the result. This is messy; it would be much better to use an instance variable instead. We cannot eliminate `setArrayReturnType` and `getArrayReturnType` because that would be a BC break. I am considering whether they should be deprecated. In the meantime, I have added a new instance property `instanceArrayReturnType` with getter and setter methods. The property is initially null, and, if it remains so when needed, the static property will be used instead. However, if it is set, its value will be used.
103 lines
4.1 KiB
PHP
103 lines
4.1 KiB
PHP
<?php
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Cell;
|
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
|
|
use PhpOffice\PhpSpreadsheet\Exception;
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PHPUnit\Framework\TestCase;
|
|
|
|
class CellArrayFormulaTest extends TestCase
|
|
{
|
|
private bool $skipUpdateInSpillageRange = true;
|
|
|
|
public function testSetValueArrayFormulaNoSpillage(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
|
|
$cell = $spreadsheet->getActiveSheet()->getCell('A1');
|
|
$cell->setValue('=MAX(ABS({5, -3; 1, -12}))');
|
|
|
|
self::assertSame(12, $cell->getCalculatedValue());
|
|
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testSetValueArrayFormulaWithSpillage(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
|
|
$cell = $spreadsheet->getActiveSheet()->getCell('A1');
|
|
$cell->setValue('=SEQUENCE(3, 3, 1, 1)');
|
|
|
|
self::assertSame([[1, 2, 3], [4, 5, 6], [7, 8, 9]], $cell->getCalculatedValue());
|
|
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testSetValueInSpillageRangeCell(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$cell = $sheet->getCell('A1');
|
|
$cell->setValue('=SEQUENCE(3, 3, 1, 1)');
|
|
|
|
$cellAddress = 'C3';
|
|
$sheet->getCell($cellAddress)->setValue('x');
|
|
|
|
self::assertSame('#SPILL!', $sheet->getCell('A1')->getCalculatedValue());
|
|
self::assertSame('x', $sheet->getCell('C3')->getCalculatedValue());
|
|
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testUpdateValueInSpillageRangeCell(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$sheet->getCell('A1')->setValue('=SEQUENCE(3, 3, 1, 1)');
|
|
$sheet->getCell('A1')->getCalculatedValue();
|
|
$attributes = $sheet->getCell('A1')->getFormulaAttributes();
|
|
if (!isset($attributes, $attributes['ref'])) {
|
|
self::fail('No formula attributes for cell A1');
|
|
}
|
|
$cellRange = $attributes['ref'];
|
|
$cellAddress = 'C3';
|
|
self::assertTrue($sheet->getCell($cellAddress)->isInRange($cellRange));
|
|
if ($this->skipUpdateInSpillageRange) {
|
|
$spreadsheet->disconnectWorksheets();
|
|
self::markTestIncomplete('Preventing update in spill range not yet implemented');
|
|
}
|
|
|
|
$this->expectException(Exception::class);
|
|
$this->expectExceptionMessage("Cell {$cellAddress} is within the spillage range of a formula, and cannot be changed");
|
|
$sheet->getCell($cellAddress)->setValue('PHP');
|
|
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testUpdateArrayFormulaForSpillageRange(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
$calculation = Calculation::getInstance($spreadsheet);
|
|
$calculation->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$sheet->getCell('A1')->setValue('=SEQUENCE(3, 3, 1, 1)');
|
|
$sheet->getCell('A1')->getCalculatedValue();
|
|
self::assertSame([[1, 2, 3], [4, 5, 6], [7, 8, 9]], $sheet->toArray(formatData: false, reduceArrays: true));
|
|
|
|
$sheet->getCell('A1')->setValue('=SEQUENCE(2, 2, 4, -1)');
|
|
$calculation->clearCalculationCache();
|
|
$sheet->getCell('A1')->getCalculatedValue();
|
|
self::assertSame([[4, 3, null], [2, 1, null], [null, null, null]], $sheet->toArray(formatData: false, reduceArrays: true));
|
|
|
|
$cellAddress = 'C3';
|
|
$sheet->getCell($cellAddress)->setValue('PHP');
|
|
self::assertSame('PHP', $sheet->getCell($cellAddress)->getValue(), 'change cell formerly in spill range');
|
|
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
}
|