A pure PHP library for reading and writing spreadsheet files
Note that this will be the last 1.x branch release before the 2.x release. We will maintain both branches in parallel for a time; but users are requested to update to version 2.0 once that is fully available.
Added removeComment() method for Worksheet PR #2875
Add point size option for scatter charts Issue #2298 PR #2801
Basic support for Xlsx reading/writing Chart Sheets PR #2830
Note that a ChartSheet is still only written as a normal Worksheet containing a single chart, not as an actual ChartSheet.
Added Worksheet visibility in Ods Reader PR #2851 and Gnumeric Reader PR #2853
Added Worksheet visibility in Ods Writer PR #2850
Allow Csv Reader to treat string as contents of file Issue #1285 PR #2792
Allow Csv Reader to store null string rather than leave cell empty Issue #2840 PR #2842
Provide new Worksheet methods to identify if a row or column is "empty", making allowance for different definitions of "empty":
Modify rangeBoundaries(), rangeDimension() and getRangeBoundaries() Coordinate methods to work with row/column ranges as well as with cell ranges and cells PR #2926
Better enforcement of value modification to match specified datatype when using setValueExplicit()
Relax validation of merge cells to allow merge for a single cell reference Issue #2776
Memory and speed improvements, particularly for the Cell Collection, and the Writers.
See the Discussion section on github for details of performance across versions
Improved performance for removing rows/columns from a worksheet
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 the castFormattedNumberToNumeric() setting is enabled.
This functionality is locale-aware, using the server's locale settings to identify the thousands and decimal separators.
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).
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 boolean true if the Locale is set to fr.)
Allow psr/simple-cache 2.x
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 than 5, 3).
These functions now accept either a cell address string ('E3') or an array with columnId and rowId ([5, 3]) or a new CellAddress object as their cellAddress/coordinate argument.
This includes the methods:
setCellValueByColumnAndRow() use the equivalent setCellValue()
setCellValueExplicitByColumnAndRow() use the equivalent setCellValueExplicit()
getCellByColumnAndRow() use the equivalent getCell()
cellExistsByColumnAndRow() use the equivalent cellExists()
getStyleByColumnAndRow() use the equivalent getStyle()
setBreakByColumnAndRow() use the equivalent setBreak()
mergeCellsByColumnAndRow() use the equivalent mergeCells()
unmergeCellsByColumnAndRow() use the equivalent unmergeCells()
protectCellsByColumnAndRow() use the equivalent protectCells()
unprotectCellsByColumnAndRow() use the equivalent unprotectCells()
setAutoFilterByColumnAndRow() use the equivalent setAutoFilter()
freezePaneByColumnAndRow() use the equivalent freezePane()
getCommentByColumnAndRow() use the equivalent getComment()
setSelectedCellByColumnAndRow() use the equivalent setSelectedCells()
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.
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+ to wb 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
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:
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
flattenSingleValue() that are no longer required when we're checking for array values as arguments #2590
ReferenceHelper@insertNewBefore behavior when removing column before last column with null value
PR #2541
DOLLARDE() and DOLLARFR() functions when the dollar value is negative Issue #2578 PR #2579
Comment::setSizeAsBackgroundImage() to change the size of a comment to the size of a background image. Issue #1547 PR #2422
NumberFormatter Issue #2385 PR #2399
# in name Issue #2405 PR #2409
insertNewBefore PR #2433
AutoFilter::setRange() Issue #2281 PR #2454
load() and Writer save()methods, and through the IOFactory, to set behaviours PR #2136
px, pt, pc, in, cm, mm) PR #2152
px, pt, pc, in, cm, mm), rather than only in points or MS Excel column width units PR #2152
PPMT(), PMT(), FV(), PV(), NPER(), etc) Issue #2163 PR #2164
Style::applyFromArray() when applied to several cells PR #1785.IOFactory::load() with a callback to set delimiter, enclosure, charset etc. PR #2103 - See documentation for details.nb rather than no as the locale code for Norsk Bokmål.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.nb rather than no as the locale language code for Norsk Bokmål.R[-2]C-R[2]C) Issue #2076 PR #2086
Font::setSize() behavior for PHP8. PR #2100
AVERAGEIFS() functions as part of a restructuring of Database functions and Conditional Statistical functions.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().
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.
getCell() method when trying to get a cell by defined name. #1858
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.AVERAGEIFS() functions as part of a restructuring of Database functions and Conditional Statistical functions.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().
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.
getCell() method when trying to get a cell by defined name. #1858
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.