mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-08-22 09:23:40 +00:00
oleibman
9beacd21be
* Complete Breakup Of Calculation/DateTime Functions In conjunction with parallel breakups happening in other areas of Calculation, this change breaks up all the DateTime functions into their own classes. All methods remaining in DateTime itself have a doc block deprecation notice, and consist only of stub code to call the replacement methods. Coverage of DateTime itself and all the replacement methods is 100%. There is only one substantive change to the code (see next paragraph). Among the non-substantive changes, it now adopts the same parsing technique (throwing and catching exceptions) already in use in Engineering and MathTrig. Boolean parameters are allowed in lieu of numbers when Excel allows them. Most of the code changes involve refactoring due to the need to avoid Scrutinizer "complexity" failures in what it will consider to be new methods. Issue #1936 was opened just as I was staging this. It is now fixed. One existing WORKDAY test was wrong (noted in a comment in the test data file), and a bunch of new tests are added. I found it confusing to use DateTime as a node of the the class name since most of the methods invoke native DateTime methods. So, everything is moved to directory DateTimeExcel, and that is what is used in the class names. There are several follow-up activities that I am planning to undertake if this PR is merged. - ODS supports dates well before 1900. There are exactly 2 assertions for this functionality. More are needed (and some functions might have to change to accept this). - WEEKDAY has some poorly documented extra options for "style" which are not yet implemented. - Most tests have been changed to use a formula as entered on a spreadsheet rather than a direct call to the method which implements the formula. There are 3 exceptions at this time. WORKDAY and NETWORKDAYS, which include arrays as part of their parameters, are more complicated than most. YEARFRAC was just too large to deal with now. - There are direct calls to the now-deprecated methods in both source code and tests, mostly in Financial code, but possibly in others as well. These need to be changed. - Some constants, none "officially" documented, remain in the original class. These should be either deleted or marked deprecated. I wasn't sure if deprecation was even possible (or desirable), and did not want that to be something which would cause Scrutinizer to fail the change. * Deprecate Now-unused Constants, Fix Yearfrac bug, Change 3 Tests Add new DateTime/Constants class, initially populated with constants used in Weeknum. MS has another inconsistency with how it handles null cells in Yearfrac. Change PhpSpreadsheet to behave compatibly with this bug. I have modified YearFrac, WorkDay, and NetworkDays tests to be more to my liking. Many tests added to YearFrac because of the bug above. Only minor modifications to the existing tests for the others.
98 lines
3.5 KiB
PHP
98 lines
3.5 KiB
PHP
<?php
|
|
|
|
// Date String, Result
|
|
// Note that Excel fails ordinal number forms but PhpSpreadsheet parses them
|
|
|
|
return [
|
|
['#VALUE!', '"25-Dec-1899"'],
|
|
['#VALUE!', '"31-Dec-1899"'],
|
|
[1, '"1-Jan-1900"'],
|
|
[59, '"1900/2/28"'],
|
|
['60', '"29-02-1900"'],
|
|
['60', '"29th February 1900"'], // ordinal
|
|
[61, '"1900/3/1"'],
|
|
[713, '"13-12-1901"'],
|
|
[714, '"14-12-1901"'],
|
|
[1461, '"1903/12/31"'],
|
|
[1462, '"1-Jan-1904"'],
|
|
[1463, '"2nd-Jan-1904"'], // ordinal
|
|
[22269, '"19-12-1960"'],
|
|
[25569, '"1st January 1970"'], // ordinal
|
|
[30292, '"7-Dec-1982"'],
|
|
[39448, '"1-1-2008"'],
|
|
[50424, '"2038-01-19"'],
|
|
[39601, '"2-6-2008"'],
|
|
[39807, '"December 25th 2008"'], // ordinal
|
|
[39448, '"1 Jan-2008"'],
|
|
// MS Excel success or failure dependent on country settings
|
|
[39813, '"12-31-2008"'],
|
|
// PhpSpreadsheet tries to handle both US and UK formats, irrespective of country settings
|
|
[39813, '"31-12-2008"'],
|
|
// MS Excel success or failure dependent on country settings
|
|
[39682, '"8/22/2008"'],
|
|
// PhpSpreadsheet tries to handle both US and UK formats, irrespective of country settings
|
|
[39682, '"22/8/2008"'],
|
|
[39682, '"22/8/08"'],
|
|
[39682, '"22-AUG-2008"'],
|
|
[39501, '"2008/02/23"'],
|
|
[39635, '"6-7-2008"'],
|
|
// MS Excel success or failure dependent on country settings
|
|
[39141, '"28-2-2007"'],
|
|
// PhpSpreadsheet tries to handle both US and UK formats, irrespective of country settings
|
|
[39141, '"2-28-2007"'],
|
|
['#VALUE!', '"29-2-2007"'],
|
|
[36161, '"1/1/1999"'],
|
|
[19925, '"1954-07-20"'],
|
|
[36029, '"22 August 98"'],
|
|
[39142, '"1st March 2007"'], // ordinal
|
|
['#VALUE!', '"The 1st day of March 2007"'],
|
|
['Y-01-01', '"1 Jan"'], // Jan 1 of the current year
|
|
['Y-12-31', '"31/12"'], // Dec 31 of the current year
|
|
// Excel reads as 1st December 1931, not 31st December in current year.
|
|
// This result is locale-dependent in Excel, in a manner not
|
|
// supported by PhpSpreadsheet.
|
|
[11658, '"12/31"'],
|
|
['Y-07-05', '"5-JUL"'], // July 5 of the current year
|
|
['Y-07-05', '"5 July"'], // July 5 of the current year
|
|
[39783, '"12/2008"'],
|
|
[11963, '"10/32"'],
|
|
['#VALUE!', '11'],
|
|
['#VALUE!', 'true'],
|
|
['#VALUE!', 'false'],
|
|
['#VALUE!', '1'],
|
|
['#VALUE!', '12345'],
|
|
['#VALUE!', '12'],
|
|
[40210, '"Feb-2010"'], // implicit day of month is 1
|
|
[40221, '"12-Feb-2010"'],
|
|
[40221, '"Feb-12-2010"'], // MS Excel #VALUE!
|
|
[40221, '"February-12-2010"'], // MS Excel #VALUE!
|
|
[40221, '"February 12 2010"'], // MS Excel #VALUE!
|
|
[40227, '"18 Feb 2010"'],
|
|
[40254, '"17th 3rd 2010"'], // MS Excel #VALUE!
|
|
[40227, '"Feb 18th 2010"'], // MS Excel #VALUE!
|
|
[40210, '"1st Feb 2010"'], // MS Excel #VALUE!
|
|
[40210, '"1st-Feb-2010"'], // Excel #VALUE!
|
|
['#VALUE!', '"1me Fev 2010"'],
|
|
// MS Excel will fail with a #VALUE return, but PhpSpreadsheet can parse this date
|
|
[40210, '"February 1st 2010"'], // Excel #VALUE!
|
|
[40211, '"2nd Feb 2010"'], // Excel #VALUE!
|
|
['#VALUE!', '"Second Feb 2010"'],
|
|
['#VALUE!', '"First August 2010"'],
|
|
[40391, '"1st August 2010"'], // Excel #VALUE!
|
|
[0, '"15:30:25"'],
|
|
['#VALUE!', '"ABCDEFGHIJKMNOPQRSTUVWXYZ"'],
|
|
['#VALUE!', '1999'],
|
|
['#VALUE!', '"32/32"'],
|
|
['#VALUE!', '"1910-"'],
|
|
['#VALUE!', '"10--"'],
|
|
['#VALUE!', '"--10"'],
|
|
['#VALUE!', '"--1910"'],
|
|
//['#VALUE!', '-JUL-1910'], We can parse this, Excel can't
|
|
['#VALUE!', '"2008-08-"'],
|
|
[36751, '"0-08-13"'],
|
|
['#VALUE!', 'Q15'],
|
|
['#VALUE!', 'false'],
|
|
['#VALUE!', 'true'],
|
|
[19925, 'B1'],
|
|
];
|