PhpSpreadsheet Excel File Handling with PHP Made Easy

PhpSpreadsheet is an open-source library written in pure PHP that allows developers to create, read, write, and modify spreadsheet documents. It supports various formats such as Excel (.xlsx, .xls), OpenDocument (.ods), and CSV. It is the next-generation version of PHPExcel and is maintained by the PHPOffice project.

Quick Example

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue(‘A1’, ‘Hello World’);

15K+

GitHub Stars

2M+

Monthly Downloads

500+

Contributors

Advanced Features

🖋️

Formatting cells

Customize cell appearance with bold text, font sizes, colors, borders, and background fills using the getStyle() method.

🔗

Merging cells and alignment

Merge multiple cells into one for titles or headers and align text horizontally or vertically.

🧮

Formulas and functions

Insert Excel formulas (like =SUM(A1:A5)) directly into cells using setCellValue().

📊

Charts and images

Add visual elements like bar charts, pie charts, and images to your spreadsheets.

Requirements

Before using PhpSpreadsheet in your project, it’s important to ensure that your server environment meets the necessary requirements. This will help you avoid common compatibility issues and ensure optimal performance.

PhpSpreadsheet PHP Extensions

Extension	Purpose	Required
php_zip	Required for reading/writing `.xlsx` files	Yes
php_xml	Required for reading/writing XML-based formats	Yes
php_gd	Needed for rendering images and charts	Optional
php_iconv	Character encoding conversion	Optional
php_mbstring	Multibyte string processing	Optional
php_fileinfo	Detect file types	Optional
php_dom	Manipulate and parse XML documents	Yes
php_simplexml	Used for reading spreadsheet structure	Yes

Composer Dependency Management

PhpSpreadsheet uses Composer for installation and autoloading. Composer is the standard dependency manager for PHP and is required to install and manage PhpSpreadsheet and its dependencies.

Installation

PhpSpreadsheet can be installed in several ways, but the recommended and easiest approach is using Composer, PHP’s dependency manager. Composer automatically handles downloading the library and its dependencies, plus setting up autoloading so you can use the classes right away.

Using Composer

To install PhpSpreadsheet via Composer, open your terminal or command prompt and run the following command in your project directory:

composer require phpoffice/phpspreadsheet

This command does the following:

Downloads the latest stable version of PhpSpreadsheet.
Installs it into your project’s vendor directory.
Updates your composer.json and composer.lock files to track the dependency.

Using the –prefer-source Option for Examples

If you want to download the source code of PhpSpreadsheet and its examples or documentation (for learning or modification), use the –prefer-source flag like this:

composer require –prefer-source phpoffice/phpspreadsheet

This tells Composer to clone the repository instead of just downloading a packaged archive, which gives you access to the full project files, including samples.

Autoloading with Composer

PhpSpreadsheet relies on Composer’s autoloader to automatically load the required classes when you use them. After installing PhpSpreadsheet with Composer, add this line at the beginning of your PHP script to enable autoloading:

require ‘vendor/autoload.php’;

This includes Composer’s autoloader script, which registers all necessary class loaders for PhpSpreadsheet and any other dependencies you have installed.

File Format Support in PhpSpreadsheet

PhpSpreadsheet is designed to work with a wide variety of spreadsheet file formats. This makes it versatile and suitable for many applications, from web apps and data imports to reporting tools and document generation.

Excel (.xlsx, .xls)

.xlsx (Excel 2007 and newer)
Fully supported by PhpSpreadsheet.
Ideal for modern applications and compatibility.
Uses the Office Open XML format (compressed and XML-based).
Recommended for most use cases due to better performance and lower file size.
.xls (Excel 97–2003)
Supported through the use of the BIFF format.
Legacy support — use only when you need compatibility with older systems.
Limited compared to .xlsx in terms of formatting and features.

LibreOffice Calc (.ods)

.ods (OpenDocument Spreadsheet)
Native format used by LibreOffice and OpenOffice.
Open standard and XML-based.
PhpSpreadsheet supports both reading and writing ODS files.
Useful for applications requiring open-source office suite compatibility.

CSV (.csv)

Comma-Separated Values (CSV)
Simple and widely-used plain text format.
Supported for both import (reading) and export (writing).
Flexible delimiter (e.g., comma, semicolon, tab) support.
Lightweight and ideal for data exchange between systems.
Note: Lacks support for formatting, formulas, and charts.

HTML (.html)

HTML Table Format
PhpSpreadsheet can generate HTML tables from spreadsheet content.
Useful for displaying spreadsheets directly in a browser.
Can be styled using CSS for web-based reports.
Not suitable for re-import into spreadsheet editors, as it lacks native spreadsheet features.

Basic Usage PhpSpreadsheet

PhpSpreadsheet allows you to create, edit, and read spreadsheet files (such as Excel or CSV) in PHP. Below are the core operations you’ll typically perform.

Creating a New Spreadsheet

To begin, you create a new instance of Spreadsheet:

				
					use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet(); // Gets the default first worksheet

Writing Data to Cells

You can write data to specific cells using setCellValue():

				
					$sheet->setCellValue('A1', 'Hello World!');
$sheet->setCellValue('B1', 123);
$sheet->setCellValue('C1', '=SUM(B1, 100)'); // Formula

Saving Files to Disk

To save a file, use a writer object based on the file format you want (e.g., XLSX, CSV):

				
					use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$writer = new Xlsx($spreadsheet);
$writer->save('example.xlsx'); // Saves the file to disk

For CSV:

				
					use PhpOffice\PhpSpreadsheet\Writer\Csv;

$writer = new Csv($spreadsheet);
$writer->save('example.csv');

If you want to prompt the user to download the file in a browser:

				
					header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="export.xlsx"');
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;

Reading Existing Spreadsheet Files

You can load existing files using a Reader:

				
					use PhpOffice\PhpSpreadsheet\IOFactory;

$spreadsheet = IOFactory::load('example.xlsx');
$sheet = $spreadsheet->getActiveSheet();
$data = $sheet->getCell('A1')->getValue();

echo "Cell A1: " . $data;

To read multiple rows (e.g., in a loop):

				
					foreach ($sheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false); // Optional: include empty cells

    foreach ($cellIterator as $cell) {
        echo $cell->getValue() . "\t";
    }
    echo "\n";
}

Performance Tips in PhpSpreadsheet

Working with large spreadsheets can lead to high memory usage and long execution times. PhpSpreadsheet provides several tools and best practices to optimize performance when working with big data sets.

Handling Large Datasets

When working with spreadsheets that contain thousands (or millions) of rows:

Avoid loading the entire spreadsheet into memory when it’s not needed.
Use cell caching mechanisms (see below).
Limit styling and formatting as these increase memory usage.
Use generators or batching to load/write data in chunks.

Example: Instead of writing cell by cell in a loop with formatting, try to minimize formatting and write in blocks if possible.

Error Handling & Debugging in PhpSpreadsheet

PhpSpreadsheet provides a robust way to work with spreadsheets, but like any PHP library, issues may arise due to invalid input, missing files, formatting errors, or unsupported features. Proper error handling and debugging are essential for building a stable application.

Common Exceptions and How to Handle Them

PhpSpreadsheet throws exceptions (typically \PhpOffice\PhpSpreadsheet\Exception) to signal problems. Below are common exceptions you may encounter:

\PhpOffice\PhpSpreadsheet\Exception
Occurs when a general issue arises in the library, such as invalid cell coordinates or non-existent worksheet.

				
					use PhpOffice\PhpSpreadsheet\Exception;

try {
    $spreadsheet->getActiveSheet()->setCellValue('ZZ1000000', 'Data'); // Invalid cell
} catch (Exception $e) {
    echo 'Error: ' . $e->getMessage();
}

\PhpOffice\PhpSpreadsheet\Reader\Exception
Thrown when loading an invalid or corrupted file, or a file in an unsupported format.

Example:

				
					use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Exception;

try {
    $spreadsheet = IOFactory::load('nonexistent-file.xlsx');
} catch (Exception $e) {
    echo 'Reader error: ' . $e->getMessage();
}

PHP Native Errors

Errors such as file permissions, memory limits, or invalid input data can still occur. Use standard PHP error handling practices like try-catch, custom error handlers, and configuration tweaks.

Logging Errors
Log errors to a file or a monitoring system instead of displaying them to users.

Example:

				
					try {
    // load spreadsheet or do something
} catch (\Throwable $e) {
    error_log(date('[Y-m-d H:i:s] ') . $e->getMessage() . "\n", 3, 'logs/phpspreadsheet-errors.log');
    // Optionally notify admin or monitoring tool
}

Integration Examples

PhpSpreadsheet can be integrated into various PHP-based environments to handle spreadsheet tasks such as data export, import, and reporting. Here are a few common integration scenarios:

Using PhpSpreadsheet in Laravel

Laravel is a popular PHP framework that works seamlessly with PhpSpreadsheet through service providers or third-party wrappers.

Option 1: Use Laravel Excel (recommended wrapper)
Laravel Excel is built on top of PhpSpreadsheet and provides an elegant syntax.
Installation:

composer require maatwebsite/excel

Exporting data example:

				
					use Maatwebsite\Excel\Facades\Excel;
use App\Exports\UsersExport;

return Excel::download(new UsersExport, 'users.xlsx');

Exporting data example:

				
					Creating UsersExport:

use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection {
    public function collection() {
        return User::all();
    }
}

Example: Export user data in a plugin:

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

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

    $sheet->setCellValue('A1', 'Username');
    $sheet->setCellValue('B1', 'Email');

    $users = get_users();
    $row = 2;
    foreach ($users as $user) {
        $sheet->setCellValue("A$row", $user->user_login);
        $sheet->setCellValue("B$row", $user->user_email);
        $row++;
    }

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="users.xlsx"');
    header('Cache-Control: max-age=0');

    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}

REST API with PhpSpreadsheet (Import/Export)

Creating a REST API with PhpSpreadsheet lets you handle spreadsheet files via HTTP requests. This is useful for frontend apps or 3rd-party integrations.

Example Use Case:

A user uploads a spreadsheet of inventory items.
The API processes and stores the data in a database.
Another endpoint allows downloading a report as an Excel file.
Frameworks: Laravel, Symfony, Slim, or even pure PHP.

Import Endpoint (POST /api/import):

Accept file via multipart/form-data
Use PhpSpreadsheet’s IOFactory to read data
Save into database

Export Endpoint (GET /api/export):

Fetch data from database
Use PhpSpreadsheet to generate Excel
Return as downloadable file in response

Example snippet:

				
					$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Product Name');
// Add more data...

$writer = new Xlsx($spreadsheet);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="products.xlsx"');
$writer->save("php://output");
exit;

Frequently Asked Questions (FAQs)

What is PhpSpreadsheet?

PhpSpreadsheet is a PHP library for reading, writing, and manipulating spreadsheet files such as Excel (.xlsx, .xls), LibreOffice (.ods), and CSV formats.

How do I install PhpSpreadsheet?

You can install PhpSpreadsheet using Composer with the following command:

composer require phpoffice/phpspreadsheet

What are the system requirements for PhpSpreadsheet?

You need PHP 7.4 or higher, Composer, and extensions like php_zip, php_xml, and php_gd (for charts/images).

Is PhpSpreadsheet compatible with the latest PHP versions?

Yes, PhpSpreadsheet supports active PHP versions and continues support for six months after a version reaches end-of-life.

Can I use PhpSpreadsheet without Composer?

It’s not recommended, but technically possible by manually downloading and including all required classes. Composer is the preferred method.

What file formats does PhpSpreadsheet support?

PhpSpreadsheet supports Excel (.xlsx, .xls), OpenDocument (.ods), CSV (.csv), HTML (.html), and PDF (requires external renderer).

Can I read and write CSV files with PhpSpreadsheet?

Yes, PhpSpreadsheet provides full support for reading and writing CSV files.

Does PhpSpreadsheet support macros in Excel files?

No, macros in .xlsm files are preserved but cannot be created or modified.

Can PhpSpreadsheet generate charts?

Yes, PhpSpreadsheet supports basic chart generation within Excel spreadsheets.

Does PhpSpreadsheet support formulas?

Yes, you can write and read Excel formulas. PhpSpreadsheet also supports formula calculation in some cases.

How do I create a new Excel file with PhpSpreadsheet?

You create a Spreadsheet object, modify cells, and save the file using a writer (e.g., Xlsx or Csv).

How do I read an existing spreadsheet file?

Use the appropriate Reader class (e.g., Xlsx, Csv), load the file, and access cell values through worksheet objects.

How do I apply styles like font, color, and borders to cells?

PhpSpreadsheet provides a set of style arrays and functions for formatting cell fonts, colors, fills, and borders.

Can I merge cells using PhpSpreadsheet?

Yes, you can merge cells using the mergeCells() method on a worksheet.

How can I protect or lock a worksheet or cells?

PhpSpreadsheet allows you to protect sheets or lock individual cells through protection settings.

Can I export a spreadsheet directly to the browser for download?

Yes, you can send the spreadsheet to the browser using appropriate HTTP headers and the Writer output stream.

How can I use PhpSpreadsheet in Laravel or another PHP framework?

Include PhpSpreadsheet using Composer and call its methods in controllers or services like any PHP class.

Can I use PhpSpreadsheet in WordPress?

Yes, you can include it via Composer in a plugin or theme and use it to import/export spreadsheet data.

Can I read uploaded Excel files from a form?

Yes, handle file uploads via $_FILES and load the uploaded file using PhpSpreadsheet’s Reader.

Can I convert Excel to PDF with PhpSpreadsheet?

Yes, but it requires an external PDF rendering library such as Dompdf, mPDF, or TCPDF.

Why is PhpSpreadsheet consuming a lot of memory?

Working with large spreadsheets may increase memory usage. You can optimize using cell caching and stream writers.

Why are formulas not calculating automatically?

Some formulas require Excel to perform the calculation. You may need to enable calculation or recalculate in Excel.

Why does my file not open in Excel after saving with PhpSpreadsheet?

Ensure that you are using the correct writer type (e.g., Xlsx for .xlsx) and setting the correct headers for downloads.

How do I debug or log errors in PhpSpreadsheet?

Use try/catch blocks to capture exceptions and log error messages or use var_dump for intermediate debugging.

Can I add images to a spreadsheet using PhpSpreadsheet?

Yes, PhpSpreadsheet supports inserting images (JPEG, PNG, BMP, GIF) into cells using the \PhpOffice\PhpSpreadsheet\Worksheet\Drawing class.

Can I create multiple worksheets in a single Excel file?

Yes, you can create and manage multiple worksheets using $spreadsheet->createSheet() and set them with setActiveSheetIndex().

How can I autosize columns or set custom column widths?

Use methods like $worksheet->getColumnDimension(‘A’)->setAutoSize(true) or setWidth() for custom widths.

Can I freeze panes or split windows in PhpSpreadsheet?

Yes, use the freezePane() method to freeze rows or columns and keep headers visible while scrolling.

How do I set background colors or fill patterns in cells?

Use the setFillType() and getStartColor()->setARGB() methods within a style array to apply backgrounds or patterns.

Can I load data from a remote URL into PhpSpreadsheet?

Not directly. You’ll need to download the file via cURL or file_get_contents() first and then load it using the appropriate reader.

Schema

PhpSpreadSheet - PHP Library for Excel & CSV Files

PhpSpreadsheet is a PHP library for reading, writing, and formatting Excel and CSV files. Supports XLSX, XLS, CSV, formulas, and more. #PhpSpreadSheet

Price: Free

Price Currency: $

Operating System: Windows, Linux, macOS

Application Category: Software

Editor's Rating:
4.6