PhpSpreadsheet introduced many breaking changes by introducing namespaces and renaming some classes. To help you migrate existing project, a tool was written to replace all references to PHPExcel classes to their new names. But there are also manual changes that need to be done.
RectorPHP can be used to migrate
automatically your codebase. Assuming your files to be migrated lives
in src/
, you can run the migration like so:
composer require rector/rector rector/rector-phpoffice phpoffice/phpspreadsheet --dev
# this creates rector.php config
vendor/bin/rector init
Add PHPOfficeSetList
set to rector.php
declare(strict_types=1);
use Rector\Config\RectorConfig;
use Rector\PHPOffice\Set\PHPOfficeSetList;
return static function (RectorConfig $rectorConfig): void {
$rectorConfig->sets([
PHPOfficeSetList::PHPEXCEL_TO_PHPSPREADSHEET
]);
};
And run Rector on your code:
vendor/bin/rector process src
For more details, see rector-phpoffice.
RectorPHP should take care of everything, but if somehow it does not work, you can review/apply the following manual changes
When using IOFactory::createReader()
, IOFactory::createWriter()
and
IOFactory::identify()
, the reader/writer short names are used. Those were
changed, along as their corresponding class, to remove ambiguity:
Before | After |
---|---|
'CSV' |
'Csv' |
'Excel2003XML' |
'Xml' |
'Excel2007' |
'Xlsx' |
'Excel5' |
'Xls' |
'Gnumeric' |
'Gnumeric' |
'HTML' |
'Html' |
'OOCalc' |
'Ods' |
'OpenDocument' |
'Ods' |
'PDF' |
'Pdf' |
'SYLK' |
'Slk' |
The following methods :
PHPExcel_IOFactory::getSearchLocations()
PHPExcel_IOFactory::setSearchLocations()
PHPExcel_IOFactory::addSearchLocation()
were replaced by IOFactory::registerReader()
and IOFactory::registerWriter()
. That means
IOFactory now relies on classes autoloading.
Before:
// Before
\PHPExcel_IOFactory::addSearchLocation($type, $location, $classname);
// After
\PhpOffice\PhpSpreadsheet\IOFactory::registerReader($type, $classname);
// Before
$worksheet->duplicateStyleArray($styles, $range, $advanced);
// After
$worksheet->getStyle($range)->applyFromArray($styles, $advanced);
// Before
DataType::dataTypeForValue($value);
// After
DefaultValueBinder::dataTypeForValue($value);
// Before
$conditional->getCondition();
// After
$conditional->getConditions()[0];
// Before
$conditional->setCondition($value);
// After
$conditional->setConditions($value);
// Before
$worksheet->getDefaultStyle();
// After
$worksheet->getParent()->getDefaultStyle();
// Before
$worksheet->setDefaultStyle($value);
// After
$worksheet->getParent()->getDefaultStyle()->applyFromArray([
'font' => [
'name' => $pValue->getFont()->getName(),
'size' => $pValue->getFont()->getSize(),
],
]);
// Before
$worksheet->setSharedStyle($sharedStyle, $range);
// After
$worksheet->duplicateStyle($sharedStyle, $range);
// Before
$worksheet->getSelectedCell();
// After
$worksheet->getSelectedCells();
// Before
$writer->setTempDir();
// After, there is no way to set temporary storage directory anymore
The class PHPExcel_Autoloader
was removed entirely and is replaced by composer
autoloading mechanism.
PDF libraries must be installed via composer. And the following methods were removed
and are replaced by IOFactory::registerWriter()
instead:
PHPExcel_Settings::getPdfRenderer()
PHPExcel_Settings::setPdfRenderer()
PHPExcel_Settings::getPdfRendererName()
PHPExcel_Settings::setPdfRendererName()
Before:
\PHPExcel_Settings::setPdfRendererName(PHPExcel_Settings::PDF_RENDERER_MPDF);
\PHPExcel_Settings::setPdfRenderer($somePath);
$writer = \PHPExcel_IOFactory::createWriter($spreadsheet, 'PDF');
After:
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Mpdf');
// Or alternatively
\PhpOffice\PhpSpreadsheet\IOFactory::registerWriter('Pdf', \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf::class);
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Pdf');
// Or alternatively
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet);
When rendering charts for HTML or PDF outputs, the process was simplified.
And, while JpGraph support is still available,
the version distributed via Composer is no longer maintained,
so you would need to install the current version manually.
If you rely on this package, please consider
contributing patches either to JpGraph or another IRenderer
implementation (a good
candidate might be CpChart).
The package mitoteam/jpgraph is distributed via Composer, and is fully compatible with Jpgraph. We recommend that it be used for rendering.
Before:
$rendererName = \PHPExcel_Settings::CHART_RENDERER_JPGRAPH;
$rendererLibrary = 'jpgraph3.5.0b1/src/';
$rendererLibraryPath = '/php/libraries/Charts/' . $rendererLibrary;
\PHPExcel_Settings::setChartRenderer($rendererName, $rendererLibraryPath);
After:
Require the dependency via composer:
composer require mitoteam/jpgraph
And then:
Settings::setChartRenderer(\PhpOffice\PhpSpreadsheet\Chart\Renderer\MtJpGraphRenderer::class);
Support for PclZip were dropped in favor of the more complete and modern PHP extension ZipArchive. So the following were removed:
PclZip
PHPExcel_Settings::setZipClass()
PHPExcel_Settings::getZipClass()
PHPExcel_Shared_ZipArchive
PHPExcel_Shared_ZipStreamWrapper
Cell caching was heavily refactored to leverage PSR-16. That means most classes related to that feature were removed:
PHPExcel_CachedObjectStorage_APC
PHPExcel_CachedObjectStorage_DiscISAM
PHPExcel_CachedObjectStorage_ICache
PHPExcel_CachedObjectStorage_Igbinary
PHPExcel_CachedObjectStorage_Memcache
PHPExcel_CachedObjectStorage_Memory
PHPExcel_CachedObjectStorage_MemoryGZip
PHPExcel_CachedObjectStorage_MemorySerialized
PHPExcel_CachedObjectStorage_PHPTemp
PHPExcel_CachedObjectStorage_SQLite
PHPExcel_CachedObjectStorage_SQLite3
PHPExcel_CachedObjectStorage_Wincache
In addition to that, \PhpOffice\PhpSpreadsheet::getCellCollection()
was renamed
to \PhpOffice\PhpSpreadsheet::getCoordinates()
and
\PhpOffice\PhpSpreadsheet::getCellCacheController()
to
\PhpOffice\PhpSpreadsheet::getCellCollection()
for clarity.
Refer to the new documentation to see how to migrate.
For all the following methods, it is not possible to change the type of returned value. They will always return the Worksheet and never the Cell or Rule:
- Worksheet::setCellValue()
- Worksheet::setCellValueByColumnAndRow() (deprecated)
- Worksheet::setCellValueExplicit()
- Worksheet::setCellValueExplicitByColumnAndRow() (deprecated)
- Worksheet::addRule()
Migration would be similar to:
// Before
$cell = $worksheet->setCellValue('A1', 'value', true);
// After
$cell = $worksheet->getCell('A1')->setValue('value');
Array keys used for styling have been standardized for a more coherent experience. It now uses the same wording and casing as the getter and setter:
// Before
$style = [
'numberformat' => [
'code' => NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE,
],
'font' => [
'strike' => true,
'superScript' => true,
'subScript' => true,
],
'alignment' => [
'rotation' => 90,
'readorder' => Alignment::READORDER_RTL,
'wrap' => true,
],
'borders' => [
'diagonaldirection' => Borders::DIAGONAL_BOTH,
'allborders' => [
'style' => Border::BORDER_THIN,
],
],
'fill' => [
'type' => Fill::FILL_GRADIENT_LINEAR,
'startcolor' => [
'argb' => 'FFA0A0A0',
],
'endcolor' => [
'argb' => 'FFFFFFFF',
],
],
];
// After
$style = [
'numberFormat' => [
'formatCode' => NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE,
],
'font' => [
'strikethrough' => true,
'superscript' => true,
'subscript' => true,
],
'alignment' => [
'textRotation' => 90,
'readOrder' => Alignment::READORDER_RTL,
'wrapText' => true,
],
'borders' => [
'diagonalDirection' => Borders::DIAGONAL_BOTH,
'allBorders' => [
'borderStyle' => Border::BORDER_THIN,
],
],
'fill' => [
'fillType' => Fill::FILL_GRADIENT_LINEAR,
'startColor' => [
'argb' => 'FFA0A0A0',
],
'endColor' => [
'argb' => 'FFFFFFFF',
],
],
];
Methods to manipulate coordinates that used to exists in PHPExcel_Cell
were extracted
to a dedicated new class \PhpOffice\PhpSpreadsheet\Cell\Coordinate
. The methods are:
absoluteCoordinate()
absoluteReference()
buildRange()
columnIndexFromString()
coordinateFromString()
extractAllCellReferencesInRange()
getRangeBoundaries()
mergeRangesInCollection()
rangeBoundaries()
rangeDimension()
splitRange()
stringFromColumnIndex()
Column indexes are now based on 1. So column A
is the index 1
. This is consistent
with rows starting at 1 and Excel function COLUMN()
that returns 1
for column A
.
So the code must be adapted with something like:
// Before
$cell = $worksheet->getCellByColumnAndRow($column, $row);
for ($column = 0; $column < $max; ++$column) {
$worksheet->setCellValueByColumnAndRow($column, $row, 'value');
}
// After
$cell = $worksheet->getCell([$column + 1, $row]);
for ($column = 1; $column <= $max; ++$column) {
$worksheet->setCellValue([$column, $row], 'value');
}
All the following methods are affected, and all are now deprecated (see example above for how to replace them):
PHPExcel_Worksheet::cellExistsByColumnAndRow()
PHPExcel_Worksheet::freezePaneByColumnAndRow()
PHPExcel_Worksheet::getCellByColumnAndRow()
PHPExcel_Worksheet::getColumnDimensionByColumn()
PHPExcel_Worksheet::getCommentByColumnAndRow()
PHPExcel_Worksheet::getStyleByColumnAndRow()
PHPExcel_Worksheet::insertNewColumnBeforeByIndex()
PHPExcel_Worksheet::mergeCellsByColumnAndRow()
PHPExcel_Worksheet::protectCellsByColumnAndRow()
PHPExcel_Worksheet::removeColumnByIndex()
PHPExcel_Worksheet::setAutoFilterByColumnAndRow()
PHPExcel_Worksheet::setBreakByColumnAndRow()
PHPExcel_Worksheet::setCellValueByColumnAndRow()
PHPExcel_Worksheet::setCellValueExplicitByColumnAndRow()
PHPExcel_Worksheet::setSelectedCellByColumnAndRow()
PHPExcel_Worksheet::stringFromColumnIndex()
PHPExcel_Worksheet::unmergeCellsByColumnAndRow()
PHPExcel_Worksheet::unprotectCellsByColumnAndRow()
PHPExcel_Worksheet_PageSetup::addPrintAreaByColumnAndRow()
PHPExcel_Worksheet_PageSetup::setPrintAreaByColumnAndRow()
Default values for many methods were removed when it did not make sense. Typically, setter methods should not have default values. For a complete list of methods and their original default values, see that commit.