mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-08-30 20:18:00 +00:00
MarkBaker
100 lines
3.6 KiB
PHP
100 lines
3.6 KiB
PHP
<?php
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Cell;
|
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\DataType;
|
|
use PhpOffice\PhpSpreadsheet\Exception;
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PHPUnit\Framework\TestCase;
|
|
|
|
class CellArrayFormulaTest extends TestCase
|
|
{
|
|
public function testSetValueArrayFormulaNoSpillage(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
$cell = $spreadsheet->getActiveSheet()->getCell('A1');
|
|
$cell->setValueExplicit('=MAX(ABS({5, -3; 1, -12}))', DataType::TYPE_FORMULA, true);
|
|
|
|
self::assertSame(12, $cell->getCalculatedValue());
|
|
self::assertTrue($cell->isArrayFormula());
|
|
self::assertSame('A1', $cell->arrayFormulaRange());
|
|
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testSetValueArrayFormulaWithSpillage(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
$cell = $spreadsheet->getActiveSheet()->getCell('A1');
|
|
$cell->setValueExplicit('=SEQUENCE(3, 3, 1, 1)', DataType::TYPE_FORMULA, true, 'A1:C3');
|
|
|
|
self::assertSame(1, $cell->getCalculatedValue());
|
|
self::assertTrue($cell->isArrayFormula());
|
|
self::assertSame('A1:C3', $cell->arrayFormulaRange());
|
|
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testSetValueInSpillageRangeCell(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
$cell = $spreadsheet->getActiveSheet()->getCell('A1');
|
|
$cell->setValueExplicit('=SEQUENCE(3, 3, 1, 1)', DataType::TYPE_FORMULA, true, 'A1:C3');
|
|
|
|
$cellAddress = 'C3';
|
|
$spillageCell = $spreadsheet->getActiveSheet()->getCell($cellAddress);
|
|
self::assertTrue($spillageCell->isInSpillageRange());
|
|
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testUpdateValueInSpillageRangeCell(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
$cell = $spreadsheet->getActiveSheet()->getCell('A1');
|
|
$cell->setValueExplicit('=SEQUENCE(3, 3, 1, 1)', DataType::TYPE_FORMULA, true, 'A1:C3');
|
|
|
|
$cellAddress = 'C3';
|
|
$spillageCell = $spreadsheet->getActiveSheet()->getCell($cellAddress);
|
|
self::assertTrue($spillageCell->isInSpillageRange());
|
|
|
|
$this->expectException(Exception::class);
|
|
$this->expectExceptionMessage("Cell {$cellAddress} is within the spillage range of a formula, and cannot be changed");
|
|
$spillageCell->setValue('PHP');
|
|
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testUpdateArrayFormulaForSpillageRange(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
$cell = $spreadsheet->getActiveSheet()->getCell('A1');
|
|
$cell->setValueExplicit('=SEQUENCE(3, 3, 1, 1)', DataType::TYPE_FORMULA, true, 'A1:C3');
|
|
|
|
$cell->setValueExplicit('=SEQUENCE(2, 2, 4, -1)', DataType::TYPE_FORMULA, true, 'A1:B2');
|
|
|
|
self::assertSame(4, $cell->getCalculatedValue());
|
|
self::assertTrue($cell->isArrayFormula());
|
|
self::assertSame('A1:B2', $cell->arrayFormulaRange());
|
|
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testSetValueToFormerSpillageCell(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
$cell = $spreadsheet->getActiveSheet()->getCell('A1');
|
|
$cell->setValueExplicit('=SEQUENCE(3, 3, 1, 1)', DataType::TYPE_FORMULA, true, 'A1:C3');
|
|
|
|
$cell->setValueExplicit('=SEQUENCE(2, 2, 4, -1)', DataType::TYPE_FORMULA, true, 'A1:B2');
|
|
|
|
$cellAddress = 'C3';
|
|
$formerSpillageCell = $spreadsheet->getActiveSheet()->getCell($cellAddress);
|
|
$formerSpillageCell->setValue('PHP');
|
|
|
|
self::assertSame('PHP', $formerSpillageCell->getValue());
|
|
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
}
|