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`.
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#4823. When writing a spreadsheet to Html, a data Uri can be used if `embedImages` is true. Reading such an Html spreadsheet and attempting to write it to Xlsx results in an Exception. It should be noted that Excel itself cannot open the Html properly; none of the images are present.
The PhpSpreadsheet problem arises not with the inclusion of the image, but rather with attempting to include the appropriate entry in `[ContentTypes].xml`. This PR corrects that problem.
For the record, Xls Writer does not have a problem with this situation. Just to demonstrate that, a parallel test for Xls Writer is added in addition to the new Xlsx Writer test.
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.
When using a read filter with the ODS reader, cells with
table:number-columns-repeated containing data were incorrectly handled.
The read filter was only checked for the first column of a repeated
group - if it didn't pass, the entire group was skipped, even if
other columns within the range should have passed the filter.
This caused column data misalignment where filtered columns would
either be missing or contain data from wrong positions.
The fix ensures that:
1. When the first column of a repeated group fails the filter, the
reader checks if ANY column in the group passes before skipping
2. During data writing, each repeated column is individually checked
against the read filter
FixesPHPOffice/PhpSpreadsheet#4802
Loading external images from a spreadsheet can subject the caller to security exploits. For those who think they need something between not loading any external images (the default and our strong recommendation) or loading them all unconditionally (the current alternative), a callback can now be provided to indicate whether a specific image is okay to load. We continue to urge caution in using this option.
Fix#1319, which went stale in 2020, and is now reopened. Rowspan is not showing up when saving some spreadsheets as Html. There is some logic concerning property `isSpannedRow` which indicates that this is deliberate, but I have no idea what purpose it serves. It leads directly to this issue, and also adversely affects (in a minor way) some samples (e.g. 17a_Html), where merged cells maintain their text but lose their top and bottom padding. After discarding this logic, the reported problem goes away, the samples look better, and no tests fail. So, whatever its purpose, it wasn't tested for. This PR permanently removes the property, and adds a little additional bulletproofing. A minor accommodation is needed for Tcpdf in one place.
This PR probably deletes more lines from Html Writer than it adds. It would not surprise me if this is one of those unusual cases where Coveralls reports a decrease in coverage despite the fact that all the new code is covered.
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.
Fix#3255, another oldie (3+ years). Allow the ability to only count rows and columns with data. To avoid a breaking change, keep present logic unless Reader `readEmptyCells` attribute is set to its non-default value, i.e. `false`. This adds very little overhead, and gives a result that I think many users would prefer.
See [Discussion 4724](https://github.com/PHPOffice/PhpSpreadsheet/discussions/4724)
PhpSpreadsheet converts all control characters (x00-x1f) in strings to and from a form which Excel recognizes (e.g. `x1c` becomes `_x001C_` when writing, and vice versa when reading). There have historically been 3 exceptions which go unconverted - tab (x09), line feed (new line) (x0a), and carriage return (x0d). PR #4536 removed those exceptions, but that caused some problems; these were fixed by PR #4619, but the exceptions were restored.
The referenced discussion deals with a spreadsheet with a cell containing `_x000D_`, carriage return. Although the writer no longer converts to that string on output, the reader should be able to handle it on input. In fact, the reader ought to handle any string of the form "underscore x 4-hex-digits underscore", whether or not it represents a control character.
And there's an interesting edge case. If a user enters into a cell the string `A_x0030_B`, it needs to be handled as-is. Excel handles this by writing it out as `A_x005F_x0030_B`, i.e. substituting `_x005F_` for the first underscore, so that the reader sees `_x005F_` (converting it to underscore) followed by `x0030_B` (no leading underscore, so no conversion). PhpSpreadsheet could probably handle this by converting all underscores on write, but I am trying to emulate Excel and do it only when needed.
PR #4687 corrected how Xls Writer generated its Dimensions records. We ignore the Dimensions record on read since it does not affect our processing in the slightest. However, the PR raises the possibility that someone might wish to see the data in the Dimensions record. (The PR did an adequate test for retrieving Dimensions data, but it is not generalizable.) To accommodate such a case, we add a new ListWorksheetDimensions function to Xls Reader, similar to ListWorksheetInfo. As luck would have it, the spreadsheet with which I tested the new function produced incorrect results for ListWorksheetInfo, which was ignoring XLS_TYPE_MULRK records. So I added the necessary code to fix ListWorksheetInfo as well.
Fix#850 (marked stale many years ago, but now reopened). User had a typo in their script which would have caused problems no matter what. However, it exposed another problem. Style Alignment Read Order was supported only by the Xlsx Reader and Writer, but it could have been supported pretty easily for most other formats. This PR adds support for the following:
- Xls (read and write)
- Html (write, and read using inline styles). Html reader does not yet process most classes.
- Pdf (write). PhpSpreadsheet does not have a Pdf reader.
- Ods (write). PhpSpreadsheet does not yet support reading most Ods styles.
- Xml (read). PhpSpreadsheet does not have an Xml writer.
- Gnumeric (no change). It appears that the Gnumeric product does not support this attribute.
- Csv (no change). Csv does not support any styles.
- Slk (no change). Slk does not support non-Latin characters, so this attribute doesn't make sense for it.
Fix#4629. Excel Xml can specify Conditional Formatting in 2 ways - either using a `conditionalFormatting` tag as a child of `worksheet`, or in a slightly different manner as a child of `worksheet->extLst`. Although these are both handled by PhpSpreadsheet, the former is lost if there exists a latter whose cell range (`sqref`) matches it. This PR merges the former with the latter in that circumstance, preserving both.
The issue also notes that `font` and `stopIfTrue` are not handled correctly. Support for those was missing in the `extlst` handling, and is now added with this PR.
Fix#1637, which went stale but is now re-opened. When Google Sheets exports a document to Xlsx or Ods, it replaces Google-only formulas with something that Excel or LibreOffice can handle. In the test case accompanying this PR, cell C1 on Google Sheets contains `=flatten(A1:A5,B1:B5)`. On export, C1:C10 (the actual result is a 10*1 array) are changed to `=IFERROR(__xludf.DUMMYFUNCTION("flatten(A1:A5, B1:B5)"),1.0)`, where `1.0` is replaced by the calculated value for each cell in question.
The issue reports an Internal Error when evaluating such a formula. I am unable to duplicate that. However, PhpSpreadsheet evaluates the cell as a `#NAME?` error rather than the correct value (1.0 for cell C1). The reason is that `__xludf.DUMMYFUNCTION` does not match the regexp for formulas, but does match the regexp for defined names. Not finding such a defined name results in the Name error. Altering the formula regexp to recognize `__xludf.` is easy, and solves the problem.
oleibman
QDenka
Indrek Haav
Thomas Lallement
Thomas Lallement
karrakoliko
Emeka Oziri
issa