mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-08-31 04:28:51 +00:00
oleibman
f1ce95eaa4
In issue #4557, the user complains, with some justification, about the way Excel handles certain calculations. We are not able to help with that problem. However, the user also notes a problem in Shared/Date when `isDateTime` has to evaluate a cell whose calculated value is an array. This is solved by flattening the calculated result to a single value. It became obvious while working on this change that the code to set `instanceArrayReturnType` was kind of awkward. Simpler methods `returnArrayAsArray` and `returnArrayAsValue` are added to `Spreadsheet`. Even these started out a bit awkward because `Spreadsheet::calculationEngine` was defined as nullable, which really isn't true. It is allocated by the constructor, and never freed except in the destructor. It is no longer nullable.
78 lines
2.8 KiB
PHP
78 lines
2.8 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Calculation;
|
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
|
|
use PhpOffice\PhpSpreadsheet\IOFactory;
|
|
use PhpOffice\PhpSpreadsheet\Worksheet\Table;
|
|
use PHPUnit\Framework\TestCase;
|
|
|
|
class StructuredReferenceFormulaTest extends TestCase
|
|
{
|
|
#[\PHPUnit\Framework\Attributes\DataProvider('structuredReferenceProvider')]
|
|
public function testStructuredReferences(float $expectedValue, string $cellAddress): void
|
|
{
|
|
$inputFileType = 'Xlsx';
|
|
$inputFileName = __DIR__ . '/../../data/Calculation/TableFormulae.xlsx';
|
|
|
|
$reader = IOFactory::createReader($inputFileType);
|
|
$spreadsheet = $reader->load($inputFileName);
|
|
|
|
$calculatedCellValue = $spreadsheet->getActiveSheet()->getCell($cellAddress)->getCalculatedValue();
|
|
self::assertEqualsWithDelta($expectedValue, $calculatedCellValue, 1.0e-14, "Failed calculation for cell {$cellAddress}");
|
|
}
|
|
|
|
public function testStructuredReferenceHiddenHeaders(): void
|
|
{
|
|
$inputFileType = 'Xlsx';
|
|
$inputFileName = __DIR__ . '/../../data/Calculation/TableFormulae.xlsx';
|
|
|
|
$reader = IOFactory::createReader($inputFileType);
|
|
$spreadsheet = $reader->load($inputFileName);
|
|
/** @var Table $table */
|
|
$table = $spreadsheet->getActiveSheet()->getTableByName('DeptSales');
|
|
|
|
$cellAddress = 'G8';
|
|
$spreadsheet->getActiveSheet()->getCell($cellAddress)->setValue('=DeptSales[[#Headers][Region]]');
|
|
$result = $spreadsheet->getActiveSheet()->getCell($cellAddress)->getCalculatedValue();
|
|
self::assertSame('Region', $result);
|
|
|
|
$spreadsheet->getCalculationEngine()->flushInstance();
|
|
$table->setShowHeaderRow(false);
|
|
|
|
$result = $spreadsheet->getActiveSheet()->getCell($cellAddress)->getCalculatedValue();
|
|
self::assertSame(ExcelError::REF(), $result);
|
|
}
|
|
|
|
public function testStructuredReferenceInvalidColumn(): void
|
|
{
|
|
$inputFileType = 'Xlsx';
|
|
$inputFileName = __DIR__ . '/../../data/Calculation/TableFormulae.xlsx';
|
|
|
|
$reader = IOFactory::createReader($inputFileType);
|
|
$spreadsheet = $reader->load($inputFileName);
|
|
|
|
$cellAddress = 'E2';
|
|
$spreadsheet->getActiveSheet()->getCell($cellAddress)->setValue('=[@Sales Amount]*[@[%age Commission]]');
|
|
|
|
$result = $spreadsheet->getActiveSheet()->getCell($cellAddress)->getCalculatedValue();
|
|
self::assertSame(ExcelError::REF(), $result);
|
|
}
|
|
|
|
public static function structuredReferenceProvider(): array
|
|
{
|
|
return [
|
|
[26.0, 'E2'],
|
|
[99.0, 'E3'],
|
|
[141.0, 'E4'],
|
|
[49.2, 'E5'],
|
|
[120.0, 'E6'],
|
|
[135.0, 'E7'],
|
|
[570.2, 'E8'],
|
|
[3970.0, 'C8'],
|
|
];
|
|
}
|
|
}
|