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).
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.
These are 5 closely related functions for manipulating arrays. Now that dynamic arrays are part of PhpSpreadsheet, this PR implements those previously-unimplemented functions. Documentation undergoes a very minor change, since they are re-categorized as "Lookup and Reference", which is how Excel classifies them, rather than "Math and Trig".
This is a partial response to issue #4282. The actual logic to implement GROUPBY is probably very complicated. And, even worse, Excel has thrown a whole new way of (internally) specifying one of the arguments into the mix. That argument is a function name, expressed not as a mapped integer (as SUBTOTAL does), nor even as a string, but as the unquoted function name prefixed by `_xleta.`. And, unlike its `_xlfn.` and `_xlws.` predecessors, it is difficult to figure out when the new prefix needs to be added, and when it needs to be ignored. I am not even going to attempt that task with this ticket.
So, what does this change do? Like earlier attempts to introduce limited functionality (such as with form controls), it is there so that using GROUPBY can be passed through - you can load a spreadsheet that contains it, and save it to a new spreadsheet, and the function and its results are preserved. Some cautionary notes. Dynamic arrays must be enabled (the function makes no sense without doing that). Changing any of the inputs used in the function may result in internal inconsistencies between PhpSpreadsheet and Excel; this is especially so if the dimensions of the returned array change as a result of changes to the input data. The programmer can avoid some of these problems by changing the formulatAttributes of the cell where the function is used; this may be difficult to do in practice. Oh, yes, using the GROUPBY cell as an argument in another formula will probably lead to problems. Finally, I confess that part of this solution looks awfully kludgey to me.
With its limitations and those cautions, is it worth proceeding with this change? My gut feel is that it is more useful to proceed than not. However, I will give others the opportunity to weigh in. I will wait at least a couple of weeks into the new year before proceeding with this.
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.
This will, I hope, be my last change prior to merge on August 7. PR is fully synced with master (except for this change), and, except for an emergency, I do not intend to merge anything else before this.
Till now we have used a static variable/getter/setter to decide what type of result should be returned when a formula is evaluated and an array is the result. This is messy; it would be much better to use an instance variable instead. We cannot eliminate `setArrayReturnType` and `getArrayReturnType` because that would be a BC break. I am considering whether they should be deprecated. In the meantime, I have added a new instance property `instanceArrayReturnType` with getter and setter methods. The property is initially null, and, if it remains so when needed, the static property will be used instead. However, if it is set, its value will be used.
oleibman
Mikko
Christian Fries
YoheiZuho
Blacknife
Guillaume
murongshaozong