Continuing the work of PR #4810 and PR #4813 and PR #4814. According to the spec for LibreOffice, the xml for both coluns and rows allow a `default-cell-style-name` attribute. However, my testing indicates that using that attribute with a row doesn't result in the results I would expect; furthermore, LibreOffice doesn't seem to use that attribute in association with a row (it does use it for columns). For rows, it fills the unpopulated rows in the cell with a repeated table cell with the appropriate style. These filler cells have been a source of problems for us, and I'm not willing to add them at this time (maybe in future). So, if a style is applied to a row in PhpSpreadsheet, it will be reflected in all cells in that row until `highestDataColumn`, but no further.
Since LibreOffice does support adding a style to a column both in the UI and the Xml, it should be a little easier. But its support is imcomplete as well - when reading a spreadsheet, the UI will apply the style only to the rows that are defined in the xml. Again, this requires filler rows for the rows beyond `highestDataRow`, and, again, I am not willing to add those. However, it is important to process the column style when reading because, unlike the case with rows, the cells which are defined in the column may omit style data altogether if the style matches the column style.
There has till now been no support for `ReadDataOnly` (default false) and `ReadEmptyCells` (default true) for Ods Reader. These are now added. ReadEmptyCells is especially important and it would be sensible to usually set it to false. The reason is that LibreOffice usually supplies a width for all the columns mentioned in its row fillers, and PhpSpreadsheet needs to create ColumnDimensions for each of those as a result, resulting in much larger xml than one would expect when the spreadsheet is saved. This arose when I was testing this change - it has always been the case, but nobody has raised an issue about it. We avoid this problem by ignoring the extra columns for ReadEmptyCells false. But this requires a little redundancy in the code. We need to define styles for an entire column before we define styles for the cells in the column, but we don't need to define column widths beforehand. So, if ReadEmptyCells is false, we need to process the columns for styles, then process the cell data (so that we know what `highDataColumn` is), then process the columns again but this time for widths (ignoring columns beyond highDataColumn). If ReadEmptyCells is true, we can process column styles and widths in a single pass.
When using a read filter with the ODS reader, cells with
table:number-columns-repeated containing data were incorrectly handled.
The read filter was only checked for the first column of a repeated
group - if it didn't pass, the entire group was skipped, even if
other columns within the range should have passed the filter.
This caused column data misalignment where filtered columns would
either be missing or contain data from wrong positions.
The fix ensures that:
1. When the first column of a repeated group fails the filter, the
reader checks if ANY column in the group passes before skipping
2. During data writing, each repeated column is individually checked
against the read filter
FixesPHPOffice/PhpSpreadsheet#4802
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.
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!`.
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#4099. Ods Reader was expecting there to always be `header-style` and `footer-style` tags when `page-layout` tag is present, but these need not exist. It seemed like there might be other exposures along this line in `readPageSettingStyles`; rather than waiting for a problem report to show up for each, the code is updated to use `->item(0)` in place of `[0]` when appropriate, and make use of the nullsafe `?->` operator introduced with Php8.
Fix#804, opened in Dec. 2018, and closed as stale in Feb. 2019, and which I have re-opened to be closed properly by this PR. Better late than never, I suppose. A third party generated an ODS spreadsheet which PhpSpreadsheet could not read. By way of explanation, the xml in the file contained lots of whitespace between tags, which is wonderful for those humans among us who have to analyze it; but PhpSpreadsheet was not prepared for it. It is now.
Fix#4081. Ods Reader was not reading entire contents of comment. On further inspection, Ods Writer also was not handling comments completely correctly. Ods comments are recorded as `text:p` children of `office:annotation` elements. A newline is inserted between successive `text:p` elements. The `text:p` element itself can have as descendants (at least):
- raw text
- `text:span` elements
- `text:line-break` elements, which also causes the insertion of a newline
Ods Writer is changed to use a single `text:p` with multiple span/linebreak elements. Ods Reader is changed to process in their entirety either that form, or multiple `text:p` elements. Styling of the individual elements of the comment is permitted in Ods. That has not been supported till now by PhpSpreadsheet, and this PR will not address that situation - Ods Reader hast little style support, and this would hardly be the most urgent case where it is missing.
* Add iterateOnlyExistingCells to Constructors
Fix#3721. That issue can already be handled, but requires several statements when one ought to suffice. Adding an extra parameter to the RowCellIterator and ColumnCellIterator constructors is useful, easy, and becomes especially practical now that our supported Php releases all support named parameters (see new test Issue3721Test).
* Update Column.php
* Update .php-cs-fixer.dist.php
* Update Row.php
* Update Column.php
* Update ByColumnAndRowTest.php
Fix#3658. Readers will call `setCalculatedValue` to save the original calculated value for a cell that contains a formula; this affects the result of `getOldCalculatedValue`. The set routine automatically casts numeric values to float, which is not appropriate when the calculated value is explicitly set to a string type. An optional parameter is added to the set routine to indicate whether a cast is appropriate (default is true so userland uses of this method will remain unaffected). The readers for Xlsx, Xls, Xml, Ods, and Slk are all changed to set the new parameter appropriately. Gnumeric saves the formula, but does not appear to save the result, so no change is needed there. Html and Csv save the result but not the formula (for preCalculateFormulas true - they save the formula but not the result for false), so no change is needed for them.
It is not clear what the use case is for oldCalculatedValue, but it does date back to PhpExcel. A possible use case is demonstrated in the new tests - Excel function INFO is not implemented, so getCalculatedValue will generally return null on such a cell, but will return oldCalculatedValue when it sees that the function is not implemented but oldCalculatedValue has been set (typically by loading an existing spreadsheet), so the result that is on the spreadsheet will be available to the program which loaded it.
Reading a spreadsheet which has been created with preCalculateFormulas false will, or at least should, result in a null oldCalculatedValue. This was true for Xls, Xml, and Slk. Xlsx was actually storing 0 in this situation; that wasn't precisely a problem, but it wasn't necessary and seems misleading. Xlsx Writer is changed to no longer set a value in this situation. This is technically a break, but the existing code was wrong; one unit test needed a very minor modification as a result of this change.
Fix#2810. Repairing some Phpstan diagnostics, used `?:` rather than `??` in a few places.
2 different Html modules are affected. Also, Ods Reader, but its problem is with sheet title rather than cell contents. And, as it turns out, Ods Reader was already not handling sheets with a title of `0` correctly - it made a truthy test before setting sheet title. That is now changed to truthy or numeric. Other readers are not susceptible to this problem. Tests are added.
* Defined names/formulae in ODS are prefixed by $$ when used in a formula; so we need to strip this out to fully convert them to an Excel formula
* Test for ODS Writer for DefinedNames
* First steps in the implementation of AutoFilters for ODS Reader and Writer, starting with reading a basic AutoFilter range (ignoring row visibility, filter types and active filters for the moment).
And also some additional refactoring to extract the DefinedNames Reader into its own dedicated class as a part of overall code improvement... on the principle of "when working on a class, always try to leave the library codebase in a better state than you found it"
* Provide a basic Ods Writer implementation for AutoFilters
* AutoFilter Reader Test
* AutoFilter Writer Test
* Update Change Log
* Improve Coverage for ODS Reader
Reader/ODS/Properties is now 100% covered.
Reader/ODS is covered except for 1 statement. As the original author
put it, "table-header-rows TODO: figure this out ... I'm not sure that
PhpExcel has an API for this". I'm still thinking about it, but, so far,
I agree with the author.
There are minimal code changes.
- Several places test !zip->open() to see whether the test failed.
However, zip->open() returns true or a string, so the test never
detects failure. Change to zip->open() !== true. No previous tests.
- Suppress warning messages from simplexml_load_string (there had
been no tests for invalid xml).
- One document property was misnamed, and one non-existent property
was tested for.
I added a number of tests, creating an ODS directory, and moving
OdsTest to that directory.
* Scrutinizer Recommendation
Unused variable in one test.
* Update CHANGELOG
Co-authored-by: Adrien Crivelli <adrien.crivelli@gmail.com>
oleibman
QDenka
Jokūbas
MarkBaker
MarkBaker
oleibman
Paolo Agostinetto