v6.16

Requires avadim/fast-excel-helper ^1.4 – it carries the ST_Xstring escaping shared by the writer and the reader, so a control character written as _xHHHH_ is decoded back on reading.

New

• Default formats of the workbook – options default_date_format, default_time_format, default_datetime_format and methods Excel::setDefaultDateFormat(), setDefaultTimeFormat(), setDefaultDateTimeFormat(), setDefaultFormat() (plus the matching methods of Options). They override the formats of the locale and survive a later setLocale()
• Data validation referring to another sheet (DataValidation::dropDown('=Lists!$A$1:$A$5')) is written to the x14 extension list, the way Excel writes it – the plain <dataValidation> element does not support such references
• Conditional formatting referring to another sheet is written to the x14 extension list too, with its style inlined into the rule
• Constants Writer::MAX_CELL_LENGTH and Sheet::MAX_HYPERLINKS – the limits of Excel the library now respects

Fixed

• A cell value longer than 32 767 characters made the whole file unreadable for Excel; now it is truncated to the limit, as Excel does on input
• Control characters and CR in cell values were silently replaced with a space; now they are written as _xHHHH_ / _x000D_ (OOXML ST_Xstring) and Excel restores them when the file is read
• Double escaping of a hyperlink text containing a literal _xHHHH_
• Attributes of <dataValidation> were not escaped at all, so any & or quote in an error title or message produced broken XML; <formula2> is escaped too now
• The searched text and the formulas of conditional formatting were not escaped, so Conditional::contains('R&D') or Conditional::expression('=B1<5') produced an unreadable file
• Panes: <selection> was written for the wrong pane (the selections of topRight and bottomLeft were swapped, and an active cell inside the frozen area was still reported as the selection of bottomRight); activePane now points to the pane that really holds the active cell
• The serial number of 29.02.1900 – Excel has this non-existent day (serial 60), PHP normalized such a date to 01.03.1900
• Silent rounding of long numeric strings with auto_convert_number – IDs, barcodes and card numbers longer than 15 significant digits are kept as text
• save() reported success when packing the xlsx failed – the result of closing the zip archive was ignored, which could leave a truncated file (zip64 is used automatically by libzip when the archive exceeds the plain zip limits)

Changed

• Above 65 530 hyperlinks per sheet Excel cannot read sheetN.xml.rels, so adding one more now throws a clear exception instead of writing a broken file
• A sheet cannot be named History anymore – the name is reserved by Excel (the change history sheet of a shared workbook), an underscore is appended to it

Tests and documentation

• Two new test suites: regression tests of the audit fixes and compatibility tests verified against the output of the real Excel – 66 tests in total, green on PHP 7.4 and 8.4
• Documented the trade-off of inline strings vs shared strings with measured numbers, the streaming mode and what is kept in memory until save(), the limits of Excel, the default formats of the workbook and the rules referring to another sheet (both languages)