Working with Excel in PHP: Libraries and Examples |…

Working with Excel in PHP: PhpSpreadsheet and PhpOffice Libraries

In modern web development, there is often a need to export data to Excel format, import reports, or process spreadsheets on the server side. PHP offers several powerful libraries for working with .xlsx, .xls, and .ods files. In this article, we will take a detailed look at PhpSpreadsheet — a modern and actively developed library that replaced the outdated PHPExcel.

What is PhpSpreadsheet?

PhpSpreadsheet is a library for reading, writing, and editing spreadsheets in various formats: Excel, OpenDocument, CSV, HTML, and PDF. It is written in pure PHP, requires no additional extensions (except for standard zip and xml), and supports all major Excel features: styles, formulas, charts, conditional formatting, multiple sheets, and much more.

Installation

Installation is done via Composer:

composer require phpoffice/phpspreadsheet

After installation, a vendor folder with autoloading appears in the project. To get started, simply include the autoloader:

require 'vendor/autoload.php';

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

Creating a Simple Excel File

Let's look at a basic example: create a file with headers and several rows of data.

// Create a new Spreadsheet object$spreadsheet = new Spreadsheet();$sheet = $spreadsheet->getActiveSheet();

// Set headers$sheet->setCellValue('A1', 'Name')      ->setCellValue('B1', 'Email')      ->setCellValue('C1', 'Registration Date');

// Fill with data$sheet->setCellValue('A2', 'Ivan Ivanov')      ->setCellValue('B2', 'ivan@example.com')      ->setCellValue('C2', '2024-01-15');

$sheet->setCellValue('A3', 'Maria Petrova')      ->setCellValue('B3', 'maria@example.com')      ->setCellValue('C3', '2024-02-20');

// Save the file$writer = new Xlsx($spreadsheet);$writer->save('users.xlsx');

echo "File users.xlsx created successfully!";

After running the script, a file users.xlsx will appear in the project root, which can be opened in Excel or Google Sheets.

Reading an Existing Excel File

PhpSpreadsheet makes it easy to read data from any format. Example of loading and outputting content:

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

// Read all rows$data = $sheet->toArray();

foreach ($data as $row) {    echo implode(' | ', $row) . "";}

The toArray() method returns a two-dimensional array, where the first element is a row with indices 0,1,2… You can access cells by address: $sheet->getCell('A2')->getValue().

Working with Styles and Formatting

One of the strong points of PhpSpreadsheet is flexible control over the table's appearance.

$styleArray = [    'font' => [        'bold' => true,        'size' => 12,        'color' => ['argb' => 'FF2E4057'],    ],    'fill' => [        'fillType' => \\PhpOffice\\PhpSpreadsheet\\Style\\Fill::FILL_SOLID,        'startColor' => ['argb' => 'FFD9E2EC'],    ],    'borders' => [        'allBorders' => [            'borderStyle' => \\PhpOffice\\PhpSpreadsheet\\Style\\Border::BORDER_THIN,        ],    ],];

// Apply style to a range$sheet->getStyle('A1:C1')->applyFromArray($styleArray);

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