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`.
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).
Fix#4285. Documentation change only - no changed code. It is not clear to me why using individual set options when styling an entire column works differently than applyFromArray, but the latter produces the expected result and the former doesn't. I will continue to research why. However, in the meantime, we can at least document that applyFromArray is preferred for this operation.
Fix#4269. In response to issue #456 PR #515, `forceFullCalc` was added to workbook.xml whenever `preCalculateFormulas` was set to false. It is not clear why this should have been needed; attempts to reproduce the error in the original 6.5-year-old issue are unable to reproduce it today. Nevertheless, it is, or was, there for a reason.
Today, the forceFullCalc option sets an option where formulas *might* not be recalculated when a cell used in the formula changes. I have not succeeded in finding a situation where it doesn't automatically recalculate, but it probably exists on complicated spreadsheets. To overcome this possibility, Excel offers a button which can be used to recalculate on demand. By itself, this might not be a terrible problem. However, it seems to come with the strange property that any spreadsheets opened at the same time as a forceFullCalc spreadsheet operate as if they too specified forceFullCalc. That *is* a problem, especially since users when closing a spreasheet affected in this way will be prompted to save it even when they haven't changed anything.
I am not willing to make a BC change at this time, although I might consider it in future (PR #4240). For now, I am adding a new property `forceFullCalc` with setter (no getter needed) to Xlsx Writer. That property can be `null` (default, in which case the Xml attribute is set as today), or `false` or `true` (in which case the Xml attribute will be set to the Writer attribute). I think that, when `preCalculateFormulas` is set to false, the calling application should give consideration to setting `forceFullCalc` to false as well. All other situations should just use the default.
Having addressed several security advisories, one evident *theoretical* problem remains. This is an attempt to future-proof our code against similar vulnerabilities. It all begins with our implementation of libXmlLoaderOptions, which uses as a default LIBXML_DTDLOAD. This unfortunate choice opens us to XXE problems, many recently solved. I do not believe that there is a legitimate use case for allowing this, and will therefore ignore and deprecate that option.
Although this might seem to be a breaking change, it is not. The setting is used only after the Xml has been subject to a security scan, and the security scan throws an exception if it detects the use of `<!DOCTYPE` within the Xml. Therefore, the setting will be effective only on Xml which does not contain that tag, and will consequently have no effect on most Xml. The only exception would be Xml which has been crafted to avoid detection by the security scanner in a manner which has not been disclosed to us. Although we hope that we've now blocked all such avenues, this provides additional protection just in case.
With this change in place, we could relax certain restrictions, e.g. the use of EBCDIC or even UTF-7. For now, these will remain in place. I will need to be convinced that there is a legitimate use case for easing the restrictions before doing so. We might even consider the elimination of the Security Scanner altogether. However, it does allow for early detection, and, in any case, provides a method to correct Xml which most Xml readers would fail but which Excel accepts.
My plan is to merge this within the next few days, and tag a new release immediately after. It will also be backported to all active branches.
Fix#1395, a 2020 issue which had been marked stale and is now re-opened. Static valueBinder property of Cell isn't ideal. It would be more flexible to make it a dynamic property of the spreadsheet. Static property will continue to be used, but dynamic property will be used first if it is set. Readers will also be changed to add a valueBinder property which they pass to the spreadsheet; however, it will make a difference only for Csv/Html/Slk, since the other readers use setValueExplicit which ignores valueBinder.
Documentation is updated in several places to note that dynamic property is preferred over static.
When String Value Binder converts a numeric value to text, the resulting spreadsheet will be full of little green triangles to indicate to the end user that something might be wrong. It is unlikely that a spreadsheet created in this manner needs that visual clutter. This PR adds a property and setter (I can't really think of a good use case for a getter) to suppress it. Suppression should arguably be the default, but, for now, I will avoid any BC problems by leaving non-suppression as the default.
oleibman
h-marumoto
Sebastian Hädrich
Mikko
Christian Fries
YoheiZuho
Blacknife
Guillaume
murongshaozong