Issue #4435 was initially described incorrectly. While investigating the original description, I came upon this problem. Ods Reader is trying to set some cells to null without supplying a valid DataType to setValueExplicit, causing that method to throw an exception. Reader is changed to no longer call that method when value is null and DataType is null-string.
Fix#4416. A peculiar problem indeed. PhpSpreadsheet has been considering a column to be filtered if any cell in the column is filtered and does not preserve the column width if that is the case. It should consider the column not filtered if any cell in the column is not filtered, and consider it filtered only if there are no cells to which that applies. At least, that's how I think it should work, and this change doesn't break any existing tests, and solves this issue.
It flagged a statement as dead code. It was correct - there was a typo in the variable name. But no tests had failed. The explanation was, of course, that this particular path was not adequately tested.
PhpSpreadsheet extends Excel (dating back to PHPExcel) by allowing the ordinal form of days in the DATE function, implemented as "take the numeric portion if the field is a string consisting of a numeric portion followed by some alphabetics". Whether or not this is a good idea, it would be a breaking change to eliminate it, so that's not going to happen. However, the same logic has been applied to month, and I don't see a use case for that, so I'm eliminating it - any non-numeric string used as the month parameter will now result in a VALUE error. It also turns out that Excel accepts null, false, and true for the month, and PhpSpreadsheet will now do likewise.
Spreadsheet clone already copies Calculation instanceArrayReturnType property. It should also copy some other Calculation instance properties, namely suppressFormulaErrors, calculationCacheEnabled, and branchPruningEnabled.
Helper/TextGrid was intended to assist with some samples. However, it has emerged recently on two tickets. PR #4342 intended to introduce functionality very similar to TextGrid, and was closed for that reason. Issue #1640 was closed as stale over 4 years ago, despite the fact that TextGrid seems an adequate resolution for it.
Since it seems that there is a use for this function beyond its original intended usage, I added a few parameters to give it some flexibility - the ability to omit row and/or column headers, and the ability to add a divider line between rows.
A description of this function is added to the formal documentation.
Fix#4415. We store the rarely-used property Drawing/Shadow/Alpha as an integer representing the percentage. Excel also stores it as an integer, but multiplies it by 1,000, so we divide by 1,000 when we read this value. This can, and in the case of the issue at hand does, leave a fractional portion. Php has deprecated passing a float with a fractional portion to an int argument, so the reporter saw a deprecation message. This is easily fixed.
Fix#4411. User copied some code from a PHPExcel program which set DisplayAsBlanks to `0`. This resulted in what Excel deemed a corrupt spreadsheet using PhpSpreadsheet. The only values allowed for that field are `gap`, `zero` (not `0`), and `span`. PHPExcel used `0` as a default, and got away with it because it ignored the value entirely when writing out the spreadsheet, using `gap` all the time.
I had to choose between throwing an exception and just using the default when an attempt is made to set that property to an invalid value. An exception just seems more punitive than helpful to me, especially if we want people to migrate from PHPExcel, which still seems to have a large user base. So I've gone with using `gap` in place of an invalid value. Note that, according to https://learn.microsoft.com/ru-ru/openspecs/office_standards/ms-oe376/b5c5c694-21d9-437c-9a4a-21e0e843eed8, `gap` is used as the default whenever it is permitted for the chart in question; and, when it isn't permitted, the chart will use its default method (which will always be `zero`).
There were no tests nor samples for this property. All the tests and samples which use it use only `gap`. I have added a small test, and a new sample to illustrate the difference between the 3 options.
See discussion #4202. Users can extend Spreadsheet, but the readers cannot return the extended class. This is solved pretty easily by adding a protected method in BaseReader which returns a new Spreadsheet. Users can then extend the Reader which they want, overriding that method to return the extended class.
Dealing mostly with mixed variable type. In a great many cases, problems can be resolved replacing `getValue` with `getValueString` or `getCalculatedValue` with `getCalculatedValueString`.
When I implemented IgnoredErrors (PR #3508), I dealt only with those that I understood well enough to come up with an example. I finally found an example for FormulaRange in the wild, so this PR adds it. Still unsupported are `calculatedColumn`, `emptyCellReferece`, `listDataValidation`, and `unlockedFormula`.
For an overview of why this is desired (and ways that people have coped with its absence), see issue #2900 and issue #4048; also PR #4043 which will be superseded by this PR.
The list of Excel functions is moved from Calculation/Calculation to its own member. I believe that it is done in a way that will not cause big complications to two experiments from @MarkBaker (PR #2714 and PR #2734). I believe it is also done in such a way that further refactoring of Calculation can follow this model.
Custom functions can be added or removed from the function list. You cannot add a function if it already exists in the list, and you cannot remove a non-custom function from the list. They will, of course, not be understood by Excel if written to a spreadsheet; the use case is mainly using the Calculation engine outside of spreadsheet context.
Fix#4381. The report refers to COUNTIF, but AVERAGEIF and SUMIF, which are implemented in the same module, exhibit the same behavior. (There may be others, but, for now, I will just fix those 3.)
Most methods which implement Excel functions should accept mixed arguments, so that they won't throw exceptions when calculated. Of course, MS often doesn't give much guidance as to how unexpected arguments should be handled. It at least seems clear that MS will often substitute #REF! for some arguments, and will return #REF! as the result in such cases. My test indicates that a formula using, say, #DIV/0! in lieu of #REF! will cause Excel to deem the spreadsheet corrupt. So, I think I am just going to deal with #REF! and let other unexpected values continue to throw exceptions.
oleibman
Mikko