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 spreadsheets – 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 the 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

The Spout package allows you to interact with XLSX, CSV, and ODS files. As an example, I am writing 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 the below entries.

Next, I will write a code in reader.php file that will read users.xlsx and print the data in it.

reader.php

<?php
require_once 'vendor/autoload.php';

use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;

try {
    $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();
} catch(Exception $e) {
    echo $e->getMessage();
}

It is that simple. Go ahead and give it a try for it.

On the other hand, we can easily create the XLSX file and write data into it. Let’s create a file writer.php and add the code below to it.

writer.php

<?php
require_once 'vendor/autoload.php';

use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Common\Entity\Row;

try {
    $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();
} catch(Exception $e) {
    echo $e->getMessage();
}

The above code creates a new-users.xlsx file in 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 the $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.