PR #4687 corrected how Xls Writer generated its Dimensions records. We ignore the Dimensions record on read since it does not affect our processing in the slightest. However, the PR raises the possibility that someone might wish to see the data in the Dimensions record. (The PR did an adequate test for retrieving Dimensions data, but it is not generalizable.) To accommodate such a case, we add a new ListWorksheetDimensions function to Xls Reader, similar to ListWorksheetInfo. As luck would have it, the spreadsheet with which I tested the new function produced incorrect results for ListWorksheetInfo, which was ignoring XLS_TYPE_MULRK records. So I added the necessary code to fix ListWorksheetInfo as well.
Fix#850 (marked stale many years ago, but now reopened). User had a typo in their script which would have caused problems no matter what. However, it exposed another problem. Style Alignment Read Order was supported only by the Xlsx Reader and Writer, but it could have been supported pretty easily for most other formats. This PR adds support for the following:
- Xls (read and write)
- Html (write, and read using inline styles). Html reader does not yet process most classes.
- Pdf (write). PhpSpreadsheet does not have a Pdf reader.
- Ods (write). PhpSpreadsheet does not yet support reading most Ods styles.
- Xml (read). PhpSpreadsheet does not have an Xml writer.
- Gnumeric (no change). It appears that the Gnumeric product does not support this attribute.
- Csv (no change). Csv does not support any styles.
- Slk (no change). Slk does not support non-Latin characters, so this attribute doesn't make sense for it.
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.
Fix#4629. Excel Xml can specify Conditional Formatting in 2 ways - either using a `conditionalFormatting` tag as a child of `worksheet`, or in a slightly different manner as a child of `worksheet->extLst`. Although these are both handled by PhpSpreadsheet, the former is lost if there exists a latter whose cell range (`sqref`) matches it. This PR merges the former with the latter in that circumstance, preserving both.
The issue also notes that `font` and `stopIfTrue` are not handled correctly. Support for those was missing in the `extlst` handling, and is now added with this PR.
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.
Fix#1637, which went stale but is now re-opened. When Google Sheets exports a document to Xlsx or Ods, it replaces Google-only formulas with something that Excel or LibreOffice can handle. In the test case accompanying this PR, cell C1 on Google Sheets contains `=flatten(A1:A5,B1:B5)`. On export, C1:C10 (the actual result is a 10*1 array) are changed to `=IFERROR(__xludf.DUMMYFUNCTION("flatten(A1:A5, B1:B5)"),1.0)`, where `1.0` is replaced by the calculated value for each cell in question.
The issue reports an Internal Error when evaluating such a formula. I am unable to duplicate that. However, PhpSpreadsheet evaluates the cell as a `#NAME?` error rather than the correct value (1.0 for cell C1). The reason is that `__xludf.DUMMYFUNCTION` does not match the regexp for formulas, but does match the regexp for defined names. Not finding such a defined name results in the Name error. Altering the formula regexp to recognize `__xludf.` is easy, and solves the problem.
Fix#4539. Conditional Formatting was recently added to Html Writer. It works fine when not using inline Css. However, when using inline Css, the code inadvertently added 2 different `style` attributes (one for the unconditional style and one for the conditional style) to the same cell. This is not valid html, and results in losing the conditional styling. This PR combines the two `style` attributes into one, which will now come after the `class`, `colspan`, and `rowspan` attributes.
Aside from the new tests, this PR changes an unusually large number of existing tests. While this might normally be considered a red flag, it is not a problem here. All of the changes involve merely changing the order of attributes within html tags; none of them affect how the generated html would appear in a browser.
Fix#4528. Fix#2507. Ods Reader expects `table:table-row` to descend directly from `table:table`. But it turns out that it can be nested under `table:table-header-rows`, `table:table-rows`, or `table:table-row-group`. Similar considerations apply to `table:table-column`. Ods Reader is changed to process the nesting tags, and therefore the nested tags underneath them. This is a major change, but it doesn't seem to have broken any existing tests or samples.
No attempt is made to add any of the nesting tags in Ods Writer.
It should be noted that 4528 has an additional problem, which has been reported as issue #4530. It is not fixed by this PR.
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.
Fix#4477. Xlsx Spreadsheet could be loaded and saved normally, but, when the load specified Dataonly, the output file was corrupt. Reader is changed to not process Printer Settings when Dataonly is in effect.
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)
Fix#407, which went stale over 7 years ago, and is now reopened. Ods handling of CEILING and FLOOR functions is, apparently by design, subject to interoperability problems with Excel. See [FLOOR](https://wiki.documentfoundation.org/Documentation/Calc_Functions/FLOOR) and [CEILING](https://wiki.documentfoundation.org/Documentation/Calc_Functions/CEILING). Ods implements its own `CEILING.XCL` and `FLOOR.XCL` functions for interoperability purposes. PhpSpreadsheet will implement those, and `CEILING.ODS` and `FLOOR.ODS` psuedo-functions as well; none of these four will be listed in the official Functions documentation.
In all of the descriptions below, FLOOR follows CEILING exactly.
Ods writes its functions to Xml as follows:
- CEILING.XCL becomes COM.MICROSOFT.CEILING
- CEILING.MATH becomes COM.MICROSOFT.CEILING.MATH
- CEILING.PRECISE becomes COM.MICROSOFT.CEILING.PRECISE
- CEILING remains unchanged
PhpSpreadsheet Ods Writer will do the same, plus:
- CEILING.ODS becomes CEILING
PhpSpreadsheet Ods Reader will act as follows:
- CEILING (not preceded by 'COM.MICROSOFT.') becomes CEILING.ODS
- COM.MICROSOFT.CEILING.MATH becomes CEILING.MATH
- COM.MICROSOFT.CEILING.PRECISE becomes CEILING.PRECISE
- COM.MICROSOFT.CEILING becomes CEILING
PhpSpreadsheet Xlsx Writer will act as follows:
- CEILING.ODS becomes CEILING.MATH
- CEILING.XCL becomes CEILING
PhpSpreadsheet Xls Writer will recognize only the CEILING and FLOOR functions.
Note that the only difference between Ods CEILING and CEILING.MATH is that CEILING will return an error if the number and significance operands have opposite signs. PhpSpreadsheet implements this as `#VALUE!`.
Fix#282, which went stale over 7 years ago, and is now reopened. This is certainly related to issue #4379. If merged cells are in the midst of deleted rows/columns, the merge may continue to exist after the deletion, leading to various problems including spreadsheet corruption. The problem is not with either Reader or Writer.
This PR will automatically unmerge all merged cells whose first cell is in the delete range before performing the deletion. This will address the corruption problem. Something more sophisticated may be required, so I won't merge this PR for a while to give me a chance to think about it some more.
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.
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.
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`.
oleibman
karrakoliko
Emeka Oziri
issa