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.
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.
* 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.
* Validate Mime Type for Images
* Extend Change to Xlsx Reader
* Attach Drawing to Sheet Only If Valid
* Suppress Some Theoretical Warning Messages
* Minor Tweak
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.
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.
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.
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.
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.
oleibman
redtailmatt
Kevin Verschaeve