mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-09-10 10:06:30 +00:00
oleibman
378beac8f2
Fix #3369. The parser had failed to account for `TRUE` and `FALSE` when supplied as arguments to a function. I had hoped to be able to do something about its inability to handle defined names as well. I failed. I think another section might need to be added to the Writer output which specifies the defined names. I haven't yet located any suitable documentation.
100 lines
3.8 KiB
PHP
100 lines
3.8 KiB
PHP
<?php
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Reader\Xls;
|
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\Xls;
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PhpOffice\PhpSpreadsheet\Writer\Xls as WriterXls;
|
|
use PhpOffice\PhpSpreadsheetTests\Functional\AbstractFunctional;
|
|
|
|
class FormulasTest extends AbstractFunctional
|
|
{
|
|
public function testFormulas(): void
|
|
{
|
|
// This file was created with Excel 365.
|
|
$filename = 'tests/data/Reader/XLS/formulas.xls';
|
|
$reader = new Xls();
|
|
$spreadsheet = $reader->load($filename);
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$originalArray = $sheet->toArray(null, false, false, false);
|
|
|
|
$newSpreadsheet = $this->writeAndReload($spreadsheet, 'Xls');
|
|
$spreadsheet->disconnectWorksheets();
|
|
$newWorksheet = $newSpreadsheet->getActiveSheet();
|
|
$newArray = $newWorksheet->toArray(null, false, false, false);
|
|
self::assertEquals($originalArray, $newArray);
|
|
$newSpreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testDatabaseFormulas(): void
|
|
{
|
|
// This file was created with Excel 2003.
|
|
$filename = 'tests/data/Reader/XLS/formulas.database.xls';
|
|
$reader = new Xls();
|
|
$spreadsheet = $reader->load($filename);
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$originalArray = $sheet->toArray(null, false, false, false);
|
|
|
|
$newSpreadsheet = $this->writeAndReload($spreadsheet, 'Xls');
|
|
$spreadsheet->disconnectWorksheets();
|
|
$newWorksheet = $newSpreadsheet->getActiveSheet();
|
|
$newArray = $newWorksheet->toArray(null, false, false, false);
|
|
self::assertEquals($originalArray, $newArray);
|
|
$newSpreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testOtherFormulas(): void
|
|
{
|
|
// This file was created with Excel 2003.
|
|
$filename = 'tests/data/Reader/XLS/formulas.other.xls';
|
|
$reader = new Xls();
|
|
$spreadsheet = $reader->load($filename);
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$originalArray = $sheet->toArray(null, false, false, false);
|
|
|
|
$newSpreadsheet = $this->writeAndReload($spreadsheet, 'Xls');
|
|
$spreadsheet->disconnectWorksheets();
|
|
$newWorksheet = $newSpreadsheet->getActiveSheet();
|
|
$newArray = $newWorksheet->toArray(null, false, false, false);
|
|
self::assertSame($originalArray, $newArray);
|
|
$newSpreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public static function customizeWriter(WriterXls $writer): void
|
|
{
|
|
$writer->setPreCalculateFormulas(false);
|
|
}
|
|
|
|
public function testCaveatEmptor(): void
|
|
{
|
|
// This test confirms only that the 5 problematic functions
|
|
// in it are parsed correctly.
|
|
// When these are written to an Xls spreadsheet:
|
|
// Excel is buggy regarding their support; only BAHTTEXT
|
|
// will work as expected.
|
|
// LibreOffice handles them without problem.
|
|
// So does Gnumeric, except it doesn't implement BAHTTEXT.
|
|
$spreadsheet = new Spreadsheet();
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$originalArray = [
|
|
[1],
|
|
[2],
|
|
['=INDEX(TRANSPOSE(A1:A2),1,2)'],
|
|
['=BAHTTEXT(2)'],
|
|
['=CELL("ADDRESS",A3)'],
|
|
['=OFFSET(A3,-2,0)'],
|
|
['=GETPIVOTDATA("Sales",A3)'],
|
|
];
|
|
$sheet->fromArray($originalArray);
|
|
|
|
/** @var callable */
|
|
$writerCustomizer = [self::class, 'customizeWriter'];
|
|
$newSpreadsheet = $this->writeAndReload($spreadsheet, 'Xls', null, $writerCustomizer);
|
|
$spreadsheet->disconnectWorksheets();
|
|
$newWorksheet = $newSpreadsheet->getActiveSheet();
|
|
$newArray = $newWorksheet->toArray(null, false, false, false);
|
|
self::assertSame($originalArray, $newArray);
|
|
$newSpreadsheet->disconnectWorksheets();
|
|
}
|
|
}
|