Before it was a bug when flooring negative numbers added to the length of
the number for the sprintf mask.
For example -0.091 becomes -9.1 in percentage, floors to -10 which is going to
increase $wholePartSize by 1 and add a leading zero to the result: -09.1% with
format 0.0%.
This happened for negative ranges where floor will increse the length of
the original number.
Fix#3909. SUMPRODUCT is mishandling multi-row ranges. In Calculation/Calculation, `checkMatrixOperands` will often resize its operands. When it does so, it needs to recalculate the dimensions of each. This fixes the reported problem.
Likely cause was PR #3260. That ticket noted the poor coverage of the code being replaced. Tests of the problem in this ticket were absent and are now added. Despite this, I note that `resizeMatricesShrink` is virtually uncovered, and `resizeMatricesExpand` has substantial gaps in its coverage. I have covered some, but not all, of the Expand gaps. I am struggling to come up with examples to fill its remaining gaps and those for Shrink. However, I will merge this fix in about a week even if I don't succeed.
Fix#3907. After row/column insertion/deletion, PhpSpreadsheet updates formulas which include cells which have moved. However, it can mis-identify cell addresses within the formula. Examples:
- `=SUM(A2,'F1 (SETTINGS)'!A1:B1)` It identifes F1 as a cell address.
- `=SUM(A2,'x F1 (SETTINGS)'!A1:B1)` It identifes F1 as a cell address. (This looks the same as the above, but, for technical reasons, it's different.)
- `=SUM(A2,definedname1A1)` It identifes A1 as a cell address.
- Sheet names in formulas are compared case-sensitively, and should be compared insensitively. This can make a difference if the formula includes its own sheet name, e.g. on sheet `Data`, formula `=SUM(DATA!A1:A2)` might have to change, but it will not do so with the existing logic.
The defined name part is fairly straightforward. The regular expressions that identify a cell address just have to be a bit more robust. It was doing a negative look-behind for an alphabetic character or dollar sign; underscore, period, and digits, all of which can be part of a defined name, need to be added to that list.
The other situations need a bit of a kludge, but not one so bad that I'm ashamed of it. The formulas will be altered before analysis so that sheet names are replaced with Unicode FFFD (sheetname does not match current sheet), FFFC (sheetname, enclosed in apostrophes, matches current sheet), and FFFB (sheetname, not enclosed in apostrophes, matches current sheet). This prevents the existing regular expressions from finding a cell address within a sheet name, and makes it easy to restore the original, with or without apostrophes, when the sheet name matches the current sheet and the cell(s) which it qualifies have to be changed.
Tests are added for all the situations mentioned above. No existing tests required changes.
Fix#3904. PR #3839 provided a huge performance boost for sparsely populated spreadsheets. Unfortunately, it degraded performance for more densely populated spreadsheets when writing Csvs. The reason is that Csv Writer calls toArray for each row, meaning that a lot of the intermediate data used to speed things up needs to be recalculated for every row. It would be better off calling toArray just once for the entire spreadsheet; however this gives back some of the memory improvements of PR #3834. However, the memory effects can be substantially alleviated by supplying a Generator function to do the work. This PR does that; the result is that Csv Writer is now quite a bit faster, and with only a small memory uptick, vs. its performance in PhpSpreadsheet 1.29.
Fix#3900. The code to adjust Decimal and Thousands separators to user-specified choices expects to convert periods to commas and vice versa - hard-coded without taking the user specification into account. This is likely to be the only significant use case, however, as the issue demonstrates, it is not the only possibility. In particular, it will mess up Csv output if decimal separator is kept as period, but thousands separator is set to null string. The code is altered to replace period with the user-selected decimal separator, and comma with the user-selected thousands separator. No existing tests broke as a result of this change in behavior.
This PR resolves about half the statements which we tell Phpstan to ignore. There will still be 23 such annotations spread over 10 source modules for various reasons (too complicated, suspect PhpSpreadsheet code, one Phpstan bug), plus some deliberate errors in the test suite.
Fix#3896. It appears that floating-point arithmetic will give different results in Php 8.4 vs. all earlier releases. This causes tests to fail in the nightly run for ROUNDDOWN, ROUNDUP, and AMORDEGRC. I imagine this won't be the last we hear of this. The failures are a distraction when reviewing PR's. This PR eliminates the distraction by adding in a fudge factor for Php 8.4+ while not changing Php 8.3-. It is not a particularly robust solution, but it should be stable for Php 8.3-, and good enough for Php 8.4+ while I study if a better solution is available.
Phpstan baseline had been reduced to almost nothing, but recently had a big bump in size. Almost all of the increase was due to trait ArrayEnabled, which is easily recoded to avoid the problems.
The samples have become unwieldy when running them from a browser. In particular, the drop-down lists are fixed size with no scrolling, and many of them are now just too large. I have moved all the Calculation samples up a level, and broken several categories (Basic, Chart, DateTime, Engineering, Financial, and Reader) into several pieces.
Convert-Online (now found in the Engineering category) had a number of different problems which are now resolved. It is the only member with any significant code change.
MarkBaker
oleibman
dependabot[bot]
Nicholas Ruunu
Adrien Crivelli
Tobias Bäthge