phpoffice/phpspreadsheet

Added

• Support writing to streams in all writers #1292
• Support CSV files with data wrapping a lot of lines #1468
• Support protection of worksheet by a specific hash algorithm #1485

Fixed

• Fix Chart samples by updating chart parameter from 0 to DataSeries::EMPTY_AS_GAP #1448
• Fix return type in docblock for the Cells::get() #1398
• Fix RATE, PRICE, XIRR, and XNPV Functions #1456
• Save Excel 2010+ functions properly in XLSX #1461
• Several improvements in HTML writer #1464
• Fix incorrect behaviour when saving XLSX file with drawings #1462,
• Fix Crash while trying setting a cell the value "123456\n" #1476
• Improved DATEDIF() function and reduced errors for Y and YM units #1466
• Stricter typing for mergeCells #1494

Changed

• Drop support for PHP 7.1, according to https://phpspreadsheet.readthedocs.io/en/latest/#php-version-support
• Drop partial migration tool in favor of complete migration via RectorPHP #1445
• Limit composer package to src/ #1424

Added

• Improved the ARABIC function to also handle short-hand roman numerals
• Added support for the FLOOR.MATH and FLOOR.PRECISE functions #1351

Fixed

• Fix ROUNDUP and ROUNDDOWN for floating-point rounding error #1404
• Fix ROUNDUP and ROUNDDOWN for negative number #1417
• Fix loading styles from vmlDrawings when containing whitespace #1347
• Fix incorrect behavior when removing last row #1365
• MATCH with a static array should return the position of the found value based on the values submitted #1332
• Fix Xlsx Reader's handling of undefined fill color #1353

Added

• Added support for the BASE function
• Added support for the ARABIC function
• Conditionals - Extend Support for (NOT)CONTAINSBLANKS #1278

Fixed

• Handle Error in Formula Processing Better for Xls #1267
• Handle ConditionalStyle NumberFormat When Reading Xlsx File #1296
• Fix Xlsx Writer's handling of decimal commas #1282
• Fix for issue by removing test code mistakenly left in #1328
• Fix for Xls writer wrong selected cells and active sheet #1256
• Fix active cell when freeze pane is used #1323
• Fix XLSX file loading with autofilter containing '$' #1326
• PHPDoc - Use @return $this for fluent methods #1362

Changed

• PHP 7.4 compatibility

Fixed

• FLOOR() function accept negative number and negative significance #1245
• Correct column style even when using rowspan #1249
• Do not confuse defined names and cell refs #1263
• XLSX reader/writer keep decimal for floats with a zero decimal part #1262
• ODS writer prevent invalid numeric value if locale decimal separator is comma #1268
• Xlsx writer actually writes plotVisOnly and dispBlanksAs from chart properties #1266

Changed

• Change license from LGPL 2.1 to MIT #140

Added

• Implementation of IFNA() logical function
• Support "showZeros" worksheet option to change how Excel shows and handles "null" values returned from a calculation
• Allow HTML Reader to accept HTML as a string into an existing spreadsheet #1212

Fixed

• IF implementation properly handles the value #N/A #1165
• Formula Parser: Wrong line count for stuff like "MyOtherSheet!A:D" #1215
• Call garbage collector after removing a column to prevent stale cached values
• Trying to remove a column that doesn't exist deletes the latest column
• Keep big integer as integer instead of lossely casting to float #874
• Fix branch pruning handling of non boolean conditions #1167
• Fix ODS Reader when no DC namespace are defined #1182
• Fixed Functions->ifCondition for allowing <> and empty condition #1206
• Validate XIRR inputs and return correct error values #1120
• Allow to read xlsx files with exotic workbook names like "workbook2.xml" #1183

Changed

• Drop support for PHP 5.6 and 7.0, according to https://phpspreadsheet.readthedocs.io/en/latest/#php-version-support

Added

• When <br> appears in a table cell, set the cell to wrap #1071 and #1070
• Add MAXIFS, MINIFS, COUNTIFS and Remove MINIF, MAXIF #1056
• HLookup needs an ordered list even if range_lookup is set to false #1055 and #1076
• Improve performance of IF function calls via ranch pruning to avoid resolution of every branches #844
• MATCH function supports *?~ Excel functionality, when match_type=0 #1116
• Allow HTML Reader to accept HTML as a string #1136

Fixed

• Fix to AVERAGEIF() function when called with a third argument
• Eliminate duplicate fill none style entries #1066
• Fix number format masks containing literal (non-decimal point) dots #1079
• Fix number format masks containing named colours that were being misinterpreted as date formats; and add support for masks that fully replace the value with a full text string #1009
• Stricter-typed comparison testing in COUNTIF() and COUNTIFS() evaluation #1046
• COUPNUM should not return zero when settlement is in the last period #1020 and #1021
• Fix handling of named ranges referencing sheets with spaces or "!" in their title
• Cover getSheetByName() with tests for name with quote and spaces #739
• Best effort to support invalid colspan values in HTML reader - #878
• Fixes incorrect rows deletion #868
• MATCH function fix (value search by type, stop search when match_type=-1 and unordered element encountered) #1116
• Fix getCalculatedValue() error with more than two INDIRECT #1115
• Writer\Html did not hide columns #985

Fixed

• Uncaught error when opening ods file and properties aren't defined #1047
• Xlsx Reader Cell datavalidations bug #1052

Fixed

• Allow nullable theme for Xlsx Style Reader class #1043

Security Fix (CVE-2019-12331)

• Detect double-encoded xml in the Security scanner, and reject as suspicious.

• This change also broadens the scope of the libxml_disable_entity_loader setting when reading XML-based formats, so that it is enabled while the xml is being parsed and not simply while it is loaded. On some versions of PHP, this can cause problems because it is not thread-safe, and can affect other PHP scripts running on the same server. This flag is set to true when instantiating a loader, and back to its original setting when the Reader is no longer in scope, or manually unset.

• Provide a check to identify whether libxml_disable_entity_loader is thread-safe or not.

  XmlScanner::threadSafeLibxmlDisableEntityLoaderAvailability()

• Provide an option to disable the libxml_disable_entity_loader call through settings. This is not recommended as it reduces the security of the XML-based readers, and should only be used if you understand the consequences and have no other choice.

Added

• Added support for the SWITCH function #963 and #983
• Add accounting number format style #974

Fixed

• Whitelist tsv extension when opening CSV files #429
• Fix a SUMIF warning with some versions of PHP when having different length of arrays provided as input #873
• Fix incorrectly handled backslash-escaped space characters in number format

• Added support for inline styles in Html reader (borders, alignment, width, height)
• QuotedText cells no longer treated as formulae if the content begins with a =
• Clean handling for DDE in formulae

Fixed

• Fix handling for escaped enclosures and new lines in CSV Separator Inference
• Fix MATCH an error was appearing when comparing strings against 0 (always true)
• Fix wrong calculation of highest column with specified row #700
• Fix VLOOKUP
• Fix return type hint

Added

• Refactored Matrix Functions to use external Matrix library
• Possibility to specify custom colors of values for pie and donut charts #768

Fixed

• Improve XLSX parsing speed if no readFilter is applied #772
• Fix column names if read filter calls in XLSX reader skip columns #777
• XLSX reader can now ignore blank cells, using the setReadEmptyCells(false) method. #810
• Fix LOOKUP function which was breaking on edge cases #796
• Fix VLOOKUP with exact matches #809
• Support COUNTIFS multiple arguments #830
• Change libxml_disable_entity_loader() as shortly as possible #819
• Improved memory usage and performance when loading large spreadsheets #822
• Improved performance when loading large spreadsheets #825
• Improved performance when loading large spreadsheets #824
• Fix color from CSS when reading from HTML #831
• Fix infinite loop when reading invalid ODS files #832
• Fix time format for duration is incorrect #666
• Fix iconv unsupported //IGNORE//TRANSLIT on IBM i #791

Changed

• master is the new default branch, develop does not exist anymore

Security

• Improvements to the design of the XML Security Scanner #771

Security

• Fix and improve XXE security scanning for XML-based and HTML Readers #771

Added

• Support page margin in mPDF #750

Fixed

• Support numeric condition in SUMIF, SUMIFS, AVERAGEIF, COUNTIF, MAXIF and MINIF #683
• SUMIFS containing multiple conditions #704
• Csv reader avoid notice when the file is empty #743
• Fix print area parser for XLSX reader #734
• Support overriding DefaultValueBinder::dataTypeForValue() without overriding DefaultValueBinder::bindValue() #735
• Mpdf export can exceed pcre.backtrack_limit #637
• Fix index overflow on data values array #748

Added

• PHP 7.3 support
• Add the DAYS() function #594

Fixed

• Sheet title can contain exclamation mark #325
• Xls file cause the exception during open by Xls reader #402
• Skip non numeric value in SUMIF #618
• OFFSET should allow omitted height and width #561
• Correctly determine delimiter when CSV contains line breaks inside enclosures #716

Fixed

• Remove locale from formatting string #644
• Allow iterators to go out of bounds with prev #587
• Fix warning when reading xlsx without styles #631
• Fix broken sample links on windows due to $baseDir having backslash #653

Added

• Add excel function EXACT(value1, value2) support #595
• Support workbook view attributes for Xlsx format #523
• Read and write hyperlink for drawing image #490
• Added calculation engine support for the new bitwise functions that were added in MS Excel 2013
  • BITAND() Returns a Bitwise 'And' of two numbers
  • BITOR() Returns a Bitwise 'Or' of two number
  • BITXOR() Returns a Bitwise 'Exclusive Or' of two numbers
  • BITLSHIFT() Returns a number shifted left by a specified number of bits
  • BITRSHIFT() Returns a number shifted right by a specified number of bits
• Added calculation engine support for other new functions that were added in MS Excel 2013 and MS Excel 2016
  • Text Functions
    • CONCAT() Synonym for CONCATENATE()
    • NUMBERVALUE() Converts text to a number, in a locale-independent way
    • UNICHAR() Synonym for CHAR() in PHPSpreadsheet, which has always used UTF-8 internally
    • UNIORD() Synonym for ORD() in PHPSpreadsheet, which has always used UTF-8 internally
    • TEXTJOIN() Joins together two or more text strings, separated by a delimiter
  • Logical Functions
    • XOR() Returns a logical Exclusive Or of all arguments
  • Date/Time Functions
    • ISOWEEKNUM() Returns the ISO 8601 week number of the year for a given date
  • Lookup and Reference Functions
    • FORMULATEXT() Returns a formula as a string
  • Financial Functions
    • PDURATION() Calculates the number of periods required for an investment to reach a specified value
    • RRI() Calculates the interest rate required for an investment to grow to a specified future value
  • Engineering Functions
    • ERF.PRECISE() Returns the error function integrated between 0 and a supplied limit
    • ERFC.PRECISE() Synonym for ERFC
  • Math and Trig Functions
    • SEC() Returns the secant of an angle
    • SECH() Returns the hyperbolic secant of an angle
    • CSC() Returns the cosecant of an angle
    • CSCH() Returns the hyperbolic cosecant of an angle
    • COT() Returns the cotangent of an angle
    • COTH() Returns the hyperbolic cotangent of an angle
    • ACOT() Returns the cotangent of an angle
    • ACOTH() Returns the hyperbolic cotangent of an angle
• Refactored Complex Engineering Functions to use external complex number library
• Added calculation engine support for the new complex number functions that were added in MS Excel 2013
  • IMCOSH() Returns the hyperbolic cosine of a complex number
  • IMCOT() Returns the cotangent of a complex number
  • IMCSC() Returns the cosecant of a complex number
  • IMCSCH() Returns the hyperbolic cosecant of a complex number
  • IMSEC() Returns the secant of a complex number
  • IMSECH() Returns the hyperbolic secant of a complex number
  • IMSINH() Returns the hyperbolic sine of a complex number
  • IMTAN() Returns the tangent of a complex number

Fixed

• Fix ISFORMULA() function to work with a cell reference to another worksheet
• Xlsx reader crashed when reading a file with workbook protection #553
• Cell formats with escaped spaces were causing incorrect date formatting #557
• Could not open CSV file containing HTML fragment #564
• Exclude the vendor folder in migration #481
• Chained operations on cell ranges involving borders operated on last cell only #428
• Avoid memory exhaustion when cloning worksheet with a drawing #437
• Migration tool keep variables containing $PHPExcel untouched #598
• Rowspans/colspans were incorrect when adding worksheet using loadIntoExisting #619

Fixed

• Ranges across Z and AA columns incorrectly threw an exception #545

Added

• Support to read Xlsm templates with form elements, macros, printer settings, protected elements and back compatibility drawing, and save result without losing important elements of document #435
• Expose sheet title maximum length as Worksheet::SHEET_TITLE_MAXIMUM_LENGTH #482
• Allow escape character to be set in CSV reader #492

Fixed

• Subtotal 9 in a group that has other subtotals 9 exclude the totals of the other subtotals in the range #332
• Helper\Html support UTF-8 HTML input #444
• Xlsx loaded an extra empty comment for each real comment #375
• Xlsx reader do not read rows and columns filtered out in readFilter at all #370
• Make newer Excel versions properly recalculate formulas on document open #456
• Coordinate::extractAllCellReferencesInRange() throws an exception for an invalid range #519
• Fixed parsing of conditionals in COUNTIF functions #526
• Corruption errors for saved Xlsx docs with frozen panes #532

Fixed

• Plain text and richtext mixed in same cell can be read #442

Added

• HTML writer creates a generator meta tag #312
• Support invalid zoom value in XLSX format #350
• Support for _xlfn. prefixed functions and ISFORMULA, MODE.SNGL, STDEV.S, STDEV.P #390

Fixed

• Avoid potentially unsupported PSR-16 cache keys #354
• Check for MIME type to know if CSV reader can read a file #167
• Use proper € symbol for currency format #379
• Read printing area correctly when skipping some sheets #371
• Avoid incorrectly overwriting calculated value type #394
• Select correct cell when calling freezePane #389
• setStrikethrough() did not set the font #403

Added

• Support for PHP 7.2
• Support cell comments in HTML writer and reader #308
• Option to stop at a conditional styling, if it matches (only XLSX format) #292
• Support for line width for data series when rendering Xlsx #329

Fixed

• Better auto-detection of CSV separators #305
• Support for shape style ending with ; #304
• Freeze Panes takes wrong coordinates for XLSX #322
• COLUMNS and ROWS functions crashed in some cases #336
• Support XML file without styles #331
• Cell coordinates which are already a range cause an exception #319

Added

• Support to write merged cells in ODS format #287
• Able to set the topLeftCell in freeze panes #261
• Support DateTimeImmutable as cell value
• Support migration of prefixed classes

Fixed

• Can read very small HTML files #194
• Written DataValidation was corrupted #290
• Date format compatible with both LibreOffice and Excel #298

BREAKING CHANGE

• Constant TYPE_DOUGHTNUTCHART is now TYPE_DOUGHNUTCHART.

Added

• Support for chart fill color - @CrazyBite #158
• Support for read Hyperlink for xml - @GreatHumorist #223
• Support for cell value validation according to data validation rules - @SailorMax #257
• Support for custom implementation, or configuration, of PDF libraries - @SailorMax #266

Changed

• Merge data-validations to reduce written worksheet size - @billblume #131
• Throws exception if a XML file is invalid - @GreatHumorist #222
• Upgrade to mPDF 7.0+ #144

Fixed

• Control characters in cell values are automatically escaped #212
• Prevent color changing when copy/pasting xls files written by PhpSpreadsheet to another file - @al-lala #218
• Add cell reference automatic when there is no cell reference('r' attribute) in Xlsx file. - @GreatHumorist #225 Refer to #201
• Reader\Xlsx::getFromZipArchive() function return false if the zip entry could not be located. - @anton-harvey #268

BREAKING CHANGE

• Extracted coordinate method to dedicate class migration guide.
• Column indexes are based on 1, see the migration guide.
• Standardization of array keys used for style, see the migration guide.
• Easier usage of PDF writers, and other custom readers and writers, see the migration guide.
• Easier usage of chart renderers, see the migration guide.
• Rename a few more classes to keep them in their related namespaces:
  • CalcEngine => Calculation\Engine
  • PhpSpreadsheet\Calculation => PhpSpreadsheet\Calculation\Calculation
  • PhpSpreadsheet\Cell => PhpSpreadsheet\Cell\Cell
  • PhpSpreadsheet\Chart => PhpSpreadsheet\Chart\Chart
  • PhpSpreadsheet\RichText => PhpSpreadsheet\RichText\RichText
  • PhpSpreadsheet\Style => PhpSpreadsheet\Style\Style
  • PhpSpreadsheet\Worksheet => PhpSpreadsheet\Worksheet\Worksheet

Added

• Initial implementation of SUMIFS() function
• Additional codepages
• MemoryDrawing not working in HTML writer #808
• CSV Reader can auto-detect the separator used in file #141
• HTML Reader supports some basic inline styles #180

Changed

• Start following SemVer properly.

Fixed

• Fix to getCell() method when cell reference includes a worksheet reference - @MarkBaker
• Ignore inlineStr type if formula element exists - @ncrypthic #570
• Excel 2007 Reader freezes because of conditional formatting - @rentalhost #575
• Readers will now parse files containing worksheet titles over 31 characters #176
• Fixed PHP8 deprecation warning for libxml_disable_entity_loader() #1625

General

• Whitespace after toRichTextObject() - @MarkBaker #554
• Optimize vlookup() sort - @umpirsky #548
• c:max and c:min elements shall NOT be inside c:orientation elements - @vitalyrepin #869
• Implement actual timezone adjustment into PHPExcel_Shared_Date::PHPToExcel - @sim642 #489

BREAKING CHANGE

• Introduction of namespaces for all classes, eg: PHPExcel_Calculation_Functions becomes PhpOffice\PhpSpreadsheet\Calculation\Functions
• Some classes were renamed for clarity and/or consistency:

For a comprehensive list of all class changes, and a semi-automated migration path, read the migration guide.

• Dropped PHPExcel_Calculation_Functions::VERSION(). Composer or git should be used to know the version.
• Dropped PHPExcel_Settings::setPdfRenderer() and PHPExcel_Settings::setPdfRenderer(). Composer should be used to autoload PDF libs.
• Dropped support for HHVM