Fix#1453, which went stale in 2000, which I unstaled in 2024, and which I finally got to. The code, and several unit tests, was substantially generated by AI, my first foray into that frontier.
Trait `ArrayEnabled` is not able to handle this function because it has too many array parameters. I would have liked to update it to handle this, but that seemed too difficult. I have let the code do its own array handling; it looks a little kludgey but seems to do the job. I may return to this at some point.
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`.
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.
Fix#2622. Fix#1191 (stale as of 2020 but just reopened). A long overdue addition. Ods Reader has till now not supported any styles (although PR #4806 added some support for number formats). This will be the first of several PRs to rectify this situation.
Ods Reader Changes
- Default font
- Font
- autoColor
- bold
- color
- italic
- font-family (name)
- size
- underline
- Fill (type=Solid only)
- startColor
Ods Writer Changes
- Default font
- Font
- strikethrough (fix#4808)
- size currently always writes to xml with one decimal point, will now eliminate `.0`
Ods Reader changes still to come
- Default fill, border, alignment, protection
- Font
- subscript
- superscript
- Fill
- types other than Solid
- Alignment
- Borders
- Protection
Ods Writer changes still to come
- Default fill, border, alignment, protections
Redo of PR #4799 due to failed attempt to resolve merge conflict.
Fix#4798. Partially address #3961. Ods Reader supports very little related to styling. Ods Writer supports many styling details, but has not heretofore supported Number Formatting. 3961 addresses both issues; I created 4798 specifically for the Writer side.
Writing number formats to Excel is pretty simple - you just supply a string and that is used directly in the Xml. Ods is much more complicated - it requires Xml nodes that give a complete description of the styling. For that reason, it is difficult and painstaking to convert from the string that Excel (and PhpSpreadsheet) uses to what Ods requires.
This PR provides code to support almost all the styles defined as constants in Style/NumberFormat. It also allows the user to add code to handle otherwise unhandled styles. New Sample55_DefinedStyles demonstrates the use of all the constant styles, plus the addition of a couple of custom styles. 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.
This PR does not fully support Ods Reader handling of styles. However, based on the new Writer output, it will often be able to guess the true type of numeric items and assign an appropriate style for that type. So, for example, if it can identify the field as a date, it will assign a date style. It will not always match the style in the sheet being read, but it is a big advance from just formatting the data as a generic number.
Fix#993, which went stale in 2019 and is now reopened. It was not explicitly stated in the documentation, but Xlsx supports hyperlinks when clicking on a drawing. User tried to use the same format for internal hyperlink that is documented for cell internal hyperlink, and that failed (corrupt spreadsheet, hyperlink doesn't work). That was because Xlsx uses `#` rather than `sheet://`. For drawings, Xlsx Writer will now automatically convert `sheet://` to `#`, and Xlsx Reader will now convert the other way. No change is needed for cell hyperlinks. Documentation is now updated, including a statement that drawing hyperlinks work only for Xlsx.
Fix#954, which went stale 6 years ago and which I have now reopened. The `setLocale` method in `Calculation` and `Settings` does not affect the 3 localizable properties in `StringHelper` - `currencyCode`, `decimalSeparator`, and `thousandsSeparator`. One way to work around this problem is to set those properties separately; this is probably how most people handle it. Another way is to call Php's own `setlocale` function; this does not require any foreknowledge of what the values need to be, but it comes with baggage (data is maintained at process level rather than thread level), so its use is discouraged.
This PR adds a new `setLocale` method to `StringHelper`. It sets the 3 properties and the `Calculation` language all at once. It depends on the `Intl` extension, which is a recommendation but not a formal requirement for PhpSpreadsheet; the method will return `false` if `Intl` is not available or it thinks the supplied locale is not valid.
This PR started because the documentation on how to configure an Http client was out of date. However, as I investigated further, I found problems. GuzzleHttp, a client mentioned in the documentation, for example - it probably works fine when executed from a browser, but it does not work for `https:` requests from the Windows command line. See https://github.com/guzzle/guzzle/issues/2601, where a user notes that Guzzle's own program to demonstrate how to use it doesn't work, a result that I can confirm is still true. A linked explanation says that the solution is to explicitly specify a path to a CA bundle. For starters, this is impractical from our perspective. One possible solution is to change a php.ini option which is not needed for any other purpose, and which probably needs to change frequently - a burden on users who follow that route. An alternative solution is to use a method `request` to specify the path to the certificate store; this also will need to change from time to time, and, worse, the only method defined in ClientInterface is `sendRequest`, so using this solution isn't client-agnostic, which is a stated goal of https://github.com/PHPOffice/PhpSpreadsheet/commit/7cb4884b96174eb611621635e6fb603ef54178f7. Additionally, it is not clear why an external interface is needed rather than a call to file_get_contents, used elsewhere in PhpSpreadsheet, and not requiring a path to a certificate store.
I also believe that automatically evaluating WEBSERVICE for any arbitrary argument is not a good idea. I am adding a domain whitelist which the user must populate. For domains not in the whitelist, the calculation will revert to `oldCalculatedValue`, which is good enough for pass-through purposes, which probably encompasses most cases. That is how Excel behaves by default - it disables WEBSERVICE calls when it opens a spreadsheet which contains them. For cases where the user adds a new WEBSERVICE call, there is a choice of whitelisting the domain, or getting the result in some other way and using `setCalculatedValue` to store it.
Finally, when a WEBSERVICE call *is* evaluated, it will now accept a cell-address argument rather than just a literal string as is now the case.
T.DIST takes 3 parameters. When the 3rd parameter, a boolean, is true, we now have code to support the request. We still do not have code to handle the false case.
Fix#4167. That problem was initially reported as a problem with TINV, however, at its root, it was actually a problem with TDIST (which TINV needs to call). Both of these are deprecated by Microsoft in favor of some newer functions. PhpSpreadsheet incorrectly treated T.INV as an alias for TINV, and did not implement T.INV.2T, which actually is an alias for TINV.
To solve this problem, I adapted the Perl CPAN module `Statistics::Disttributions`, written by Michael Kospach around 2003. Unit test results have been manually checked against Excel; a few had to be changed because they were wrong (see the original issue). I believe that all of TDIST, T.DIST.2T, T.DIST.RT, TINV, T.INV, and T.INV.2T now function correctly, at least most of the time. I have not found code or an algorithm that I can use for T.DIST nor for T.TEST, both of which remain unimplemented.
PhpSpreadsheet treats CODE and UNICODE as equivalent, likewise for CHAR and UNICHAR. They are, in fact, different. CODE and CHAR deal only with single-byte character sets (Windows-1252 or MacRoman), while UNICODE and UNICHAR deal with all of Unicode. This PR separates them. The existing unit test for CODE was, in many cases, applicable to UNICODE (for which there was no separate test). The tests are corrected for CODE, new tests are added, and a separate test for UNICODE is added. CHAR was mostly okay, new tests are added, and a separate test for UNICHAR is added.
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.
oleibman
Adedayo Oyetoke
Robin van der Vliet
h-marumoto
Sebastian Hädrich