Fix#3995. Fix#866. Fix#1681. Php DOM loadhtml defaults to character set ISO-8859-1, but our data is UTF-8. So Html Reader alters its html so that loadhtml will not misinterpret characters outside the ASCII range. This works for UTF-8, but breaks other charsets. However, loadhtml uses the correct non-default charset when charset is specified in a meta tag, or when the html starts with a BOM. So, it is sufficient for us to alter the non-ASCII characters only when (a) the data does not start with a BOM, and (b) there is no charset tag.
This will allow us to use:
- UTF-8 files or snippets without BOM, with or without charset
- UTF-8 files with BOM (charset should not be specified and will be ignored if it is)
- UTF-16 files with BOM (charset should not be specified and will be ignored if it is)
- all charsets which are ASCII-compatible for 0x00-0x7f when the charset is declared. This applies to ASCII itself, many Windows and Mac charsets, all of ISO-8859, and most CJK and other-language-specific charsets.
We cannot use:
- UTF-16BE or UTF-16LE declared in a meta tag
- UTF-32, with or without a BOM (browser recommendation is to not support UTF-32, and most browsers do not support it)
- unknown (to loadhtml) or non-ASCII-compatible charsets (EBCDIC?)
I will note that the way I detect the `charset` attribute is imperfect (e.g. might find it in text rather than a meta tag). I think we'd need to write a browser to get it perfect. Anyhow, it is about the same as XmlScanner's attempt to find the `encoding` attribute, and, if it's good enough there, it ought to be good enough here.
Excel supports the following notations for cell styles:
- `[$-F800]` and `[$-x-sysdate]` will format the date according to what appears to be the user's system long date format.
- `[$-F400]` and `[$-x-systime]`, will format the time according to what appears to be the user's system long time format.
- Builtin style 14 will format date according to what appears to be the user's system short date format.
- Builtin style 22 will format date and time according to the user's preference. It appears that the date portion is formatted according to the user's system short date format, but the time portion is formatted according to a format which is neither system short time format nor system long time format, so I'm not sure how this preference is set.
For F800, sysdate, F400, and systime, any other characters in the style are ignored, except that, if you have more than one of this type of block in the style, Excel will treat it as corrupt (error message on open and style changed to General).
Support is added for the new codes. In addition, note that the value displayed in the cell may differ in different environments. To give the PhpSpreadsheet programmer an opportunity to emulate what the intended audience will most often see, properties `shortDateFormat` (default value is builtin 14), `longDateFormat` (default value is `dddd, mmmm d, yyyy`), `dateTimeFormat` (defaults to builtin 22), and `timeFormat` (default is `FORMAT_DATE_TIME2`), with corresponding setters and getters, are added to Style/NumberFormat. Note that, if these properties are set to some other value in PhpSpreadsheet, it will not affect the values in the cell or the style - it is merely a convenience for the programmer. It will, however, affect column width if autosize is specified for the column. If the programmer does not alter any of the new properties, the output should be unchanged from before for builtins 14 and 22.
The new styles are also recognized by the `TEXT` function. In this case, the cell's calculated value may differ from user to user.
Note that this is a small subset of adding locale information to styles. No attempt is made to support any of the other possibilities - locale data will continue to be passed through to the spreadsheet, but PhpSpreadsheet will discard it before attempting to generate the formatted value of a cell.
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.
Before it was a bug when flooring negative numbers added to the length of
the number for the sprintf mask.
For example -0.091 becomes -9.1 in percentage, floors to -10 which is going to
increase $wholePartSize by 1 and add a leading zero to the result: -09.1% with
format 0.0%.
This happened for negative ranges where floor will increse the length of
the original number.
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#3843. Spreadsheet XML had a lingering Xml drawing tag despite having no drawings. When a new drawing was added, PhpSpreadsheet became confused about which drawings to keep. This PR ensures that it keeps the new and drops the old.
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#2809. User felt font files should be searched recursively. The problem could be overcome merely by specifying a more precise font location. However, user has a point - the file layout on user's system is pretty common, and can be integrated into PhpSpreadsheet code easily. As a bonus, the DocBlock for setTrueTypeFontPath, which the user felt was misleading, doesn't even have to change.
Fix#3819. Excel can add these prefixes (basically invisible to end-user). Formula translation in PhpSpreadsheet fails when dealing with these unexpected prefixes, and, even if it handled it correctly, the unexpected prefixes confuse the users. I have changed to strip those prefixes when translating to a locale. This is probably not perfect, but is almost certainly good enough. I could easily add the same change when translating from a locale to English, but I don't think there's a good use case for that, so am opting not to do so for now.
The documentation mentions `translateFormulaToLocale` and `translateFormulaToEnglish`. Neither of these exist; both names are preceded by an underscore. I have changed the code to match the documentation rather than vice versa, retaining deprecated versions of the underscored routines which merely invoke the non-underscored routines.
This was suggested by the investigation of issue #3811. No fix is necessary for the issue. However, two possible code solutions (Php setlocale, which comes with certain design flaws, and StringHelper set(Decimal/Thousands)Separator were suggested, and neither is adequately tested. This PR adds such tests.
Unusually, getting StringHelper Decimal Separator, Thousands Separator, and Currency Code can result in a change to those properties. So, the existing design in several tests where those properties are captured in Setup and restored in Teardown do not work quite as designed. Instead, the ability to set those properties to their default value (null) is added, and the tests re-done to restore the default in Teardown.
The two methods yield the same results when parsing input. However, they diverge when examining output fields through `getFormattedValue`. Such output is currently correct (usually) when using setlocale, but not when using StringHelper. The former works through the 'trick' of using `sprintf(%f)`, which generates a locale-aware string. However, using non-locale-aware `sprintf(%F)` followed by `str_replace` will produce the correct result for both setlocale and StringHelper. One place in the code uses a cast to string, which is incorrect for both methods. Following that up with the same str_replace makes it correct for both. These changes permit, but do not require, the user to avoid setlocale altogether.
It remains an open question whether Settings/Calculation::setLocale should set DecimalSeparator, CurrencySeparator, and CurrencyCode. That makes logical sense, but it would be a breaking change, and having to explicitly set those values when using setLocale does not seem especially burdensome. For now, such a change will not be made.
Fix#3782. A signature of the ReflectionMethod constructor will be deprecated, and PhpSpreadsheet runs afoul of that change in one place. Php8.4 is not yet available in any form, and I am reluctant to make this change until we see that the issue is real (and that this PR fixes it), so am leaving this PR in draft status till then.
I note that one Excel function `PI` is implemented not as a class method in PhpSpreadsheet, but rather as a call to the native Php function `pi`. The ReflectionMethod call is subject to a TypeError in the changed code, but that is already the case. We haven't seen a TypeError because (a) it will arise only if the caller supplies an argument to the function (which must be called with zero arguments), and (b) there are no test cases for that function. The code is slightly cleaned up, and test cases are now added. This is not an important enough problem to rush this PR - the existing code (and the changed code), rather than failing with TypeError, will fail with a CalculationException (wrong number of arguments) before it gets to the TypeError; that is the correct behavior.
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
* Sheet Background Images
Fix#1649, a 3-year-old issue long marked "stale". Excel supports background images on sheets; now PhpSpreadsheet will as well. Support is limited to Xlsx (read and write) and Html (write only). As far as I can tell, Excel Xml and Gnumeric do not support this, nor, of course, do Csv and Slk; Excel Xls does, but, as usual, how to handle it in BIFF format is a mystery; LibreOffice ODS supports it differently than Excel, and this is just another of many ODS style properties not currently supported by PhpSpreadsheet.
* Update CHANGELOG.md
* check if coordinate is inside range
* Added coordinateIsInsideRange tests
* fix coordinateIsInsideRange error throwing
* fix coordinateIsInsideRange error throwing
* add support to worksheet name
* validateReferenceAndGetData type
* change validate and add tests data
* fix tests data reference
* fix absolute reference error
* fix boundaries to get range
* fix scrutinizer erros
* Additional Test Cases
---------
Co-authored-by: oleibman <10341515+oleibman@users.noreply.github.com>
* 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
* Slk Shared Formulas
Fix#2267. The Slk format has a way to express a "shared formula", but the Slk reader does not yet understand it. Thanks to @SheetJSDev for documenting the problem and pointing the way towards a solution. It has taken a long time to get there. Part of the problem is that I have not been successful in getting Excel to use this type of construction when saving a Slk file. So I have resorted to saving a Slk file where shared formulas *could* be used, and then editing it by hand to actually use them. It would not surprise me in the least to have neglected one or more possible ways to specify a shared formula; but, at least the issue as documented is resolved, and if new issues arise, we'll probably be in better shape to deal with them.
* Update CHANGELOG.md
* 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>
* Add iterateOnlyExistingCells to Constructors
Fix#3721. That issue can already be handled, but requires several statements when one ought to suffice. Adding an extra parameter to the RowCellIterator and ColumnCellIterator constructors is useful, easy, and becomes especially practical now that our supported Php releases all support named parameters (see new test Issue3721Test).
* Update Column.php
* Update .php-cs-fixer.dist.php
* Update Row.php
* Update Column.php
* Update ByColumnAndRowTest.php
* 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.
While we might never be able to have 100% of our code strict, we can at
the very least do it for all of our tests. This ensures that our tests
are using our API with the types as intended by the test author, and not
silently be cast to what our API requires.
* 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.
This is a replacement for PR #2404 which has been open for almost 2 years, and which I will close now. As submitted, it broke many unit tests, and no attempt was made to fix those and add others. However, while reviewing it, I found that, among all the tests which it accidentally broke, there were tests which it "broke" (in ExplicitDateTest) which were actually wrong in the first place. That seemed a good enough reason to investigate further.
The original PR suggested the change was needed because "there is now support for microseconds when reading Datetime cells". I'm not sure that's true. Time of day is stored as a fraction of a day, and nothing prevents microseconds from being part of that fraction. It is true that Excel does not allow you to format a date/time cell to display more than 3 decimal positions for the seconds value, even if the value turns out to be accurate to the microsecond, and that has not changed. It is also true that Php supports microsecond accuracy in its DateTime objects, and it behooves PhpSpreadsheet to accommodate that.
PhpSpreadsheet, like Excel, nominally supported the use of one, two, or three decimals when displaying seconds. However, it did not do it correctly, and there had been no tests of this using a value where the decimals were anything other than 0. One existing test, in NumberFormatDates, was wrong. It is fixed and new tests added.
* Check For and Eliminate Octal Literals
Php8.1 introduced a new form of octal literal (0o123); the old form (0123) continues to be supported, but can certainly be misleading. There are exactly 6 uses of the old form in the code base (5 in tests, 1 in infra). 4 of these are clearly unintended (02 or 03), and the leading 0 should just be dropped for those. The other 2 are for file permissions and are easily replaced.
* Minor Changes to LocaleGenerator
Testing the permissions change exposed some problems when the locale files don't exist. This never shows up as an issue when the files do exist, which is pretty much all the time, but correct the theoretical exposures anyhow.
* Populate bg and en_uk Locale Files from Spreadsheet
Also rename locale generator so that it is executed first. This is when it should happen because other tests depend on its results.
* Some Bulgarian Function Omissions
Figured out a reasonable way to compare old Bulgarian function file to new; this identified a handful of now-corrected omissions and errors. Note that JIS function, in old list, is not on spreadsheet, but the Bulgarian translation is the same as English (so the translation isn't needed), and the function has been replaced by DBCS, which is also not on the spreadsheet.
* 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.)
* Read Code Page for Xls ListWorksheetInfo/Names for BIFF5
Fix#3671. Xls reader was not processing Code Page as part of functions ListWorksheetInfo/Names, which was causing them to fail for for BIFF5 (and BIFF7); this was not a problem for BIFF8. There were no unit tests for these functions for either BIFF5 or BIFF8. There are now.
* Add getVersion and getCodePage Methods
These came about because test file for non-standard codepage was supposed to be BIFF5, but turned out to be BIFF8 using UTF-16 with some string data otherwise encoded. Add a BIFF5 equivalent (some hex editing was required), and the means to distinguish one from the other.
* Found MACCENTRALEUROPE Text in BIFF8
It was used for 'Last Modified By' property, even though bulk of spreadsheet uses UTF-16LE. Add a test.
* Fixed inconsistent string handling in SUM() implementations
* Use assertEqual instead of assertSame to allow ints and floats with the
same value
* Removewd special handling of empty strings and use default casts
* Eliminate Phpstan errors
* More Test Cases
* More New Tests
---------
Co-authored-by: oleibman <10341515+oleibman@users.noreply.github.com>
* 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.
Fix#3624. Use the same logic as elsewhere in the same module to invoke `imagesavealpha` when appropriate. (I confess that I do not understand the use case where you would not use imagesavealpha.) The fix was easy; writing a test was not. Google to the rescue.
* 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.
oleibman
Nicholas Ruunu
Adrian
redtailmatt
jenasnic
Kevin Verschaeve
Adrien Crivelli
Jacek