DateTest::providerDateTimeExcelToTimestamp1904() - Code Metrics - Inspection of "Merge pull request #4562 from oleibman/issue4557da..." - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( a907f1...ea97af )

unknown

created 2025-08-06 07:20 UTC

DateTest::providerDateTimeExcelToTimestamp1904() A

↳ Parent: DateTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	3
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	1
nc	1
nop	0
dl	0
loc	3
rs	10
c	0
b	0
f	0

<?php

declare(strict_types=1);

namespace PhpOffice\PhpSpreadsheetTests\Shared;

use DateTime;
use DateTimeInterface;
use DateTimeZone;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PHPUnit\Framework\Attributes\DataProvider;
use PHPUnit\Framework\TestCase;

class DateTest extends TestCase
{
    private int $excelCalendar;

    private ?DateTimeZone $dttimezone;

    protected function setUp(): void
    {
        $this->dttimezone = Date::getDefaultTimeZoneOrNull();
        $this->excelCalendar = Date::getExcelCalendar();
    }

    protected function tearDown(): void
    {
        Date::setDefaultTimeZone($this->dttimezone);
        Date::setExcelCalendar($this->excelCalendar);
    }

    public function testSetExcelCalendar(): void
    {
        $calendarValues = [
            Date::CALENDAR_MAC_1904,
            Date::CALENDAR_WINDOWS_1900,
        ];

        $spreadsheet = new Spreadsheet();
        foreach ($calendarValues as $calendarValue) {
            $result = Date::setExcelCalendar($calendarValue);
            self::assertTrue($result);
            $result = $spreadsheet->setExcelCalendar($calendarValue);
            self::assertTrue($result);
        }
        self::assertFalse($spreadsheet->setExcelCalendar(0));
        $spreadsheet->disconnectWorksheets();
    }

    public function testSetExcelCalendarWithInvalidValue(): void
    {
        $unsupportedCalendar = 2012;
        $result = Date::setExcelCalendar($unsupportedCalendar);
        self::assertFalse($result);
    }

    #[DataProvider('providerDateTimeExcelToTimestamp1900')]
    public function testDateTimeExcelToTimestamp1900(float|int $expectedResult, float|int $excelDateTimeValue): void
    {
        if ($expectedResult > PHP_INT_MAX || $expectedResult < PHP_INT_MIN) {
            self::markTestSkipped('Test invalid on 32-bit system.');
        }
        Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);

        $result = Date::excelToTimestamp($excelDateTimeValue);
        self::assertEquals($expectedResult, $result);
    }

    public static function providerDateTimeExcelToTimestamp1900(): array
    {
        return require 'tests/data/Shared/Date/ExcelToTimestamp1900.php';
    }

    #[DataProvider('providerDateTimeTimestampToExcel1900')]
    public function testDateTimeTimestampToExcel1900(float|int $expectedResult, float|int|string $unixTimestamp): void
    {
        Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);

        $result = Date::timestampToExcel($unixTimestamp);
        self::assertEqualsWithDelta($expectedResult, $result, 1E-5);
    }

    public static function providerDateTimeTimestampToExcel1900(): array
    {
        return require 'tests/data/Shared/Date/TimestampToExcel1900.php';
    }

    #[DataProvider('providerDateTimeDateTimeToExcel')]
    public function testDateTimeDateTimeToExcel(float|int $expectedResult, DateTimeInterface $dateTimeObject): void
    {
        Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);

        $result = Date::dateTimeToExcel($dateTimeObject);
        self::assertEqualsWithDelta($expectedResult, $result, 1E-5);
    }

    public static function providerDateTimeDateTimeToExcel(): array
    {
        return require 'tests/data/Shared/Date/DateTimeToExcel.php';
    }

    /**
     * @param array{0: int, 1: int, 2: int, 3: int, 4: int, 5: float|int} $args Array containing year/month/day/hours/minutes/seconds
     */
    #[DataProvider('providerDateTimeFormattedPHPToExcel1900')]
    public function testDateTimeFormattedPHPToExcel1900(mixed $expectedResult, ...$args): void
    {
        Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);

        $result = Date::formattedPHPToExcel(...$args); // @phpstan-ignore-line
        self::assertEqualsWithDelta($expectedResult, $result, 1E-5);
    }

    public static function providerDateTimeFormattedPHPToExcel1900(): array
    {
        return require 'tests/data/Shared/Date/FormattedPHPToExcel1900.php';
    }

    #[DataProvider('providerDateTimeExcelToTimestamp1904')]
    public function testDateTimeExcelToTimestamp1904(float|int $expectedResult, float|int $excelDateTimeValue): void
    {
        if ($expectedResult > PHP_INT_MAX || $expectedResult < PHP_INT_MIN) {
            self::markTestSkipped('Test invalid on 32-bit system.');
        }
        Date::setExcelCalendar(Date::CALENDAR_MAC_1904);

        $result = Date::excelToTimestamp($excelDateTimeValue);
        self::assertEquals($expectedResult, $result);
    }

    public static function providerDateTimeExcelToTimestamp1904(): array
    {
        return require 'tests/data/Shared/Date/ExcelToTimestamp1904.php';
    }

    #[DataProvider('providerDateTimeTimestampToExcel1904')]
    public function testDateTimeTimestampToExcel1904(mixed $expectedResult, float|int|string $unixTimestamp): void
    {
        Date::setExcelCalendar(Date::CALENDAR_MAC_1904);

        $result = Date::timestampToExcel($unixTimestamp);
        self::assertEqualsWithDelta($expectedResult, $result, 1E-5);
    }

    public static function providerDateTimeTimestampToExcel1904(): array
    {
        return require 'tests/data/Shared/Date/TimestampToExcel1904.php';
    }

    #[DataProvider('providerIsDateTimeFormatCode')]
    public function testIsDateTimeFormatCode(mixed $expectedResult, string $format): void
    {
        $result = Date::isDateTimeFormatCode($format);
        self::assertEquals($expectedResult, $result);
    }

    public static function providerIsDateTimeFormatCode(): array
    {
        return require 'tests/data/Shared/Date/FormatCodes.php';
    }

    #[DataProvider('providerDateTimeExcelToTimestamp1900Timezone')]
    public function testDateTimeExcelToTimestamp1900Timezone(float|int $expectedResult, float|int $excelDateTimeValue, string $timezone): void
    {
        if ($expectedResult > PHP_INT_MAX || $expectedResult < PHP_INT_MIN) {
            self::markTestSkipped('Test invalid on 32-bit system.');
        }
        Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);

        $result = Date::excelToTimestamp($excelDateTimeValue, $timezone);
        self::assertEquals($expectedResult, $result);
    }

    public static function providerDateTimeExcelToTimestamp1900Timezone(): array
    {
        return require 'tests/data/Shared/Date/ExcelToTimestamp1900Timezone.php';
    }

    public function testConvertIsoDateError(): void
    {
        Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);

        $this->expectException(Exception::class);
        $this->expectExceptionMessage('Non-string value supplied for Iso Date conversion');
        Date::convertIsoDate(false);
    }

    public function testVarious(): void
    {
        Date::setDefaultTimeZone('UTC');
        self::assertFalse(Date::stringToExcel('2019-02-29'));
        self::assertTrue((bool) Date::stringToExcel('2019-02-28'));
        self::assertTrue((bool) Date::stringToExcel('2019-02-28 11:18'));
        self::assertFalse(Date::stringToExcel('2019-02-28 11:71'));
        $timestamp1 = Date::stringToExcel('26.05.2025 14:28:00');
        $timestamp2 = Date::stringToExcel('26.05.2025 14:28:00.00');
        self::assertNotFalse($timestamp1);
        self::assertNotFalse($timestamp2);
        self::assertEqualsWithDelta($timestamp1, 45803.60277777778, 1.0E-10);
        self::assertSame($timestamp1, $timestamp2);

        $date = Date::PHPToExcel('2020-01-01');
        self::assertEquals(43831.0, $date);
        $phpDate = new DateTime('2020-01-02T00:00Z');
        $date = Date::PHPToExcel($phpDate);
        self::assertEquals(43832.0, $date);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('B1', 'x');
        /** @var float|int|string */
        $val = $sheet->getCell('B1')->getValue();
        self::assertFalse(Date::timestampToExcel($val));

        $cell = $sheet->getCell('A1');

        $cell->setValue($date);
        $sheet->getStyle('A1')
            ->getNumberFormat()
            ->setFormatCode(NumberFormat::FORMAT_DATE_DATETIME);
        self::assertTrue(Date::isDateTime($cell));

        $cella2 = $sheet->getCell('A2');

        $cella2->setValue('=A1+2');
        $sheet->getStyle('A2')
            ->getNumberFormat()
            ->setFormatCode(NumberFormat::FORMAT_DATE_DATETIME);
        self::assertTrue(Date::isDateTime($cella2));

        $cella3 = $sheet->getCell('A3');

        $cella3->setValue('=A1+4');
        $sheet->getStyle('A3')
            ->getNumberFormat()
            ->setFormatCode('0.00E+00');
        self::assertFalse(Date::isDateTime($cella3));

        $cella4 = $sheet->getCell('A4');

        $cella4->setValue('= 44 7510557347');
        $sheet->getStyle('A4')
            ->getNumberFormat()
            ->setFormatCode('yyyy-mm-dd');
        self::assertFalse(Date::isDateTime($cella4));
        $spreadsheet->disconnectWorksheets();
    }

    public function testArray(): void
    {
        $spreadsheet = new Spreadsheet();
        $spreadsheet->returnArrayAsArray();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 45000);
        $sheet->setCellValue('A2', 44000);
        $sheet->setCellValue('A3', 46000);
        $sheet->setCellValue('C1', '=SORT(A1:A3)');
        $sheet->setCellValue('D1', '=SORT(A1:A3)');
        $sheet->getStyle('C1')
            ->getNumberFormat()
            ->setFormatCode('yyyy-mm-dd');
        self::assertTrue(Date::isDateTime($sheet->getCell('C1')));
        self::assertFalse(Date::isDateTime($sheet->getCell('D1')));
        self::assertIsArray(
            $sheet->getCell('C1')->getCalculatedValue()
        );
        $spreadsheet->disconnectWorksheets();
    }

    public function testRoundMicroseconds(): void
    {
        $dti = new DateTime('2000-01-02 03:04:05.999999');
        Date::roundMicroseconds($dti);
        self::assertEquals(new DateTime('2000-01-02 03:04:06.000000'), $dti);

        $dti = new DateTime('2000-01-02 03:04:05.500000');
        Date::roundMicroseconds($dti);
        self::assertEquals(new DateTime('2000-01-02 03:04:06.000000'), $dti);

        $dti = new DateTime('2000-01-02 03:04:05.499999');
        Date::roundMicroseconds($dti);
        self::assertEquals(new DateTime('2000-01-02 03:04:05.000000'), $dti);
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace PhpOffice\PhpSpreadsheetTests\Shared;
6
7			use DateTime;
8			use DateTimeInterface;
9			use DateTimeZone;
10			use PhpOffice\PhpSpreadsheet\Exception;
11			use PhpOffice\PhpSpreadsheet\Shared\Date;
12			use PhpOffice\PhpSpreadsheet\Spreadsheet;
13			use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
14			use PHPUnit\Framework\Attributes\DataProvider;
15			use PHPUnit\Framework\TestCase;
16
17			class DateTest extends TestCase
18			{
19			private int $excelCalendar;
20
21			private ?DateTimeZone $dttimezone;
22
23			protected function setUp(): void
24			{
25			$this->dttimezone = Date::getDefaultTimeZoneOrNull();
26			$this->excelCalendar = Date::getExcelCalendar();
27			}
28
29			protected function tearDown(): void
30			{
31			Date::setDefaultTimeZone($this->dttimezone);
32			Date::setExcelCalendar($this->excelCalendar);
33			}
34
35			public function testSetExcelCalendar(): void
36			{
37			$calendarValues = [
38			Date::CALENDAR_MAC_1904,
39			Date::CALENDAR_WINDOWS_1900,
40			];
41
42			$spreadsheet = new Spreadsheet();
43			foreach ($calendarValues as $calendarValue) {
44			$result = Date::setExcelCalendar($calendarValue);
45			self::assertTrue($result);
46			$result = $spreadsheet->setExcelCalendar($calendarValue);
47			self::assertTrue($result);
48			}
49			self::assertFalse($spreadsheet->setExcelCalendar(0));
50			$spreadsheet->disconnectWorksheets();
51			}
52
53			public function testSetExcelCalendarWithInvalidValue(): void
54			{
55			$unsupportedCalendar = 2012;
56			$result = Date::setExcelCalendar($unsupportedCalendar);
57			self::assertFalse($result);
58			}
59
60			#[DataProvider('providerDateTimeExcelToTimestamp1900')]
61			public function testDateTimeExcelToTimestamp1900(float\|int $expectedResult, float\|int $excelDateTimeValue): void
62			{
63			if ($expectedResult > PHP_INT_MAX \|\| $expectedResult < PHP_INT_MIN) {
64			self::markTestSkipped('Test invalid on 32-bit system.');
65			}
66			Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);
67
68			$result = Date::excelToTimestamp($excelDateTimeValue);
69			self::assertEquals($expectedResult, $result);
70			}
71
72			public static function providerDateTimeExcelToTimestamp1900(): array
73			{
74			return require 'tests/data/Shared/Date/ExcelToTimestamp1900.php';
75			}
76
77			#[DataProvider('providerDateTimeTimestampToExcel1900')]
78			public function testDateTimeTimestampToExcel1900(float\|int $expectedResult, float\|int\|string $unixTimestamp): void
79			{
80			Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);
81
82			$result = Date::timestampToExcel($unixTimestamp);
83			self::assertEqualsWithDelta($expectedResult, $result, 1E-5);
84			}
85
86			public static function providerDateTimeTimestampToExcel1900(): array
87			{
88			return require 'tests/data/Shared/Date/TimestampToExcel1900.php';
89			}
90
91			#[DataProvider('providerDateTimeDateTimeToExcel')]
92			public function testDateTimeDateTimeToExcel(float\|int $expectedResult, DateTimeInterface $dateTimeObject): void
93			{
94			Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);
95
96			$result = Date::dateTimeToExcel($dateTimeObject);
97			self::assertEqualsWithDelta($expectedResult, $result, 1E-5);
98			}
99
100			public static function providerDateTimeDateTimeToExcel(): array
101			{
102			return require 'tests/data/Shared/Date/DateTimeToExcel.php';
103			}
104
105			/**
106			* @param array{0: int, 1: int, 2: int, 3: int, 4: int, 5: float\|int} $args Array containing year/month/day/hours/minutes/seconds
107			*/
108			#[DataProvider('providerDateTimeFormattedPHPToExcel1900')]
109			public function testDateTimeFormattedPHPToExcel1900(mixed $expectedResult, ...$args): void
110			{
111			Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);
112
113			$result = Date::formattedPHPToExcel(...$args); // @phpstan-ignore-line
114			self::assertEqualsWithDelta($expectedResult, $result, 1E-5);
115			}
116
117			public static function providerDateTimeFormattedPHPToExcel1900(): array
118			{
119			return require 'tests/data/Shared/Date/FormattedPHPToExcel1900.php';
120			}
121
122			#[DataProvider('providerDateTimeExcelToTimestamp1904')]
123			public function testDateTimeExcelToTimestamp1904(float\|int $expectedResult, float\|int $excelDateTimeValue): void
124			{
125			if ($expectedResult > PHP_INT_MAX \|\| $expectedResult < PHP_INT_MIN) {
126			self::markTestSkipped('Test invalid on 32-bit system.');
127			}
128			Date::setExcelCalendar(Date::CALENDAR_MAC_1904);
129
130			$result = Date::excelToTimestamp($excelDateTimeValue);
131			self::assertEquals($expectedResult, $result);
132			}
133
134			public static function providerDateTimeExcelToTimestamp1904(): array
135			{
136			return require 'tests/data/Shared/Date/ExcelToTimestamp1904.php';
137			}
138
139			#[DataProvider('providerDateTimeTimestampToExcel1904')]
140			public function testDateTimeTimestampToExcel1904(mixed $expectedResult, float\|int\|string $unixTimestamp): void
141			{
142			Date::setExcelCalendar(Date::CALENDAR_MAC_1904);
143
144			$result = Date::timestampToExcel($unixTimestamp);
145			self::assertEqualsWithDelta($expectedResult, $result, 1E-5);
146			}
147
148			public static function providerDateTimeTimestampToExcel1904(): array
149			{
150			return require 'tests/data/Shared/Date/TimestampToExcel1904.php';
151			}
152
153			#[DataProvider('providerIsDateTimeFormatCode')]
154			public function testIsDateTimeFormatCode(mixed $expectedResult, string $format): void
155			{
156			$result = Date::isDateTimeFormatCode($format);
157			self::assertEquals($expectedResult, $result);
158			}
159
160			public static function providerIsDateTimeFormatCode(): array
161			{
162			return require 'tests/data/Shared/Date/FormatCodes.php';
163			}
164
165			#[DataProvider('providerDateTimeExcelToTimestamp1900Timezone')]
166			public function testDateTimeExcelToTimestamp1900Timezone(float\|int $expectedResult, float\|int $excelDateTimeValue, string $timezone): void
167			{
168			if ($expectedResult > PHP_INT_MAX \|\| $expectedResult < PHP_INT_MIN) {
169			self::markTestSkipped('Test invalid on 32-bit system.');
170			}
171			Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);
172
173			$result = Date::excelToTimestamp($excelDateTimeValue, $timezone);
174			self::assertEquals($expectedResult, $result);
175			}
176
177			public static function providerDateTimeExcelToTimestamp1900Timezone(): array
178			{
179			return require 'tests/data/Shared/Date/ExcelToTimestamp1900Timezone.php';
180			}
181
182			public function testConvertIsoDateError(): void
183			{
184			Date::setExcelCalendar(Date::CALENDAR_WINDOWS_1900);
185
186			$this->expectException(Exception::class);
187			$this->expectExceptionMessage('Non-string value supplied for Iso Date conversion');
188			Date::convertIsoDate(false);
189			}
190
191			public function testVarious(): void
192			{
193			Date::setDefaultTimeZone('UTC');
194			self::assertFalse(Date::stringToExcel('2019-02-29'));
195			self::assertTrue((bool) Date::stringToExcel('2019-02-28'));
196			self::assertTrue((bool) Date::stringToExcel('2019-02-28 11:18'));
197			self::assertFalse(Date::stringToExcel('2019-02-28 11:71'));
198			$timestamp1 = Date::stringToExcel('26.05.2025 14:28:00');
199			$timestamp2 = Date::stringToExcel('26.05.2025 14:28:00.00');
200			self::assertNotFalse($timestamp1);
201			self::assertNotFalse($timestamp2);
202			self::assertEqualsWithDelta($timestamp1, 45803.60277777778, 1.0E-10);
203			self::assertSame($timestamp1, $timestamp2);
204
205			$date = Date::PHPToExcel('2020-01-01');
206			self::assertEquals(43831.0, $date);
207			$phpDate = new DateTime('2020-01-02T00:00Z');
208			$date = Date::PHPToExcel($phpDate);
209			self::assertEquals(43832.0, $date);
210
211			$spreadsheet = new Spreadsheet();
212			$sheet = $spreadsheet->getActiveSheet();
213			$sheet->setCellValue('B1', 'x');
214			/** @var float\|int\|string */
215			$val = $sheet->getCell('B1')->getValue();
216			self::assertFalse(Date::timestampToExcel($val));
217
218			$cell = $sheet->getCell('A1');
219
220			$cell->setValue($date);
221			$sheet->getStyle('A1')
222			->getNumberFormat()
223			->setFormatCode(NumberFormat::FORMAT_DATE_DATETIME);
224			self::assertTrue(Date::isDateTime($cell));
225
226			$cella2 = $sheet->getCell('A2');
227
228			$cella2->setValue('=A1+2');
229			$sheet->getStyle('A2')
230			->getNumberFormat()
231			->setFormatCode(NumberFormat::FORMAT_DATE_DATETIME);
232			self::assertTrue(Date::isDateTime($cella2));
233
234			$cella3 = $sheet->getCell('A3');
235
236			$cella3->setValue('=A1+4');
237			$sheet->getStyle('A3')
238			->getNumberFormat()
239			->setFormatCode('0.00E+00');
240			self::assertFalse(Date::isDateTime($cella3));
241
242			$cella4 = $sheet->getCell('A4');
243
244			$cella4->setValue('= 44 7510557347');
245			$sheet->getStyle('A4')
246			->getNumberFormat()
247			->setFormatCode('yyyy-mm-dd');
248			self::assertFalse(Date::isDateTime($cella4));
249			$spreadsheet->disconnectWorksheets();
250			}
251
252			public function testArray(): void
253			{
254			$spreadsheet = new Spreadsheet();
255			$spreadsheet->returnArrayAsArray();
256			$sheet = $spreadsheet->getActiveSheet();
257			$sheet->setCellValue('A1', 45000);
258			$sheet->setCellValue('A2', 44000);
259			$sheet->setCellValue('A3', 46000);
260			$sheet->setCellValue('C1', '=SORT(A1:A3)');
261			$sheet->setCellValue('D1', '=SORT(A1:A3)');
262			$sheet->getStyle('C1')
263			->getNumberFormat()
264			->setFormatCode('yyyy-mm-dd');
265			self::assertTrue(Date::isDateTime($sheet->getCell('C1')));
266			self::assertFalse(Date::isDateTime($sheet->getCell('D1')));
267			self::assertIsArray(
268			$sheet->getCell('C1')->getCalculatedValue()
269			);
270			$spreadsheet->disconnectWorksheets();
271			}
272
273			public function testRoundMicroseconds(): void
274			{
275			$dti = new DateTime('2000-01-02 03:04:05.999999');
276			Date::roundMicroseconds($dti);
277			self::assertEquals(new DateTime('2000-01-02 03:04:06.000000'), $dti);
278
279			$dti = new DateTime('2000-01-02 03:04:05.500000');
280			Date::roundMicroseconds($dti);
281			self::assertEquals(new DateTime('2000-01-02 03:04:06.000000'), $dti);
282
283			$dti = new DateTime('2000-01-02 03:04:05.499999');
284			Date::roundMicroseconds($dti);
285			self::assertEquals(new DateTime('2000-01-02 03:04:05.000000'), $dti);
286			}
287			}
288

PHPOffice / PhpSpreadsheet

Push — master ( a907f1...ea97af )

DateTest::providerDateTimeExcelToTimestamp1904() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like