Working with Excel in PHP: PhpSpreadsheet and OpenSpout Libraries

Working with Excel in PHP: Overview of PhpSpreadsheet and OpenSpout Libraries

Working with Excel spreadsheets is one of the most common tasks in PHP web development. Whether it's generating reports, importing data from accounting systems, or exporting statistics for clients — tools for working with .xlsx and .xls formats are essential. In this article, we will look at two of the most popular PHP libraries: PhpSpreadsheet (the successor to the famous PHPExcel) and OpenSpout (a lightweight alternative for large files).

PhpSpreadsheet — A Powerful and Feature-Rich Tool

PhpSpreadsheet is a library from the PHPOffice project that allows you to read, create, and modify Excel files (XLSX, XLS, ODS, CSV, and others). It supports cell styling, formulas, charts, conditional formatting, and even working with macros.

Installing PhpSpreadsheet

Installation is done via Composer:

composer require phpoffice/phpspreadsheet

Example: Creating a Simple Excel File

<?phprequire 'vendor/autoload.php';

use PhpOffice\\PhpSpreadsheet\\Spreadsheet;use PhpOffice\\PhpSpreadsheet\\Writer\\Xlsx;

$spreadsheet = new Spreadsheet();$sheet = $spreadsheet->getActiveSheet();

// Fill in data$sheet->setCellValue('A1', 'ID');$sheet->setCellValue('B1', 'Name');$sheet->setCellValue('C1', 'Email');$sheet->setCellValue('A2', 1);$sheet->setCellValue('B2', 'Ivan Petrov');$sheet->setCellValue('C2', 'ivan@example.com');

// Header styling$styleArray = [    'font' => ['bold' => true, 'size' => 14],    'fill' => ['fillType' => 'solid', 'startColor' => ['rgb' => 'E0E0E0']]];$sheet->getStyle('A1:C1')->applyFromArray($styleArray);

// Save the file$writer = new Xlsx($spreadsheet);$writer->save('users.xlsx');echo "File users.xlsx created!";?>

Reading an Existing Excel File

<?phprequire 'vendor/autoload.php';

use PhpOffice\\PhpSpreadsheet\\IOFactory;

$inputFileName = 'users.xlsx';$spreadsheet = IOFactory::load($inputFileName);$sheet = $spreadsheet->getActiveSheet();

foreach ($sheet->getRowIterator() as $row) {    $cellIterator = $row->getCellIterator();    $cellIterator->setIterateOnlyExistingCells(false);    $rowData = [];    foreach ($cellIterator as $cell) {        $rowData[] = $cell->getValue();    }    echo implode(' | ', $rowData) . "<br>";}?>

OpenSpout — Lightweight and Fast for Large Data

OpenSpout is a library designed for working with large volumes of data (hundreds of thousands of rows). Unlike PhpSpreadsheet, it does not load the entire file into memory but processes it in a streaming fashion. This makes it ideal for exporting/importing large reports. OpenSpout supports XLSX, CSV, and ODS formats.

Installing OpenSpout

composer require openspout/openspout

Example: Exporting 100,000 Rows to XLSX

<?phprequire 'vendor/autoload.php';

use OpenSpout\\Writer\\XLSX\\Writer;

$writer = new Writer();$writer->openToFile('big_export.xlsx');

// Headers$writer->addRow(['ID', 'Name', 'Email']);

for ($i = 1; $i <= 100000; $i++) {    $writer->addRow([$i, "User $i", "user$i@example.com"]);}

$writer->close();echo "File big_export.xlsx with 100,000 rows created!";?>

Reading a Large File with OpenSpout

<?phprequire 'vendor/autoload.php';

use OpenSpout\\Reader\\XLSX\\Reader;

$reader = new Reader();$reader->open('big_export.xlsx');

foreach ($reader->getSheetIterator() as $sheet) {    foreach ($sheet->getRowIterator() as $row) {        $cells = $row->getCells();        echo implode(' | ', array_map(function($cell) {            return $cell->getValue();        }, $