Fix#3592. Fix#3594. The original issue asked that Data Validation be added for Xml spreadsheets. However, doing so exposed some other Data Validation problems, which are corrected along with the Xml portion.
The main other problem is that the code for `Cell\DataValidator::isValid` covered almost no possibilities. It is expanded to include all possibilities, except for Custom Types, which I will continue to think about. Many tests are added.
An obscure problem is that Xlsx Reader does not quite work properly when the Data Validation cells are beyond the high-used column or row. We use the high-used values because it is completely impractical to set all the cells in a range when an entire column or row is selected. This change causes the leftmost top cell in a range to be explicitly allocated, affecting the high-used values. It is not 100% effective, but it will be much more difficult to get into the problem situation, which I will continue to think about.
Although our expectation is that we will not see any Xml Spreadsheets using non-standard namespacing, much of the Xml Reader code is changed to use proper namespace techniques. A few areas which just had nothing to do with this change continue to use less robust techniques; I plan to address those soon after implementing this change.
One of the Data Validation types is for time of day. Testing revealed that `Shared\Date::convertIsoDate` was insufficiently robust when determining whether its input consisted of a time of day without date. It is now more robust.
* Unzipped Gnumeric File
A Gnumeric file is normally a gzipped Xml file. However, the Gnumeric application will also read an unzipped Xml file. Allow PhpSpreadsheet to do the same.
* Scrutinizer (legitimate)
Fix minor problem.
* HyperlinkBase Property, and Html Handling of Properties
Fix#3573. The original issue concerned non-support of Document Properties in Xml spreadsheets. However, most of the Properties mentioned there were already supported. But the investigation revealed some gaps in Html coverage.
HyperlinkBase is the one property mentioned in the issue that was not supported for Xml, nor indeed for any other format. All the other document properties are 'meta'; but HyperlinkBase is functional - if you supply a relative address for a link, Excel will use HyperlinkBase, if supplied, to convert to an absolute address. (Default is directory where spreadsheet is located.) Here's a summary of how this PR will handle this property for various formats:
- Support is added for Xlsx read and write.
- Support is added for Xml read (there is no Xml writer). Ironically, Excel messes up this processing when reading an Xml spreadsheet; however, PhpSpreadsheet will get it right.
- HyperlinkBase is supported for Xls, but I have no idea how to read or write this property. For now, when writing hyperlinked cells, PhpSpreadsheet will be changed to convert any relative addresses that it can detect to absolute references by adding HyperlinkBase to the relative address. In a similar vein, Xls supports custom properties, but PhpSpreadsheet does not know how to read or write those.
- Gnumeric has no equivalent property, so nothing needs to be done to its reader. Since we don't have a Gnumeric writer, that's not really a problem for us.
- Odt has no equivalent property, so nothing needs to be done to its reader. The Odt writer does not have any special logic for hyperlinks, so, at least for now, will remain unchanged.
- Csv has no equivalent property, so nothing needs to be done to its reader. The Csv writer does not have any special logic for hyperlinks, so, at least for now, will remain unchanged.
- Html allows for an equivalent `base` tag in the head section. Support for this is added to Html reader and writer.
Html Writer was only handling 8 of the 11 'core' properties. Support is added for `created`, `modified`, and `lastModifiedBy`. Custom properties were not supported at all, and now are.
Html Reader did not support any properties. It will now support all of them.
* Scrutinizer
Remove one dead reference.
PR #3513, developed by @SaidkhojaIftikhor, has been stuck for some time awaiting tests. This is the first of three PRs to replace that one. This accomodates the use of slash as a delimiter in functions TEXTAFTER, TEXTBEFORE, TEXTSPLIT, and NUMBERVALUE. The source changes are very simple. Additional tests exercise all the source changes.
* 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.
* Changes to NUMBERVALUE, VALUE, DATEVALUE, TIMEVALUE
Fix#3574. Reporter received deprecation notice for NUMBERVALUE function with invalid arguments. In fact, the arguments turn out to be valid after all; NUMBERVALUE treats a null-string or an all-blank-string in the first argument as if it were 0. Fixed this, and added several test cases suggested by it.
VALUE had been parsing its argument the same way as NUMBERVALUE. However, VALUE does not substitute 0 for null-string or all-blank-string. Coded up the difference between the two, and added the same tests for VALUE as for NUMBERVALUE.
VALUE can also pass its argument to DATEVALUE or TIMEVALUE. It is currently over-permissive about that, because Php is over-permissive, e.g. `new DateTime('q')` will return a DateTime object with the current date and time with a timezone of 'q'. Excel will, naturally, return `#VALUE!` for `DATEVALUE('q')`. I don't know that we can ever match Excel's (AFAIK not formally documented) decisions here 100%, but we can get a lot closer by parsing the date string if and only if it contains at least one digit. Code to enforce that is added to DATEVALUE and TIMEVALUE, and appropriate tests are added.
* Failure for Php 7.4 Linux
After not seeing any such problem for many months, this is the second day in a row where result is different on Windows than Linux with no apparent reason to think why that should be the case. At any rate, easily solved.
Fix#3561. PhpSpreadsheet VLOOKUP/HLOOKUP allow the specification of index_number (third parameter) as an array when lookup_value (first parameter) is supplied as an array. Change to permit index_number as an array even when lookup_value is not an array, as Excel does.
* Update Html.php
Add position attr for image whitch driving by MemoryDrawing
* add unit test for pr#3535
* Fix code styles
* Fix code styles
* use a local image instead of web image
* add image for pr#3535
* code style fix
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.
* Font and Effects Support for Chart Data Labels and Axis
Addresses some remaining issues with 32readwriteLineChart5 (see issue #1797). Font size is covered. So are effects, although the results are a bit odd. For the new spreadsheet 32readwriteLineChart6, the Axis labels have a yellow-ish glow, but reading and writing the spreadsheet in PhpSpreadsheet gives them a purple-ish glow. Nevertheless, the new test shows that the output file uses schemeClr accent4, as does the input file. So the effect is handled correctly, but it seems there is likely to be a difference between theme colors (Writer/Xlsx/Theme appears to write hard-coded color schemes, and, in any case, Reader/Xlsx does not appear to handle schemeClr). Fixing that will be a great deal more difficult, with a large chance of regression, and will need to happen in a separate PR (one that I am not currently investigating, but I will open a new issue). Effects using srgbClr (and probably sysclr) should be okay.
* Better Theme Support
When reading Xlsx, the theme colors will now also be used for writing. This means that a file can be loaded and saved and its chart colors will now be preserved. If the spreadsheet is created new, Excel 2007-2010 colors are used. The writer is currently hard-coded to use them, so this avoids making this a breaking change. The theme colors can be explicitly changed if desired, and Excel 2013+ colors can be introduced very easily.
```php
$spreadsheet->getTheme()
->setThemeColorName(Theme::COLOR_SCHEME_2013_PLUS_NAME);
```
Likewise, if the old behavior of changing to the 2007-2010 scheme rather than using the input values is desired, that is easy to achieve after the load has taken place.
```php
$spreadsheet->getTheme()
->setThemeColorName(Theme::COLOR_SCHEME_2007_2010_NAME);
```
The new Theme class introduced by this change can easily be extended to include Fonts and Effects. Unlike Colors, I am unsure what the practical effects of changing those to, say, the 2013+ defaults would be.
* Scrutinizer
Use an alias in a use statement.
* Update Change Log
Due to potential behavior change.
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.
Also enforce more rigorous Excel Function implementation by testing the underlying implementation, call via the Calc Engine, and execution from in a worksheet.
Separate out unhappy path (exception) checks into a separate test, so that a single test isn't made overcomplex checking for every potentiality.
Scrutinizer may dislike variadics, for variable number of arguments of mixed type; but tough. It's 100% valid PHP, accepted by phpstan, and makes life a lot easier.
Initial work here covers all the database and datetime unit tests for Excel function implementations.
* 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.
I hadn't noticed some minor corruption in a file. It is fixed. Xls now supports BAHTTEXT as _xlfn.BAHTTEXT, not as an index to a list of built-in functions, so it is no longer suitable for this test and is removed from the spreadsheet. TRANSPOSE changes, e.g. A1:B2 to {1,2;3,4}, and the Xls Writer Parser has trouble with that, so its test is also removed from the test spreadsheet.
PR #3340 increased coverage in Reader Xls for most functions. Some of the omissions from that PR (see below) were intended from the start. However, the set of (BINOMDIST, EXPONDIST, GAMMADIST, NORMDIST, POISSON, WEIBULL, and FIXED) were not intended to be omitted; they just did not seem to work. Having had time to research, it appears that the problem is not those functions themselves, but rather the use of Boolean constants as function arguments (see issue #3369). Knowing that, it is possible to add those missing functions back in, just taking care to use `0` or `1` or `TRUE()` or `FALSE()` rather than boolean constants as arguments. No update to source or test code; just adding a few new cells to an Xls spreadsheet.
Functions still omitted because they return array results:
- GROWTH
- LINEST
- LOGEST
- MINVERSE
- MMULT
- TRANSPOSE
- TREND
Functions still omitted because they are not implemented in PhpSpreadsheet:
- BAHTTEXT
- CELL
- FREQUENCY
- GETPIVOTDATA
* 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.
* Allow More Fonts/Fontnames for Exact Width Calculation
Fix#3190. A limited set of explicitly-named font files can be used when an exact width calculation is required. User noted that font files are named differently on Mac than on Windows (if the fonts are installed on Linux, the font names probably match Windows). Since the algorithm for generating the Mac file name from the font name seems easy, that algorithm is invoked when the Windows-named file is not found.
Moving on from there, it seems odd that only a small set of fonts are supported. It is, of course, impossible to support all possible fonts out of the box. However, it is possible to allow the user to supply additional mappings from font name to file name (or override existing mappings if neither the Windows nor Mac name matches the user's system), and doing so is permitted with this change:
```php
\PhpOffice\PhpSpreadsheet\Shared\Font::setExtraFontArray([
'fontname' => [ /* More than 1 can be specified */
'x' => 'fontfilenamefornormal.ttf',
'xb' => 'fontfilenameforbold.ttf',
'xi' => 'fontfilenameforitalic.ttf',
'xbi' => 'fontfilenameforbolditalic.ttf',
],
]);
```
* Unexpected Test Difference
... between Windows and Linux.
* Additional Coverage for Reader Xls
More than 750 lines are newly covered, increasing overall coverage on my machine from 91.14% to 93.09%.
* Update FormulasTest.php
* Additional Unit Tests for XIRR and XNPV
See discussion in issue #3297. Excel result disagreed with PhpSpreadsheet, but it seems apparent that the error is Excel's. Although we have other test cases where Excel and PhpSpreadsheet disagree, those have so far been cases where Excel cannot give an answer because its algorithm does not converge, which is possible for any algorithm. In the issue in question, Excel seems to converge to a different answer; this is different than the other exceptions, and so deserves documentation with a formal test case. The discussion also mentions some cases where XNPV seems to give the wrong result in Excel, and those cases are added for XNPV.
* Scrutinizer
Whatever.
* Minor Fix for AND/OR/XOR
These 3 fall into the set of functions where Excel treats string literals differently depending on whether they are passed to the function directly or as a cell reference. PhpSpreadsheet is updated to try to duplicate that logic. New tests are added. Some existing test results had to change as a result of this code change.
* Adopt A Suggestion From Mark Baker
Reduce if statements by adding functions.
oleibman
dubox
Mark Baker
anhoder
Kevin Bond