It currently parsed by byte, which is not a good thing in a UTF-8 system. See the discussion at the bottom of PR #4203. That turns out to not be the change which led to this problem; that would have been PR #4323. That change came about because using Composer/Pcre revealed bugs in several regular expressions used in Writer/Xls/Parser. This ticket comes about because more bugs were revealed in the same module. The problem is that the `advance` method needs to process formulas character by character, but is instead doing it byte by byte. It is changed to advance by characters, and tests for non-ASCII characters are added.
Fix#4092. Change default value for Csv Reader autodetect line endings. Prior behavior can be enabled via `setTestAutodetect(true)`.
Change default value for Html Writer "better boolean" logic. Prior behavior can be explicitly enabled via `setBetterBoolean(false)`.
Change default for Xlsx Writer forceFullCalc option. Prior behavior can be explicitly enabled via `setForceFullCalc(null)`.
Fix#4331. Parser treats TRUE/FALSE as functions rather than constants, which is nominally harmless, but it then expects an argument count of 0 and instead sees null-string. Changed to recognize this situation and leave TRUE/FALSE/TRUE()/FALSE() unchanged.
Styles are a property of Spreadsheet, not Worksheet. There is a Styles property in Worksheet, initially set to an empty array and *never* updated. This PR removes the property and the getStyles method of Worksheet. This is, technically, a breaking change, so it will be installed with PhpSpreadsheet V4 (PR #4240).
When the Dynamic Array PR #3962 was introduced, it left the default as Return Array as Value. At some point, the default should be changed to Return Array as Array. This would, of course, be a breaking change, one which will not be part of Release 4. However, it will possibly be part of Release 5.
Rather than relying on the default setting, this PR explicitly sets Return Array as Value when tests require that setting. This will make it easier to identify potential breaks when the default is changed. The entire test suite will now succeed with either setting as default.
In making these changes, a few minor problems were discovered with how Array as Array is handled. These are fixed with this PR.
Fix#4324. Serialization was explicity forbidden by PR #3199. This was in response to several issues, and concern that the Spreadsheet object contained non-serializable properties. This PR restores the ability to serialize a spreadsheet. Json serialization remains unsupported.
Fix#1757, closed in Nov. 2023 but just reopened. At the time, Cell property `formulaAttributes` was stored as a SimpleXmlElement. Dynamic arrays PR #3962 defined that property as `null|array<string, string>` in the doc block. However, it left the formal Php type for the property as `mixed`. This PR changes the formal type to `?array`.
Fix#1741, closed in Dec. 2020 but just reopened. Calculation property `referenceHelper` was defined as static, and, since static properties don't take part in serialization, this caused a problem after unserialization. There are at least 3 trivial ways to deal with this - make it an instance property, reinitialize it when unserialized using a wakeup method, or remove the property altogether. This PR uses the last of those 3.
Calculation does have other static properties. Almost all of these deal with locale. So serialize/unserialize might wind up using a default locale when non-default is desired (but not necessarily required). If that is a problem for end-users, it will be a new one, and I will work on a solution if and when the time comes.
Static property `returnArrayAsType` is potentially problematic. However, instance property `instanceArrayReturnType` is the preferred method of handling this, and using that will avoid any problems.
Issue #932 also dealt with serialization. I do not have the wherewithal to investigate that issue. If it is not solved by this and the earlier PR's, I will have to leave it to others to re-raise it.
Spreadsheet `copy` is now simplified to use serialize followed by unserialize. Formal tests are added. In addition, I have made a number of informal tests on very complicated spreadsheets, and it has performed correctly for all of them.
The native preg functions (preg_match, preg_replace, etc.) often require us to add a lot of useless boilerplate code to satisfy Phpstan, Scrutinizer, etc. Composer/Pcre offers us a way to remove that boilerplate, thereby giving us a cleaner codebase. I decided to try it on a few modules, and saw a result that clearly demonstrated the usefulness of doing this (aside from the cleaner codebase).
Sample 22_Reader_issue1767 reads an Xlsx spreadsheet with complex sheet names used in defined names, and writes it to Xlsx and Xls output files. When I changed Writer/Xls/Parser to use Composer/Pcre, this sample failed in many different places writing the Xls file. It turns out that some regexes were failing not because the string didn't match, but because the regex encountered "catastrophic backtracing". Composer/Pcre throws an exception when this happens; the native preg_match does return false, but we were not checking for that. The regexes in question are now changed to something which works, and formal unit tests are added for them. Finding this previously undetected error indicates that we should proceed with this change.
An alternative to using Composer/Pcre would be to test for false after all the preg calls. I have done this in the two samples changed with this PR. That seems adequate for a small number of changes, but it really just makes for more clutter considering the large number of regexps that we use in our code. I think Composer/Pcre is a better choice.
It isn't quite transparent. Composer forces all regexps to use PREG_UNMATCHED_AS_NULL, so some match fields will now be null instead of null-string (or non-existent if the unmatched field comes at the end). Our test suite doesn't report any problem (yet) due to this change, although Phpstan is sensitive to it. Several Phpstan annotations were eliminated due to this change, but some others are now needed.
It is not necessary to do this all at once. This PR addresses all the calls in Writer. I intend to address other components in several tickets.
Fix#4319. PR #3495 allowed Mpdf and Tcpdf to handle hidden rows and columns. The handling is a little off when merged cells are part of the output. The fix for Mpdf is pretty straightforward. Tcpdf is a little more convoluted. Dompdf and Html do not require a change. Unit tests are added for the same scenario for all of Mpdf, Tcpdf, Dompdf, and Html.
Fix#4318. Also make getConditionalStyles more useful, by adding a non-default parameter so that all rules pertaining to a single-cell coordinate can be returned, and in priority order. By default, just the first matching rule will be returned.
This is the second recent occasion where style for whole row or column is behaving unexpectedly (see issue #4285). For the earlier issue, a documentation update was made to show the preferred method of styling. Setting styles for row(s) or column(s) in that way will work just fine, however retrieving the style doesn't yield the expected result. Although that problem can be overcome with existing code, simpler methods are needed, and this PR adds methods getRowStyle and getColumnStyle to Worksheet. I will continue to investigate why this problem and the one from 4285 have unexpected results.
Cloning a worksheet attached to a spreadsheet creates a clone which is detached from the spreadsheet. This can have its uses, but I think it would also be useful to have the ability to duplicate the worksheet and keep the duplicate attached to the spreadsheet. You can do that in Excel and LibreOffice, and you can now do it in PhpSpreadsheet as well. The duplicated worksheet will come immediately after its source.
The worksheet being duplicated could be identified in a number of ways - by passing the worksheet itself to the new method, by passing the worksheet title, or by passing the index of the worksheet within the spreadsheet. For now, I am just implementing the one I think is most useful (title).
Fix#4311. Excel applies Conditional Formatting rules according to a priority specified in the xml. The priority must be a natural number; the rules are applied in order from lowest priority number to highest. When reading an Xlsx spreadsheet, PhpSpreadsheet has been ignoring the priority, which can result in differences from Excel's behavior, especially when CF cell ranges overlap (note that overlapping ranges are not supported in Xls format).
If an application uses PhpSpreadsheet to add new Conditional Formatting to a worksheet and does not change its priority from the default (0), the Xlsx Writer will assign a priority with a higher value than any of the CF objects which have been assigned a priority (either from reading it or explicitly assigning it).
Fix#4311. Period is a valid character in a sheet name. When a sheet with such a name is referenced in Ods format, the sheet name must be enclosed in apostrophes, because Ods uses period to separate sheet name from cell address. (Excel uses exclamation point so doesn't necessarily need to enclose the sheet name in apostrophes.) This causes a problem for the Ods Reader whenever it tries to parse such an address; however, the problem showed up specifically for auto filters, because the Ods xml for those specifies *'sheetname'.startcell:'sheetname'.endcell* (Excel omits sheetname).
Ods Reader translates these addresses in 2 different methods in FormulaTranslator. I had a relatively elegant method for handling this situation in convertToExcelAddressValue, but I could not make it work in convertToExcelFormulaValue. A kludgier method works for Formula, and also for Address. I decided it's better to be consistent, so I'm going with the kludgier method for both.
It would not surprise me in the least if there are similar problems lying in wait for other special characters in sheet names, and for other formats besides Ods. For now, I will limit myself to fixing the known problem.
Fix#641 (marked stale in 2018, but now reopened). When a sheet's title is changed, PhpSpreadsheet updates references to the old sheet name found in formulas. Which is a good idea when the sheet is attached to the spreadsheet, but a bad idea when it isn't (often because it has been cloned without re-attaching to the spreadsheet). This PR continues to change formulas in the former case, but will no longer do so for the latter.
They have made a change at our request to help us eliminate runInSeparateProcess for one or more tests. This will be helpful when we get to PhpUnit 11 (not imminent, since it doesn't support Php8.1, but it will happen eventually).
oleibman