1.21.0

Added

• Ability to add a picture to the background of the comment. Supports four image formats: png, jpeg, gif, bmp. New Comment::setSizeAsBackgroundImage() to change the size of a comment to the size of a background image. Issue #1547 PR #2422
• Ability to set default paper size and orientation PR #2410
• Ability to extend AutoFilter to Maximum Row PR #2414

Changed

• Xlsx Writer will evaluate AutoFilter only if it is as yet unevaluated, or has changed since it was last evaluated PR #2414

Deprecated

• Nothing

Removed

• Nothing

Fixed

• Rounding in NumberFormatter Issue #2385 PR #2399
• Support for themes Issue #2075 Issue #2387 PR #2403
• Read spreadsheet with # in name Issue #2405 PR #2409
• Improve PDF support for page size and orientation Issue #1691 PR #2410
• Wildcard handling issues in text match Issue #2430 PR #2431
• Respect DataType in insertNewBefore PR #2433
• Handle rows explicitly hidden after AutoFilter Issue #1641 PR #2414
• Special characters in image file name Issue #1470 Issue #2415 PR #2416
• Mpdf with very many styles Issue #2432 PR #2434
• Name clashes between parsed and unparsed drawings Issue #1767 Issue #2396 PR #2423
• Fill pattern start and end colors Issue #2441 PR #2444
• General style specified in wrong case Issue #2450 PR #2451
• Null passed to AutoFilter::setRange() Issue #2281 PR #2454
• Another undefined index in Xls reader (#2470) Issue #2463 PR #2470
• Allow single-cell checks on conditional styles, even when the style is configured for a range of cells (#) PR #2483

1.20.0

Added

• Xlsx Writer Support for WMF Files #2339
• Use standard temporary file for internal use of HTMLPurifier #2383

Changed

• Drop support for PHP 7.2, according to https://phpspreadsheet.readthedocs.io/en/latest/#php-version-support
• Use native typing for objects that were already documented as such

Deprecated

• Nothing

Removed

• Nothing

Fixed

• Fixed null conversation for strToUpper #2292
• Fixed Trying to access array offset on value of type null (Xls Reader) #2315
• Don't corrupt XLSX files containing data validation #2377
• Non-fixed cells were not updated if shared formula has a fixed cell #2354
• Declare key of generic ArrayObject
• CSV reader better support for boolean values #2374
• Some ZIP file could not be read #2376
• Fix regression were hyperlinks could not be read #2391
• AutoFilter Improvements #2393
• Don't corrupt file when using chart with fill color #589
• Restore imperfect array formula values in xlsx writer #2343
• Restore explicit list of changes to PHPExcel migration document #1546

1.19.0

Added

• Ability to set style on named range, and validate input to setSelectedCells Issue #2279 PR #2280
• Process comments in Sylk file Issue #2276 PR #2277
• Addition of Custom Properties to Ods Writer, and 32-bit-safe timestamps for Document Properties PR #2113
• Added callback to CSV reader to set user-specified defaults for various properties (especially for escape which has a poor PHP-inherited default of backslash which does not correspond with Excel) PR #2103
• Phase 1 of better namespace handling for Xlsx, resolving many open issues PR #2173 PR #2204 PR #2303
• Add ability to extract images if source is a URL Issue #1997 PR #2072
• Support for passing flags in the Reader load() and Writer save()methods, and through the IOFactory, to set behaviours PR #2136
  • See documentation for details
• More flexibility in the StringValueBinder to determine what datatypes should be treated as strings PR #2138
• Helper class for conversion between css size Units of measure (px, pt, pc, in, cm, mm) PR #2152
• Allow Row height and Column Width to be set using different units of measure (px, pt, pc, in, cm, mm), rather than only in points or MS Excel column width units PR #2152
• Ability to stream to an Amazon S3 bucket Issue #2249
• Provided a Size Helper class to validate size values (pt, px, em) PR #1694

Changed

• Nothing.

Deprecated

• PHP 8.1 will deprecate auto_detect_line_endings. As a result of this change, Csv Reader using PHP8.1+ will no longer be able to handle a Csv with Mac line endings.

Removed

• Nothing.

Fixed

• Unexpected format in Xlsx Timestamp Issue #2331 PR #2332
• Corrections for HLOOKUP Issue #2123 PR #2330
• Corrections for Xlsx Read Comments Issue #2316 PR #2329
• Lowercase Calibri font names Issue #2273 PR #2325
• isFormula Referencing Sheet with Space in Title Issue #2304 PR #2306
• Xls Reader Fatal Error due to Undefined Offset Issue #1114 PR #2308
• Permit Csv Reader delimiter to be set to null Issue #2287 PR #2288
• Csv Reader did not handle booleans correctly PR #2232
• Problems when deleting sheet with local defined name Issue #2266 PR #2284
• Worksheet passwords were not always handled correctly Issue #1897 PR #2197
• Gnumeric Reader will now distinguish between Created and Modified timestamp PR #2133
• Xls Reader will now handle MACCENTRALEUROPE with or without hyphen Issue #549 PR #2213
• Tweaks to input file validation Issue #1718 PR #2217
• Html Reader did not handle comments correctly Issue #2234 PR #2235
• Apache OpenOffice Uses Unexpected Case for General format Issue #2239 PR #2242
• Problems with fraction formatting Issue #2253 PR #2254
• Xlsx Reader had problems reading file with no styles.xml or empty styles.xml Issue #2246 PR #2247
• Xlsx Reader did not read Data Validation flags correctly Issue #2224 PR #2225
• Better handling of empty arguments in Calculation engine PR #2143
• Many fixes for Autofilter Issue #2216 PR #2141 PR #2162 PR #2218
• Locale generator will now use Unix line endings even on Windows Issue #2172 PR #2174
• Support differences in implementation of Text functions between Excel/Ods/Gnumeric PR #2151
• Fixes to places where PHP8.1 enforces new or previously unenforced restrictions PR #2137 PR #2191 PR #2231
• Clone for HashTable was incorrect PR #2130
• Xlsx Reader was not evaluating Document Security Lock correctly PR #2128
• Error in COUPNCD handling end of month Issue #2116 PR #2119
• Xls Writer Parser did not handle concatenation operator correctly PR #2080
• Xlsx Writer did not handle boolean false correctly Issue #2082 PR #2087
• SUM needs to treat invalid strings differently depending on whether they come from a cell or are used as literals Issue #2042 PR #2045
• Html reader could have set illegal coordinates when dealing with embedded tables Issue #2029 PR #2032
• Documentation for printing gridlines was wrong PR #2188
• Return Value Error - DatabaseAbstruct::buildQuery() return null but must be string Issue #2158 PR #2160
• Xlsx reader not recognize data validations that references another sheet Issue #1432 Issue #2149 PR #2150 PR #2265
• Don't calculate cell width for autosize columns if a cell contains a null or empty string value Issue #2165 PR #2167
• Allow negative interest rate values in a number of the Financial functions (PPMT(), PMT(), FV(), PV(), NPER(), etc) Issue #2163 PR #2164
• Xls Reader changing grey background to black in Excel template Issue #2147 PR #2156
• Column width and Row height styles in the Html Reader when the value includes a unit of measure Issue #2145.
• Data Validation flags not set correctly when reading XLSX files Issue #2224 PR #2225
• Reading XLSX files without styles.xml throws an exception ...

1.18.0

Added

• Enhancements to CSV Reader, allowing options to be set when using IOFactory::load() with a callback to set delimiter, enclosure, charset etc. PR #2103 - See documentation for details.
• Implemented basic AutoFiltering for Ods Reader and Writer PR #2053
• Implemented basic AutoFiltering for Gnumeric Reader PR #2055
• Improved support for Row and Column ranges in formulae Issue #1755 PR #2028
• Implemented URLENCODE() Web Function
• Implemented the CHITEST(), CHISQ.DIST() and CHISQ.INV() and equivalent Statistical functions, for both left- and right-tailed distributions.
• Support for ActiveSheet and SelectedCells in the ODS Reader and Writer. PR #1908
• Support for notContainsText Conditional Style in xlsx Issue #984

Changed

• Use of nb rather than no as the locale code for Norsk Bokmål.

Deprecated

• All Excel Function implementations in Calculation\Database, Calculation\DateTime, Calculation\Engineering, Calculation\Financial, Calculation\Logical, Calculation\LookupRef, Calculation\MathTrig, Calculation\Statistical, Calculation\TextData and Calculation\Web have been moved to dedicated classes for individual functions or groups of related functions. See the docblocks against all the deprecated methods for details of the new methods to call instead. At some point, these old classes will be deleted.

Removed

• Use of nb rather than no as the locale language code for Norsk Bokmål.

Fixed

• Fixed error in COUPNCD() calculation for end of month Issue #2116 - PR #2119
• Resolve default values when a null argument is passed for HLOOKUP(), VLOOKUP() and ADDRESS() functions Issue #2120 - PR #2121
• Fixed incorrect R1C1 to A1 subtraction formula conversion (R[-2]C-R[2]C) Issue #2076 PR #2086
• Correctly handle absolute A1 references when converting to R1C1 format PR #2060
• Correct default fill style for conditional without a pattern defined Issue #2035 PR #2050
• Fixed issue where array key check for existince before accessing arrays in Xlsx.php. PR #1970
• Fixed issue with quoted strings in number format mask rendered with toFormattedString() Issue 1972# PR #1978
• Fixed issue with percentage formats in number format mask rendered with toFormattedString() Issue 1929# PR #1928
• Fixed issue with _ spacing character in number format mask corrupting output from toFormattedString() Issue 1924# PR #1927
• Fix for Issue #1887 - Lose Track of Selected Cells After Save
• Fixed issue with Xlsx@listWorksheetInfo not returning any data
• Fixed invalid arguments triggering mb_substr() error in LEFT(), MID() and RIGHT() text functions. Issue #640
• Fix for Issue #1916 - Invalid signature check for XML files
• Fix change in Font::setSize() behavior for PHP8. PR #2100

1.17.1

Added

• Implementation of the Excel AVERAGEIFS() functions as part of a restructuring of Database functions and Conditional Statistical functions.
• Support for date values and percentages in query parameters for Database functions, and the IF expressions in functions like COUNTIF() and AVERAGEIF(). #1875
• Support for booleans, and for wildcard text search in query parameters for Database functions, and the IF expressions in functions like COUNTIF() and AVERAGEIF(). #1876
• Implemented DataBar for conditional formatting in Xlsx, providing read/write and creation of (type, value, direction, fills, border, axis position, color settings) as DataBar options in Excel. #1754
• Alignment for ODS Writer #1796
• Basic implementation of the PERMUTATIONA() Statistical Function

Changed

• Formula functions that previously called PHP functions directly are now processed through the Excel Functions classes; resolving issues with PHP8 stricter typing. #1789

  The following MathTrig functions are affected:
  ABS(), ACOS(), ACOSH(), ASIN(), ASINH(), ATAN(), ATANH(),
  COS(), COSH(), DEGREES() (rad2deg), EXP(), LN() (log), LOG10(),
  RADIANS() (deg2rad), SIN(), SINH(), SQRT(), TAN(), TANH().

  One TextData function is also affected: REPT() (str_repeat).

• formatAsDate correctly matches language metadata, reverting c55272e

• Formulae that previously crashed on sub function call returning excel error value now return said value.
  The following functions are affected CUMPRINC(), CUMIPMT(), AMORLINC(),
  AMORDEGRC().

• Adapt some function error return value to match excel's error.
  The following functions are affected PPMT(), IPMT().

Deprecated

• Calling many of the Excel formula functions directly rather than through the Calculation Engine.

  The logic for these Functions is now being moved out of the categorised Database, DateTime, Engineering, Financial, Logical, LookupRef, MathTrig, Statistical, TextData and Web classes into small, dedicated classes for individual functions or related groups of functions.

  This makes the logic in these classes easier to maintain; and will reduce the memory footprint required to execute formulae when calling these functions.

Removed

• Nothing.

Fixed

• Avoid Duplicate Titles When Reading Multiple HTML Files.Issue #1823 PR #1829
• Fixed issue with Worksheet's getCell() method when trying to get a cell by defined name. #1858
• Fix possible endless loop in NumberFormat Masks #1792
• Fix problem resulting from literal dot inside quotes in number format masks. PR #1830
• Resolve Google Sheets Xlsx charts issue. Google Sheets uses oneCellAnchor positioning and does not include *Cache values in the exported Xlsx. PR #1761
• Fix for Xlsx Chart axis titles mapping to correct X or Y axis label when only one is present. PR #1760
• Fix For Null Exception on ODS Read of Page Settings. #1772
• Fix Xlsx reader overriding manually set number format with builtin number format. PR #1805
• Fix Xlsx reader cell alignment. PR #1710
• Fix for not yet implemented data-types in Open Document writer Issue #1674
• Fix XLSX reader when having a corrupt numeric cell data type PR #1664
• Fix on CUMPRINC(), CUMIPMT(), AMORLINC(), AMORDEGRC() usage. When those functions called one of YEARFRAC(), PPMT(), IPMT() and they would get back an error value (represented as a string), trying to use numeral operands (+, /, -, *) on said return value and a number (float or int`) would fail.

1.17.0

Added

• Implementation of the Excel AVERAGEIFS() functions as part of a restructuring of Database functions and Conditional Statistical functions.
• Support for date values and percentages in query parameters for Database functions, and the IF expressions in functions like COUNTIF() and AVERAGEIF(). #1875
• Support for booleans, and for wildcard text search in query parameters for Database functions, and the IF expressions in functions like COUNTIF() and AVERAGEIF(). #1876
• Implemented DataBar for conditional formatting in Xlsx, providing read/write and creation of (type, value, direction, fills, border, axis position, color settings) as DataBar options in Excel. #1754
• Alignment for ODS Writer #1796
• Basic implementation of the PERMUTATIONA() Statistical Function

Changed

• Formula functions that previously called PHP functions directly are now processed through the Excel Functions classes; resolving issues with PHP8 stricter typing. #1789

  The following MathTrig functions are affected:
  ABS(), ACOS(), ACOSH(), ASIN(), ASINH(), ATAN(), ATANH(),
  COS(), COSH(), DEGREES() (rad2deg), EXP(), LN() (log), LOG10(),
  RADIANS() (deg2rad), SIN(), SINH(), SQRT(), TAN(), TANH().

  One TextData function is also affected: REPT() (str_repeat).

• formatAsDate correctly matches language metadata, reverting c55272e

• Formulae that previously crashed on sub function call returning excel error value now return said value.
  The following functions are affected CUMPRINC(), CUMIPMT(), AMORLINC(),
  AMORDEGRC().

• Adapt some function error return value to match excel's error.
  The following functions are affected PPMT(), IPMT().

Deprecated

• Calling many of the Excel formula functions directly rather than through the Calculation Engine.

  The logic for these Functions is now being moved out of the categorised Database, DateTime, Engineering, Financial, Logical, LookupRef, MathTrig, Statistical, TextData and Web classes into small, dedicated classes for individual functions or related groups of functions.

  This makes the logic in these classes easier to maintain; and will reduce the memory footprint required to execute formulae when calling these functions.

Removed

• Nothing.

Fixed

• Avoid Duplicate Titles When Reading Multiple HTML Files.Issue #1823 PR #1829
• Fixed issue with Worksheet's getCell() method when trying to get a cell by defined name. #1858
• Fix possible endless loop in NumberFormat Masks #1792
• Fix problem resulting from literal dot inside quotes in number format masks. PR #1830
• Resolve Google Sheets Xlsx charts issue. Google Sheets uses oneCellAnchor positioning and does not include *Cache values in the exported Xlsx. PR #1761
• Fix for Xlsx Chart axis titles mapping to correct X or Y axis label when only one is present. PR #1760
• Fix For Null Exception on ODS Read of Page Settings. #1772
• Fix Xlsx reader overriding manually set number format with builtin number format. PR #1805
• Fix Xlsx reader cell alignment. PR #1710
• Fix for not yet implemented data-types in Open Document writer Issue #1674
• Fix XLSX reader when having a corrupt numeric cell data type PR #1664
• Fix on CUMPRINC(), CUMIPMT(), AMORLINC(), AMORDEGRC() usage. When those functions called one of YEARFRAC(), PPMT(), IPMT() and they would get back an error value (represented as a string), trying to use numeral operands (+, /, -, *) on said return value and a number (float or int`) would fail.

1.16.0

Added

• CSV Reader - Best Guess for Encoding, and Handle Null-string Escape #1647

Changed

• Updated the CONVERT() function to support all current MS Excel categories and Units of Measure.

Deprecated

• Nothing.

Removed

• Nothing.

Fixed

• Fix for Xls Reader when SST has a bad length #1592
• Resolve Xlsx loader issue whe hyperlinks don't have a destination
• Resolve issues when printer settings resources IDs clash with drawing IDs
• Resolve issue with SLK long filenames #1612
• ROUNDUP and ROUNDDOWN return incorrect results for values of 0 #1627
• Apply Column and Row Styles to Existing Cells #1712 PR #1721
• Resolve issues with defined names where worksheet doesn't exist (#1686)[https://github.com//issues/1686] and #1723 - PR #1742
• Fix for issue #1735 Incorrect activeSheetIndex after RemoveSheetByIndex - PR #1743
• Ensure that the list of shared formulae is maintained when an xlsx file is chunked with readFilterIssue #169.
• Fix for notice during accessing "cached magnification factor" offset #1354
• Fix compatibility with ext-gd on php 8

Security Fix (CVE-2020-7776)

• Prevent XSS through cell comments in the HTML Writer.

1.15.0

Added

• Implemented Page Order for Xlsx and Xls Readers, and provided Page Settings (Orientation, Scale, Horizontal/Vertical Centering, Page Order, Margins) support for Ods, Gnumeric and Xls Readers #1559
• Implementation of the Excel LOGNORM.DIST(), NORM.S.DIST(), GAMMA() and GAUSS() functions. #1588
• Named formula implementation, and improved handling of Defined Names generally #1535
  • Defined Names are now case-insensitive
  • Distinction between named ranges and named formulae
  • Correct handling of union and intersection operators in named ranges
  • Correct evaluation of named range operators in calculations
  • fix resolution of relative named range values in the calculation engine; previously all named range values had been treated as absolute.
  • Calculation support for named formulae
  • Support for nested ranges and formulae (named ranges and formulae that reference other named ranges/formulae) in calculations
  • Introduction of a helper to convert address formats between R1C1 and A1 (and the reverse)
  • Proper support for both named ranges and named formulae in all appropriate Readers
    • Xlsx (Previously only simple named ranges were supported)
    • Xls (Previously only simple named ranges were supported)
    • Gnumeric (Previously neither named ranges nor formulae were supported)
    • Ods (Previously neither named ranges nor formulae were supported)
    • Xml (Previously neither named ranges nor formulae were supported)
  • Proper support for named ranges and named formulae in all appropriate Writers
    • Xlsx (Previously only simple named ranges were supported)
    • Xls (Previously neither named ranges nor formulae were supported) - Still not supported, but some parser issues resolved that previously failed to differentiate between a defined name and a function name
    • Ods (Previously neither named ranges nor formulae were supported)
• Support for PHP 8.0

Changed

• Improve Coverage for ODS Reader #1545
• Named formula implementation, and improved handling of Defined Names generally #1535
• fix resolution of relative named range values in the calculation engine; previously all named range values had been treated as absolute.
• Drop $this->spreadSheet null check from Xlsx Writer #1646
• Improving Coverage for Excel2003 XML Reader #1557

Deprecated

• IMPORTANT NOTE: This Introduces a BC break in the handling of named ranges. Previously, a named range cell reference of B2 would be treated identically to a named range cell reference of $B2 or B$2 or $B$2 because the calculation engine treated then all as absolute references. These changes "fix" that, so the calculation engine now handles relative references in named ranges correctly.
  This change that resolves previously incorrect behaviour in the calculation may affect users who have dynamically defined named ranges using relative references when they should have used absolute references.

Removed

• Nothing.

Fixed

• PrintArea causes exception #1544
• Calculation/DateTime Failure With PHP8 #1661
• Reader/Gnumeric Failure with PHP8 #1662
• ReverseSort bug, exposed but not caused by PHP8 #1660
• Bug setting Superscript/Subscript to false #1567

1.14.1

Added

• nothing

Fixed

• WEBSERVICE is HTTP client agnostic and must be configured via Settings::setHttpClient() #1562
• Borders were not complete on rowspanned columns using HTML reader #1473

Changed

• nothing

1.14.0

Added

• Add support for IFS() logical function #1442
• Add Cell Address Helper to provide conversions between the R1C1 and A1 address formats #1558
• Add ability to edit Html/Pdf before saving #1499
• Add ability to set codepage explicitly for BIFF5 #1018
• Added support for the WEBSERVICE function #1409

Fixed

• Resolve evaluation of utf-8 named ranges in calculation engine #1522
• Fix HLOOKUP on single row #1512
• Fix MATCH when comparing different numeric types #1521
• Fix exact MATCH on ranges with empty cells #1520
• Fix for Issue #1516 (Cloning worksheet makes corrupted Xlsx) #1530
• Fix For Issue #1509 (Can not set empty enclosure for CSV) #1518
• Fix for Issue #1505 (TypeError : Argument 4 passed to PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet::writeAttributeIf() must be of the type string) #1525
• Fix for Issue #1495 (Sheet index being changed when multiple sheets are used in formula) #1500
• Fix for Issue #1533 (A reference to a cell containing a string starting with "#" leads to errors in the generated xlsx.) #1534
• Xls Writer - Correct Timestamp Bug #1493
• Don't ouput row and columns without any cells in HTML writer #1235