PhpSpreadsheet - a library written in pure PHP and offers a set of classes that allow you to read and write various spreadsheet file formats such as Excel and LibreOffice Calc

PHPOffice

Last update: Dec 31, 2022

Related tags

Office php excel libreoffice xlsx xls spreadsheet office gnumeric hacktoberfest ods phpexcel msexcel sylk

Overview

PhpSpreadsheet

PhpSpreadsheet is a library written in pure PHP and offers a set of classes that allow you to read and write various spreadsheet file formats such as Excel and LibreOffice Calc.

Documentation

Read more about it, including install instructions, in the official documentation. Or check out the API documentation.

Please ask your support questions on StackOverflow, or have a quick chat on Gitter.

PHPExcel vs PhpSpreadsheet ?

PhpSpreadsheet is the next version of PHPExcel. It breaks compatibility to dramatically improve the code base quality (namespaces, PSR compliance, use of latest PHP language features, etc.).

Because all efforts have shifted to PhpSpreadsheet, PHPExcel will no longer be maintained. All contributions for PHPExcel, patches and new features, should target PhpSpreadsheet master branch.

Do you need to migrate? There is an automated tool for that.

License

PhpSpreadsheet is licensed under MIT.

Comments

Change license from LGPL 2.1 to MIT ?

From what I understand, notably from a similar move from jQuery, it would technically be legal to change our license from LGPL to MIT. The reasons for such a move would to get rid of the boilerplate header in all files (which is not always consistent in our case), and make the end-user life easier by choosing a well-known, very permissive and broadly used license.

But I am not a lawyer, and I may be missing something. Is it really doable ? is there anything else to do than edit the files ? should we contact previous contributors ?

@MarkBaker, @maartenba as the two most prominent contributors, would you have any opinion on the matter ?

Also @Progi1984 what was the rationale for choosing LGPL 3 for PhpWord ? I see it was talked about in https://github.com/PHPOffice/PHPWord/issues/211, but would your mind has changed in the last 3 years ?

opened by PowerKiKi 119

Read XLSX with namespaces in their XML

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Xlsx that contains namespaces in their XML are correctly loaded

What is the current behavior?

The array of data after reading the xlsx is empty

What are the steps to reproduce?

I'm using the following code to read this file: example.xlsx

<?php
		$reader = IOFactory::createReaderForFile($filePath);
		$spreadsheet=$reader->load($filePath);
		$sheet=$spreadsheet->getSheet(0);
                print_r($sheet->toArray());
?>

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 1.6
PHP >= 5.6

Solution

As requested in #206, I tried to find a better solution instead of just str_replace. As I'm not an expert in XLSX and XML, I don't know if it works correctly for all cases.

All tests I've done are OK with this solution, so can you take a look this and give me your review? If it's seems good, I can submit a pull request with this.

private function removeXMLNamespaces($xml)
{
	$nameSpaceToRemove = array('x','d');

		if(!empty($xml))
		{
			$simpleXMLElement=new \SimpleXMLElement($xml);
			$domSimpleXMLElement=dom_import_simplexml($simpleXMLElement);

			if(!is_null($domSimpleXMLElement))
			{
				$dom=new \DOMDocument('1.0');
				$domSimpleXMLElement=$dom->importNode($domSimpleXMLElement,true);
				$domSimpleXMLElement=$dom->appendChild($domSimpleXMLElement);

				$element=$dom->childNodes->item(0);

				foreach($simpleXMLElement->getDocNamespaces() as $name=>$uri)
				{
					if(in_array($name, $nameSpaceToRemove))
						$element->removeAttributeNS($uri,$name);
				}

				return $dom->saveXML();
			}
			else
				return $xml;
		}
		else
			return $xml;
}

reader/xlsx

opened by JulienChavee 37

Created spreadsheets are corrupted
This is:

[X] a bug report

[ ] a feature request

[ ] not a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Serves blank spreadsheet to the browser

What is the current behavior?

Serves a corrupted excel file. Excel error message:

We found a problem with some content in test.xlsx. Do you want us to try and recover as much as we can? If you trust the source of this workbook, click Yes.

After Repair I get the following message:

Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.

What are the steps to reproduce?

Run the code below

<?php require __DIR__ . '/vendor/autoload.php'; // add code that show the issue here... $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); // Redirect output to a client’s web browser (Xlsx) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="test.xlsx"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header('Pragma: public'); // HTTP/1.0 $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output');

Which versions of PhpSpreadsheet and PHP are affected?

PHP: v7.0.22 Excel: 2016 OS: Windows 10, 64bit Webserver: Apache httpd-2.4.27-win64-VC14 (via Laragon v3.1.4)

Notes

The same behaviour exists if I load a blank excel file via load()
opened by alex6169 35
Reading large File increases Execution time & Memory (E.g file with 500000 records)
This is: a feature request

- [ ] a bug report - [ ] a feature request - [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

To Read large files quickly

What is the current behavior?

Takes more time to Read

What are the steps to reproduce?

Try loading csv file of 500000 records and split it into smaller set of files of about 100000 records each using chunks which will be 5 files.

As the size of file keeps reducing, performance increases

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php require __DIR__ . '/vendor/autoload.php'; // Create new Spreadsheet object $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); // add code that show the issue here...

Which versions of PhpSpreadsheet and PHP are affected?
enhancement reader/xlsx stale
opened by kumarkal 31

Excel from template didn't copy charts.

This is:

- [ ] a bug report
- [x] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Should copy Charts if there exists.

What is the current behavior?

Using "Generating Excel files from templates (read, modify, write)" on a file where created by Excel and if it contains Charts, the Charts are not saved. The name of worksheet is copied but the Chart not.

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = IOFactory::load("template.xlsx");
$spreadsheet->setActiveSheetIndexByName('score');
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->fromArray($score_v);

for ($i=1; $i <= count($score_v); $i++) { 
    $worksheet->setCellValue('E'.$i, '50');
}

// Save Excel 2007 file
$filename = 'ok.xlsx';
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save($filename);
// add code that show the issue here...

Which versions of PhpSpreadsheet and PHP are affected?

i'm using the current version.

invalid

opened by Bust3D 28

xls file cause the exception during open by Xls reader

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Excel file opened for reading/write I tried to use deprecated PhpExcel classes and the file opens successfully!

What is the current behavior?

Trying to open test excel file cause the exception:

Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Reader\Exception: Parameter data is empty. in /home/.../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/OLERead.php:331 Stack trace: #0 /home/.../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/OLERead.php(281): PhpOffice\PhpSpreadsheet\Shared\OLERead::getInt4d('\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00...', 116) #1 /home/.../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/OLERead.php(187): PhpOffice\PhpSpreadsheet\Shared\OLERead->readPropertySets() #2 /home/.../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xls.php(1363): PhpOffice\PhpSpreadsheet\Shared\OLERead->read('/tmp/phpk3bjPS') #3 /home/.../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xls.php(628): PhpOffice\PhpSpreadsheet\Reader\ in /home/.../vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/OLERead.php on line 331

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
  $spreadsheet = $reader->load($_FILES['EXP_FILE']['tmp_name']);
  var_dump($spreadsheet->getSheet(0)->getCell('A1')->getValue());die;

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 1.1 php 7.1

test.zip

bug help wanted reader/xls

opened by odyswise 27

PHP 7.1: Numbers shown with scientific notation even if explicitely formatted as text
This is:

- [x] a bug report

With PHP 7.1, if I put a number in a cell and set explicitely a text format, it's shown with scientific notation in Excel, instead of showing the number "as is".

Maybe related with this BC break introduced in 7.1? https://secure.php.net/manual/fr/migration71.incompatible.php#migration71.incompatible.numerical-strings-scientific-notation

It's worth to know that it works perfectly fine in LibreOffice 5.4!

What is the expected behavior?

Numbers shown as real text (ex : 150000197301), because I explicitely specify a text format style.

What is the current behavior?

Numbers shown with scientific notation (ex: 1,5E+11).

What are the steps to reproduce?

The following code works as expected with PHP 5.6 (value shown = 150000197301), but fails with PHP 7.1 (value shown = 1,5E+11).

<?php require 'vendor/autoload.php'; $data = array('foo' => '150000197301'); $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet(); $sheet = $spreadsheet->getSheet(0); $sheet->fromArray($data, null, 'A1'); $sheet->getStyle('A1:A1') ->getNumberFormat() ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT); $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet); $filename = 'test_' . PHP_VERSION . '.xlsx'; $writer->save($filename);

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreasheet 1.1.0 PHP 7.1.2 Tested on Windows french and Excel 2016 french.
invalid
opened by oveach 25
Xlsx Reader Better Namespace Handling Phase 1
There have been a number of issues concerning the handling of legitimate but unexpected namespace prefixes in Xlsx spreadsheets created by software other than Excel and PhpSpreadsheet/PhpExcel.I have studied them, but, till now, have not had a good idea on how to act on them. A recent comment https://github.com/PHPOffice/PhpSpreadsheet/issues/860#issuecomment-824926224 in issue #860 by @IMSoP has triggered an idea about how to proceed.

Gnumeric Reader was recently changed to handle namespaces better. Using that as a model, this PR begins the process of doing the same for Xlsx. Xlsx is much larger and more complicated than Gnumeric, hence the need to tackle it in multiple phases. I believe that this PR handles all of:

listWorkSheetNames

listWorkSheetInfo. Note that there was a bug in this function which would cause it to count only used columns rather than all columns. That bug is corrected.

active sheet

selected cell and top left cell

cell content (formulas, numbers, text)

hyperlinks

comments (partial - see below)

This PR does not address:

styles

images and charts

VBA and ribbons

many other items, I'm sure

The issue for non-standard namespacing till now has been the use of unexpected prefixes. While I was working on this change, @Lambik introduced issue #2067 PR #2068 which introduced a completely different problem - the use of unexpected URLs. That PR and the issue associated with it were quite well documented, including the supplying of a test file and tests for it. I asked if I could take a look to see if it could be integrated with my change, and the result seems to be yes, so those changes are also part of this PR.

While adding a comment to my test file, I discovered that Microsoft had added "threaded comments" as a new feature. I believe these are not yet supported by PhpSpreadsheet, and I am not going to add it, at least not now. I believe that, among other things, this will make identifying the author of a comment more difficult.

Although there are a number of Phpstan baseline changes as part of this PR, I did not attempt to resolve all Phpstan reports for Reader/Xlsx. Nor did I do anything to increase coverage. This change is already large and complex enough without those efforts.

I will add more detail as comments after I push this change.

This is:

- [x] a bugfix - [ ] a new feature

Checklist:

[x] Changes are covered by unit tests

[x] Code style is respected

[x] Commit message explains why the change is made (see https://github.com/erlang/otp/wiki/Writing-good-commit-messages)

[ ] CHANGELOG.md contains a short summary of the change

[ ] Documentation is updated as necessary

Why this change is needed?
opened by oleibman 24
32_Chart_read_write.php corrupted Excel 2013 file wth chart "xl/drawings/drawing1.xml"
This is:

- [x] a bug report - [ ] a feature request - [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

I try to copy .xlsx file with chart and insert data to this file for this chart. Tried different ways and still when file is saved, I can't open it successfully because it's corrupted. How can I fix it?

What is the expected behavior?

Successfully save and open new .xlsx file with my data and chart

What is the current behavior?

The error that Component "xl/drawings/drawing1.xml" is deleted. Don't understand why my chart became drawing.

What are the steps to reproduce?

I have tried to reproduce the process with the help of ready sample. Sample/32_Chart_read_write.php

Even with this sample, I can't save not corrupted file with chart.

All code I have from "32_Chart_read_write.php" sample and used 2 my test.xlsx files that contain some date and 1 chart,

Which versions of PhpSpreadsheet and PHP are affected?
opened by DenisDT 24
Data validation not working when opening & saving a file
This is:

- [x ] a bug report - [ ] a feature request - [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What am I trying to accomplish?

I want to open an XLSX file that has some List Dropdown validation rules in it, write some data in it and save it.

What is the expected behavior?

When opening and saving an Excel file, it should retain all the data validation inside it.

What is the current behavior?

It loses all data validation fields ( dropdowns, explanations etc)

What are the steps to reproduce?

Open the bellow excel file with data validation -> save it -> it loses validation.

Sample XLSX file to help reproduce

sample.xlsx

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php require __DIR__ . '/vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\IOFactory; // Declare the path of the file $inputFileName = __DIR__ . '/sample.xlsx'; // Load the file $spreadsheet = IOFactory::load($inputFileName); // Get first sheet in preparation for changes $sheetData = $spreadsheet->getSheet(0); // Create an XLSX writer $writer = IOFactory::createWriter($spreadsheet, "Xlsx"); // Save the file $writer->save("05featuredemo.xlsx");

Which versions of PhpSpreadsheet and PHP are affected?

"phpoffice/phpspreadsheet": "^1.11" PHP 7.3
opened by tntsoft 22

Column limit exceeded when reading a file and then saving it.

This is:

- [X] a bug report

What is the expected behavior?

Excel file should open in LibreOffice and Excel without warnings.

What is the current behavior?

I get a warning about column size exceeding the sheet limit in LibreOffice. Excel gives another warning.

What are the steps to reproduce?

Make sure your machine has LibreOffice installed (the test script uses soffice commandline).
Run the script below.

<?php


use PhpOffice\PhpSpreadsheet\IOFactory;

require 'src/vendor/autoload.php';

$testFile = 'src/vendor/phpoffice/phpspreadsheet/samples/Reader/sampleData/example1.xls';

// CONVERSION WITH PHPSPREADSHEET
$writer = IOFactory::createWriter(IOFactory::load($testFile), 'Xlsx');
$testFile1 = tempnam(sys_get_temp_dir(), 'test') . '.xlsx';
$writer->save($testFile1);

// CONVERSION WITH LIBREOFFICE
passthru('soffice --headless --convert-to xlsx --outdir ' . sys_get_temp_dir() . ' ' . $testFile);
$testFile2 = sys_get_temp_dir() . '/' . basename($testFile) . 'x';

foreach([$testFile1, $testFile2] as $file) {
    $writer = IOFactory::createWriter(IOFactory::load($file), 'Xlsx');
    $outFile = strtr($file, ['.xlsx' => '-output.xlsx']);
    $writer->save($outFile);
    echo "Created $outFile from $file\n";
}

Confirm that:

The scripts executes without error.
One of the created XLSX files throws error(s) when opening in Calc or MS Excel.

Which versions of PhpSpreadsheet and PHP are affected?

1.3.1 on PHP 7.2

opened by SamMousa 22

Releases(1.26.0)

1.26.0(Dec 21, 2022)
1.26.0 - 2022-12-21

Added

Extended flag options for the Reader load() and Writer save() methods

Apply Row/Column limits (1048576 and XFD) in ReferenceHelper PR #3213

Allow the creation of In-Memory Drawings from a string of binary image data, or from a stream. PR #3157

Xlsx Reader support for Tables PR #2829

Permit Date/Time Entered on Spreadsheet to be calculated as Float Issue #1416 PR #3121

Changed

Nothing

Deprecated

Direct update of Calculation::suppressFormulaErrors is replaced with setter.

Font public static variable defaultColumnWidths replaced with constant DEFAULT_COLUMN_WIDTHS.

ExcelError public static variable errorCodes replaced with constant ERROR_CODES.

NumberFormat constant FORMAT_DATE_YYYYMMDD2 replaced with existing identical FORMAT_DATE_YYYYMMDD.

Removed

Nothing

Fixed

Fixed handling for _xlws prefixed functions from Office365 Issue #3245 PR #3247

Conditionals formatting rules applied to rows/columns are removed Issue #3184 PR #3213

Treat strings containing currency or accounting values as floats in Calculation Engine operations Issue #3165 PR #3189

Treat strings containing percentage values as floats in Calculation Engine operations Issue #3155 PR #3156 and PR #3164

Xlsx Reader Accept Palette of Fewer than 64 Colors Issue #3093 PR #3096

Use Locale-Independent Float Conversion for Xlsx Writer Custom Property Issue #3095 PR #3099

Allow setting AutoFilter range on a single cell or row Issue #3102 PR #3111

Xlsx Reader External Data Validations Flag Missing Issue #2677 PR #3078

Reduces extra memory usage on __destruct() calls PR #3092

Additional properties for Trendlines Issue #3011 PR #3028

Calculation suppressFormulaErrors fix Issue #1531 PR #3092

Permit Date/Time Entered on Spreadsheet to be Calculated as Float Issue #1416 PR #3121

Incorrect Handling of Data Validation Formula Containing Ampersand Issue #3145 PR #3146

Xlsx Namespace Handling of Drawings, RowAndColumnAttributes, MergeCells Issue #3138 PR #3136

Generation3 Copy With Image in Footer Issue #3126 PR #3140

MATCH Function Problems with Int/Float Compare and Wildcards Issue #3141 PR #3142

Fix ODS Read Filter on number-columns-repeated cell Issue #3148 PR #3149

Problems Formatting Very Small and Very Large Numbers Issue #3128 PR #3152

XlsxWrite preserve line styles for y-axis, not just x-axis PR #3163

Xlsx Namespace Handling of Drawings, RowAndColumnAttributes, MergeCells Issue #3138 PR #3137

More Detail for Cyclic Error Messages Issue #3169 PR #3170

Improved Documentation for Deprecations - many PRs Issue #3162

Source code(tar.gz)
Source code(zip)
1.25.2(Sep 25, 2022)
Added

Nothing

Changed

Nothing

Deprecated

Nothing

Removed

Nothing

Fixed

Composer dependency clash with ezyang/htmlpurifier

Source code(tar.gz)
Source code(zip)
1.25.1(Sep 25, 2022)

Source code(tar.gz)
Source code(zip)
1.25.0(Sep 25, 2022)
Added

Implementation of the new TEXTBEFORE(), TEXTAFTER() and TEXTSPLIT() Excel Functions

Implementation of the ARRAYTOTEXT() and VALUETOTEXT() Excel Functions

Support for mitoteam/jpgraph implementation of JpGraph library to render charts added.

Charts: Add Gradients, Transparency, Hidden Axes, Rounded Corners, Trendlines, Date Axes.

Changed

Allow variant behaviour when merging cells Issue #3065

Merge methods now allow an additional $behaviour argument. Permitted values are:

Worksheet::MERGE_CELL_CONTENT_EMPTY - Empty the content of the hidden cells (the default behaviour)

Worksheet::MERGE_CELL_CONTENT_HIDE - Keep the content of the hidden cells

Worksheet::MERGE_CELL_CONTENT_MERGE - Move the content of the hidden cells into the first cell

Deprecated

Axis getLineProperty deprecated in favor of getLineColorProperty.

Moved majorGridlines and minorGridlines from Chart to Axis. Setting either in Chart constructor or through Chart methods, or getting either using Chart methods is deprecated.

Chart::EXCEL_COLOR_TYPE_* copied from Properties to ChartColor; use in Properties is deprecated.

ChartColor::EXCEL_COLOR_TYPE_ARGB deprecated in favor of EXCEL_COLOR_TYPE_RGB ("A" component was never allowed).

Misspelled Properties::LINE_STYLE_DASH_SQUERE_DOT deprecated in favor of LINE_STYLE_DASH_SQUARE_DOT.

Clone not permitted for Spreadsheet. Spreadsheet->copy() can be used instead.

Removed

Nothing

Fixed

Fix update to defined names when inserting/deleting rows/columns Issue #3076 PR #3077

Fix DataValidation sqRef when inserting/deleting rows/columns Issue #3056 PR #3074

Named ranges not usable as anchors in OFFSET function Issue #3013

Fully flatten an array Issue #2955 PR #2956

cellExists() and getCell() methods should support UTF-8 named cells Issue #2987 PR #2988

Spreadsheet copy fixed, clone disabled. PR #2951

Fix PDF problems with text rotation and paper size. Issue #1747 Issue #1713 PR #2960

Limited support for chart titles as formulas Issue #2965 Issue #749 PR #2971

Add Gradients, Transparency, and Hidden Axes to Chart Issue #2257 Issue #2229 Issue #2935 PR #2950

Chart Support for Rounded Corners and Trendlines Issue #2968 Issue #2815 PR #2976

Add setName Method for Chart Issue #2991 PR #3001

Eliminate partial dependency on php-intl in StringHelper Issue #2982 PR #2994

Minor changes for Pdf Issue #2999 PR #3002 PR #3006

Html/Pdf Do net set background color for cells using (default) nofill PR #3016

Add support for Date Axis to Chart Issue #2967 PR #3018

Reconcile Differences Between Css and Excel for Cell Alignment PR #3048

R1C1 Format Internationalization and Better Support for Relative Offsets Issue #1704 PR #3052

Minor Fix for Percentage Formatting Issue #1929 PR #3053

Source code(tar.gz)
Source code(zip)
1.24.1(Jul 18, 2022)
Added

Add Chart Axis Option textRotation Issue #2705 PR #2940

Changed

Nothing

Deprecated

Nothing

Removed

Nothing

Fixed

Fix Encoding issue with Html reader (PHP 8.2 deprecation for mb_convert_encoding) Issue #2942 PR #2943

Additional Chart fixes

Pie chart with part separated unwantedly Issue #2506 PR #2928

Chart styling is lost on simple load / save process Issue #1797 Issue #2077 PR #2930

Can't create contour chart (surface 2d) Issue #2931 PR #2933

VLOOKUP Breaks When Array Contains Null Cells Issue #2934 PR #2939

Source code(tar.gz)
Source code(zip)
1.24.0(Jul 9, 2022)
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

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":

Treat rows/columns containing no cell records as empty (default)

Treat cells containing a null value as empty

Treat cells containing an empty string as empty

Changed

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

Deprecated

Nothing

Removed

Nothing

Fixed

Xls Reader resolving absolute named ranges to relative ranges Issue #2826 PR #2827

Null value handling in the Excel Math/Trig PRODUCT() function Issue #2833 PR #2834

Invalid Print Area defined in Xlsx corrupts internal storage of print area Issue #2848 PR #2849

Time interval formatting Issue #2768 PR #2772

Copy from Xls(x) to Html/Pdf loses drawings PR #2788

Html Reader converting cell containing 0 to null string Issue #2810 PR #2813

Many fixes for Charts, especially, but not limited to, Scatter, Bubble, and Surface charts. Issue #2762 Issue #2299 Issue #2700 Issue #2817 Issue #2763 Issue #2219 Issue #2863 PR #2828 PR #2841 PR #2846 PR #2852 PR #2856 PR #2865 PR #2872 PR #2879 PR #2898 PR #2906 PR #2922 PR #2923

Adjust both coordinates for two-cell anchors when rows/columns are added/deleted. Issue #2908 PR #2909

Keep calculated string results below 32K. PR #2921

Filter out illegal Unicode char values FFFE/FFFF. Issue #2897 PR #2910

Better handling of REF errors and propagation of all errors in Calculation engine. PR #2902

Calculating Engine regexp for Column/Row references when there are multiple quoted worksheet references in the formula Issue #2874 PR #2899

Source code(tar.gz)
Source code(zip)
1.23.0(Apr 24, 2022)
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 the castFormattedNumberToNumeric() 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 boolean true if the Locale is set to fr.)

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 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.

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+ 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

Source code(tar.gz)
Source code(zip)
1.22.0(Feb 18, 2022)
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 ReferenceHelper@insertNewBefore behavior when removing column before last column with null value PR #2541

Fix bug with DOLLARDE() and DOLLARFR() 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

Source code(tar.gz)
Source code(zip)
1.21.0(Jan 6, 2022)
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

Source code(tar.gz)
Source code(zip)
1.20.0(Nov 23, 2021)
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

Source code(tar.gz)
Source code(zip)
1.19.0(Oct 31, 2021)
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 Issue #2246

Improved performance of Style::applyFromArray() when applied to several cells PR #1785.

Improve XLSX parsing speed if no readFilter is applied (again) - #772

Source code(tar.gz)
Source code(zip)
1.18.0(May 31, 2021)
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

Source code(tar.gz)
Source code(zip)
1.17.1(Mar 2, 2021)
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 orint`) would fail.

Source code(tar.gz)
Source code(zip)
1.17.0(Mar 2, 2021)
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 orint`) would fail.

Source code(tar.gz)
Source code(zip)
1.16.0(Dec 31, 2020)
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/PHPOffice/PhpSpreadsheet/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.

Source code(tar.gz)
Source code(zip)
1.15.0(Oct 11, 2020)
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

Source code(tar.gz)
Source code(zip)
1.14.1(Jul 19, 2020)
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

Source code(tar.gz)
Source code(zip)
1.14.0(Jun 29, 2020)
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

Source code(tar.gz)
Source code(zip)
1.13.0(May 31, 2020)
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

Source code(tar.gz)
Source code(zip)
1.12.0(Apr 27, 2020)
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

Source code(tar.gz)
Source code(zip)
1.11.0(Mar 2, 2020)
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

Source code(tar.gz)
Source code(zip)
1.10.1(Dec 1, 2019)
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

Source code(tar.gz)
Source code(zip)
1.10.0(Nov 18, 2019)
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

Source code(tar.gz)
Source code(zip)
1.9.0(Aug 17, 2019)
Changed

Drop support for PHP 5.6 and 7.0, according to our policy

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

Source code(tar.gz)
Source code(zip)
1.8.2(Jul 27, 2019)
Fixed

Uncaught error when opening ods file and properties aren't defined - #1047

Xlsx Reader Cell datavalidations bug - #1052

Source code(tar.gz)
Source code(zip)
1.8.1(Jul 27, 2019)
Fixed

Allow nullable theme for Xlsx Style Reader class - #1043

Source code(tar.gz)
Source code(zip)
1.8.0(Jul 27, 2019)
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

Source code(tar.gz)
Source code(zip)
1.7.0(May 26, 2019)
Added

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

Source code(tar.gz)
Source code(zip)
1.6.0(Jan 2, 2019)
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

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

Source code(tar.gz)
Source code(zip)
1.5.2(Jan 2, 2019)
Security

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

Source code(tar.gz)
Source code(zip)

PhpSpreadsheet - a library written in pure PHP and offers a set of classes that allow you to read and write various spreadsheet file formats such as Excel and LibreOffice Calc

Related tags

Overview

PhpSpreadsheet

Documentation

PHPExcel vs PhpSpreadsheet ?

License

Comments

What is the expected behavior?

What is the current behavior?

What are the steps to reproduce?

Which versions of PhpSpreadsheet and PHP are affected?

Solution

What is the expected behavior?

What is the current behavior?

What are the steps to reproduce?

Which versions of PhpSpreadsheet and PHP are affected?

Notes

What is the expected behavior?

What is the current behavior?

What are the steps to reproduce?

Which versions of PhpSpreadsheet and PHP are affected?

What is the expected behavior?

What is the current behavior?

What are the steps to reproduce?

Which versions of PhpSpreadsheet and PHP are affected?

What is the expected behavior?

What is the current behavior?

What are the steps to reproduce?

Which versions of PhpSpreadsheet and PHP are affected?

What is the expected behavior?

What is the current behavior?

What are the steps to reproduce?

Which versions of PhpSpreadsheet and PHP are affected?

Why this change is needed?

What is the expected behavior?

What is the current behavior?

What are the steps to reproduce?

Which versions of PhpSpreadsheet and PHP are affected?

What am I trying to accomplish?

What is the expected behavior?

What is the current behavior?

What are the steps to reproduce?

Sample XLSX file to help reproduce

Which versions of PhpSpreadsheet and PHP are affected?

What is the expected behavior?

What is the current behavior?

What are the steps to reproduce?

Which versions of PhpSpreadsheet and PHP are affected?

Releases(1.26.0)

1.26.0(Dec 21, 2022)

Added

Changed

Deprecated

Removed

Fixed

1.25.2(Sep 25, 2022)

Added

Changed

Deprecated

Removed

Fixed

1.25.1(Sep 25, 2022)

1.25.0(Sep 25, 2022)

Added

Changed

Deprecated

Removed

Fixed

1.24.1(Jul 18, 2022)

Added

Changed

Deprecated

Removed

Fixed

1.24.0(Jul 9, 2022)

Added

Changed

Deprecated

Removed