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#4626. Previous changes had increased the precision of floating point numbers when cast to string, making for greater accuracy after save and load operations, without affecting the values displayed by Excel. Although the results of the cast are now more accurate computationally, they can appear unexpected to humans. A new boolean parameter `lessFloatPrecision` (defaulting to false) is added to `StringHelper::convertToString`, to `NumberFormat::toFormattedString` and `NumberFormat\Formatter::toFormattedString`, and to the entire `Worksheet::toArray` family of functions. When the new parameter is set to true, the result can be less surprising to humans. It should not, however, be used in subsequent computations.
In the case of the NumberFormat functions, the new parameter will be considered only when the NumberFormat for the cell in question is `General` or equivalent. Setting an actual numeric format for the cell is probably a better solution than using the new parameter.
Fix#4600. String incrementation through the `++` operator is deprecated in Php 8.5. Because we make use of that operator to iterate through columns, we are particularly hard hit by that change - unaddressed, it causes over 2,000 errors in our test suite! It is, fortunately, not as difficult as I feared to correct. Replacing the `++` operator with a call to new method `StringHelper::stringIncrement` in 79 statements scattered over 31 source modules (in src, samples, test, and infra) eliminates all the messages in the test suite. It is possible that others are lurking, but I don't know a systematic way of determining if there are others. We'll stick with this for now, and deal with any others as they show up.
This PR will be applied to the master, release390, and release222 branches. It will not be applied to the release210 or release1291 branches, which will now accept security changes only.
PR #455 was submitted by @Aketos in 2018. It added no unit tests, so it was not merged, and it eventually went stale. Without the tests, I'm not sure exactly what the user had in mind. But my investigation indicates the following:
- for Xls files, if the column width in the file specifies a value greater than 255, Excel will choose its own width when it opens the file.
- for Xlsx files:
- Excel does not allow you to set a column width > 255, neither by dragging the column boundary, nor by right-clicking and setting a value, nor by auto-fitting the column width.
- Nevertheless, if the XML specifies a column width > 255, Excel will honor that value in the displayed spreadsheet, and even write it out if the file is saved.
I have taken a different approach than the original PR, which changed `Worksheet::calculateColumnWidths`. Instead, this PR adds an option to the Xlsx Writer to either restrict column widths to 255 or not. The default is "not" in order to avoid a breaking change. For emulating Excel's behavior, in the unusual situation where it might matter, the user might consider using the non-default option.
This PR also restricts column dimension width to 255 when saving an Xls file.
Fix#484. Fix#1318. Both went stale a long time ago. The images can currently specify their location as Left, Center, or Right. However, they also need to specify their type - first page, even pages, or odd pages (which is what is used when neither of the others is applicable). This is accomplished by adding 18 new constants to `Worksheet\HeaderFooter`, any of which can be specified as the second argument to `HeaderFooter::addImage`. Somewhat surprisingly, no changes are required to Xlsx Writer or Reader.
Xls Reader and Writer support header/footer, but do not support using images in them. No other readers nor writers support header/footer.
Add to all readers the option to allow or forbid fetching external images. This is unconditionally allowed now. The default will be set to "allow", so no code changes are necessary. However, we are giving consideration to changing the default.
My system short date format is set to `yyyy-mm-dd`.
I used Excel to create a spreadsheet, and included some dates, specifying `yyyy-mm-dd` formatting. When I looked at the resulting spreadsheet, I was surprised to see that Excel had stored the style not as `yyyy-mm-dd`, but rather as builtin style 14 (system short date format). Apparently the fact that the Excel styling matched my system choice was sufficient for it to override my choice! This is an astonishingly user-hostile implementation. Even though there are formats which, by design, "respond to changes in regional date and time settings", and even though the format I selected was not among those, Excel decided it was appropriate to vary the display even when I said I wanted an unvarying format.
This PR adds a new method `replaceBuiltinNumberFormat` to undo the damage that Excel does in such a situation. It also adds an `Excel Anomalies` document to the formal documentation, just to make situations like this readily available to the community.
BTW, Excel's sabotage can be avoided by using a number format style like `[Black]yyyy-mm-dd`.
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#4485. VSTACK and HSTACK were introduced to Excel in 2024, and will now be supported by PhpSpreadsheet. Special thanks to @SlowFox71, who posted some code to implement VSTACK in the linked issue. I didn't have to change much between that version and the one in this ticket.
Excel has at least one idiosyncrasy with these functions. If you try to stack 2 tables with, say, `=VSTACK(TABLE1, TABLE2)`, Excel will silently change it to `=VSTACK(TABLE1[], TABLE2[])` and all will be well. This would be difficult for PhpSpreadsheet to do. The problem is that, when Excel reads the formula without the square brackets, it calculates it as a `#NAME?` error. This is baffling, and all the more so because you can "correct" it by editing the formula *without changing anything*, hit enter, and the formula will magically work again. So, if you plan to stack tables using PhpSpreadsheet, you're probably best off using the square brackets.
The entries in the "function list by name" document are very long, and almost all of them require horizontal scrolling, which is not very convenient for the end-user. This PR creates a "function list by name compact" document, in which `CATEGORY_` is stripped from all the category names and `PhpOffice\PhpSpreadsheet\Calculation\` is stripped from all the functions. I believe this eliminates the need to scroll horizontally within the document. I waffled on whether this should replace the existing document, or whether it should be an additional document. I decided there was probably some merit in keeping the old, and hardly any down-side, so, at least for now, we will generate both documents, and they will link to each other.
No changes to source code.
Theme colors subject to tints occur in the wild, and should not be targeted by this PR. Our use case is an unmodified Theme color. There is probably a decent case to be made for adding a Tint property to Color, but that can come later if desired.
`Color::setHyperlinkTheme` will now try to fill in `rgb` as well as `theme` if it can locate an appropriate value in the Spreadsheet Theme. This will permit non-Xlsx formats to at least duplicate the color that Xlsx uses for hyperlinks.
Setting `Color::rgb\argb` will reset `theme` to none. If you want to use both, do rgb first, then theme. This will, I hope, avoid surpises for end-users.
Fix#1275, which had been closed as stale, and is now reopened pending the implementation of this PR. If there is a page break inside a defined print area, Excel may not render the print correctly unless the xml `brk` tag contains a `max` attribute. Libre Office renders it correctly. This seems like a bug in Excel (https://learn.microsoft.com/en-us/openspecs/office_standards/ms-oe376/b32ae11b-dee7-4dcb-9b46-a0feb32ce94f states that Office ignores min and max). PR #3345 (issue #3143) already addressed this problem by allowing the user to explicitly specify a `max` property in the PageBreak object. This PR eliminates the need for the user to make use of that kludge, by adding `max` to the xml whenever a page break is specified on a sheet with a defined print area. Xlsx Reader will now ignore the `max` attribute for row breaks, since it is no longer needed; it already ignores it for column breaks. The user may still set the `max` property if desired, just in case the new treatment is not adequate (I have not found a case where that is true). Two existing unit tests are very marginally changed because of this PR.
Document some cases where solution might not work well. Making class extendable and offering some over-rideable methods may allow for solutions to some of these problems.
Helper/TextGrid was intended to assist with some samples. However, it has emerged recently on two tickets. PR #4342 intended to introduce functionality very similar to TextGrid, and was closed for that reason. Issue #1640 was closed as stale over 4 years ago, despite the fact that TextGrid seems an adequate resolution for it.
Since it seems that there is a use for this function beyond its original intended usage, I added a few parameters to give it some flexibility - the ability to omit row and/or column headers, and the ability to add a divider line between rows.
A description of this function is added to the formal documentation.
Fix #4357.`identify` returns a type, not a class name, and this result is not always usable for createReader. The docs say that it is usable in that manner. Changing the behavior of `identify` would be a breaking change, but adding an optional parameter (defaulting to current behavior) allowing it to return a class name rather than a type would not. This PR adds that parameter and updates the documentation to suggest its use for the code in question.
To complete this change, `createReader` now accepts either a type (current behavior) or class name (new). Although it is not particularly identified as a possible problem by the issue, `createWriter` is similarly changed for consistency's sake.
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).
oleibman
h-marumoto
Sebastian Hädrich
Mikko
Christian Fries
YoheiZuho