When an IF sits in the condition of another IF, BranchPruner only
looked at the immediately enclosing IF to decide which branch the
condition tokens depend on. If that enclosing IF was itself still in
its condition, nothing was recorded, so the link to an outer IF whose
branch is being pruned was lost. Those tokens were then evaluated and
left on the stack while the rest of the branch was pruned, and the
calculation ended with "internal error".
Walk up the stack of enclosing IFs to the closest one whose then or
else branch contains the nested IF instead.
Fixes#3790Fixes#3755
RIGHT("abc",5) returned "bc" because the start offset went negative and
mb_substr then counted it from the end of the string. Excel returns the
whole text when num_chars is greater than its length, so clamp the offset
at 0.
@dkulyk noticed a case which wasn't handled properly. I believe the fix works for everything ... except `COLUMNS` and `ROWS`, where Excel seems to ignore the sheet, and I just can't figure out how to do it.
GAMMA.INV silently returned the first bisection midpoint for alpha in
~[143, 171.62] (e.g. GAMMA.INV(0.5, 143, 1) gave 358.0 instead of
142.667) and #NUM! above that, because the Newton-step pdf evaluates
Gamma(a), b**a and value**(a-1) in linear domain, all of which overflow
even though the density itself is a small representable number. The same
pattern breaks the GAMMA.DIST, CHISQ.DIST and F.DIST densities and
GAMMALN, which computed log(Gamma(x)) through Gamma(x).
Evaluate these in log domain via the existing logGamma, and scale the
incomplete-gamma series/continued-fraction iteration cap as O(sqrt(a)),
which both expansions need to converge near x ~ a once the shape is
large (GAMMA.INV drifted from the true quantile above alpha ~5000 and
returned alpha+1 by alpha=10000; same for CHISQ.INV at high df).
calculateInverse() treated a CDF value of exactly 0.0 as "the guess is the
root" and collapsed the bracket with $b = $a, ending the search. inverse()
already rejects probability <= 0, so a CDF of 0.0 inside the bisection can
only be a float64 underflow at a guess far below the root - never an exact
hit. It is just an ordinary "guess too low" and belongs in the existing
else branch.
The underflow is reached whenever a probe lands many standard deviations
from the mean, so the search collapses after two or three iterations and
returns whichever midpoint it was holding. BETAINV(0.5, 5000, 5000) gave
0.25, though Beta(a, a) is symmetric and its median is exactly 0.5;
BETAINV(0.5, 20000, 3) gave 0.5 against a true 0.99986. Onset is around
alpha = 1080 for beta = 1, where the closed form 0.5 ** (1 / alpha) is
available to check against.
That test also happened to stop the search for shapes where incompleteBeta
declines to evaluate at all and returns 0 for every x, so inverse() now
rejects alpha + beta above that documented limit up front: the CDF is
identically zero there, so no quantile exists to search for.
Tests cover a shape x probability grid against scipy reference values plus
three checks that need no external oracle: the symmetric median, the
Beta(alpha, 1) closed form, and the BETADIST round trip / mirror identity.
The incomplete gamma primitive used a fixed 32-term power series with no
convergence test, so GAMMA.DIST, GAMMADIST, CHISQ.DIST(.RT), GAMMAINV and
CHISQ.INV were grossly wrong once the series argument reached ~32
(e.g. CHISQ.DIST.RT(80, 4) returned 0.806 instead of 1.74e-16).
Replace it with the standard convergence-tested regularized incomplete
gamma: series P(a,x) for x < a+1, continued fraction Q(a,x) for x >= a+1.
CHISQ.DIST.RT now uses Q directly so the right tail stays free of
1 - P cancellation. Consolidates the duplicate copy that already existed
privately in ChiSquared onto the shared primitive.
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.
Fix#4061. CONCATENATE, which has slightly different behavior than CONCAT, and which MS has deprecated for that reason, behaves in an unexpected way when a cell range is presented to it and the spreadsheet does not allow for array results. This would almost certainly occur only for Legacy spreadsheets, but such is what was presented in the issue. The code is changed so that when an array of cells is presented to CONCATENATE, and RETURN_ARRAY_AS_VALUE is in effect, the array will be treated as if it were wrapped in the SINGLE pseudo-function (which is what Excel does by somewhat mysteriously prefixing the cell range with `@`).
This is a niche case. This one stands out because of its deprecation and replacement function. It is possible that other functions exhibit this behavior. I have made no attempt to identify others. A similar approach can probably be applied if issues are raised for others.
This PR started because the documentation on how to configure an Http client was out of date. However, as I investigated further, I found problems. GuzzleHttp, a client mentioned in the documentation, for example - it probably works fine when executed from a browser, but it does not work for `https:` requests from the Windows command line. See https://github.com/guzzle/guzzle/issues/2601, where a user notes that Guzzle's own program to demonstrate how to use it doesn't work, a result that I can confirm is still true. A linked explanation says that the solution is to explicitly specify a path to a CA bundle. For starters, this is impractical from our perspective. One possible solution is to change a php.ini option which is not needed for any other purpose, and which probably needs to change frequently - a burden on users who follow that route. An alternative solution is to use a method `request` to specify the path to the certificate store; this also will need to change from time to time, and, worse, the only method defined in ClientInterface is `sendRequest`, so using this solution isn't client-agnostic, which is a stated goal of https://github.com/PHPOffice/PhpSpreadsheet/commit/7cb4884b96174eb611621635e6fb603ef54178f7. Additionally, it is not clear why an external interface is needed rather than a call to file_get_contents, used elsewhere in PhpSpreadsheet, and not requiring a path to a certificate store.
I also believe that automatically evaluating WEBSERVICE for any arbitrary argument is not a good idea. I am adding a domain whitelist which the user must populate. For domains not in the whitelist, the calculation will revert to `oldCalculatedValue`, which is good enough for pass-through purposes, which probably encompasses most cases. That is how Excel behaves by default - it disables WEBSERVICE calls when it opens a spreadsheet which contains them. For cases where the user adds a new WEBSERVICE call, there is a choice of whitelisting the domain, or getting the result in some other way and using `setCalculatedValue` to store it.
Finally, when a WEBSERVICE call *is* evaluated, it will now accept a cell-address argument rather than just a literal string as is now the case.
T.DIST takes 3 parameters. When the 3rd parameter, a boolean, is true, we now have code to support the request. We still do not have code to handle the false case.
Fix#4167. That problem was initially reported as a problem with TINV, however, at its root, it was actually a problem with TDIST (which TINV needs to call). Both of these are deprecated by Microsoft in favor of some newer functions. PhpSpreadsheet incorrectly treated T.INV as an alias for TINV, and did not implement T.INV.2T, which actually is an alias for TINV.
To solve this problem, I adapted the Perl CPAN module `Statistics::Disttributions`, written by Michael Kospach around 2003. Unit test results have been manually checked against Excel; a few had to be changed because they were wrong (see the original issue). I believe that all of TDIST, T.DIST.2T, T.DIST.RT, TINV, T.INV, and T.INV.2T now function correctly, at least most of the time. I have not found code or an algorithm that I can use for T.DIST nor for T.TEST, both of which remain unimplemented.
PhpSpreadsheet treats CODE and UNICODE as equivalent, likewise for CHAR and UNICHAR. They are, in fact, different. CODE and CHAR deal only with single-byte character sets (Windows-1252 or MacRoman), while UNICODE and UNICHAR deal with all of Unicode. This PR separates them. The existing unit test for CODE was, in many cases, applicable to UNICODE (for which there was no separate test). The tests are corrected for CODE, new tests are added, and a separate test for UNICODE is added. CHAR was mostly okay, new tests are added, and a separate test for UNICHAR is added.
Intl is only a "suggested" extension. A lot of the NumberFormat Wizard code depends on it. That's insufficient reason to make it required, but the suggestion text now mentions this dependency explicitly. Also clean up the Wizard documentation to reflect some changes since PhpSpreadsheet 1.28.
This is a theoretical problem. Building FalseTrueArray only looks at languages, not countries. The only example we have is `pt` vs `pt_br`, and since True and False have the same translation for both locales, there is no problem. This PR will help it from becoming a problem in future if a new language/country combo is added.
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.
TOCOL and TOROW were introduced to Excel in 2024, and will now be supported by PhpSpreadsheet. The documentation says that, under certain circumstances, "blanks" will be ignored. This seems demonstrably wrong. In the right circumstance, Excel will ignore nulls, not blanks. Further, when it decides to not ignore the nulls, it changes them to 0, which also seems insufficiently documented. PhpSpreadsheet will behave as Excel does.
I discovered some minor problems and some missing test conditions for the TRANSPOSE function while testing these. Those are now fixed.
Very peculiar implementation in Excel. PhpSpreadsheet will match it:
- FLOOR(8,0) is #DIV/0. This already matched, and is the only result I understand.
- CEILING(8,0) is 0 (already matched)
- FLOOR.MATH(8,0) is 0 (changed to match)
- FLOOR.PRECISE(8,0) is 0 (changed to match)
- CEILING.MATH(8,0) is 0 (already matched)
- CEILING.PRECISE(8,0) is 0 (already matched)
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.
Fix#4249. Technically speaking, only the 1.29 branch needs fixing, and only for TEXT. It was fixed for the other branches by PR #3898. However, in adding test cases for the fix, it became apparent that PhpSpreadsheet's parsing in TIMEVALUE (which is called from TEXT in the original issue) did not really match Excel's. There are probably still edge cases where it doesn't, but, in the absence of a spec for how it operates, this will do for now.
We do not usually backport fixes from the master branch. Because this is more of a forward port from the earlier branch, there is an equivalent PR for each active branch.
Its defaults are to unescape within single quotes, and escape within double quotes and here-docs. Right now, it leaves everything as-is, which means our code is inconsistent and need not be. Further, although dealing with complex regular expressions will never be easy, I find it much easier to figure out what's going on when superfluous back-slashes are removed.
These changes were all made automatically using the "fix" script, so should be reliable. They, of course, pass all unit tests.
These are 5 closely related functions for manipulating arrays. Now that dynamic arrays are part of PhpSpreadsheet, this PR implements those previously-unimplemented functions. Documentation undergoes a very minor change, since they are re-categorized as "Lookup and Reference", which is how Excel classifies them, rather than "Math and Trig".
oleibman
HawkForge
kevin9327
Guy Tomer
youzixu
Vincent Gao
Robin van der Vliet