Excel supports the following notations for cell styles:
- `[$-F800]` and `[$-x-sysdate]` will format the date according to what appears to be the user's system long date format.
- `[$-F400]` and `[$-x-systime]`, will format the time according to what appears to be the user's system long time format.
- Builtin style 14 will format date according to what appears to be the user's system short date format.
- Builtin style 22 will format date and time according to the user's preference. It appears that the date portion is formatted according to the user's system short date format, but the time portion is formatted according to a format which is neither system short time format nor system long time format, so I'm not sure how this preference is set.
For F800, sysdate, F400, and systime, any other characters in the style are ignored, except that, if you have more than one of this type of block in the style, Excel will treat it as corrupt (error message on open and style changed to General).
Support is added for the new codes. In addition, note that the value displayed in the cell may differ in different environments. To give the PhpSpreadsheet programmer an opportunity to emulate what the intended audience will most often see, properties `shortDateFormat` (default value is builtin 14), `longDateFormat` (default value is `dddd, mmmm d, yyyy`), `dateTimeFormat` (defaults to builtin 22), and `timeFormat` (default is `FORMAT_DATE_TIME2`), with corresponding setters and getters, are added to Style/NumberFormat. Note that, if these properties are set to some other value in PhpSpreadsheet, it will not affect the values in the cell or the style - it is merely a convenience for the programmer. It will, however, affect column width if autosize is specified for the column. If the programmer does not alter any of the new properties, the output should be unchanged from before for builtins 14 and 22.
The new styles are also recognized by the `TEXT` function. In this case, the cell's calculated value may differ from user to user.
Note that this is a small subset of adding locale information to styles. No attempt is made to support any of the other possibilities - locale data will continue to be passed through to the spreadsheet, but PhpSpreadsheet will discard it before attempting to generate the formatted value of a cell.
While we might never be able to have 100% of our code strict, we can at
the very least do it for all of our tests. This ensures that our tests
are using our API with the types as intended by the test author, and not
silently be cast to what our API requires.
* PHP8.1 Deprecation Passing Null to String Function
For each of the files in this PR, one or more statements can pass a null to string functions like strlower. This is deprecated in PHP8.1, and, when deprecated messages are enabled, causes many tests to error out. In every case, use coercion to pass null string rather than null.
* TextData - Minor Changes, Test Coverage
Per agreement on a previous push, I looked into standardizing the initialization of the TextData functions (like Engineering and MathTrig), with particular regard for avoiding multiple later null coercions. This simplifies the code quite a bit. This PR also increases coverage to 100% for all TextData modules. All entries in Phpstan baseline for non-deprecated TEXTDATA functions are removed. There were some minor bugfixes.
Whereas Excel (and Gnumeric) treat booleans when supplied as strings as 'TRUE' or 'FALSE', ODS treats them as '1' or '0'. Unlike Excel, ODS generally does not allow bool for int arguments; it does, however, allow them for FIND and SEARCH. ODS allows boolean for into for SUBSTITUTE even though Excel doesn't. ODS allows bool for string for NUMBERVALUE and VALUE even though Excel doesn't. ODS accepts 0 as an argument for CHAR; Excel doesn't. Most of this seems like random decisions on the part of the developers; I've done my best to follow the products in each case. There is a new test member devoted to ODS tests.
Gnumeric has an anomaly vis-a-vis the others - if length is supplied to LEFT/MID/RIGHT as null, Gnumeric treats it as 0 rather than 1.
All tests now take place in the context of a spreadsheet ...
Except for RETURNSTRING, which is not the implementation of an Excel function, and is referred to in the rest of PhpSpreadsheet only in the unit tests for itself. It should probably be deprecated, but that is not part of this PR, just in case there is some reason for it that I couldn't discern.
I have tried to make the first line of each doc block identify the Excel function name rather than its name in PhpSpreadsheet. I think it makes things more comprehensible.
Some tests call Settings::setLocale, but there was no Settings::getLocale. At the end of the tests which do it, they invoke setLocale('EN-US'), which, in a practical sense, is sufficient. However, in theory it would be better for them to get the current locale before changing it, then changing it back to the original when the time came. I have added getLocale and made the appropriate testing change.
The CHAR function took an interesting turn. One can set the value of a cell to, say, CHAR(2), the ASCII/UTF-8 representation of a control character, which is not legal in certain contexts. The only Reader/Writer that could handle this without problems is Xls, which deals with binary data all the time. However, if you tried to write it to Xlsx, Excel would not be able to open the resulting file because of what it considers an illegal character. I changed the Xlsx writer to escape such characters when writing the value of a string function. I did not make any other changes to the Xlsx writer - it seems to me that setting a cell to CHAR(2) is legitimate, but setting it to say `"\x02"` seems less likely to be legitimate, so the latter will still fail (although `="\x02"` should work). The Xlsx reader already supports the escape mechanism that I added to the writer.
CHAR control character and Ods - not supported by either Reader or Writer. I did not attempt to add this now. There is lots still missing from ODS, and this item just can't be a high priority amongst all of those.
CHAR control character and Csv - it is supported by reader and writer if the file has a csv extension. However, trying to guess the mime type without an extension - the control character makes mime_get_type guess application/octet-stream, and PhpSpreadsheet therefore thinks that Csv can't read it.
CHAR control character and Html. Actual use of the control character in the file is subject to the same problems as Xml (i.e. Xlsx and Ods). It wasn't terribly difficult to get the Html Writer to change `"\x02"` to "``". I believe that this is technically legal; however, DOMDocument.loadHTML rejects it as an illegal entity, and I am not convinced that it is wrong to do so, so I haven't changed the Html writer.
* Scrutinizer
Correct 3 minor errors.
oleibman
Adrien Crivelli
oleibman