As was pointed out, the entire `pow` piece, as well as extracting exponent and exponent sign, was not necessary as casting to `float` will correctly handle scientific notation in a string.
Also added case-insensitive flag to the regexp to handle upper and lower case `e`.
* Eliminate Some Scrutinizer 'Major' Problems Part 4
Mostly docblock changes and annotations. Some code changes. A few more of these to go still.
* Missed a Couple
Fix them now.
Implemented the suggested named capture groups (nice trick) in the regexp to pull out only the values we need to perform the calculation. Making use of the null coalescing operator to keep things nice and clean. Note that I did have to add the `PREG_UNMATCHED_AS_NULL` to `preg_match` to ensure PHP returned null (since null coalescing only works on null and not '') when a match wasn't found for a capture group.
Also extended the regular expression to handle scientific notation (both positive and negative, and positive and negative exponents) as well as a negative symbol before or after a leading percentage sign.
* Problems Formatting Very Small and Very Large Numbers
Fix#3128 (author found a workaround but the problem remains). For some complex masks, when a cast of the cell value from float to string results in the use of scientific notation, the result of the formatting is unusable. I believe this PR solves the problem for numbers close to zero (Php cast from float to string uses scientific notation starting with 1E-5), and for a range of large numbers which are not handled correctly now.
However, I have not found a way to ensure that the results match Excel for very large numbers (1E18 or larger); this change at least ensures that the resulting string is an accurate rendition of the number (which is not the case now) even if doesn't match Excel. As an example, if you use the mask reported in the original issue `0 000.0` and enter a value of 1E90 into the cell, Excel will show it as 1 followed by 87 zeros, a space, 3 more zeros, decimal point and zero. I have not figured out how to get PhpSpreadsheet to do that; for now, it will just return the formatted value as 1 followed by 90 zeroes instead (I might have chosen to go with scientific notation instead). I will continue to think about those, but do not feel it is worth delaying the improvements in this ticket while I do so.
The affected section of code also truncated to the appropriate precision. It now rounds, as Excel does.
This seemed to be an area of code where problems might arise on 32-bit systems, and, indeed, I found something in the formatting code which had to be changed for 32-bit to work correctly. As long as I was doing that anyhow, I ran the full test suite, and found that Php8.1 had introduced some new stringencies which caused problems in a handful of places. All were found in Xls Reader, and all are corrected now.
* Remove Dead Assignment
Scrutinizer will be happy now.
As suggested, the initial check has been tightened up using a regular expression.
Expressing allows for leading and trailing spaces, leading and trailing percentage symbols, with spaces mixed throughout.
Added 57 permutations of leading and trailing spaces, leading and trailing percent symbols, decimals, and negatives. All seem to be working. I will also attach an Excel worksheet that verifies the behaviour of all 57 test cases aligns with Excel.
* MATCH Problems with Int/Float Compare and Wildcards
Fix#3141. Function matchSmallestValue did not recognize that an integer could match a float. Adding test cases, it seems that matchFirstValue had the same problem. However, matchLargestValue seemed to handle things correctly - but see below.
In addition, the wildcard logic in matchFirstValue is faulty. It ignored tilde as a wildcard character. Although it would have been easy to just add that, I think it was wrong to determine on its own if a wildcard was in use. Just using the already available wildcard functions whenever comparing two strings is sufficient.
I note that Excel doesn't seem to follow its own rules for MATCH (https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a?ns=excel&version=90&syslcid=1033&uilcid=1033&appver=zxl900&helpid=xlmain11.chm60112&ui=en-us&rs=en-us&ad=us). PhpSpreadsheet's results match Excel's, so no problem. However, when match_type is not zero, the match array is supposed to be sorted, so I would expect `#N/A` when it isn't; but that's not how Excel operates. I have no idea what Excel is doing. If `MATCH(2,{2,0,4,3},1)` isn't `#N/A` because of the unsorted array, then surely it should be `1` (item 1 of the array is the largest number less than or equal to the lookup value); but Excel and PhpSpreadsheet (before and after changes) return `2`. I have moved this example to be the first of the test cases.
One would think strings would behave similarly. But, no - see the second test case. This time Excel does look for an exact match. But the existing logic doesn't get the matching result in PhpSpreadsheet. It requires a whole new block of code, one which doesn't work correctly for numeric lookup value. Ugh.
LibreOffice doesn't always agree with Excel. It seems that it will use wildcard matching even when the match type is not zero (Excel documentation says wildcards are only for type zero, which is just as well because I don't really know what greater/less mean when wildcards are involved). I have not attempted to duplicate this behavior. For the record, Gnumeric agrees with Excel here.
* More Changes - LibreOffice
Add support for LibreOffice matching wildcard strings when type is not zero. Add support for type to be specified as integer other than 0/1/-1, or as float, or as numeric string; non-numeric string should case `#VALUE!` error.
I have found an example of undefined behavior (unsorted array where type is non-zero) where PhpSpreadsheet does not produce the same result as Excel. It is present as a new `incomplete` test case. I can fix it, but not without breaking other tests where the proper behavior is undefined. IMO, this is not a problem we should be concerned about.
Many test cases are added. Chances are I will add some more before merging this change.
Adding two test cases:
1) Test the function in `StringHelper.php`. Modeled this test on the test for the `convertToNumberIfFraction` function.
2) Test a spreadsheet with a string percentage in a formula to see if it calculates the formula correctly.
* Generation3 Copy With Image in Footer
Fix#3126. A worksheet contained an image in its footer. It could be loaded and saved as another spreadsheet. However, if you tried to load and save that spreadsheet, PhpSpreadsheet would be unable to find the footer image and would therefore throw an exception.
This error was introduced a long time ago, in PhpSpreadsheet 1.3.0. The apparent cause of the problem was PR #435, sometime around June 2018. That change was very useful, but it had problems which exposed themselves only with a third generation copy. An additional contributor to the issue at hand was PR #1690 (December 2020), which again exposed itself with a third generation copy.
The issue from 1690 is easier to explain and deal with. It added a 'ps' suffix to printer settings resources in Xlsx Reader (to avoid name conflicts), but did not limit itself to a single addition (so subseqent generations would have multiple ps's). It also neglected to add the suffix in Reader/Xlsx/PageSetup.
As for 435, it loops through all the worksheet relationships, and uses the last that it finds as the base for header/footer drawings. It has been changed to use only the relationship whose `rId` matches the worksheet's `legacyDrawingHF` `rId`. It also needs a bit extra validation to make sure a drawing exists before adding it to its array of header/footer images. It also meant that Xlsx/Writer/Rels might write an entry with the same rId twice. I have also changed the header/footer image processing to be namespace aware (see PR #3137).
* Minor Change
I didn't like the way I performed one operation.
* Fix Test
Array index should not have been constant.
* Xlsx Reader Namespace Aware Handling of Drawings, RowAndColumnAttributes, MergeCells
Fix#1482 (actually fix a problem recently attached to that ticket long after it closed). There were problems processing a spreadsheet generated by third party software. That spreadsheet used unexpected namespacing, and absolute paths within the zip file where relative paths were expected.
Xlsx Reader handles most, but not all, of its processing in a namespace-aware manner. Two versions of a worksheet's xml are available - `$xmlSheet` is not namespace aware and `$xmlSheetNS` is aware. This was necessary in order to add namespace support in an incremental manner. The primary reason to continue to use the unaware version is the absence of test cases. In particular, drawings, row and column attributes, and merge cells continue to use the unaware version; this PR changes those to use the aware version.
As noted in the summary above, a couple of new places in the handling of the those items were expecting file locations to be specified as relative paths in the zip file, but the file used absolute paths instead. Those unexpected usages are now addressed.
The user reporting the new problem tried a change which effectively made all uses of `$xmlSheet` namespace aware, and that seemed helpful. It may be time eliminate its usage altogether, whether or not we have appropriate examples of unexpected namespaces to test with. I will not do that with this change, but I may add a new PR to do so after this one is merged. Remaining areas which still use the unaware version include conditional formatting (internal or external), sheet view options, sheet protection, auto filters, unparsed loaded data, data validation (internal or external), alternate content, and header/footer images.
There is an interesting anomaly with the new test file. When I load it and save it, the appearance of the output file does not quite match the input. Oddly, the output file seems much better than the input - the picture no longer covers any data, for example. This is because, in particular, the output file row heights and column widths seem to match the xml, but the input file does not. For example, the xml in both files seems to indicate that row 5 should have a height of 234, which it does in the output file, but the height of that row when the input file is opened is 156. It appears that all row heights and column widths when the input file is opened are very close to 2/3 of what is expected. I will continue to research that anomaly for a few days, but I will not let it prevent me from moving forward with this PR if I don't find the explanation. Whatever that problem is, it seems distinct from the namespacing/pathing problems which the PR addresses.
* Scrutinizer New False Positives
Eliminate them with annotations.
* Using htmlspecialchars to fix issue #3145: this prevents the SimpleXMLElement error "unterminated entity reference" when HTML entities are present in a worksheet
* replace htmlspecialchars with proposal of oleibman
* eliminate string cast, because PhpStan has a problem with that
* ignore string cast for phpstan + enhance unit test
* Permit Date/Time Entered on Spreadsheet to be Calculated as Float
Fix#1416. I do not entirely understand the use case for this old issue, but resolving it seems straightforward. Issue complains that user-entered date/time fields may be interpreted as either float or int when PhpSpreadsheet reads them. Issue suggests getCalculatedValue treat all date/time fields as float; that seems like a breaking change. However, adding an option to permit it seems okay. That option might be implemented as either a property of Calculation, or a static property of Cell. Since the changed logic is found in Cell (and Shared/Date), I opted for the latter.
In Cell, the property `$parent` is incorrectly described in doc block as `Cells`, and should be `?Cells`. This change eliminates some Phpstan and Scrutinizer problems, and should allow the elimination of some try/catch blocks - I have not done an exhaustive search for those.
Calls to `isDateTime` could have affected activeSheet and selectedCells; they no longer can. Optional parameters are added to it and the functions it calls to accommodate the new functionality; the defaults for the new parameters will, of course, return the same result as the earlier versions of the functions would have returned.
* Scrutinizer - Self-inflicted
Tests used constant which I deprecated.
Fix#3095. Issue is not a problem for Php8, but is for 7.4. Code used to write Xml for float custom property uses locale-dependent (in Php7) cast. Change to use locale-independent (all Php releases) `sprintf('%F'...)` instead.
Fix#3093. PR #2595 added the ability for Xlsx Reader to accept a custom color palette. With no examples other than the one at hand, which included a full complement of 64 colors, that PR required 64 colors in the palette. It turns out that Mac Numbers exports to Excel with a palette with less than 64 colors. So, with an example of that at hand, relax the original restriction and accept a palette of any size.
Fix#1531. This is a replacement for PR #3081 (see last paragraph below), which I will close.
Calculation has a property `suppressFormulaErrors`, which really doesn't work as one might expect. If a calculation throws an exception, the setting of this property might prevent the Exception from being thrown, but it will still trigger an Error. I do not think this makes sense, and will change it so the calculation will return `false`, which is part of the original design but which would essentially never happen. This allows the user to save a corrupt spreadsheet, but this was already possible through the use of `setPreCalculateFormulas(false)` on the Writer, so this doesn't really open any new exposures. It nevertheless might be considered a breaking change because of the difference in behavior.
Deprecation - the visibility of the existing property is public, which means it can be changed directly. A new private property is added with a public setter/getter. The new property will be used when the existing property is null (default), which will allow the existing property to be deprecated.
Function getFunctions is changed to static - the array which it returns is static. Existing callers using it as non-static will still function correctly.
Although I am enabling this ability, I don't necessarily think it's a good idea to make use of it. See the original issue for a discussion of why. It is not mentioned in the official documentation, and I will not be adding documentation for it. The originator discovered it by reading the code, and I think that is sufficient for what will often be an ill-advised choice.
Many of the large number of problems with Calculation.php in phpstan baseline are addressed. PR 3081 ran afoul of something in phpstan. The changes in this ticket are more limited, adding a number of doc blocks but leaving executable code unchanged.
Scrutinizer had previously suggested annotations for 3 LookupRef tests, but it no longer accepts its own annotation for those cases. This PR cleans them up. ColumnsTest and RowsTest are extremely straightforward.
IndexTest is a bit more complicated, but only because, unlike the other two, it had no test which executed in the context of a spreadsheet. And, when I added those, I discovered a couple of bugs. INDEX always requires at least 2 parameters (row# is always required), but its entry in the function table specified 1-4 parameters, now changed to 2-4. And, omitting col# is not handled the same way as specifying 0 for col#, though the code had treated them identically. (The same would have been true for row# but, because it is now required, ...)
* Phpstan Baseline Fixes 2022-09-21
Eliminate about 200 more lines from Phpstan baseline. For Helper/Sample and Helper/Html and others, many properties are declared as protected despite the fact that the classes do not extend any other class, and there are no classes which extend them. They are changed to private; this could be a breaking change in circumstances for which I cannot think of a use case (user extends class for some reason).
* Slightly Botched Merge Commit
Hope this fixes phpstan.
* Xlsx Reader External Data Validations Flag Missing
Fix#2677. This PR supersedes #2679, written by @technghiath, which lacks tests, and probably doesn't solve the problem entirely. The code causing the problem appears to be the last remnant in Xlsx Reader which calls `children` using a namespace prefix rather than a namespace. That is changed, and tests are added where the tag is unexpectedly missing, and also where it uses a non-standard namespace prefix.
* Scrutinizer
Reports 1 "new" error. It isn't, but fix it anyhow.
* Fix One Existing Scrutinizer Problem
Only remaining problem in Reader/Xlsx.
When I cloned this morning, composer gave me a message that the lock file was not up to date with the latest changes in composer.json. I do not understand why, but it suggested to run `composer update`, which I did. This led to a handful of problems with php-cs-fixer, all fixed with changes to doc-blocks, and phpstan (only Writer/Xls/Worksheet required a change to code). We would presumably have had these problems at the start of next month when dependabot did its thing, so fix them now.
No source code changes. Mitoteam added a change to better accomodate Cygwin; pick up their new release. While testing, discovered one test that was already skipped on Windows and needs to be skipped on Cygwin as well.
* Scrutinizer Changes
Scrutinizer appears to be working again. But the PRs that have used it have neither added new issues nor fixed existing ones. This PR should fix some exisiting; let's see what Scrutinizer does with it.
* Address Some False Positives
In Reader/Xlsx/Chart.
* R1C1 Format and Internationalization, plus Relative Offsets
Fix#1704, albeit imperfectly. Excel's implementation of this feature makes it impossible to fix perfectly. I don't know why it was necessary to internationalize R1C1 in the first place - the benefits are so minimal,and the result is worksheets that break when opened in different locales. Ugh. I can't even find complete documentation about the format in different languages; I am using https://answers.microsoft.com/en-us/officeinsider/forum/all/indirect-function-is-broken-at-least-for-excel-in/1fcbcf20-a103-4172-abf1-2c0dfe848e60 as my definitive reference.
This fix concentrates on the original report, using the INDIRECT function; there may be other areas similarly affected. As with ambiguous date formats, PhpSpreadsheet will do a little better than Excel itself when reading spreadsheets with internationalized R1C1 by trying all possibilities before giving up. When it does give up, it will now return `#REF!`, as Excel does, rather than throwing an exception, which is certainly friendlier. Although read now works better, when writing it will use whatever the user specified, so spreadsheets breaking in the wrong locale will still happen.
There were some bugs that turned up as I added test cases, all of them concerning relative addressing in R1C1 format, e.g. `R[+1]C[-1]`. The regexp for validating the format allowed for minus signs, but not plus signs. Also, the relevant functions did not allow for passing the current cell address, which made relative addressing impossible. The code now allows these, and suitable test cases are added.
* Use Locale for Formats, but Not for XML
Implementing a suggestion from @MarkBaker to use the system locale for determining R1C1 format rather than looping through a set of regexes and accepting any that work. This is closer to how Excel itself operates. The assumption we are making is to use the first character of the translated ROW and COLUMN functions. This will not work for Russian or Bulgarian, where each starts with the same letter, but it appears that Russian, at least, still uses R1C1. So our algorithm will not use non-ASCII characters, nor characters where ROW and COLUMN start with the same letter, falling back to R/C in those cases. Turkish falls into that category. Czech uses an accented character for one of the functions, and I'm guessing to use the unaccented character in that case. Polish COLUMN function is NR.KOLUMNY, and I'm guessing to use K in that case.
The function that converts R1C1 references is also used by the XML reader *where the format is always R1C1*, not locale-based (confirmed by successfully opening in Excel an XML spreadsheet when my language is set to French). The conversion code now handles that distinction through the use of an extra parameter. Xml Reader Load Test is duplicated to confirm that spreadsheet is loaded properly whether the locale is English or French. (No, I did not add an INDIRECT function to the Xml spreadsheet.)
Tests CsvIssue2232Test and TranslationTest both changed locale without resetting it when done. That omission was exposed by the new code, and both are now corrected.
* OpenOffice and Gnumeric
OpenOffice and Gnumeric make it much easier to test with other languages - they can be handled with an environment variable. Sensibly, they require R and C as the characters for R1C1 notation regardless of the language. Change code to recognize this difference from Excel.
* Handle Output of ADDRESS Function
One other function has to deal with R1C1 format as a string. Unlike INDIRECT, which receives the string on input, ADDRESS generates the string on output. Ensure that the ADDRESS output is consistent with the INDIRECT input.
ADDRESS expects its 4th arg to be bool, but it can also accept int, and many examples on the net supply it as an int. This had not been handled properly, but is now corrected.
* More Structured Test
I earlier introduced a new test for relative R1C1 addressing. Rewrite it to be clearer.
* Add Row for This to Locale Spreadsheet
It took a while for me to figure out how it all works. I have added a new row (with English value `*RC`) to Translations.xlsx, in the "Lookup and Reference" section of sheet "Excel Functions". By starting the "function name" with an asterisk, it will not be confused with a "real" function (confirmed by a new test). This approach also gives us the flexibility to do something similar if another surprise case occurs in future; in particular, I think this is more flexible than adding this as another option on the "Excel Localisation" sheet. It also means that any errors or omissions in the list below will be handled as with any other translation problem, by updating the spreadsheet without needing to touch any code.
The spreadsheet has the following entries in the *RC row:
- first letter of ROW/COLUMN functions for da, de, es, fi, fr, hu, nl, nb, pt, pt_br, sv
- no value for locales where ROW/COLUMN functions start with same letter - bg, ru, tr
- no value for locales with a multi-part name for ROW and/or COLUMN - it, pl (I had not previously noted Italian as an exception)
- no value for locales where ROW and/or COLUMN starts with a non-ASCII character - cs (this would also apply to bg and ru which are already included under "same letter")
- it does nothing for locales which are defined on the "Excel Localisation" sheet but have no entries yet on the "Excel Functions" sheet (e.g. eu)
Note that all but the first bullet item will continue to use R/C, which leaves them no worse off than they were before this change.
* Floating-point Equality in Two Tests
Merging a change today, Git reported failures that did not occur during "normal" unit testing. The merge still succeeded, but ... The problem was an error comparing float values for equal, and the inequality occurred beyond the 14th decimal digit. Change the tests in question, which incidentally were not part of the merged changed, to use assertEqualsWithDelta.
* Egad - 112 More Precision-related Problems
Spread across 9 test members.
Fix#1929. This was already substantially fixed, but there was a lingering problem with an unexpected leading space. It turns out there was also a problem with leading zeros, also fixed. There are also problems involving commas; fixing those seems too complicated to delay these changes, but I will add it to my to-do list.
They just released a Php8.2-compatible version. We should use that version going forward. Some tests had been disabled in 8.2 due to the problems which the new release fixes; these are now restored.
* More Scrutinizer Catch Up
Continue the work of PR #3043 by attending to the 12 remaining 'new' issues.
* Php 8.1 Problem
One new null-instead-of-string problem.
This PR expands on PR #2195 from @nkjackzhang. That PR has been stalled for some time awaiting requested fixes. Those fixes are part of this PR, and additional tests and samples are added. The original request was to handle `vertical-align:middle` in Css (Excel uses `center`). This PR does its best to also handle vertical alignment Excel values not found in Css - `justify` (as `middle`) and `distributed` (as `middle`). It likewises handles valid Css values not found in Excel (`baseline`, `sub`, and `text-bottom` as `bottom`; `super` and `text-top` as `top`; `middle` as `center`).
It also handles horizontal alignment Excel values not found in Css - `center-continuous` as `center` and `distributed` as `justify`; I couldn't think of a reasonable equivalent for `fill`, so it is ignored.
The values assigned for vertical and horizontal alignment are now lower-cased (special handling required for `centerContinuous`).
A lot of easily fixed problems throughout Writer/Xlsx/*, mostly supplying int rather than string as input to WriteAttribute/WriteElement. There are, in fact, so many opportunities, that I will split it over 2 or 3 PRs. But this first one will get Phpstan baseline down to the goal on its own.
Some of the other problems are also easily fixed. In particular, the docBlocks in Style/ConditionalFormatting/ConditionalDataBar do not allow for null values, and should.
For one of the Phpstan upgrades, some message text had changed so drastically that the only practical solution at the time was to move the messages from phpstan-baseline.neon to phpstan.neon.dist. This was not ideal, but it allowed us time to move on and study the errors, which I have now done. At one point, Parser is expecting a variable to be an array, and that was not clear from the code. If not an array, the code will error out (which was Phpstan's concern); I have changed it to throw an exception instead. This satisfies Phpstan, and I can get the message out of neon.dist (without needing to restore it to baseline). Unsurprisingly, the exception was never thrown in the existing test suite, although I added a couple of tests to exercise that code.
In Helper/Dimension, Phpstan flagged a statement inappropriately. I suppressed the message using an annotation and filed a bug report https://github.com/phpstan/phpstan/issues/7563. A fix for the problem was merged yesterday, which is good, but it puts us in a tenuous position. The annotation is needed now, but, when the fix is inevitably pushed to the version we use, the no-longer-needed annotation will trigger a different message. Recode so that neither the current nor the future versions will issue a message, eliminating the annotation in the process.
Fix#3011. Some properties for Trendlines were omitted in the original request for this feature. Also, the trendlines sample spreadsheet included two charts. The rendering script 35_Chart_render handles this, but overlays the first output file with the second. It is changed to produce files with different names.
MarkBaker
fjohnston@avatarasoftware.com
oleibman
Jokūbas
Robin