We have identical constants defined in several places, and use literals in others. We aren't consistent in checking limits. This PR makes the use of the constants in Cell/AddressRange the "official" source, deprecates all other constants, and substitutes the constants wherever literals are used. A number of different edge case tests are added.
During testing, I discovered that `columnIndexFromString` correctly throws an exception for 4-character string, but allows `XFE` through `ZZZ`, all of which are also invalid. There are similar inconsistencies with related routines, and this PR attempts to make them operate consistently. One suprise is that throwing for `row=0` causes serious regression problems, so it continues to be permitted (but the high row limit is enforced).
Further, Reference Helper sometimes dips into negative numbers, resulting in totally unexpected results (-1 affects column Z, -2 column Y, etc.). It is changed to ignore rows and columns outside the limits.
The original issue leading to this PR was fixed by correctly treating a token in Calculator as a function rather than a defined name. However, it *should* have worked even when treating it as a defined name. There was a problem because Calculator was raising an exception for a missing defined name rather than returning `#NAME?`. It is now changed to return the error.
That change initially had some adverse affects for functions ROW, ROWS, COLUMN, and COLUMNS. Those are fixed to handle the change correctly. As a bonus, a test for each which had been commented out, because it didn't work, is now uncommented and works correctly. One test for ISFORMULA and one for ISREF were also changed - the old expected result did not reflect Excel's behavior and the new one does.
Sheet title and Defined Name matching used `strtoupper` to achieve case-insensitive compares. This handles only ASCII characters. They are changed to use a conversion routine which handles non-ASCII UTF-8 character sets.
Spill operator now works both as trailing `#` and ARRAYANCHOR function. `#` is converted to ARRAYANCHOR when writing. I do not think it is important to convert the other way when reading.
Documentation updates have started, but are a work in progress.
SINGLE function is implemented. I believe it works correctly when referring to a cell, but not when referring to a cell range. No attempt is yet made to convert leading `@` to and from SINGLE; I haven't figured out how to do so without interfering with `@` in structured references.
ISREF has problems. At least one of its tests was wrong, and many of those that were right were so accidentally. The code is changed, quite kludgily, so that almost all the tests are now deliberately correct. One very complicated test is incorrect; for now, I will skip it, and will open an issue when this PR is merged.
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.
* Clean Up Documentation for Worksheet
This PR was intended to clean up Phpstan/Scrutinizer messages regarding Worksheet. It is, for the most part, straightforward, but there is one problem which complicates things. The frequently-called public method `getParent` returns `Worksheet` or `null` but is documented to return only `Worksheet`. This can be addressed in either of two ways - change the code to match the documentation (smaller number of changes but a backwards compatibility break), or change the documentation to match the code (larger number of changes but no compatibility break). I have prepared a PR for each approach, but avoiding a compatibility break seems better, so I am pushing the latter. I can switch to the other if preferred. Most existing internal calls to `getParent` are changed to use a new method `getParentOrThrow`, which will throw an exception if parent is null. These calls would all have thrown a null pointer exception anyhow in that situation, so this should not cause any new breaks.
* Scrutinizer
One false positive, and one message leading to a minor code improvement.
oleibman
Adrien Crivelli
Mark Baker