| 1 |
efrain |
1 |
<?php
|
|
|
2 |
|
|
|
3 |
declare(strict_types=1);
|
|
|
4 |
|
|
|
5 |
namespace OpenSpout\Writer\XLSX\Helper;
|
|
|
6 |
|
|
|
7 |
use DateTimeInterface;
|
|
|
8 |
|
|
|
9 |
/**
|
|
|
10 |
* @internal
|
|
|
11 |
*/
|
|
|
12 |
final class DateHelper
|
|
|
13 |
{
|
|
|
14 |
/**
|
|
|
15 |
* @see https://github.com/PHPOffice/PhpSpreadsheet/blob/1.22.0/src/PhpSpreadsheet/Shared/Date.php#L296
|
|
|
16 |
*/
|
|
|
17 |
public static function toExcel(DateTimeInterface $dateTime): float
|
|
|
18 |
{
|
|
|
19 |
$year = (int) $dateTime->format('Y');
|
|
|
20 |
$month = (int) $dateTime->format('m');
|
|
|
21 |
$day = (int) $dateTime->format('d');
|
|
|
22 |
$hours = (int) $dateTime->format('H');
|
|
|
23 |
$minutes = (int) $dateTime->format('i');
|
|
|
24 |
$seconds = (int) $dateTime->format('s');
|
|
|
25 |
// Fudge factor for the erroneous fact that the year 1900 is treated as a Leap Year in MS Excel
|
|
|
26 |
// This affects every date following 28th February 1900
|
|
|
27 |
$excel1900isLeapYear = 1;
|
|
|
28 |
if ((1900 === $year) && ($month <= 2)) {
|
|
|
29 |
$excel1900isLeapYear = 0;
|
|
|
30 |
}
|
|
|
31 |
$myexcelBaseDate = 2415020;
|
|
|
32 |
|
|
|
33 |
// Julian base date Adjustment
|
|
|
34 |
if ($month > 2) {
|
|
|
35 |
$month -= 3;
|
|
|
36 |
} else {
|
|
|
37 |
$month += 9;
|
|
|
38 |
--$year;
|
|
|
39 |
}
|
|
|
40 |
|
|
|
41 |
// Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
|
|
|
42 |
$century = (int) substr((string) $year, 0, 2);
|
|
|
43 |
$decade = (int) substr((string) $year, 2, 2);
|
|
|
44 |
$excelDate =
|
|
|
45 |
floor((146097 * $century) / 4)
|
|
|
46 |
+ floor((1461 * $decade) / 4)
|
|
|
47 |
+ floor((153 * $month + 2) / 5)
|
|
|
48 |
+ $day
|
|
|
49 |
+ 1721119
|
|
|
50 |
- $myexcelBaseDate
|
|
|
51 |
+ $excel1900isLeapYear;
|
|
|
52 |
|
|
|
53 |
$excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;
|
|
|
54 |
|
|
|
55 |
return $excelDate + $excelTime;
|
|
|
56 |
}
|
|
|
57 |
}
|