PhpSpreadsheet v1.23.0 Release Notes
Release Date: 2022-04-24 // 2 months ago-
โ Added
- ๐ Ods Writer support for Freeze Pane Issue #2013 PR #2755
- ๐ Ods Writer support for setting column width/row height (including the use of AutoSize) Issue #2346 PR #2753
- Introduced CellAddress, CellRange, RowRange and ColumnRange value objects that can be used as an alternative to a string value (e.g.
'C5'
,'B2:D4'
,'2:2'
or'B:C'
) in appropriate contexts. - Implementation of the FILTER(), SORT(), SORTBY() and UNIQUE() Lookup/Reference (array) functions.
- Implementation of the ISREF() Information function.
- โ Added support for reading "formatted" numeric values from Csv files; although default behaviour of reading these values as strings is preserved.
(i.e a value of "12,345.67" can be read as numeric
12345.67
, not simply as a string"12,345.67"
, if thecastFormattedNumberToNumeric()
setting is enabled.This functionality is locale-aware, using the server's locale settings to identify the thousands and decimal separators.
- ๐ Support for two cell anchor drawing of images. #2532 #2674
- ๐ Limited support for Xls Reader to handle Conditional Formatting:
Ranges and Rules are read, but style is currently limited to font size, weight and color; and to fill style and color.
- โ Add ability to suppress Mac line ending check for CSV #2623
- ๐ Initial support for creating and writing Tables (Xlsx Writer only) PR #2671
See
/samples/Table
for examples of use.Note that PreCalculateFormulas needs to be disabled when saving spreadsheets containing tables with formulae (totals or column formulae).
๐ Changed
- Gnumeric Reader now loads number formatting for cells.
- Gnumeric Reader now correctly identifies selected worksheet and selected cells in a worksheet.
- ๐จ Some Refactoring of the Ods Reader, moving all formula and address translation from Ods to Excel into a separate class to eliminate code duplication and ensure consistency.
- ๐ Make Boolean Conversion in Csv Reader locale-aware when using the String Value Binder.
This is determined by the Calculation Engine locale setting.
(i.e.
"Vrai"
wil be converted to a booleantrue
if the Locale is set tofr
.)- ๐ Allow
psr/simple-cache
2.x
๐ Deprecated
- ๐ All Excel Function implementations in
Calculation\Functions
(including the Error functions) have been moved to dedicated classes for 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. - Worksheet methods that reference cells "byColumnandRow". All such methods have an equivalent that references the cell by its address (e.g. '
E3'
rather than5, 3
).
These functions now accept either a cell address string (
'E3')
or an array with columnId and rowId ([5, 3]
) or a newCellAddress
object as theircellAddress
/coordinate
argument. This includes the methods:setCellValueByColumnAndRow()
use the equivalentsetCellValue()
setCellValueExplicitByColumnAndRow()
use the equivalentsetCellValueExplicit()
getCellByColumnAndRow()
use the equivalentgetCell()
cellExistsByColumnAndRow()
use the equivalentcellExists()
getStyleByColumnAndRow()
use the equivalentgetStyle()
setBreakByColumnAndRow()
use the equivalentsetBreak()
mergeCellsByColumnAndRow()
use the equivalentmergeCells()
unmergeCellsByColumnAndRow()
use the equivalentunmergeCells()
protectCellsByColumnAndRow()
use the equivalentprotectCells()
unprotectCellsByColumnAndRow()
use the equivalentunprotectCells()
setAutoFilterByColumnAndRow()
use the equivalentsetAutoFilter()
freezePaneByColumnAndRow()
use the equivalentfreezePane()
getCommentByColumnAndRow()
use the equivalentgetComment()
setSelectedCellByColumnAndRow()
use the equivalentsetSelectedCells()
This change provides more consistency in the methods (not every "by cell address" method has an equivalent "byColumnAndRow" method); and the "by cell address" methods often provide more flexibility, such as allowing a range of cells, or referencing them by passing the defined name of a named range as the argument.
โ Removed
- Nothing
๐ Fixed
- ๐ Make allowance for the AutoFilter dropdown icon in the first row of an Autofilter range when using Autosize columns. Issue #2413 PR #2754
- ๐ Support for "chained" ranges (e.g.
A5:C10:C20:F1
) in the Calculation Engine; and also support for using named ranges with the Range operator (e.g.NamedRange1:NamedRange2
) Issue #2730 PR #2746 - โก๏ธ Update Conditional Formatting ranges and rule conditions when inserting/deleting rows/columns Issue #2678 PR #2689
- ๐ Allow
INDIRECT()
to accept row/column ranges as well as cell ranges PR #2687 - ๐ Fix bug when deleting cells with hyperlinks, where the hyperlink was then being "inherited" by whatever cell moved to that cell address.
- ๐ Fix bug in Conditional Formatting in the Xls Writer that resulted in a broken file when there were multiple conditional ranges in a worksheet.
- ๐ Fix Conditional Formatting in the Xls Writer to work with rules that contain string literals, cell references and formulae.
- ๐ Fix for setting Active Sheet to the first loaded worksheet when bookViews element isn't defined Issue #2666 PR #2669
- ๐ Fixed behaviour of XLSX font style vertical align settings PR #2619
- ๐ Resolved formula translations to handle separators (row and column) for array functions as well as for function argument separators; and cleanly handle nesting levels.
Note that this method is used when translating Excel functions between
en_us
and other locale languages, as well as when converting formulae between different spreadsheet formats (e.g. Ods to Excel).Nor is this a perfect solution, as there may still be issues when function calls have array arguments that themselves contain function calls; but it's still better than the current logic.
- ๐ Fix for escaping double quotes within a formula Issue #1971 PR #2651
- ๐ Change open mode for output from
wb+
towb
Issue #2372 PR #2657 - ๐ Use color palette if supplied Issue #2499 PR #2595
- Xls reader treat drawing offsets as int rather than float PR #2648
- ๐ Handle booleans in conditional styles properly PR #2654
- ๐ Fix for reading files in the root directory of a ZipFile, which should not be prefixed by relative paths ("./") as dirname($filename) does by default.
- ๐ Fix invalid style of cells in empty columns with columnDimensions and rows with rowDimensions in added external sheet. PR #2739
- Time Interval Formatting Issue #2768 PR #2772
Previous changes from v1.22.0
-
โ Added
๐ Namespacing phase 2 - styles. PR #2471
๐ Improved support for passing of array arguments to Excel function implementations to return array results (where appropriate). Issue #2551
This is the first stage in an ongoing process of adding array support to all appropriate function implementations,
- ๐ Support for the Excel365 Math/Trig SEQUENCE() function PR #2536
- ๐ Support for the Excel365 Math/Trig RANDARRAY() function PR #2540
Note that the Spill Operator is not yet supported in the Calculation Engine; but this can still be useful for defining array constants.
๐ Improved support for Conditional Formatting Rules PR #2491
- Provide support for a wider range of Conditional Formatting Rules for Xlsx Reader/Writer:
- Cells Containing (cellIs)
- Specific Text (containing, notContaining, beginsWith, endsWith)
- Dates Occurring (all supported timePeriods)
- Blanks/NoBlanks
- Errors/NoErrors
- Duplicates/Unique
- Expression
- Provision of CF Wizards (for all the above listed rule types) to help create/modify CF Rules without having to manage all the combinations of types/operators, and the complexities of formula expressions, or the text/timePeriod attributes.
See documentation for details
- Full support of the above CF Rules for the Xlsx Reader and Writer; even when the file being loaded has CF rules listed in the
<extLst><ext><ConditionalFormattings>
element for the worksheet rather than the<ConditionalFormatting>
element. - Provision of a CellMatcher to identify if rules are matched for a cell, and which matching style will be applied.
- Improved documentation and examples, covering all supported CF rule types.
- Add support for one digit decimals (FORMAT_NUMBER_0, FORMAT_PERCENTAGE_0). PR #2525
- Initial work enabling Excel function implementations for handling arrays as arguments when used in "array formulae" #2562
- Enable most of the Date/Time functions to accept array arguments #2573
- Array ready functions - Text, Math/Trig, Statistical, Engineering and Logical #2580
๐ Changed
- โ Additional Russian translations for Excel Functions (courtesy of aleks-samurai).
- ๐ Improved code coverage for NumberFormat. PR #2556
- Extract some methods from the Calculation Engine into dedicated classes #2537
- Eliminate calls to
flattenSingleValue()
that are no longer required when we're checking for array values as arguments #2590
๐ Deprecated
- Nothing
โ Removed
- Nothing
๐ Fixed
- ๐ Fixed
[email protected]
behavior when removing column before last column with null value PR #2541 - ๐ Fix bug with
DOLLARDE()
andDOLLARFR()
functions when the dollar value is negative Issue #2578 PR #2579 - ๐ Fix partial function name matching when translating formulae from Russian to English Issue #2533 PR #2534
- Various bugs related to Conditional Formatting Rules, and errors in the Xlsx Writer for Conditional Formatting PR #2491
- ๐ Xlsx Reader merge range fixes. Issue #2501 PR #2504
- ๐ Handle explicit "date" type for Cell in Xlsx Reader. Issue #2373 PR #2485
- ๐ Recalibrate Row/Column Dimensions after removeRow/Column. Issue #2442 PR #2486
- Refinement for XIRR. Issue #2469 PR #2487
- Xlsx Reader handle cell with non-null explicit type but null value. Issue #2488 PR #2489
- Xlsx Reader fix height and width for oneCellAnchorDrawings. PR #2492
- Fix rounding error in NumberFormat::NUMBER_PERCENTAGE, NumberFormat::NUMBER_PERCENTAGE_00. PR #2555
- Don't treat thumbnail file as xml. Issue #2516 PR #2517
- โ Eliminating Xlsx Reader warning when no sz tag for RichText. Issue #2542 PR #2550
- ๐ Fix Xlsx/Xls Writer handling of inline strings. Issue #353 PR #2569
- Richtext colors were not being read correctly after namespace change #2458
- ๐ Fix discrepancy between the way markdown tables are rendered in ReadTheDocs and in PHPStorm #2520
- โก๏ธ Update Russian Functions Text File #2557
- ๐ Fix documentation, instantiation example #2564