If you are working with data, you might have come across Excel files that need to be imported and manipulated in your PHP application. There are several PHP libraries available that allow you to read Excel files.
In this article, we’ll cover three popular libraries:
Quick Comparison
The following table provides a quick overview of the key features and differences between the three libraries.
| Feature | SimpleXLSX | PhpSpreadsheet | Spout |
| File Formats | XLSX, XLS | XLSX, XLS, CSV | XLSX, CSV |
| Advanced Features | Basic | Advanced | Basic |
| Reading Efficiency | Good | Medium | Excellent |
| Writing Efficiency | Good | Medium | Excellent |
| API Complexity | Simple | Advanced | Simple |
| Maintenance | Low | High | Medium |
| Documentation | Basic | Comprehensive | Basic |
Read Excel Files in PHP Code Examples
Read Excel Files in PHP with SimpleXLSX
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
$spreadsheet = IOFactory::load('example.xlsx');
$worksheet = $spreadsheet->getActiveSheet();
$data = $worksheet->toArray(null, true, true, true);
print_r($data);
?>
Read Excel Files in PHP with PhpSpreadsheet
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
$spreadsheet = IOFactory::load('example.xlsx');
$worksheet = $spreadsheet->getActiveSheet();
$data = $worksheet->toArray(null, true, true, true);
print_r($data);
?>
Read Excel Files in PHP with Spout
<?php
require_once('vendor/autoload.php');
use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
$reader = ReaderEntityFactory::createXLSXReader();
$reader->open('example.xlsx');
foreach ($reader->getSheetIterator() as $sheet) {
foreach ($sheet->getRowIterator() as $row) {
print_r($row->toArray());
}
}
$reader->close();
?>
Article Highlights
- SimpleXLSX is a lightweight and easy-to-use library that supports various Excel file formats.
- PhpSpreadsheet provides advanced Excel features like charts and pivot tables, but it is a bit more complex than SimpleXLSX.
- Spout is designed for performance and can handle large Excel files efficiently.
- Each library has its strengths and weaknesses, and the choice depends on your specific requirements.
Table of Contents
- The example.xlsx File
- PHP Read Excel Files using SimpleXLSX Library
- PHP Read Excel Files using PhpSpreadsheet Library
- PHP Read Excel Files using Spout Library
- Frequently Asked Questions
- Conclusion
The example.xlsx File
The article uses ‘example.xlsx’ as a demo Excel file in all the code examples.
PHP Read Excel Files using SimpleXLSX Library
SimpleXLSX is a lightweight and easy-to-use PHP library that can read and write Excel files. It supports Excel 2007 and above versions and can handle large files efficiently. SimpleXLSX is a good choice if you only need basic Excel file reading functionality and want a library that is easy to set up and use.
SimpleXLSX can handle both XLSX and XLS file formats and provides a simple API for reading Excel files. The library can parse Excel files into arrays or objects, making it easy to work with the data in your PHP code. SimpleXLSX also provides methods for working with formulas, formatting, and other Excel features.
Installation
Install Simplexlsx for PHP via Composer.
composer require shuchkin/simplexlsxCode Example
Let’s take a look at how you can use SimpleXLSX to read an Excel file in PHP.
<?php
require 'vendor/autoload.php';
use Shuchkin\SimpleXLSX;
if ( $xlsx = SimpleXLSX::parse('example.xlsx') ) {
print_r( $xlsx->rows() );
}
else {
echo SimpleXLSX::parseError();
}
?>
In the above example, we first require the Shuchkin\SimpleXLSX library, and then parse the ‘example.xlsx’ file. If the file is parsed successfully, we print the rows of the file. If there is an error while parsing, we print the error message using the parseError() method.
Output
The above code prints the following array.
Array
(
[0] => Array
(
[0] => Firstname
[1] => Lastname
[2] => Grade
)
[1] => Array
(
[0] => John
[1] => Bruce
[2] => 4
)
[2] => Array
(
[0] => Mike
[1] => Strand
[2] => 3.7
)
[3] => Array
(
[0] => Richard
[1] => Stalward
[2] => 3.8
)
[4] => Array
(
[0] => Ali
[1] => Shah
[2] => 3.9
)
[5] => Array
(
[0] => Sarah
[1] => Fassen
[2] => 3.5
)
[6] => Array
(
[0] => Julia
[1] => Altenhofer
[2] => 3.9
)
)
Pros & Cons
| Pros | Cons |
| Lightweight and easy to use | Limited support for Excel features and file formats |
| Supports reading and writing Excel files | Does not support advanced Excel features like macros and charts |
PHP Read Excel Files using PhpSpreadsheet Library
PhpSpreadsheet is a powerful PHP library that can read and write Excel files. It supports a wide range of Excel file formats, including Excel 2007 and above versions, and provides advanced features like formatting, formulas, charts, and more. PhpSpreadsheet is a good choice if you need advanced Excel file reading functionality and want a library that is actively maintained and well-documented.
PhpSpreadsheet provides a rich API for reading and manipulating Excel files, making it easy to work with the data in your PHP code. The library can parse Excel files into arrays, objects, or even directly into a database. PhpSpreadsheet also supports a wide range of Excel features like conditional formatting, pivot tables, and more.
Installation
Install PhpSpreadsheet via Composer.
composer require phpoffice/phpspreadsheetCode Example
Let’s take a look at how you can use PhpSpreadsheet to read an Excel file in PHP.
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
$spreadsheet = IOFactory::load('example.xlsx');
$worksheet = $spreadsheet->getActiveSheet();
$data = $worksheet->toArray(null, true, true, true);
print_r($data);
?>
In the above example, we first require the PhpSpreadsheet library using Composer and then load the ‘example.xlsx’ file using the IOFactory class. We then get the active worksheet and convert it to an array using the toArray() method.
Output
It prints the same array output as seen above.
Pros & Cons
| Pros | Cons |
| Supports a wide range of Excel file formats and features including formatting, formulas, charts, and more. | Can be slower and more resource-intensive than other libraries |
| Easy to use and well-documented | Requires additional configuration and dependencies |
| Provides additional functionality like calculations and formatting |
PHP Read Excel Files using Spout Library
Spout is a fast and memory-efficient PHP library for reading and writing Excel files. It is designed for performance and can handle large Excel files with ease. Spout supports a wide range of Excel file formats, including XLSX and CSV, and provides a simple and easy-to-use API.
Spout can parse Excel files into arrays or objects, making it easy to work with the data in your PHP code. The library also provides features that go beyond just reading and writing, like formatting, formulas, and cell merging.
Spout is a good choice if you need to handle large Excel files efficiently and want a library that is fast and easy to use.
Installation
Install Spoutt via Composer.
composer require box/spoutCode Example
Let’s take a look at how you can use Spout to read an Excel file in PHP.
<?php
require_once('vendor/autoload.php');
use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
$reader = ReaderEntityFactory::createXLSXReader();
$reader->open('example.xlsx');
foreach ($reader->getSheetIterator() as $sheet) {
foreach ($sheet->getRowIterator() as $row) {
print_r($row->toArray());
}
}
$reader->close();
?>
The code creates a new XLSXReader object using the ReaderEntityFactory class and opens the Excel file for reading. The XLSXReader object provides methods for iterating over the rows and columns of the Excel file.
The code uses a foreach loop to iterate over the sheets in the Excel file using the getSheetIterator() method. For each sheet, it iterates over the rows using the getRowIterator() method and prints the row data using the toArray() method.
Finally, the code closes the XLSXReader object using the close() method to free up any resources used by the object.
Output
The code prints every row as an array which looks as follows.
Array
(
[0] => Firstname
[1] => Lastname
[2] => Grade
)
Array
(
[0] => John
[1] => Bruce
[2] => 4
)
Array
(
[0] => Mike
[1] => Strand
[2] => 3.7
)
Array
(
[0] => Richard
[1] => Stalward
[2] => 3.8
)
Array
(
[0] => Ali
[1] => Shah
[2] => 3.9
)
Array
(
[0] => Sarah
[1] => Fassen
[2] => 3.5
)
Array
(
[0] => Julia
[1] => Altenhofer
[2] => 3.9
)
Pros & Cons
| Pros | Cons |
| Supports reading and writing large Excel files efficiently | Limited support for advanced Excel features like macros and charts |
| Supports a wide range of Excel file formats | Can be slower than other libraries for smaller Excel files |
| Provides a simple and easy-to-use API |
Frequently Asked Questions
How do I handle null or empty values when reading from an Excel file?
When reading data from an Excel file using PHP and one of the libraries mentioned in this article, you may encounter null or empty values. You can use conditional statements or try/catch blocks to handle these values to ensure your code does not break or produce unexpected results.
For example, if you are using PhpSpreadsheet, you can use the getCell() method to retrieve the value of a cell, and then check if it is null or empty using the isNullOrEmpty() method. Here is an example.
use PhpOffice\PhpSpreadsheet\IOFactory;
$reader = IOFactory::createReader('Xlsx');
$spreadsheet = $reader->load('example.xlsx');
$worksheet = $spreadsheet->getActiveSheet();
foreach ($worksheet->getRowIterator() as $row) {
$cellValue = $worksheet->getCell('A1')->getValue();
if (!is_null($cellValue) && !empty($cellValue)) {
// Do something with the cell value
}
}
In this example, we use the is_null() and empty() functions to check if the cell value is null or empty, and then only execute the code within the conditional statement if it is not. This can help prevent errors or unexpected behavior in your code when working with null or empty values in Excel files.
Learn more about how to handle null values.
Can these libraries handle password-protected Excel files?
Yes, the libraries discussed in this article can handle password-protected Excel files. However, you will need to provide the password to the library in order to access the data in the file.
For example, if you are using PhpSpreadsheet, you can use the setPassword() method to set the password for the file before loading it. Here is an example.
use PhpOffice\PhpSpreadsheet\IOFactory;
$reader = IOFactory::createReader('Xlsx');
$reader->setPassword('password');
$spreadsheet = $reader->load('example.xlsx');
In this example, we set the password for the file using the setPassword() method before loading it with the load() method. This will allow us to access the data in the file even if it is password-protected.
Similarly, if you are using SimpleXLSX or Spout, you can use the appropriate method to set the password before reading the data from the file. It’s important to note that if you provide an incorrect password, the library won’t read the data from the file, so be sure to double-check the password before attempting to read the file.
Can I use these libraries to read data from multiple sheets or workbooks in an Excel file?
Yes, all three libraries discussed in this article allow you to read data from multiple sheets in an Excel file.
In PhpSpreadsheet and Spout, you can use the setLoadSheetsOnly() method to specify which sheets you want to load. Here is an example using PhpSpreadsheet.
use PhpOffice\PhpSpreadsheet\IOFactory;
$reader = IOFactory::createReader('Xlsx');
$reader->setLoadSheetsOnly(['Sheet1', 'Sheet2']);
$spreadsheet = $reader->load('example.xlsx');
In this example, we specify that we only want to load data from Sheet1 and Sheet2 in the Excel file. If you want to read data from all sheets in the file, you can omit the setLoadSheetsOnly() method.
Similarly, in SimpleXLSX, you can use the sheet() method to specify which sheet you want to read data from. Here is an example.
$xlsx = SimpleXLSX::parse('example.xlsx');
$data = $xlsx->sheet('Sheet1')->rows();
In this example, we read data from Sheet1 using the sheet() method and then use the rows() method to get an array of all rows in the sheet.
Reading data from multiple workbooks in an Excel file using these libraries is also possible. Still, the specific method for doing so may vary depending on the library you are using. Be sure to consult the documentation for the library you are using for more information.
How to Read Excel Files in PHP
This article examined three PHP libraries for reading Excel files: SimpleXLSX, PhpSpreadsheet, and Spout. Each library has its strengths and weaknesses, and the choice of which library to use depends on your specific requirements.
If you need a lightweight and easy-to-use library for reading Excel files, SimpleXLSX might be a good option. It supports various Excel file formats and provides a simple and intuitive API. However, it lacks advanced Excel features like charts and pivot tables.
If you need advanced Excel functionality, like charts and pivot tables, PhpSpreadsheet might be a better choice. It provides various Excel features and supports various Excel file formats. However, it is a bit more complex and memory-intensive than SimpleXLSX.
If you need to handle large Excel files efficiently, Spout is a great choice. It is designed for performance and can handle large Excel files with ease. It provides a simple and easy-to-use API for reading and writing Excel files. However, it does not provide advanced Excel features like charts and pivot tables.
Each library provides a solid solution for reading Excel files in PHP. Depending on your specific needs, you can choose the library that best suits your requirements. With the help of these libraries, you can easily read and manipulate data from Excel files using PHP.
