avadim/fast-excel-writer

A string containing a line break now gets the wrap text automatically. If you relied on multi-line text being displayed in one line, turn it off with the option auto_wrap_text => false or Excel::setAutoWrapText(false).

New

• The wrap text is enabled automatically for a string containing a line break ("\n"), rich text included. Excel displays such text in several lines only when the wrap text is on, so a multi-line header looked glued together, see #141. An explicit text-wrap of the cell style wins; the option auto_wrap_text (Options::autoWrapText()) and Excel::setAutoWrapText(false) turn the feature off
• The auto width of a column with a wrapped multi-line text is calculated by its longest line, not by the whole text

Fixed

• Incomplete fonts in styles.xml – a cell font got only the properties set explicitly, so a bold header was written as <font><b/></font> without the name and the size, and applications like LibreOffice rendered it with their own default (serif) font, see #141. The name, size and color missing in a style are taken from the default font now; the same applies to Excel::setDefaultFontName() and setDefaultFont(), which used to drop the other properties of the default font
• The short keys of the default font were ignored – Excel::create([], ['default_font' => ['name' => 'Arial', 'size' => 14]]) produced Calibri 11; name, size, color and style work there now as they do in cell styles
• An empty rich text (new RichText('')) was written as a 40-character hash – the key of the shared strings table instead of the text

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)