They have made some changes at my request, the major effect of which is that it will now work with 33_Chart_create_bar_stacked. This is a departure for them in that they have changed the functionality of jpgraph, not merely made sure that it is compatible with new Php releases.
Fix#3015. Fix#2766. Both issues requested a way to set Mpdf configuration parameters. I explained this could be done by extending class Mpdf and overriding protected function createExternalWriterInstance. So, no change is needed to PhpSpreadsheet. The parameters they wanted to override didn't seem interesting from a PhpSpreadsheet perspective. However, the configuration parameters include the ability to add fonts on the fly to those distributed by Mpdf, and that does sound useful. Normally, Mpdf (and Dompdf and Tcpdf) support a limited number of fonts, and will substitute for fonts they don't know (most fonts, even commonly used one, wind up using a DejaVu variant). Using these configuration options can lead to a more faithful Pdf representation of the spreadsheet.
In order to demonstrate this in action, I've added a new sample, a new class extending Mpdf, and a distinctive small font file from Google fonts with its (SIL Open Font) license. IANAL, but I can't see how this wouldn't be permitted use. There are no source code changes.
An alternate approach would be to add a callback to Writer/Pdf/Mpdf. It seems that this would be at least as much effort for the end-user as extending the class, and more effort for us.
It appears that Dompdf and Tcpdf need prep work to achieve the same end, and are thus less suitable for demonstrating via an example. I will continue to research.
* Changes for Chart Rendering Samples
PR #3521 added chart rendering to the 33* samples. Some of those samples had rendering problems; this PR fixes some of the errors by changing some of the code in JpGraphRendererBase. I am discussing approaches to some other problems with mitoteam.
The changes in 3521 applied only when running the samples as web pages. A new member 35_Chart_render33 is added to do the same when running the samples from the command line. Because of 3521, 35_Chart_render and new 35_Chart_render33 (in web page mode) display rendering which is already displayed by other samples and so are superfluous as web pages, so they are no longer presented as options.
The samples in 35_Chart_render properly accounted for multiple charts on a single worksheet, but did not properly account for charts on multiple worksheets. This problem is fixed.
33_Chart_create_stock2 was added using a base from before 3521, but did not incorporate the 3521 changes. It now does so.
32_Chart_read_write_HTML and 32_Chart_read_write_PDF are made consistent with the 32* Xlsx samples by allowing the download of the generated file when run as web pages. Helper/Downloader needed a minor change to support Html. Some of the changes in PR #3522 require the Downloader change to work properly. Some samples which were not changed by 3522 are changed in this PR to give a more uniform look and feel to the web samples.
* Scrutinizer
Fix class name conflict. Ignore "complexity" problem.
* Improve Sample 26
The UTF8 sample 26 includes CJK characters. These are generated in Xls, Xlsx, Html, and Csv (with BOM) correctly. Csv without BOM has a problem, but that's Microsoft's problem, not ours. That leaves PDF, which currently does not display the CJK characters (nor a Latin extension character). Mpdf, in combination with editHtmlCallback (and earlier commits in this PR), can support them; change the sample to do so. Dompdf, which had been used for the sample, apparently supported Web Fonts at one time, which might also have solved this problem, but no longer seems to do so.
* Fix Style Problem
Need blank line.
* Change Some Spacing in Source Code
Make Header PSR12-compliant.
Stock charts currently ignore upDownBars tag and its subsidiary gapWidth, upBars, and downBars tags when reading, and hard-codes those tags on write. As a result, the sample reproductions of stock charts in the 32* series aren't faithful to the originals. This PR fixes samples 1, 2, and 5. Samples 3 and 4 are reproduced better, but they require currently unsupported secondary axes (issue #560, issue #1072, and PR #1073 were closed as stale; see also https://github.com/PHPOffice/PHPExcel/pull/1037). I will start to look at those, but it could take a while, and I don't think there's a reason to delay this in the meantime.
Charts which depended on the hard-coded values written by the Xlsx Chart writer will be slightly different as a result of this change. To restore the hard-coded behavior:
```php
$plotArea->setGapWidth(300);
$plotArea->setUseUpBars(true);
$plotArea->setUseDownBars(true);
```
The new behavior is demonstrated in 33_Chart_create_stock. The old behavior is demonstrated (with the code above) in new 33_Chart_create_stock2.
* 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.
* Support Border for Charts
All chart linestyles will be supported for Chart Border. Also add fill color for Chart. Also 'nofill' for Axis (allows suppressing of vertical axis line). These are demonstrated in sample 32_readwriteChartLine5, and a new unit test member is added. This addresses some vague problems added to issue #1797 over 8 months after it was closed; there is still at least one problem, much more complicated than the 2 being addressed in this PR.
* Handle Legend Borders in Same Way as Chart Borders
Consistency is good, and the implementation of Chart Borders offers more possibilities than the implementation of Legend Borders had. Since Legend Borders hasn't made it to a release yet, there is no need for deprecations.
No source changes. Act on some items that have come up in recent discussions.
- Sample 33_Chart_create_line creates a stacked line chart. According to @MarkBaker, the stacking is done on the wrong variable, and, even were that not the case, stacking is unusual for line charts. Since this is our primary sample showing how to create a line chart, remove the stacking. Another sample, with a more appropriate choice of chart (33_Chart_create_bar_stacked), still shows how to create a stacked chart.
- The test for reading a styled cell from Html is flawed. It sets a date format for a string date/time, but the format is applied only to numeric data, so the format, although set correctly, is ineffective. Keep the test, but add some explanation in the assertion, and add some new more effective tests, also with explanations in the assertions.
- Wrong namespace used for Writer/Xlsx/ConditionalFillTest.
* Allow Color and Effects on Data Points on Chart Axis
Fix#3414. There had been no way to do this. It is now supported via a new AxisText class, which derives from Properties, and has FillColor and effects (glow/shadow/softEdges) properties. The code changes in chart sample 33_Chart_create_scatter2 illustrate usage.
* Minor Performance Improvement
May also get rid of Scrutinizer complaint.
* Eliminate New Spacing Patch
No need for it.
* Add Legend Formatting to Change
Much in common with Data Point formatting.
* Update PR template
* Trim trailing space
* Support for configuring a Chart Title's overlay
* Provide test coverage for getting/setting a Chart Legend's overlay
Add missing docblock
Fix#3055. Xls Reader can set vertical break specifying row 0, causing an exception. It is doubtful that Excel needs a row for a vertical break; code is changed to use row 1 if the input file specifies row 0 (or lower). Code in question has not been exercised in unit test suite. Similarly, code to set horizontal break (which probably does not have a bug) is not exercised in test suite. Finally, page order in Writer incorrectly uses value in opposite way that Reader does. A new sample is added to illustrate that these are all handled correctly; it is easier to verify this by visually comparing the source spreadsheet and the copy made from it. A unit test is also added for the same spreadsheet to formally assert that the 3 properties in question are both read and written correctly.
* Attempt To Honor Fit to 1-Page Height for Html/Pdf
Fix#3266. Each sheet in a spreadsheet can specify that it be set to fit width or height to a fixed number of pages. Mpdf and most browsers already handle the common case of fit to 1 page wide; I am unable to find a solution for Dompdf or Tcpdf. Code is added for the common case of fit to 1 page high when possible; this will usually work in Mpdf, Dompdf, and most browsers. I am not able to come up with a way to handle fit to more than 1 page wide or high.
* Synchronizer
Remove one unused assignment in test.
Fix#2333. Thanks to @SSI-johnnypops for discovering the problem and formulating most of the solution. The classes involved in setting up composite charts made it relatively easy to code them; however, there can be problems when reading a spreadsheet with such a chart and then saving a copy of it. In particular, the ordering conventions when reading the chart may not match the expectations when writing. The original attempt to resolve this worked for read/write but broke one of our samples (33_Chart_create_composite), which probably means that it would break other code which is now working in the wild. It has now been refined so that it (hopefully) makes adjustments to the ordering if and only if they are required. The new code works with the failing spreadsheet reported in the issue, with the unchanged sample code, and with an alternate version of the sample which contains what is probably a better model for people to use when coding such a chart.
* WIP Limited Support for Form Controls V2 (ListBox, Buttons, etc.)
This is a replacement for draft PR #2455 and draft PR #3127. There is some useful commentary in those PRs which I have mostly, but not entirely, duplicated below. Fix#2396. Fix#1770. Fix#2388.
A related problem is that the vml files used for the form controls sometimes contain invalid xml. Fix#3125 (rejected previous PR #1181 as too risky, issue was also reported as #170). Vml file should be valid Xml, but Excel can generate unclosed `<br>` tags, preventing Xlsx reader from reading file correctly. I believe a very narrowly targeted fix, changing `<br>` to `<br/>`, and only when reading vml files, probably mitigates the risk. The sample file formscomments.xlsx which is part of this change shows this problem with `Button 1` on sheet `Forms`; the spreadsheet was created with Excel 365, so the problem is not restricted to Excel 2013 as originally reported. A comment on PR 3127 indicates that other tags might be involved, but, without a file demonstrating that, I will restrict this change to br tags for now.
I am starting this out in draft status, and will probably leave it that way for some time. I'm not sure where we want to go with this. It fixes some problems, but in a limited manner, and creates some others. I'm not sure the pain of the others is balanced considering the limitations of the fix. If enough interest is generated as a result of this ticket being out there, we can proceed; if not, it probably isn't worth it.
This fix allows form control elements to be read in and written out. It does not allow you to add such elements, nor even to locate them or determine their properties (so you can't modify or delete them). Although it handles reading and writing of sheets containing both form controls and comments, it will probably create a corrupt spreadsheet if you try adding a new comment to a sheet with form controls - probably quite difficult to solve. Cloning the sheet probably won't work either - probably easier than the other. It is conceivable that we want to add a new property to the Xlsx Reader which turns the reading of form elements on or off (default=off), so that negative effects will be limited to those who have explictly opted in. The change in its current form does not implement such a property.
Because of its limitations, the change isn't really testable. As in some other recent installs, I have added a sample to demonstrate that it works correctly.
As it turns out, if we have a worksheet which contains both form controls and comments (see formscomments.xlsx which is part of this PR), PhpSpreadsheet already creates a corrupt file when it tries to load and save the spreadsheet with such a worksheet. With this change, the file is saved without corruption. This tilts things in favor of proceeding. I'm still not ready, but this will be an important consideration.
A sample file for issue #2621 illustrated a problem with shape files. Since they are involved here, I took a look at how the sample worked with this code. In master, and with this change, a corrupt file results. Fixing that is probably easier than the general problem of handling shape files, but it's an argument against moving this forward until the corruption problem can be addressed.
Fix#2661. A template including checkboxes was leading to file corruption solved by this PR. Another argument for moving forward.
* Improved Sample File, and Documentation
Add more realistic worksheet to spreadsheet. Document new feature, adding caveats to how it can be used.
* 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.
* Fix Unintential Deprecated Calls - Everything Else
I think it's best to install these before PR #3166. This one, which I hope to be the last in this series, does have some minor changes to source code, as well as to doc-blocks and to test members which continue to inadvertently use calls to deprecated functions.
* Some Remaining Deprecations in Tests
Fix them now.
* Minor Docblock Updates
Worksheet::unprotectCellsByColumnAndRow was incorrect. Other changes are cosmetic, leading to slightly better documentation.
* Update Worksheet.php
* Eliminate Some Scrutinizer 'Major' Problems Part 5
More of the same. 2 or 3 of these still to go after this.
Note one deprecation. Shared/Drawing has a method imagecreatefrombmp. However, that was introduced as a native PHP function with 7.2, so the method is no longer needed.
* More Silliness
Keep trying.
* If At First You Don't Succeed
Try, try again.
* Problems Formatting Very Small and Very Large Numbers
Fix#3128 (author found a workaround but the problem remains). For some complex masks, when a cast of the cell value from float to string results in the use of scientific notation, the result of the formatting is unusable. I believe this PR solves the problem for numbers close to zero (Php cast from float to string uses scientific notation starting with 1E-5), and for a range of large numbers which are not handled correctly now.
However, I have not found a way to ensure that the results match Excel for very large numbers (1E18 or larger); this change at least ensures that the resulting string is an accurate rendition of the number (which is not the case now) even if doesn't match Excel. As an example, if you use the mask reported in the original issue `0 000.0` and enter a value of 1E90 into the cell, Excel will show it as 1 followed by 87 zeros, a space, 3 more zeros, decimal point and zero. I have not figured out how to get PhpSpreadsheet to do that; for now, it will just return the formatted value as 1 followed by 90 zeroes instead (I might have chosen to go with scientific notation instead). I will continue to think about those, but do not feel it is worth delaying the improvements in this ticket while I do so.
The affected section of code also truncated to the appropriate precision. It now rounds, as Excel does.
This seemed to be an area of code where problems might arise on 32-bit systems, and, indeed, I found something in the formatting code which had to be changed for 32-bit to work correctly. As long as I was doing that anyhow, I ran the full test suite, and found that Php8.1 had introduced some new stringencies which caused problems in a handful of places. All were found in Xls Reader, and all are corrected now.
* Remove Dead Assignment
Scrutinizer will be happy now.
* 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.
* Permit Date/Time Entered on Spreadsheet to be Calculated as Float
Fix#1416. I do not entirely understand the use case for this old issue, but resolving it seems straightforward. Issue complains that user-entered date/time fields may be interpreted as either float or int when PhpSpreadsheet reads them. Issue suggests getCalculatedValue treat all date/time fields as float; that seems like a breaking change. However, adding an option to permit it seems okay. That option might be implemented as either a property of Calculation, or a static property of Cell. Since the changed logic is found in Cell (and Shared/Date), I opted for the latter.
In Cell, the property `$parent` is incorrectly described in doc block as `Cells`, and should be `?Cells`. This change eliminates some Phpstan and Scrutinizer problems, and should allow the elimination of some try/catch blocks - I have not done an exhaustive search for those.
Calls to `isDateTime` could have affected activeSheet and selectedCells; they no longer can. Optional parameters are added to it and the functions it calls to accommodate the new functionality; the defaults for the new parameters will, of course, return the same result as the earlier versions of the functions would have returned.
* Scrutinizer - Self-inflicted
Tests used constant which I deprecated.
* Phpstan Baseline Fixes 2022-09-21
Eliminate about 200 more lines from Phpstan baseline. For Helper/Sample and Helper/Html and others, many properties are declared as protected despite the fact that the classes do not extend any other class, and there are no classes which extend them. They are changed to private; this could be a breaking change in circumstances for which I cannot think of a use case (user extends class for some reason).
* Slightly Botched Merge Commit
Hope this fixes phpstan.
* Memory Leak in Sample35
All but 6 chart samples can be rendered by Sample35. Of those 6, 3 of the problems are because the script runs out of memory processing them. Adopting a suggestion from @MAKS-dev in issue #2092, adding a call to gc_collect_cycles after the charts from each spreadsheet is rendered appears to make it possible to include those 3 spreadsheets in Sample35 after all.
Also take advantage of this opportunity to correct a number (hopefully all) of Scrutinizer problems with JpgraphRendererBases.
* Minor Fix
Problem running 8.1 unit tests.
* Resolve Problems with Pie 3D Charts
Minor fix, leaving only one spreadsheet unusable in Sample35. The reasons for its unusability are now documented in the code.
* Mitoteam Made Changes
Discussing this problem with them, they decided they should make a change for Pie3D rather than forcing us to use the workaround pushed earlier. Change to require mitoteam 10.2.3, revert workaround.
* More Scrutinizer Catch Up
Continue the work of PR #3043 by attending to the 12 remaining 'new' issues.
* Php 8.1 Problem
One new null-instead-of-string problem.
This PR expands on PR #2195 from @nkjackzhang. That PR has been stalled for some time awaiting requested fixes. Those fixes are part of this PR, and additional tests and samples are added. The original request was to handle `vertical-align:middle` in Css (Excel uses `center`). This PR does its best to also handle vertical alignment Excel values not found in Css - `justify` (as `middle`) and `distributed` (as `middle`). It likewises handles valid Css values not found in Excel (`baseline`, `sub`, and `text-bottom` as `bottom`; `super` and `text-top` as `top`; `middle` as `center`).
It also handles horizontal alignment Excel values not found in Css - `center-continuous` as `center` and `distributed` as `justify`; I couldn't think of a reasonable equivalent for `fill`, so it is ignored.
The values assigned for vertical and horizontal alignment are now lower-cased (special handling required for `centerContinuous`).
MarkBaker
oleibman
Jonathan Goode
Adrien Crivelli