Fix#3730. File workbook.xml.rels in test file specified absolute addressing for shared strings, styles, and worksheets. Previous changes had addressed absolute addressing for worksheets, but shared strings and styles had not yet been addressed.
Fix#3909. SUMPRODUCT is mishandling multi-row ranges. In Calculation/Calculation, `checkMatrixOperands` will often resize its operands. When it does so, it needs to recalculate the dimensions of each. This fixes the reported problem.
Likely cause was PR #3260. That ticket noted the poor coverage of the code being replaced. Tests of the problem in this ticket were absent and are now added. Despite this, I note that `resizeMatricesShrink` is virtually uncovered, and `resizeMatricesExpand` has substantial gaps in its coverage. I have covered some, but not all, of the Expand gaps. I am struggling to come up with examples to fill its remaining gaps and those for Shrink. However, I will merge this fix in about a week even if I don't succeed.
Fix#3863. Data Validation default operator is `between`. When Excel writes out a data validation item, it may omit the operator. Xlsx reader will therefore initialize operator to null string. Issue indicates that user wants `between` returned for `getOperator`. A more serious problem is that `isValid` method does not handle this situation correctly. Data Validation is changed to set Operator to the default value if an attempt is made to set it to null string.
Fix#3687. Worksheet methods insertNewRowBefore and insertNewColumnBefore call ReferenceHelper insertNewBefore. That function fills in "missing" cells with null values. However, for boundaries, it uses getHighestRow and getHighestColumn. It should be sufficient to use getHighestDataRow and getHighestDataColumn. When there is a big gap between getHighest... and getHighestData..., this can result in a big increase in memory usage, and in file space when saving the spreadsheet. New test InsertTest demonstrates the problem by populating a worksheet with cells A1:D5 (so highestDataRow is 5), but also setting row 1000 to invisible (so highestRow is 1000).
The major part of the change is in ReferenceHelper::insertNewBefore, which will now use getHighestData... for its boundaries when filling in the missing cells. Changes of less impact are made to duplicateStylesByColumn and duplicateStylesByRow so that cells which don't yet exist are not created unless the style that will be applied is not the workbook default style.
As for reducing the file size, Writer/Xlsx/Worksheet is changed so that cells whose value is null or null-string and which use the workbook default style are not written to the output spreadsheet. This requires some changes to existing test ReadBlankCellsTest; I don't think the difference should matter to the end-user.
Fix#3833. Among the options for chart axes in Excel are the ability to show labels as multiples of specific powers of 10, with or without an explanatory label. The Excel spec seems to indicate that that you can customize the multiples to other values, but I don't see how to do that in Excel, so that can be a project for another day if someone figures out how. The label title can also be styled; that is not part of this PR, but I will look into it in future. For now, this PR supports the use of multiples with or without labels.
The axes can also be displayed on a logarithmic scale (any base between 2 and 1000). This PR supports that.
Fix#3807. Function attempts to rationalize `..` in filenames in a way that normally works just fine. Reporter notes that at least one of the filenames that will be analyzed when a spreadsheet is read can be maliciously altered in a manner which does not harm Excel when reading the file, but which puts PhpSpreadsheet into a loop. This PR fixes the problem.
* Xlsx Support Flipping of Image
Fix#731. Opened over 5 years ago, probably the second oldest problem I've worked on. Images attached to an Xlsx spreadsheet can be rotated, which is supported by PhpSpreadsheet. They can also be flipped along their horizontal and/or vertical axes, and that has not been supported. This PR adds that support.
* Update CHANGELOG.md
* Address Some Chart Problems
Fix#3767. The basic problem in that issue was user error, but it exposed a couple of problems in code which are being addressed.
When a spreadsheet with charts is loaded without the includeCharts option and then saved, a corrupt spreadsheet is created. I believe this has been the case for quite some time. Nothing in the test suite covers this scenario. It is, in fact, a difficult thing to test, since the problem is exposed only when the file is opened through Excel. The specific problem is that a rels file generated by the output writer continues to refer to the drawing file which described the chart, but that file is not included (and not needed) in the output spreadsheet. The resolution is kludgey. The information that the file will not be needed is not available when the rels file is written. But, when it comes time to write the drawing file, it is known whether the rels file included it. So, if nothing else has caused the file to be generated, it is written out as a basically empty xml file after all. This solves the problem, but I will continue to search for a less kludgey solution. This solution is, at least, testable; if a different solution is applied later on, the test being introduced here is likely to break so a new one will be needed.
When the provided spreadsheet is loaded with the includeCharts option and then saved, an error is exposed in processing the Chart Title caption when it doesn't exist. The change to Writer/Xlsx/Chart is simple and clearly justifiable. What is peculiar is that the error does not arise with release 1.29, but does arise with master. It is not at all clear to me what has changed since the release to expose the error - the code in question certainly hasn't changed. It is difficult to isolate changes because of the extensive number of changes following on the elimination of Php7.4 as a supported platform.
The provided spreadsheet is unusual in at least two senses. When opened in Excel, it will show a clearly default value for the chart title, namely 'Chart Title'. I cannot find anything in the xml corresponding to that text. Since I have no idea why Excel is using that title, I will not try to duplicate its behavior, so that loading and saving the provided spreadsheet will omit the chart title. I will continue to investigate.
The other sense in which it is unusual is that it includes some style files in the same directory as the chart. I doubt that PhpSpreadsheet looks at these. The styling after load and save seems to mostly match the original, although there is at least one color in the graph which does not match. I imagine it would be pretty complicated to formally support these files.
* Unused Assignment
* Theme File Missing But Referenced in Spreadsheet
Fix#3770. A rels file points to a non-existent theme file in the spreadsheet. In other similar cases (e.g. PR #3771), Excel opens such a spreadsheet, but with an error pop-up. Not so with this file; it just opens the spreadsheet without the pop-up. PhpSpreadsheet will now account for this unusual situation as well.
* Update CHANGELOG.md
* Added Conditional Formatting: ColorScale for Xlsx
* Add Reader Support, Tests, Sample
Also correct Phpstan and phpcs problems.
* Update cond08_colorscale.php
* Improve Coverage
* More Coverage Improvements
* Use StyleReader for Colors for ColorScale and DataBar
The implementation of DataBar looks for an rgb attribute, but the color may be provided via theme attribute instead. The initial implementation for ColorScale did the same. Change both to use the existing code in Reader\Xlsx\Styles to parse the color.
* Change Some Doc Blocks to Type Declarations
---------
Co-authored-by: oleibman <10341515+oleibman@users.noreply.github.com>
* fix: Load tables when flag IReader::READ_DATA_ONLY is used
* Add test for table read when IReader:::READ_DATA_ONLY is used
---------
Co-authored-by: Kevin Verschaeve <kevin.verschaeve@exotec.com>
* Unexpected Namespacing in rels File
Fix#3720. Third-party product created a spreadsheet which PhpSpreadsheet could not read because of unexpected namespacing in workbook.xml.rels.
The file which demonstrated the problem was attached to #3423, however I do not believe it was related to the original problem. Nevertheless, the original issue specifically called out Protection, so I put some Protection tests in the validation test for the fix. In doing so, I found that Style/Protection is particularly confusing. Its properties will often have the value `inherit`, which isn't all that helpful; and, even when the `locked` value is `protected`, the cell won't actually be locked unless the sheet is protected as well. The `hidden` property is even more obscure - it applies only to formulas, and refers to hiding the property on the formula bar, not in the cell. I have added methods `isLocked` and `isHiddenOnFormulaBar` to `Cell`. I corrected the docs to explain this. And, as long as I was looking at the docs, I corrected some examples to use `getHighestDataRow/Column` rather than `getHighestRow/Column`, a frequent problem for users (e.g. #3721).
As a side note, the change to Cell.php is my first use of the nullsafe operator. This is one of many new options available now that we require Php8.0+.
* Minor Simplifications
* Scrutinizer
It's being silly again. In many tests, we test a variable for non-null, then use that variable later and Scrutinizer knows it's not null. Not here. Oh well.
* Add Methods
Test if protected without allocating cell if it doesn't exist.
* ListWorksheetInfo/Names for Html/Csv/Slk
Fix#3706. ListWorksheetInfo is implemented for all Readers except Html. For most (not all), ListWorksheetInfo is more efficient than reading the spreadsheet. I can't think of a way to make that so for Html, but that shouldn't be a reason to leave it unimplemented.
ListWorksheetNames is not implemented for Html, Csv, or Slk. It isn't terribly useful for those formats, but that isn't a reason to omit it. The requester's use case consists of using IOFactory to create a reader for a file of unknown format and determining the first sheet name. That seems legitimate, but it is currently not possible without extra user code if the file is Html, Csv, or Slk; this PR will make it possible.
When Excel opens a Slk or Csv file, the sheet name is based on the file name. PhpSpreadsheet does this for Slk, but it uses a default name for Csv. I am not interested in creating a break for that behavior, but I have added a new boolean property `sheetNameIsFileName` with a setter to Csv Reader. The requester actually mentioned that possibility in our discussion, although it is not essential to the request.
As an adjunct to the issue, the requester wishes to use the worksheet name in `setLoadSheetsOnly`. That is already possible for Html, Csv, and Slk, but that particular property is ignored for those formats. I do not see a reason to change that behavior. This treatment is now explicitly noted in the documentation for property `loadSheetsOnly`.
There had been no tests for what happens when `loadSheetsOnly` is specified but no sheets match the criteria for the formats for which this makes sense (Xlsx, Xls, Ods, Gnumeric, Xml). The behavior was not consistent - some formats threw an Exception while others continued with a single empty worksheet. All cases attempt to set the active sheet, and they will now all throw identical Exceptions when they attempt to do so in this situation. Tests are added for each.
There also had been no tests for `loadSheetsOnly` returning more than one sheet. One is added.
* Update LoadSheetsOnlyTest.php
Add strict types to this new test, consistent with work being done in PR #3718.
* Update LoadSheetsOnlyTest.php
Add strict types to this new test, consistent with work being done in PR #3718.
* Use Column Style When Row Dimension Exists Without Style
Fix#3534. For new cells, style is set to Row Dimension Style when Row Dimension exists (even if Row Dimension Style does not exist), else Column Dimension Style when Column Dimension exists. However it should be set to Row Dimension Style when Row Dimension exists *and* Row Dimension Style exists, else ...
* Address Issue 3533
If cell is defined in xml, it uses neither row nor column style. If style is omitted in cell definition, default style is used.
* Update CHANGELOG.md
* Fix Several Problems in a Very Complicated Spreadsheet
Fix#3679. That issue was opened for a problem which was already solved by PR #3659, however there were additional problems with the spreadsheet.
The main problem is that Data Validations and Conditional Styles can each be supplied in the Xml in either "external" or "internal" formats. The code for each to handle "external" assumes that each is the only "external" item on the worksheet in the Xml, but some of the worksheets in the sample spreadsheet provide both as "external" on some sheets. The code to fix this is verified against the supplied sample, however no formal test has been added for it. The sample is much too large and complicated to be added to the test suite - it takes several minutes to read, and even longer to write (`setPreCalculateFormulas(false)` is highly recommended). I will leave this ticket open for a few days to see if I can hand-craft a suitable test case, but I am not hopeful.
A second problem is that something in the Xlsx Reader `$xmlSheetNS->sheetData->row` loop breaks the selected cell for the worksheet. This is easily fixed and verified by eye (and with the supplied sample), but, again, no explicit test case is added.
A third problem is that drawings which are part of the supplied sample use `srcRect` tags in the Xml to effectively produce a cropped version of the image. This tag has hitherto been ignored. It is now supported in Xlsx Reader, Xlsx Writer, and Worksheet/BaseDrawing object. This is again verified with the supplied sample; unlike the other parts, it was easy to add a new formal test case for this part of the fix.
* Scrutinizer False Positives
Fix#3658. Readers will call `setCalculatedValue` to save the original calculated value for a cell that contains a formula; this affects the result of `getOldCalculatedValue`. The set routine automatically casts numeric values to float, which is not appropriate when the calculated value is explicitly set to a string type. An optional parameter is added to the set routine to indicate whether a cast is appropriate (default is true so userland uses of this method will remain unaffected). The readers for Xlsx, Xls, Xml, Ods, and Slk are all changed to set the new parameter appropriately. Gnumeric saves the formula, but does not appear to save the result, so no change is needed there. Html and Csv save the result but not the formula (for preCalculateFormulas true - they save the formula but not the result for false), so no change is needed for them.
It is not clear what the use case is for oldCalculatedValue, but it does date back to PhpExcel. A possible use case is demonstrated in the new tests - Excel function INFO is not implemented, so getCalculatedValue will generally return null on such a cell, but will return oldCalculatedValue when it sees that the function is not implemented but oldCalculatedValue has been set (typically by loading an existing spreadsheet), so the result that is on the spreadsheet will be available to the program which loaded it.
Reading a spreadsheet which has been created with preCalculateFormulas false will, or at least should, result in a null oldCalculatedValue. This was true for Xls, Xml, and Slk. Xlsx was actually storing 0 in this situation; that wasn't precisely a problem, but it wasn't necessary and seems misleading. Xlsx Writer is changed to no longer set a value in this situation. This is technically a break, but the existing code was wrong; one unit test needed a very minor modification as a result of this change.
* Fix Xlsx Read Ignoring Comments
Fix#3654. Several places in Reader Xlsx make a truthy test for `$zip->locateName()`. However, zero is a legitimate result which should not be treated as false. Change all existing tests in this form to test for (in)equality to false. For the record, there is no existing exposure of this kind in Reader Ods.
* Absolute Path In Printer Settings File
Another unexpected use of an absolute path in a rels file. Easily fixed, but the test file is 7+ MB. I have asked the problem reporter to try to find a smaller file.
* Reduced Size of Test File
It was 7.8MB. I manipulated the file to replace all the comment backgrounds in column AN with a single image, much smaller than the existing ones. This reduced the file size by over 6MB. It's still larger than I'd like, but might be acceptable. (AN121 uses a different background than the others just so that I could check that they would all be copied faithfully.)
* Xlsx Reader Namespacing for Tables, AutoFilters
Fix#3665. The original issue was the use of an absolute path in the rels file pointing to the comments file. That was easy to take care of, but a bigger problem with the spreadsheet accompanying the problem report was that it used unexpected spacing for AutoFilters and Tables. AutoFilters were already known not to be covered, but Tables appeared after the namespacing changes, but without namespacing support. This PR fixes the absolute path problem and adds namespacing support for Tables and AutoFilters.
Remaining areas which are still namespace unaware, mainly because of the absence of test samples which use them with unexpected namespacing, include conditional formatting (internal or external), sheet view options, sheet protection, unparsed loaded data, data validation (internal or external), alternate content, and header/footer images.
* Mysterious Warning for Php7.4 Only
Node no longer exists, doesn't affect result. Suppress warning.
* Scrutinizer
What would a change be without some new false positives?
* Scrutinizer
A gift that keeps on giving :-( Now it's deciding that things that it didn't report in a scan from a few minutes ago are worth reporting, even with no relevant code changes.
* Scrutinizer
It is an idiot. Let me see if I can fix one false positive and succeed in guessing where it might report another one, even though it didn't do so with this last run.
* Split Screens
Fix#3601. Split screens are a feature that affects the display of the spreadsheet to the end user; they do not affect the data. They are conceptually similar to "freeze panes". The differences are explained in the issue. As will be explained, support is fairly full for Xlsx, and less full for Xml; no attempt is yet made to support Xls or Ods.
For freeze or split, the window can be divided into 2 horizontal panes, or 2 vertical panes, or 4 horizontal+vertical panes. In Excel, you can split or freeze on cell A1, which causes 4 panes centered at the middle of the screen. PhpSpreadsheet will not duplicate that functionality, and code is added to ignore an attempt to freeze at A1. This breaks one existing nonsensical test, which is changed to something sensible for this PR.
In the spreadsheet xml, both 'freeze' and 'split' use attributes 'xSplit' and 'ySplit' to indicate the position. Unfortunately, the attributes have different meanings for 'freeze' (*columns* from top/left) than for 'split' (*distance* from top/left). For that reason, it is difficult to change between 'freeze' and 'split', so PhpSpreadsheet will not yet support doing so.
There are 3 possible states when freeze/split is used - 'frozen', 'split', or 'frozenSplit'. All will be maintained during read/write, and the user can set them. In Excel, you get 'frozenSplit' by first splitting, then by changing to freezing. In that case, when you unfreeze, you revert to split. PhpSpreadsheet will not duplicate that functionality - when you unfreeze, the panes will go away regardless of whether you were 'frozen' or 'frozenSplit'.
Changing the selected cell will cause the 'active pane' to update when the panes are frozen. PhpSpreadsheet will not yet update the active pane when the panes are split. The programmer will, of course, have the opportunity to explicitly change the active pane in this circumstance.
I have not yet been able to figure out how Xml spreadsheets map their panes, or how to determine selected cell for the sheet as a whole or for individual panes when freeze/split is in effect. The programmer will have the opportunity to specify these explicitly. However, loading an Xml spreadsheet in PhpSpreadsheet will not fill in these values.
Each pane has its own selected cells, and you can navigate between these with the F6 key in Excel (this may work only with split but not with freeze, but Excel maintains the values for freeze and so will PhpSpreadsheet). For Xlsx, PhpSpreadsheet loads and saves these values.
* Eliminate Some Dead Code
Pointed out by Scrutinizer.
The overall problem is described in issue #3613. This PR represents only a partial solution. Very complicated formulas are resulting in calls to string functions using null arguments, which is deprecated in recent Php releases. In fact, there are many such deprecations for the spreadsheet in question. Eliminating the deprecations is easy. However, the result of the calculation is, for many cells, 0 rather than what Excel determines it should be. This can be overlooked to a certain extent, because Excel will recalculate when the spreadsheet is opened, so loading and saving the spreadsheet in question will result in a spreadsheet which looks okay when it is opened in Excel. Resolving the incorrect calculation in PhpSpreadsheet would be nice, so I'm leaving the issue open, but that looks too complicated for me to get a toehold.
* Redo Calculation of Color Tinting
Fix#3550. Some colors are specified in Excel by specifying a theme color to which a tint is applied. The original PHPExcel algorithm for doing this was developed by trial and error, and is good enough a lot of the time. However, for the issue at hand, the resulting color is detectably different from the calculation that Excel makes. Searching the web, I found https://gist.github.com/Mike-Honey/b36e651e9a7f1d2e1d60ce1c63b9b633 which comes much closer for the case in hand, and for all the other cases that I've looked at. That code depends on Python colorsys package; I have adapted the code from the Python gist and package into a new Php class. This doesn't agree perfectly with Excel. However, if each of the red, green, and blue components (each a value between 0 and 255 inclusive) agree within plus or minus 3 (arbitrary choice) of Excel's result, I think that is good enough.
I have added a new test member which reads from a spreadsheet with Xml altered by hand to set up several theme/tint cells. These tests use the plus-or-minus-3 criterion. They result in 100% code coverage of the new class.
Unsuprisingly, some existing tests failed with the new code. Issue2387Test reads a theme/tint font color, and is changed to use the plus-or-minus-3 criterion, comparing against the color as Excel shows it.
ColorChangeBrightness showed 9 failures with the new code. It consists of calculations not involving a spreadsheet. For that reason, I felt it was sufficient to just do an exact match test, changing the 9 old results for new results confirmed with the Python code. I also added one new test case, the one that kicked off this entire PR.
* Scrutinizer Being Stupid
It strikes again.
Fix#3552. Inserting rows or columns can cause page break arrays to be out of order. Sort them in Worksheet functions getBreaks, getRowBreaks, and getColumnBreaks before returning to caller.
* Not Finding Rels File
Fix#3553. Read of `\xl\worksheets\_rels\sheet1.xml.rels` fails on the new test worksheet (generated by 3rd party product), but read of `xl\worksheets\_rels\sheet1.xml.rels` (same name without leading slash) succeeds. I'm not sure why (over 400 tests succeed with the slash), so will probably delay this a bit while I do more research.
* Move Fix
Move to more logical place.
* Add Ability to Ignore Cell Errors in Excel
Fix#1141, which had been closed as stale, but which I have reopened. Excel will show cells with certain "errors" with a green triangle in the upper left. The suggestion in the issue to use quotePrefix to suppress the numberStoredAsText error is ineffective. In Excel, the user can turn this indicator off for individual cells. Cells where this is turned off can be detected at read time, and PhpSpreadsheet will now process those. In addition, the user can explicitly set the ignored error as in Excel.
```php
$cell->setIgnoredErrorNumberStoredAsText(true);
```
There are a number of different errors that can be ignored in this fashion. This PR implements `numberStoredAsText` (which is likely to be by far the most useful one), `formula`, `twoDigitTextYear`, and `evalError`, all of which are demonstrated in the new test spreadsheet. There are several others for which I am not able to create good examples; I have not implemented those, but they can be easily added if needed (`calculatedColumn`, `emptyCellReference`, `formulaRange`, `listDataValidation`, and `unlockedFormula`).
* Scrutinizer
A new change, a new Scrutinizer false positive.
* Move Ignored Errors to Own Class
In response to comments from @MarkBaker, implement ignoredError as a new class. This simplifies Cell by requiring only 1 new method, rather than 8+. This requires a slightly more complicated syntax.
```php
$cell->getIgnoredErrors()->setNumberScoredAsText(true);
```
Mark had also suggested that there might be a pre-existing regexp for processing the cells/cellranges when reading the sqref attribute. Those in Calculation are too complicated (read "non-performant") for this piece of code; the one in Coordinates is slightly less complicated than Calculation, but still more complicated than the one I'm using, and doesn't handle ranges.
Fix#3495. This seems to be a bug in Excel, one which it manages to cover up but PhpSpreadsheet is affected. User enters a formula preceded by an apostrophe into a cell. Excel turns on `quotePrefix` style and stores the data as a string rather than a formula. User now enters a formula not preceded by an apostrophe into the same cell. Excel stores it is a formula but does not turn `quotePrefix` off. When the spreadsheet is saved, the cell's style specifies `quotePrefix`, but the cell's content indicates it's a formula. Till now, PhpSpreadsheet sees that quotePrefix is set, and therefore treats the cell's contents as a string rather than a formula. This PR will change that behavior so that quotePrefix is automatically turned off when Xlsx Reader sees that the cell indicates that it is a formula.
Fix#3453. User sets a valid formula (e.g. `=SUM(Sheet2!B1:Sheet2!B3)`), and then does something to invalidate the formula (e.g. delete Sheet2). Excel changes the formula to `SUM(#REF!:#REF!)` when the spreadsheet is saved; apparently someone thought this was a good idea. But PhpSpreadsheet (a) used to throw an Exception when it evaluated the formula, and (b) now gives a result of `0` when evaluating the formula. Neither is ideal. It would be better to propagate the `#REF!` error.
It is likely that more tests are needed, which is why I will keep this in draft status for a bit.
* Correct Xlsx Parsing of quotePrefix="0"
Fix#3435. Mis-parsed attribute is not normally generated by Excel or PhpSpreadsheet, but some 3rd-party software (correctly) generates it.
* Update Issue3435Test.php
* WIP Conditional Formatting Improvements for Xlsx
Fix#3370. Conditional styles are always generated with 5 borders (right, left, top, bottom, diagonal) even though the border style is none in each case. For the spreadsheet in question, top and bottom were inappropriate and interfered with the desired formatting. A new border style, BORDER_OMIT is added which will cause the Xlsx Writer to not generate that style. All conditional borders will be initialized with that value. Any border included in the Xml will, of course, change it to the specified type.
Fix#3202. User wants a condition to use "No format set" as you can in Excel. A new boolean property `$noFormatSet`, along with setter and getter, is added to Style/Conditional. It is initialized to false. User can call setter to change it. More importantly for the issue in question, if the Xlsx Reader encounters a `cfRule` tag which does not have a `dxfId` attribute (i.e. no style is associated with the rule), it will set noFormatSet to true. Similarly, the Xlsx writer will not generate a `dfxId` tag when noFormatSet is true.
This change is applicable only to Xlsx. Html, Csv, and Ods do not have support for Conditional Formatting. Limited support was added to Xls with PR #2696 in April 2022 and PR #2702 about a month later. However, with the current release code, Xls equivalents of the two new test spreadsheets in this PR are too complicated to be handled correctly by PhpSpreadsheet - loading and then saving them as Xls results in Excel complaining of corruption, and the results don't meet expectations. Since I have no idea how BIFF works, and since the problems with those spreadsheets are not caused by this PR, I am not planning to address those problems at this time.
* Update Documentation, Write Alignment and Font Less Often
It doesn't cause any particular harm except for small increases in file size and run time, but Alignment tags are written even when (a) all its attributes are null for Conditional Formatting, and (b) when the xml specifically indicates that Alignment should not be applied. Similarly, Font is written even when all its attributes are null for Conditional Formatting.
There are some errors in the Conditional Formatting documentation. Specifying a solid fill color in a Conditional Style requires the use of endColor, not StartColor. The discussion of Order of Evaluating is not entirely accurate. I have changed it to what I believe is an accurate explanation of how Excel works; and also added a mention that other spreadsheet programs might not work the same way, adding a couple of illustrations of the difference. The description of the multiple conditions did not quite match the diagram. 'Stop if true' was a blank paragraph; it is now described, and the new 'No format set' option is described in that paragraph since (I think) it would be used most often in conjunction with 'Stop if true'.
* Xlsx Writer Allow StartColor for Conditional Solid Fill
To set a solid fill in a non-conditional style, you set StartColor (xml will use that value as fgColor and a default value as bgColor). If you instead set EndColor (xml will use that value as bgColor and a default value as fgColor), the styling will not work as expected.
However, for conditional styles, if you set StartColor (xml will use that value as fgColor and not specify bgColor), the styling will not work as expected. If you instead set EndColor (xml will use that value as bgColor and not specify fgColor), the styling will work as expected.
Together, this means that you need to use different methods for non-conditional style fill than for conditional style fill. This isn't a big problem, but it is a bit weird. This PR changes Xlsx Writer so that if (a) fill is olid and (b) startColor is specified and (c) endColor is null, the xml will be written as bgColor without specifying fgColor. This means that you can set StartColor for both conditional and non-conditional and get the expected styling. You may, of course, continue to specify EndColor instead for conditional.
* Fix Some (Not Many) Xls Problems
I will open an issue for the (pre-existing) remainder.
* Permit Max Column for Row Breaks
Fix#3143. Page break was dropped. Difference between good and bad was the use of attribute `max` in `brk` tag in the good spreadsheet. However, `max` was *not* required in a similar spreadsheet. So the reason for the problem isn't completely explained. Nevertheless, it can't really hurt to capture the `max` value on read (if present) and generate it on write. This resolves the issue. User is also permitted to specify max column when setting a row break programatically. I am not yet in position to document when that might be a good idea.
* Case-sensitive Directory Name
Not a problem on my Windows system.
* Update Documentation and Add Tests
Change is necessitated by probable Excel bug.
* Unhappy With Initial Implementation
I kind of shoe-horned it in. Better to create a new PageBreak class, which will make it easier to accomodate any future surprises about page break handling. The only difficulty with the new approach is making sure getBreaks maintains backwards compatibility. New tests will ensure that.
* Namespace-Aware Code for SheetViewOptions, SheetProtection (minor break)
This is fairly straightforward for SheetViewOptions.
SheetProtection is a bit less straightforward. All the attributes should allow for null as well as boolean; this is especially important because Excel defaults to true for some of them and false for others (existing code always treated attributes missing from the XML as false). DocBlocks are updated to indicate the defaults. The test for isProtectionEnabled is changed to reflect this reality. Also, PhpSpreadsheet has been using true/false when writing these, but Excel uses 1/0. Because of these differences, this is a breaking change, but the consequences of the breaks should be minor. Two unit test results needed to change.
Remaining areas in Reader/Xlsx which still use the namespace-unaware code include conditional formatting (internal or external), auto filters, unparsed loaded data, data validation (internal or external), alternate content, and header/footer images.
* Corrections to Xls Reader and Writer
New test and changed sample to go with code changes. Also doc change.
* Generation3 Copy With Image in Footer
Fix#3126. A worksheet contained an image in its footer. It could be loaded and saved as another spreadsheet. However, if you tried to load and save that spreadsheet, PhpSpreadsheet would be unable to find the footer image and would therefore throw an exception.
This error was introduced a long time ago, in PhpSpreadsheet 1.3.0. The apparent cause of the problem was PR #435, sometime around June 2018. That change was very useful, but it had problems which exposed themselves only with a third generation copy. An additional contributor to the issue at hand was PR #1690 (December 2020), which again exposed itself with a third generation copy.
The issue from 1690 is easier to explain and deal with. It added a 'ps' suffix to printer settings resources in Xlsx Reader (to avoid name conflicts), but did not limit itself to a single addition (so subseqent generations would have multiple ps's). It also neglected to add the suffix in Reader/Xlsx/PageSetup.
As for 435, it loops through all the worksheet relationships, and uses the last that it finds as the base for header/footer drawings. It has been changed to use only the relationship whose `rId` matches the worksheet's `legacyDrawingHF` `rId`. It also needs a bit extra validation to make sure a drawing exists before adding it to its array of header/footer images. It also meant that Xlsx/Writer/Rels might write an entry with the same rId twice. I have also changed the header/footer image processing to be namespace aware (see PR #3137).
* Minor Change
I didn't like the way I performed one operation.
* Fix Test
Array index should not have been constant.
* Xlsx Reader Namespace Aware Handling of Drawings, RowAndColumnAttributes, MergeCells
Fix#1482 (actually fix a problem recently attached to that ticket long after it closed). There were problems processing a spreadsheet generated by third party software. That spreadsheet used unexpected namespacing, and absolute paths within the zip file where relative paths were expected.
Xlsx Reader handles most, but not all, of its processing in a namespace-aware manner. Two versions of a worksheet's xml are available - `$xmlSheet` is not namespace aware and `$xmlSheetNS` is aware. This was necessary in order to add namespace support in an incremental manner. The primary reason to continue to use the unaware version is the absence of test cases. In particular, drawings, row and column attributes, and merge cells continue to use the unaware version; this PR changes those to use the aware version.
As noted in the summary above, a couple of new places in the handling of the those items were expecting file locations to be specified as relative paths in the zip file, but the file used absolute paths instead. Those unexpected usages are now addressed.
The user reporting the new problem tried a change which effectively made all uses of `$xmlSheet` namespace aware, and that seemed helpful. It may be time eliminate its usage altogether, whether or not we have appropriate examples of unexpected namespaces to test with. I will not do that with this change, but I may add a new PR to do so after this one is merged. Remaining areas which still use the unaware version include conditional formatting (internal or external), sheet view options, sheet protection, auto filters, unparsed loaded data, data validation (internal or external), alternate content, and header/footer images.
There is an interesting anomaly with the new test file. When I load it and save it, the appearance of the output file does not quite match the input. Oddly, the output file seems much better than the input - the picture no longer covers any data, for example. This is because, in particular, the output file row heights and column widths seem to match the xml, but the input file does not. For example, the xml in both files seems to indicate that row 5 should have a height of 234, which it does in the output file, but the height of that row when the input file is opened is 156. It appears that all row heights and column widths when the input file is opened are very close to 2/3 of what is expected. I will continue to research that anomaly for a few days, but I will not let it prevent me from moving forward with this PR if I don't find the explanation. Whatever that problem is, it seems distinct from the namespacing/pathing problems which the PR addresses.
* Scrutinizer New False Positives
Eliminate them with annotations.
* Using htmlspecialchars to fix issue #3145: this prevents the SimpleXMLElement error "unterminated entity reference" when HTML entities are present in a worksheet
* replace htmlspecialchars with proposal of oleibman
* eliminate string cast, because PhpStan has a problem with that
* ignore string cast for phpstan + enhance unit test
Fix#3093. PR #2595 added the ability for Xlsx Reader to accept a custom color palette. With no examples other than the one at hand, which included a full complement of 64 colors, that PR required 64 colors in the palette. It turns out that Mac Numbers exports to Excel with a palette with less than 64 colors. So, with an example of that at hand, relax the original restriction and accept a palette of any size.
* Xlsx Reader External Data Validations Flag Missing
Fix#2677. This PR supersedes #2679, written by @technghiath, which lacks tests, and probably doesn't solve the problem entirely. The code causing the problem appears to be the last remnant in Xlsx Reader which calls `children` using a namespace prefix rather than a namespace. That is changed, and tests are added where the tag is unexpectedly missing, and also where it uses a non-standard namespace prefix.
* Scrutinizer
Reports 1 "new" error. It isn't, but fix it anyhow.
* Fix One Existing Scrutinizer Problem
Only remaining problem in Reader/Xlsx.
This is a start in addressing issue #2965 (and earlier issue #749). Chart Titles are usually entered as strings or Rich Text strings, and PhpSpreadsheet supports that. They can also be entered as formulas (typically a pointer to a cell with the title text), and, not only did PhpSpreadsheet not support that, it threw an exception when reading a spreadsheet that did so.
This change does:
- eliminate the exception
- set a static chart title when it can determine it from the Xml
This change does not:
- fully support dynamic titles (e.g. if you change the contents of the source cell, or delete or insert cells or rows or columns)
- permit the user to set the title to a formula
- allow the use of formulas when writing a chart title to a spreadsheet
- provide styling for titles when it has read them as a formula
* Ignore square-$-brackets prefix in format string
* Test for square-$-brackets prefix in format string issue fixed
* Fix for phpstan compliance
* Additional assert for checking number format of tested source cell
File from https://www.rondebruin.nl/win/s2/win003.htm. I have been in conversation with the author, who has no objection to its use. I have not actually opened the file in Excel (at least not with macros enabled); I am using it merely to demonstrate that the ribbon data is read and written correctly. Test added; no source code changed. This should slightly increase coverage for Reader/Xlsx (moderate), Writer/Xlsx (slight), and Spreadsheet (substantial). Note that this file has no Ribbon Bin objects, so some coverage is still lacking.
oleibman
redtailmatt
Kevin Verschaeve
Mark Baker
anhoder
Kevin Bond
Robin
FlameStorm
aswinkumar863