Based on recent discussions in #2938, it can do a bit better with date cells that do not use a recognized date style. Improve differentiating `date` from `date+time`, and in deciding whether 4-digit years have been used in the input cells. This causes a change to one test, where the old result incorrectly expected a 2-digit year (you can verify by opening the spreadsheet in question that it is saved with a 4-digit year).
Use getSheetByNameOrThrow() instead of the nullable getSheetByName() for
chained calls, assert non-null before dereferencing getCacheDefinition() and
getPivotTableByName(), and replace an inline array_map closure with a typed
helper so PHPStan (level 8) is satisfied.
Previously, loading an Xlsx file that contained a pivot table and saving it
again silently dropped every pivot part, corrupting the user's pivot tables.
This makes the writer round-trip them intact.
Reader: alongside parsing pivot tables into the object model, the raw pivot
parts are now captured into the unparsed loaded data - the pivot table part
and its rels (per sheet), the pivot cache definition, its rels and the cache
records - each keyed by its original archive path so the relationships between
them stay valid. The workbook <pivotCaches> registry (cacheId -> cache
definition) and the pivot content-type overrides are captured too.
Writer: the preserved parts are re-added to the archive; worksheet
relationships gain a pivotTable relationship, workbook relationships gain a
pivotCacheDefinition relationship, and workbook.xml re-emits <pivotCaches>
with an r:id that matches the regenerated workbook relationship. Content types
are restored via the existing override_content_types pass-through.
The output is a structurally consistent OPC package (all rels and content
types resolve) and is stable across repeated round-trips. Tests cover part
preservation, workbook/rels/content-type wiring, and that the pivot object
model still reads back after a save.
Introduces a first-class, read-only representation of pivot tables that
already exist in a loaded Xlsx file (issue #4534). Previously pivot tables
were completely inaccessible from the object model.
New model classes under Worksheet\PivotTable:
- PivotTable: name, location, source cache definition and field layout,
with axis helpers (getRowFields/getColumnFields/getPageFields/getDataFields).
- PivotCacheDefinition: cache id, source worksheet/range and cache field names.
- PivotField: field index, name, axis placement, and data-field aggregation.
Reader\Xlsx\PivotTableReader parses a pivotTableDefinition part and its
associated pivotCacheDefinition part into that model. The main Xlsx reader
discovers pivot parts via worksheet relationships and wires them up (skipped
under setReadDataOnly). Worksheet gains getPivotTableCollection(),
getPivotTables(), addPivotTable(), getPivotTableByName(), getPivotTableNames()
and removePivotTableCollection().
This is inspection-only: it does not create, modify, recalculate or render
pivot tables, and does not change what the writer emits. A minimal pivot-table
fixture and functional tests cover reading name, location, cache source,
fields and axis placement, plus the read-data-only path.
Add tests for previously-uncovered SparklineGroup setters (axis types,
manual limits, additional display options and colours) and a hand-crafted
Xlsx fixture exercising the reader's edge cases (non-sparkline ext, group
without sparklines, sparkline with empty sqref, missing colour elements).
Harden Reader/Xlsx/Sparklines against malformed input: guard the
sparklineGroup and sparkline iterations against a null children set so an
empty <x14:sparklineGroups> or <x14:sparklines> element no longer emits a
PHP warning.
A number of interesting performance ideas have been submitted by @kemo. PR #4833 optimizes Xls Reader in a number of different ways. One of those, slightly modified, is to use the protected `readFilter` property which is available to all classes which inherit from `BaseReader` (which all our supported readers do), rather than call the `getReadFilter` method to obtain it (often in a loop). That applies to all readers, not just Xls, and is such a good idea that I am fast-tracking it ahead of 4833. Gnumeric, Ods, Xls, Xlsx, and Xml will all receive small performance boosts from it. Csv already does this, so no change is needed there. Html and Slk do not appear to do any filtering; possibly work for another day.
Building on PR #3508 and PR #4393, which allowed ignoring 5 different cell 'errors'. Adding this new one should have been dead easy, but MS, in its own inscrutable way, decided to complicate this by making the xml attribute require its very own namespace, unlike any of its relatives.
There are supposed to be several ways to activate this tag. One is to enter a date in cell A1, then in cell B1 enter the formula `=A1`, and set B1's numeric format to `General`.
This PR will be merged at the end of this month. As the README says, we maintain support for Php Versions for six months beyond their end of life. That time has now arrived for 8.1.
Html Reader recognizes a `data-format` attribute for storing the number format associated with a cell. However, Html Writer does not set this attribute. This PR offers a new Writer property `preserveFormatAndValue` and setter; when `true`, for any cell which uses a number format other than the default `General` the writer will generate a `data-format` attribute and, for strings or numbers, it will also generate a `data-value` attribute. This will increase the accuracy of the spreadsheet when reading/writing Html.
To make this change less abstract, assume a cell which has a value of `1234` and a number format of `$#,###.00`. The cell displayed in the generated Html will appear, regardless of the setting of the new property, as `$1,234.00`. However, the Reader currently treats it as a string with that value. If the html has been generated with the new property set to `true`, the Reader will treat it as a numeric cell with a value of `1234` and a style of `#,###.00`. This permits a more accurate reproduction of the original spreadsheet.
I suspect that setting the new property, aside from causing a slight increase in file size, will not cause any significant breaks. However, I'm not totally convinced, so the property will default to `false` (no change in behavior). The default may change in a future breaking release.
Researching a security advisory, the code for handling text colors in Html Writer when the cell is non-numeric and the color is hard-coded in the Number Format looked a bit suspicious. As it turns out, the code was *not* subject to a security exploit. However, the color for the Number Format was ignored. This PR corrects that omission.
* Test Protocol Not Supplied Before Using is_file
* added missing assertFile and renamed prohibitPhar
* More Tests
---------
Co-authored-by: calligraf0 <115566010+calligraf0@users.noreply.github.com>
A follow-up to PR #4837. PR #4827, to which I am not necessarily committed, shows that there could be a use case for letting a user perform customized logic to convert a non-UTF8-encoded CSV. In addition, several other CSV Reader properties require the use of static or locale properties, which is somewhat problematic; this PR allows them to be set as instance variables, falling back to static/locale only when unset.
Fix#4836, an issue which I was about to dismiss out-of-hand, but I decided that we could and should be making it a bit easier for our users to avoid problems. To that end, I have changed most of the private properties in Reader/Csv from `private` to `protected`. This allows users to extend the class easily; it is a better option than the existing `constructorCallback` - less awkward, not reliant on static settings, etc. (I know more now than I did when I introduced it.) Taking advantage of the new extendability, a new class `Reader\CsvNoEscape` is added which forces the use of null-string as the escape character, and prevents the use of auto-detection of Mac line-endings; both of these are schedule for removal with Php9.
This actually makes `CsvNoEscape` a better option than `Csv`;
it should probably be used for all new development.
Because `IOFactory` is heavily used for loading files, it has some changes to better accommodate `CsvNoEscape` (and other classes). In particular, methods `createReader`, `load`, `identify`, and `createReaderForFile` now allow a new `mergeArray` parameter, where that parameter is merged with the static `IOFactory::$readers` array when deciding on a Reader class to be used. This allows the user to override the default Csv Reader, without having to change a static array, and without affecting IOFactory's ability to find other matching readers if no suitable entry is found in `mergeArray`.
Fix#1606, which went stale and is now reopened. PR #4806 supplied support for some Number Formats for Ods, but did not directly address this issue. This PR does. In addition to the original issue, this could be useful for zip codes. As I stated in 4806, I may be amenable to adding some unsupported styles to the built-in list, but the custom style option will always be around in case I am being slow or unreasonable.
We have identical constants defined in several places, and use literals in others. We aren't consistent in checking limits. This PR makes the use of the constants in Cell/AddressRange the "official" source, deprecates all other constants, and substitutes the constants wherever literals are used. A number of different edge case tests are added.
During testing, I discovered that `columnIndexFromString` correctly throws an exception for 4-character string, but allows `XFE` through `ZZZ`, all of which are also invalid. There are similar inconsistencies with related routines, and this PR attempts to make them operate consistently. One suprise is that throwing for `row=0` causes serious regression problems, so it continues to be permitted (but the high row limit is enforced).
Further, Reference Helper sometimes dips into negative numbers, resulting in totally unexpected results (-1 affects column Z, -2 column Y, etc.). It is changed to ignore rows and columns outside the limits.
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.
Continuing the work of PR #4810 and PR #4813. All "real" borders are implemented. Pseudo-borders (outline, vertical, horizontal, inside, and allBorders) are not because I'm not really sure how, or even if, Ods handles them. However, Sample20_Read_Ods demonstrates a way to emulate `outline` using just the real borders. In template OOCalcTest.ods cells B18:C19 are merged, and the merged cell has multi-colored borders, something which `outline` isn't able to do. Ods Reader handles this by assigning the border style first to cell B18 (which is what Ods wants, and it doesn't hurt Xlsx/Xls) and then to cells B18:C19 (which is what Xlsx and Xls want, and it doesn't hurt Ods).
Ods Reader changes still to come
- Style applied to entire row
- Style applied to entire column
- Parent styles?
Ods Writer changes still to come
- Style applied to entire row
- Style applied to entire column
- Parent styles?
Not currently on my to-do list
- Pseudo-borders
- Subscript and superscript fonts (these really only make sense for RichText, and I'm not sure how Ods handles RichText)
- Default border, alignment, and protection. I'm not convinced there's a use case. Ods handles its defaults in two different manners, and I don't want to spend the time trying to understand it without justification.
Continuing the work of PR #4810.
Ods Reader changes still to come
- Default border, alignment, protection
- Font
- subscript
- superscript
- Borders (up next)
- Style applied to entire row
- Style applied to entire column
- Parent styles?
Ods Writer changes still to come
- Default border, alignment, protections
- Style applied to entire row
- Style applied to entire column
- Parent styles?
oleibman
saifulislamferoz