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.
A solution, at least in part, for issue #4280. Xlsx Reader is not handling shared formulae correctly. As a result, some cells are treated as if they contain boolean values rather than formulae.
After breaking up Xls Reader (PR #4118), it is a little easier to identify uncovered code. BIFF8 had no tests involving constant arrays. This PR adds some. Most of the work is in the tests, but some source code is modernized to use things like null coercion.
Fix#4248. PhpSpreadsheet has used what appear to be default attributes and tags when they are missing from Fill patterns and colors. However, Excel handles their absence a little differently from what the "default" would require. PhpSpreadsheet is changed to omit the attributes and tags in question when missing. This change is mostly targeted towards Xlsx read and write, but minor changes for Xls and Html write are also included.
This seems like it could be a breaking change, but I don't think it is. One test (DefaultFillTest introduced by PR #2050) must change, but the change is internal - loading and then saving the spreadsheet used in that change will appear the same after this change as it did before. Other differences are very likely to be bug fixes rather than breaks.
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.
* Security Patch
* Throw Exception for EBCDIC Encoding
* Mixed UTF-8 and UTF-16
Further mischief. I don't know if the examples truly are valid Xml, but PhpSpreadsheet is letting them sneak through.
See #4161. The best way to future-proof is to set the escape character to null string, and set testAutoDetect to false before reading. However, depending on the file being read, this may lead to different results than expected. This will be unavoidable because Php itself will change. This PR adds a new static method `affectedByPhp9` to Csv Reader. This can be used to identify in advance whether an input file will be affected by the changes. This will allow users to identify problems in advance, and prepare for how they might be handled.
Fix#2157. Excel 2003 format allows some things which a real Xml parser would reject. In particular, it permits Html entities, and leading whitespace. Change Xml Reader to do likewise.
* Validate Mime Type for Images
* Extend Change to Xlsx Reader
* Attach Drawing to Sheet Only If Valid
* Suppress Some Theoretical Warning Messages
* Minor Tweak
This supersedes PR #607 by @christian-forgacs, who deserves all the credit for reporting the problem and devising the solution. The PR went stale in 2018, and it is just easier to resubmit a clean version rather than clean up the old one. Among the suggestions in the PR was that you should try to create a spreadsheet from scratch to demonstrate the problem rather than supply one. However, my attempts to match the failing spreadsheet do not have a problem when they are read. So, a supplied spreadsheet it is.
Fix#1570. No sample spreadsheet was supplied with that issue, but I am almost certain that this is another example of the same problem. I am removing the stale label from that issue; it will be closed properly when this PR is merged.
Prevent XEE by hiding custom entities by using single quote to
declare a non-UTF-8 encoding.
XML standard, https://www.w3.org/TR/xml/#NT-EncodingDecl, allows single
quote to declare encoding, but we did not support it. Instead, we
incorrectly fell back on the default of UTF-8. That incorrectly kept the
XML as non-UTF-8, and thus prevented our regexp-based custom entity
detection mechanism to work.
Fix#4099. Ods Reader was expecting there to always be `header-style` and `footer-style` tags when `page-layout` tag is present, but these need not exist. It seemed like there might be other exposures along this line in `readPageSettingStyles`; rather than waiting for a problem report to show up for each, the code is updated to use `->item(0)` in place of `[0]` when appropriate, and make use of the nullsafe `?->` operator introduced with Php8.
Fix#804, opened in Dec. 2018, and closed as stale in Feb. 2019, and which I have re-opened to be closed properly by this PR. Better late than never, I suppose. A third party generated an ODS spreadsheet which PhpSpreadsheet could not read. By way of explanation, the xml in the file contained lots of whitespace between tags, which is wonderful for those humans among us who have to analyze it; but PhpSpreadsheet was not prepared for it. It is now.
Fix#4081. Ods Reader was not reading entire contents of comment. On further inspection, Ods Writer also was not handling comments completely correctly. Ods comments are recorded as `text:p` children of `office:annotation` elements. A newline is inserted between successive `text:p` elements. The `text:p` element itself can have as descendants (at least):
- raw text
- `text:span` elements
- `text:line-break` elements, which also causes the insertion of a newline
Ods Writer is changed to use a single `text:p` with multiple span/linebreak elements. Ods Reader is changed to process in their entirety either that form, or multiple `text:p` elements. Styling of the individual elements of the comment is permitted in Ods. That has not been supported till now by PhpSpreadsheet, and this PR will not address that situation - Ods Reader hast little style support, and this would hardly be the most urgent case where it is missing.
Fix#2581 (not obvious - see next paragraph for explanation). This continues the work of PR #2902 (and also PR #3467) to have errors propagated through function calculations rather than treating them as strings. All text functions, and the concatenation operator, are addressed in this PR.
In the original issue, the spreadsheet being loaded uses the result of an unimplemented function as an argument to another function. When `getCalculatedValue` is used on the cell in question, the result is returned as `#VALUE!`. If the cell had just contained a function call to the unimplemented function, getCalculatedValue would have recognized the situation and returned oldCalculatedValue as the result. Not perfect, but good enough most of the time. User would like oldCalculatedValue returned here as well, which seems like a reasonable request.
PhpSpreadsheet always returns `#Not Yet Implemented` as the result for a function which it knows about but which is not yet implemented. That is the key to the `Cell` class being able to substitute oldCalculatedValue in the first place. However, in order to do that for the issue in question, that result has to be propagated to any functions for which the result is an argument. I don't want to add unimplemented to the list of known error codes, but I am willing to add a parameter to `ErrorValue::isError` to indicate whether that value should be considered an error (default is "no").
The first use of that new parameter would be by the text functions. They go through a common Helper routine, so it is pretty easily implemented. And, as it turns out, most of the text functions do not currently propagate errors, e.g. if A1 results in a value error, `=LEFT(A1,2)` will result in `#V` rather than `#VALUE!`. With this PR, they will now be handled correctly.
This change is extracted from PR #2787 by @MarkBaker. That change mostly deals with array functions, and that part will be superseded by PR #3962. However, this part of 2787 is not included in 3962.
Fix#1036 (closed as stale in 2019 and just reopened). Excel spreadsheets can have either of 2 base dates, 1900 or 1904, and the numeric value of any date cells will vary depending on which base date is in use. PhpSpreadsheet has, till now, handled that as a static property of Shared/Date. This does not work well if two spreadsheets with different base dates are open simultaneously. The code is changed to store the base date as a property of the spreadsheet when an Xls/Xlsx spreadsheet is loaded, and use that property when saving an Xls/Xlsx spreadsheet. Any call to `getCalculatedValue` or `getFormattedValue` will temporarily set the Shared/Date value to that of the spreadsheet, and restore it at completion. In order to avoid a BC break, the Xls and Xlsx readers will continue to populate the Shared/Date value as before.
SINGLE function can be used to return first value from a dynamic array result, or to return the value of the cell which matches the current row (VALUE error if not match) for a range. Excel allows you to specify an at-sign unary operator rather than SINGLE function; this PR does not permit that.
Add support for reading CSE array functions for Gnumeric.
Throw an exception if setValueExplicit Formula is invalid (not a string, or doesn't begin with equal sign. This is equivalent to what happens when setValueExplicit Numeric specifies a non-numeric value.
Added a number of tests from PR #2787.
Fix#4063. Fix#1560. Fix#1293. PhpSpreadsheet is not accounting for an empty string in Xlsx sharedStrings.xml.The code which parses it in Reader/Xlsx looks for a `t` or `r` tag descending from `si`, but, in this case, the tag is coded as `<si/>`, with neither t nor r tag descending. An else clause is added to set the string to empty string in this case.
I was surprised that this had not turned up before, and a search through the archives found at least 2 earlier reports from 4 years ago. Those had been marked stale; the stale indicator is removed, and the issues are re-opened, to be closed when this PR is merged.
Fix#4049. Some possible options were not included for read or write. In addition, although it isn't well documented, it appears that 2-color scale always has 2 cvfo entries in Xml in order minimum/maximum, and 3-color scale always has 3 entries in order minimum/midpoint/maximum.
Xls Reader processing Conditionals interferes with the previously established SelectedCells. Make sure that value is restored.
StopIfTrue should always be set for Xls spreadsheet.
Set NoFormatSet to true unless any of Font, Fill, or Borders is specified in Conditional Style.
In my notes for PR #3372, I mentioned that I could not include some Xls tests because of errors in the software at that time. This PR fixes those errors, so I am adding the missing test, and making the equivalent Xlsx test more comprehensive.
Fix#3982. A number of issues submitted about Xlsx read performance have a common theme, namely that row 1,048,576 and a few rows before it are defined in the worksheet Xml with no cells attached to them. These might be the work of a third party product. While these extraneous rows do not cause any problems for the cells that are actually used on the worksheet, they can lead to excessive memory use. This PR provides an option for the application to ignore rows with no cells when loading.
Recent changes to the load logic had already made a significant difference to memory consumption and load time. For the spreadsheet attached to issue 3982, which had caused out-of-memory errors on the user's system, peak memory usage was already reduced to 40-odd MB. With the new option, this is drastically reduced again, to just over 9MB. Specifying the new option is very easy:
```php
$reader->setIgnoreRowsWithNoCells(true);
```
Note that there are cases where you might not want this (non-default) behavior. For example, if you set a row height on a row with no cells, the height would be lost with this option. Unfortunately, the extraneous row definitions in the problematic spreadsheets claim to have a custom height, so I can't just use "no custom row styles" as an additional filter.
While researching another problem, I noticed that font color was not working as expected for Xls Conditional Formats, at least not when a "non-standard" color is used. In such cases, the color might wind up being rendered as black. The reason is as follows. Xls Writer includes a color palette which is dynamically generated from the (non-Conditional) styles used in the workbook. Any colors used in the workbook are indexes to this dynamic palette. However, Conditional colors use a static palette found in class ColorMap to determine the index, so the determination of index will often not find a match, and, if a match is found, it is not necessarily correct. (Also, the ColorMap method was case-sensitive and needs to be insensitive.)
In order to correct this, the `addColor` method in Xls Writer Workbook needs to be accessible to the Conditional logic which is found in Xls Writer Worksheet. This is accomplished by passing the Workbook in the Worksheet's constructor, and changing the method to public, and changing Conditional Font to use this method rather than ColorMap.
The logic for Conditional Fill colors is similarly changed. Although Xls Conditional Fill has appeared to just not work, I was finally able to figure out the problem. Excel Xls Conditional Fill with fill type Solid requires that the fill color be specified as startColor, and that endColor be omitted. Our conditional samples used endColor, and are now changed to use startColor instead; the same is true for our online documentation, and for some tests. Xlsx continues to work as expected, and now Xls does at least some of the time. If the condition is one that Excel Xls does not recognize (e.g. cell contains), it will, of course, not work. A surprising situation that also doesn't work is the use of ISODD or ISEVEN in formulas. Those are "add-in functions" which are handled differently than other functions, and I'm not sure how to support them. I will document this in issue #3403.
Samples 08_Conditional_Formatting(_2) had produced corrupt Xls versions. This turned out to be because the code was using hash codes to avoid having to write out duplicate conditionals; this is often a good idea, but not in this case. Allowing the duplicates fixes the corruption problem.
Conditional Border colors also ought to figure in this change, but the current code does not support Border colors, and I have not yet been able to figure out how to implement it (BIFF format can be very messy to figure out).
With this change, I could delete ColorMap altogether. However, it is a public class with a static public method, so maybe someone is using it for a purpose I'm not familiar with. I will just deprecate it.
Fix#3995. Fix#866. Fix#1681. Php DOM loadhtml defaults to character set ISO-8859-1, but our data is UTF-8. So Html Reader alters its html so that loadhtml will not misinterpret characters outside the ASCII range. This works for UTF-8, but breaks other charsets. However, loadhtml uses the correct non-default charset when charset is specified in a meta tag, or when the html starts with a BOM. So, it is sufficient for us to alter the non-ASCII characters only when (a) the data does not start with a BOM, and (b) there is no charset tag.
This will allow us to use:
- UTF-8 files or snippets without BOM, with or without charset
- UTF-8 files with BOM (charset should not be specified and will be ignored if it is)
- UTF-16 files with BOM (charset should not be specified and will be ignored if it is)
- all charsets which are ASCII-compatible for 0x00-0x7f when the charset is declared. This applies to ASCII itself, many Windows and Mac charsets, all of ISO-8859, and most CJK and other-language-specific charsets.
We cannot use:
- UTF-16BE or UTF-16LE declared in a meta tag
- UTF-32, with or without a BOM (browser recommendation is to not support UTF-32, and most browsers do not support it)
- unknown (to loadhtml) or non-ASCII-compatible charsets (EBCDIC?)
I will note that the way I detect the `charset` attribute is imperfect (e.g. might find it in text rather than a meta tag). I think we'd need to write a browser to get it perfect. Anyhow, it is about the same as XmlScanner's attempt to find the `encoding` attribute, and, if it's good enough there, it ought to be good enough here.
Thinking about #3958 - user wondered if unsupported formulas with array results could be handled better. I said that the answer was "no", but I think Xlsx Reader can make use of the dimensions of the result after all, so the answer is actually "sometimes". This is an initial attempt to do that. Implementing it revealed a bug in how Xlsx Reader handles array formula attributes, and that is now corrected. Likewise, Xlsx Writer did not indicate a value for the first cell in the array, and does now.
oleibman
Adrien Crivelli