Working with Excel in PHP: PhpSpreadsheet and OpenSpout Libraries

Working with Excel in PHP: Overview of the Best Libraries

Spreadsheet processing is one of the common tasks in web development. There are several proven tools in PHP for creating, reading, and editing Excel files. In this article, we will look at two key libraries: PhpSpreadsheet (the successor to the popular PHPExcel) and the lightweight OpenSpout, designed for fast writing and reading of large files.

1. Installation via Composer

Both libraries are installed using the standard package manager Composer. Run in the terminal in the project root:

# For PhpSpreadsheet (full-featured Excel work)composer require phpoffice/phpspreadsheet

# For OpenSpout (high performance on large data)composer require openspout/openspout

After installation, include the autoloader at the beginning of the script:

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

2. PhpSpreadsheet: Creating and Styling Excel Files

The library supports XLSX, XLS, ODS, CSV and other formats. It allows you to manage styles, merge cells, insert images and formulas.

Example: Creating a Simple Report

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

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

// Headers$sheet->setCellValue('A1', 'Product');$sheet->setCellValue('B1', 'Quantity');$sheet->setCellValue('C1', 'Price, RUB');

// Data$data = [    ['Laptop', 5, 45000],    ['Monitor', 10, 15000],    ['Keyboard', 20, 2500],];$row = 2;foreach ($data as $item) {    $sheet->setCellValue("A{$row}", $item[0]);    $sheet->setCellValue("B{$row}", $item[1]);    $sheet->setCellValue("C{$row}", $item[2]);    $row++;}

// Total formula$sheet->setCellValue("A{$row}", 'Total');$sheet->setCellValue("C{$row}", "=SUM(C2:C" . ($row-1) . ")");

// Header styling$styleHeader = [    'font' => ['bold' => true, 'size' => 12],    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],    'fill' => ['fillType' => \\PhpOffice\\PhpSpreadsheet\\Style\\Fill::FILL_SOLID, 'color' => ['rgb' => 'E0E0E0']]];$sheet->getStyle('A1:C1')->applyFromArray($styleHeader);

// Auto-width columnsforeach (range('A', 'C') as $col) {    $sheet->getColumnDimension($col)->setAutoSize(true);}

// Saving$writer = new Xlsx($spreadsheet);$writer->save('report.xlsx');echo "File report.xlsx created!";

Reading an Existing XLSX File

use PhpOffice\\PhpSpreadsheet\\IOFactory;

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

$highestRow = $sheet->getHighestRow();$highestColumn = $sheet->getHighestColumn();

for ($row = 1; $row <= $highestRow; $row++) {    $rowData = $sheet->rangeToArray("A{$row}:{$highestColumn}{$row}", NULL, TRUE, FALSE);    echo implode(', ', $rowData[0]) . "<br>";}

3. OpenSpout: Fast Work with Large Tables

OpenSpout is optimized for streaming writing and reading. It does not support styles and formulas, but is ideal for exporting thousands of rows of data (e.g., database dumps).

Example: Writing 10,000 Rows to CSV/XLSX

use OpenSpout\\Writer\\XLSX\\Writer;use OpenSpout\\Common\\Entity\\Row;

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

// Headers$headerRow = Row::fromValues(['ID', 'Name', 'Email']);$writer->addRow($headerRow);

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

$writer->close();echo "Export completed!";

Reading a Large XLSX File Row by Row

use OpenSpout\\Read