While working on web applications, often we come across a situation where we need to play with spreadsheet files. We may want to read data from spreadsheets and process it. Sometimes, it is also required to create and write spreadsheet files.
In this article, we study how to read and write spreadsheet files in PHP. The code we are going to explain will work on 3 types of spreadsheet – CSV, XLSX and ODS.
Getting Started
As a developer, I would like to experiment with different packages which can be helpful to solve real-world problems. Recently, I came across a package called box/spout. I liked the way the library performed the tasks. Using this library, you can process spreadsheet files with just a few lines of code.
This library is capable of processing files of any size. It may be possible you are dealing with a large file. During the process, the large file should not consume the memory and creates a performance issue. Spout library solves this problem. It needs only 3MB of memory to process any file.
Before getting started, your server should pass the below requirements.
- PHP version 7.2 or higher
- PHP extension
php_zip
enabled - PHP extension
php_xmlreader
enabled
The user can check if all above requirements are satisfied on your server using the phpinfo()
method. Create a temporary PHP file and place the below statement in it.
<?php
phpinfo();
Now run this PHP file on the browser and you will see PHP configurations like version, extensions, etc. You should see xmlreader
and zip
extensions are enabled as shown in the screenshot below.

Once you meet the requirements, install the box/spout
package through the command below.
composer require box/spout
Read and Write Spreadsheet Files in PHP
Spout package allows you to interact with XLSX, CSV and ODS files. As an example, I am writing a code for XLSX files.
Create a sample XLSX file users.xlsx
in the root directory and add some dummy entries to it. In my case, the file has below entries.

Next, I will write a code in reader.php
file which will read the users.xlsx
and print the data in it.
reader.php
<?php
require_once 'vendor/autoload.php';
use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;
$filePath = getcwd().'/users.xlsx';
$reader = ReaderEntityFactory::createReaderFromFile($filePath);
$reader->open($filePath);
foreach ($reader->getSheetIterator() as $sheet) {
foreach ($sheet->getRowIterator() as $row) {
$cells = $row->getCells();
foreach ($cells as $cell) {
echo $cell->getValue();
}
}
}
$reader->close();
It is that simple. Go ahead and give a try for it.
On the other hand, we can easily create a XLSX file and write data into it. Let’s create a file writer.php
and add code below in it.
writer.php
<?php
require_once 'vendor/autoload.php';
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Common\Entity\Row;
$filePath = getcwd().'/new-users.xlsx';
$writer = WriterEntityFactory::createXLSXWriter();
$writer->openToFile($filePath);
// Here is data for XLSX file
$data = [
['Name', 'Email'],
['Steve', 'steve@test.com'],
['David', 'david@test.com'],
];
foreach ($data as $d) {
$cells = [
WriterEntityFactory::createCell($d[0]),
WriterEntityFactory::createCell($d[1]),
];
$singleRow = WriterEntityFactory::createRow($cells);
$writer->addRow($singleRow);
}
$writer->close();
The above code creates a new-users.xlsx
file into your root directory. If you open this file, you will see a few rows(used in code) are inserted into it.
In the case of CSV and ODS files, you need to create a $writer
object as follows.
$filePath = getcwd().'/new-users.ods';
$writer = WriterEntityFactory::createODSWriter();
$filePath = getcwd().'/new-users.csv';
$writer = WriterEntityFactory::createCSVWriter();
I hope you understand how to read and write spreadsheet files in PHP. I would like to hear your thoughts and suggestions in the comment section below.
Related Articles
- How to Read CSV and Excel File in PHP Using PhpSpreadsheet
- How to Export MySQL Database Data to Excel Using PHP
- How to Convert HTML to PDF in PHP
If you liked this article, then please subscribe to our YouTube Channel for video tutorials.
How to insert records in mysql database?