mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-08-27 05:08:34 +00:00
oleibman
8ab834520d
Fix #2373. Excel can handle DateTime/Date/Time as a string if the datatype of the cell is set to "d". The string is, apparently, supposed to follow the ISO8601 spec. Openpyxl can be configured to generate a file with such values, so I've added support and set up unit tests. Excel, naturally, converts such a string input into its numeric representation of the date/time stamp. So will PhpSpreadsheet, so a call to setValueExplicit specifying Date format will actually see the cell wind up with Numeric format - there is no way (and no reason) for the Date type to 'stick'.
53 lines
1.9 KiB
PHP
53 lines
1.9 KiB
PHP
<?php
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Reader\Xlsx;
|
|
|
|
use PhpOffice\PhpSpreadsheet\IOFactory;
|
|
|
|
class ExplicitDateTest extends \PHPUnit\Framework\TestCase
|
|
{
|
|
/**
|
|
* @var string
|
|
*/
|
|
private static $testbook = 'tests/data/Reader/XLSX/explicitdate.xlsx';
|
|
|
|
public function testPreliminaries(): void
|
|
{
|
|
$file = 'zip://';
|
|
$file .= self::$testbook;
|
|
$file .= '#xl/worksheets/sheet1.xml';
|
|
$data = file_get_contents($file);
|
|
if ($data === false) {
|
|
self::fail('Unable to read file');
|
|
} else {
|
|
// confirm that file contains type "d" cells
|
|
self::assertStringContainsString('<c r="A3" s="1" t="d"><v>2021-12-31T23:44:51.894</v></c>', $data);
|
|
self::assertStringContainsString('<c r="B3" s="2" t="d"><v>2021-12-31</v></c>', $data);
|
|
self::assertStringContainsString('<c r="C3" s="3" t="d"><v>23:44:51.894</v></c>', $data);
|
|
}
|
|
}
|
|
|
|
public static function testExplicitDate(): void
|
|
{
|
|
$spreadsheet = IOFactory::load(self::$testbook);
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
// DateTime
|
|
$value = $sheet->getCell('A3')->getValue();
|
|
$formatted = $sheet->getCell('A3')->getFormattedValue();
|
|
self::assertEqualsWithDelta(44561.98948, $value, 0.00001);
|
|
self::assertSame('2021-12-31 23:44:51', $formatted);
|
|
// Date only
|
|
$value = $sheet->getCell('B3')->getValue();
|
|
$formatted = $sheet->getCell('B3')->getFormattedValue();
|
|
self::assertEquals(44561, $value);
|
|
self::assertSame('2021-12-31', $formatted);
|
|
// Time only
|
|
$value = $sheet->getCell('C3')->getValue();
|
|
$formatted = $sheet->getCell('C3')->getFormattedValue();
|
|
self::assertEqualsWithDelta(0.98948, $value, 0.00001);
|
|
self::assertSame('23:44:51', $formatted);
|
|
|
|
$spreadsheet->disconnectWorksheets();
|
|
}
|
|
}
|