mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-09-10 01:56:26 +00:00
oleibman
84de45c2f4
Based on recent discussions in #2938, it can do a bit better with date cells that do not use a recognized date style. Improve differentiating `date` from `date+time`, and in deciding whether 4-digit years have been used in the input cells. This causes a change to one test, where the old result incorrectly expected a 2-digit year (you can verify by opening the spreadsheet in question that it is saved with a 4-digit year).
282 lines
11 KiB
PHP
282 lines
11 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Reader\Ods;
|
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\DataType;
|
|
use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
|
|
use PhpOffice\PhpSpreadsheet\Reader\Exception as ReaderException;
|
|
use PhpOffice\PhpSpreadsheet\Reader\Ods;
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PhpOffice\PhpSpreadsheet\Style\Font;
|
|
use PHPUnit\Framework\TestCase;
|
|
|
|
class OdsTest extends TestCase
|
|
{
|
|
private const ODS_TEST_FILE = 'samples/templates/OOCalcTest.ods';
|
|
|
|
private const ODS_DATA_FILE = 'tests/data/Reader/Ods/data.ods';
|
|
|
|
private string $timeZone;
|
|
|
|
protected function setUp(): void
|
|
{
|
|
$this->timeZone = date_default_timezone_get();
|
|
date_default_timezone_set('UTC');
|
|
}
|
|
|
|
protected function tearDown(): void
|
|
{
|
|
date_default_timezone_set($this->timeZone);
|
|
}
|
|
|
|
private function loadOdsTestFile(): Spreadsheet
|
|
{
|
|
$reader = new Ods();
|
|
|
|
return $reader->loadIntoExisting(self::ODS_TEST_FILE, new Spreadsheet());
|
|
}
|
|
|
|
protected function loadDataFile(): Spreadsheet
|
|
{
|
|
$reader = new Ods();
|
|
|
|
return $reader->load(self::ODS_DATA_FILE);
|
|
}
|
|
|
|
public function testLoadWorksheets(): void
|
|
{
|
|
$spreadsheet = $this->loadDataFile();
|
|
self::assertEquals(2, $spreadsheet->getSheetCount());
|
|
self::assertEquals('Sheet1', $spreadsheet->getSheet(0)->getTitle());
|
|
self::assertEquals('Second Sheet', $spreadsheet->getSheet(1)->getTitle());
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testLoadOneWorksheet(): void
|
|
{
|
|
$reader = new Ods();
|
|
//$reader->setLoadSheetsOnly(['Sheet1']);
|
|
$names = $reader->listWorksheetNames(self::ODS_DATA_FILE);
|
|
$reader->setLoadSheetsOnly([$names[0]]);
|
|
$spreadsheet = $reader->load(self::ODS_DATA_FILE);
|
|
|
|
self::assertEquals(1, $spreadsheet->getSheetCount());
|
|
|
|
self::assertEquals('Sheet1', $spreadsheet->getSheet(0)->getTitle());
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testLoadOneWorksheetNotActive(): void
|
|
{
|
|
$reader = new Ods();
|
|
$reader->setLoadSheetsOnly(['Second Sheet']);
|
|
$spreadsheet = $reader->load(self::ODS_DATA_FILE);
|
|
|
|
self::assertEquals(1, $spreadsheet->getSheetCount());
|
|
|
|
self::assertEquals('Second Sheet', $spreadsheet->getSheet(0)->getTitle());
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testLoadNoSelectedWorksheet(): void
|
|
{
|
|
$this->expectException(PhpSpreadsheetException::class);
|
|
$this->expectExceptionMessage('You tried to set a sheet active by the out of bounds index');
|
|
$reader = new Ods();
|
|
$reader->setLoadSheetsOnly(['xSecond Sheet']);
|
|
$reader->load(self::ODS_DATA_FILE);
|
|
}
|
|
|
|
public function testLoadBadFile(): void
|
|
{
|
|
$this->expectException(ReaderException::class);
|
|
$this->expectExceptionMessage('Could not find zip member');
|
|
$reader = new Ods();
|
|
$reader->load(__FILE__);
|
|
}
|
|
|
|
public function testLoadCorruptFile(): void
|
|
{
|
|
$this->expectException(ReaderException::class);
|
|
$filename = 'tests/data/Reader/Ods/corruptMeta.ods';
|
|
$reader = new Ods();
|
|
$spreadsheet = $reader->load($filename);
|
|
|
|
self::assertEquals(2, $spreadsheet->getSheetCount());
|
|
|
|
$firstSheet = $spreadsheet->getSheet(0);
|
|
|
|
$secondSheet = $spreadsheet->getSheet(1);
|
|
self::assertNotSame($firstSheet, $secondSheet);
|
|
}
|
|
|
|
public function testReadValueAndComments(): void
|
|
{
|
|
$spreadsheet = $this->loadOdsTestFile();
|
|
|
|
$firstSheet = $spreadsheet->getSheet(0);
|
|
|
|
// 5 rows added to test file with this PR
|
|
self::assertEquals(34, $firstSheet->getHighestDataRow());
|
|
self::assertEquals('N', $firstSheet->getHighestDataColumn());
|
|
|
|
// Simple cell value
|
|
self::assertEquals('Test String 1', $firstSheet->getCell('A1')->getValue());
|
|
|
|
// Merged cell
|
|
self::assertEquals('BOX', $firstSheet->getCell('B18')->getValue());
|
|
|
|
// Comments/Annotations
|
|
self::assertEquals(
|
|
'Test for a simple colour-formatted string',
|
|
$firstSheet->getComment('A1')->getText()->getPlainText()
|
|
);
|
|
|
|
// Data types
|
|
self::assertEquals(DataType::TYPE_STRING, $firstSheet->getCell('A1')->getDataType());
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('B1')->getDataType()); // Int
|
|
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('B6')->getDataType()); // Float
|
|
self::assertEquals(1.23, $firstSheet->getCell('B6')->getValue());
|
|
self::assertEquals(0, $firstSheet->getCell('G10')->getValue());
|
|
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('A10')->getDataType()); // Date
|
|
self::assertEquals('19-Dec-1960', $firstSheet->getCell('A10')->getFormattedValue());
|
|
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('A13')->getDataType()); // Time
|
|
self::assertEquals('2:30:00', $firstSheet->getCell('A13')->getFormattedValue());
|
|
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('A15')->getDataType()); // Date + Time
|
|
self::assertEquals('19-Dec-60 1:30:00', $firstSheet->getCell('A15')->getFormattedValue());
|
|
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('A11')->getDataType()); // Fraction
|
|
|
|
self::assertEquals(DataType::TYPE_BOOL, $firstSheet->getCell('D6')->getDataType());
|
|
self::assertTrue($firstSheet->getCell('D6')->getValue());
|
|
|
|
self::assertEquals(DataType::TYPE_FORMULA, $firstSheet->getCell('C6')->getDataType()); // Formula
|
|
self::assertEquals('=TRUE()', $firstSheet->getCell('C6')->getValue()); // Formula
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testReadPercentageAndCurrency(): void
|
|
{
|
|
// Percentage, Currency
|
|
|
|
$spreadsheet = $this->loadDataFile();
|
|
|
|
$firstSheet = $spreadsheet->getSheet(0);
|
|
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('A1')->getDataType()); // Percentage (10%)
|
|
self::assertEquals(0.1, $firstSheet->getCell('A1')->getValue());
|
|
self::assertSame('10%', $firstSheet->getCell('A1')->getFormattedValue());
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('A2')->getDataType()); // Percentage (10%)
|
|
self::assertEquals(0.1, $firstSheet->getCell('A2')->getValue());
|
|
self::assertSame('10.00%', $firstSheet->getCell('A2')->getFormattedValue());
|
|
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('A2')->getDataType()); // Percentage (10.00%)
|
|
self::assertEquals(0.1, $firstSheet->getCell('A2')->getValue());
|
|
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('A4')->getDataType()); // Currency (€10.00)
|
|
self::assertSame(10.00, $firstSheet->getCell('A4')->getValue());
|
|
self::assertSame('10.00 €', $firstSheet->getCell('A4')->getFormattedValue());
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('B4')->getDataType()); // Currency ($10)
|
|
self::assertSame(10.00, $firstSheet->getCell('B4')->getValue());
|
|
self::assertSame('$10 ', $firstSheet->getCell('B4')->getFormattedValue());
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('C4')->getDataType()); // Currency (€10.12)
|
|
self::assertEquals(10.12, $firstSheet->getCell('C4')->getValue());
|
|
self::assertSame('10.12 €', $firstSheet->getCell('C4')->getFormattedValue());
|
|
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('A5')->getDataType()); // Currency ($20)
|
|
self::assertSame(20.00, $firstSheet->getCell('A5')->getValue());
|
|
self::assertSame('$20 ', $firstSheet->getCell('A5')->getFormattedValue());
|
|
self::assertEquals(DataType::TYPE_NUMERIC, $firstSheet->getCell('C5')->getDataType()); // Currency ($20.34)
|
|
self::assertEquals(20.34, $firstSheet->getCell('C5')->getValue());
|
|
self::assertSame('$20.34 ', $firstSheet->getCell('C5')->getFormattedValue());
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testReadColors(): void
|
|
{
|
|
$spreadsheet = $this->loadOdsTestFile();
|
|
$firstSheet = $spreadsheet->getSheet(0);
|
|
|
|
// Background color
|
|
|
|
$style = $firstSheet->getCell('K3')->getStyle();
|
|
|
|
self::assertEquals('solid', $style->getFill()->getFillType());
|
|
self::assertEquals('FFff0000', $style->getFill()->getStartColor()->getARGB());
|
|
self::assertEquals('FF000000', $style->getFill()->getEndColor()->getARGB());
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testReadRichText(): void
|
|
{
|
|
$spreadsheet = $this->loadOdsTestFile();
|
|
$firstSheet = $spreadsheet->getSheet(0);
|
|
|
|
self::assertEquals(
|
|
"I don't know if OOCalc supports Rich Text in the same way as Excel, "
|
|
. 'And this row should be autofit height with text wrap',
|
|
$firstSheet->getCell('A28')->getValue()
|
|
);
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testReadCellsWithRepeatedSpaces(): void
|
|
{
|
|
$spreadsheet = $this->loadDataFile();
|
|
$firstSheet = $spreadsheet->getSheet(0);
|
|
|
|
self::assertEquals('This has 4 spaces before and 2 after ', $firstSheet->getCell('A8')->getValue());
|
|
self::assertEquals('This only one after ', $firstSheet->getCell('A9')->getValue());
|
|
self::assertEquals('Test with DIFFERENT styles and multiple spaces: ', $firstSheet->getCell('A10')->getValue());
|
|
self::assertEquals("test with new \nLines", $firstSheet->getCell('A11')->getValue());
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
public function testReadHyperlinks(): void
|
|
{
|
|
$spreadsheet = $this->loadOdsTestFile();
|
|
$firstSheet = $spreadsheet->getSheet(0);
|
|
|
|
$hyperlink = $firstSheet->getCell('A29');
|
|
|
|
self::assertEquals(DataType::TYPE_STRING, $hyperlink->getDataType());
|
|
self::assertEquals('PhpSpreadsheet', $hyperlink->getValue());
|
|
self::assertEquals('https://github.com/PHPOffice/phpspreadsheet', $hyperlink->getHyperlink()->getUrl());
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
|
|
// Below some test for features not implemented yet
|
|
|
|
public function testReadBoldItalicUnderline(): void
|
|
{
|
|
$spreadsheet = $this->loadOdsTestFile();
|
|
$firstSheet = $spreadsheet->getSheet(0);
|
|
|
|
// Font styles
|
|
|
|
$style = $firstSheet->getCell('A1')->getStyle();
|
|
self::assertEquals('FFff0000', $style->getFont()->getColor()->getARGB());
|
|
self::assertEquals(11, $style->getFont()->getSize());
|
|
self::assertEquals(Font::UNDERLINE_NONE, $style->getFont()->getUnderline());
|
|
|
|
$style = $firstSheet->getCell('A3')->getStyle();
|
|
self::assertEquals(Font::UNDERLINE_SINGLE, $style->getFont()->getUnderline());
|
|
$style = $firstSheet->getCell('A7')->getStyle();
|
|
self::assertEquals(Font::UNDERLINE_DOUBLE, $style->getFont()->getUnderline());
|
|
self::assertFalse($style->getFont()->getStrikethrough());
|
|
$style = $firstSheet->getStyle('A8');
|
|
self::assertTrue($style->getFont()->getStrikethrough());
|
|
|
|
$style = $firstSheet->getCell('E1')->getStyle();
|
|
self::assertTrue($style->getFont()->getBold());
|
|
self::assertTrue($style->getFont()->getItalic());
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
}
|