★ A PHP package to read and write simple Excel or CSV files
Tags: laravel, php
Published: 2020-01-02 23:41:10

---
title: ★ A PHP package to read and write simple Excel or CSV files
published: true
date: 2019-10-28 13:30:00 UTC
tags: laravel, php
canonical_url: https://freek.dev/1488-a-php-package-to-read-and-write-simple-excel-or-csv-files
---

For a couple of projects I needed to read and write a couple of very large Excel and CSV files. I didn't find a good package that does this so I decided to create one myself. Under the hood it uses generators, so memory usage will remain low, even when working with large files.

In this blogpost I'd like to walk you through [spatie/simple-excel](https://github.com/spatie/simple-excel).

## Using simple-excel #

As the name implies using simple-excel is... simple :-)

Imagine you have a CSV with this content.

```csv
email,first_name
john@example.com,john
jane@example.com,jane
```

You can read it like this:


```php
// $rows is an instance of Illuminate\Support\LazyCollection
$rows = SimpleExcelReader::open($pathToCsv)->getRows();

$rows->each(function(array $rowProperties) {
   // in the first pass $rowProperties will contain
   // ['email' => 'john@example', 'first_name' => 'john']
});
```

`getRows` will return an instance of [`Illuminate\Support\LazyCollection`](https://laravel.com/docs/master/collections#lazy-collections). This class is part of the Laravel framework. Behind the scenes generators are used, so memory usage will be low, even for large files.

You'll find a list of methods you can use on a `LazyCollection` [in the Laravel documentation](https://laravel.com/docs/master/collections#the-enumerable-contract).

Here's a quick, silly example where we only want to process rows that have a `first_name` that contains more than 5 characters.

```php
SimpleExcelReader::open($pathToCsv)->getRows()
    ->filter(function(array $rowProperties) {
       return strlen($rowProperties['first_name']) > 5
    })
    ->each(function(array $rowProperties) {
        // processing rows
    });
```

Reading an Excel file is identical to reading a CSV file. Just make sure that the path given to the `create` method of `SimpleExcelReader` ends with `xlsx` or `xls`.

Let's turn our attention to writing files. Here's how you can do that:

```php
$writer = SimpleExcelWriter::create($pathToCsv)
     ->addRow([
        'first_name' => 'John',
        'last_name' => 'Doe',
    ])
    ->addRow([
        'first_name' => 'Jane',
        'last_name' => 'Doe',
    ]);
```

The file at `pathToCsv` will contain:

```csv
first_name,last_name
John,Doe
Jane,Doe
```

Again, if you want create an excel file, just use `xls` or `xlsx` as the extension.

## How does it work under the hood #

Reading very large files will still only use a tiny bit of memory. Let's take a look at how the package accomplishes that. If you take a look at the requirements of the package, you'll see that [`box/spout`](https://github.com/box/spout) is [listed as a dependency](http://opensource.box.com/spout/).

Let's work with this CSV:

```csv
email,first_name
john@example.com,john
```

This is how you can use Spout directly.

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

$reader = ReaderEntityFactory::createReaderFromFile('/path/to/file.csv');

$filePath = '/path/to/file.ext';

$reader = ReaderEntityFactory::createReaderFromFile($filePath);

$reader->open($filePath);

$sheet = $this->reader->getSheetIterator()->current();

foreach ($sheet->getRowIterator() as $row) {        
    $arrayWithValuesOfRow = $this->getValueFromRow($row)
}

$reader->close();
```

It's not too much code, but it's not very intuitive (I have a very low tolerance for code that is not easy to use).

In the first pass the `$arrayWithValuesOfRow` will be filled with `['email','first_name']`. The second one with `['john@example.com','john']`. You'll have to write some boring code to combine the header names with the values.

Our simple-excel package will do all of that for you. Here is the code needed:

```php
SimpleExcelReader::open($pathToCsv)
   ->getRows();
   ->each(function(array $rowProperties) {
   // in the first pass $rowProperties will contain
   // ['email' => 'john@example', 'first_name' => 'john']
});
```

The `getRows` method will return an instance of `Illuminate\Support\LazyCollection`. This is a class that was recently introduced in Laravel. In short, this class allows you to wrap a generator so you can use [most of the `Collection` API on it](https://laravel.com/docs/master/collections#the-enumerable-contract).

Here's [the code](https://github.com/spatie/simple-excel/blob/16eae1a102b5c99158020af49e5235faaa49bf0b/src/SimpleExcelReader.php#L85-L93) that wraps Spout's generator in a `LazyCollection.
php
```
// inside the getRows function

return LazyCollection::make(function () {
    while ($this->rowIterator->valid()) {
        $row = $this->rowIterator->current();
        yield $this->getValueFromRow($row);
        $this->rowIterator->next();
    }
});
```

## Alternatives #

If you just have to working with CSV files and don't mind processing a header row yourself, you might now even need a package. Take a look at [the native `fgetcsv` function](https://www.php.net/manual/ro/function.fgetcsv.php).

If you need something framework agnostic, that can handle both CSVs and Excel files, you could also opt to use [Spout](https://github.com/box/spout) directly.

In the Laravel ecosystem, [Laravel Excel](https://laravel-excel.com/) is a popular choice. It's a well written package with lots of powerful options. You can write files to disks you configured in Laravel and use [importables](https://docs.laravel-excel.com/3.1/imports/importables.html) and [exportables](https://docs.laravel-excel.com/3.1/exports/exportables.html) to describe your files.

[Patrick](https://github.com/patrickbrouwers), the author of the laravel-excel, did an awesome job creating it, but I think his use cases are a bit different from mine. I like to read files residing at a given path (and not having to use [a configured disk](https://laravel.com/docs/master/filesystem#configuration)). For simple imports and exports the importables and exportables feel to heavy for me. I just want to write the data directly and be done with it. There also seem to be some performance/memory issues when handling very large files.

Still, if you like it's API and don't have to work with large files, [laravel-excel](https://github.com/maatwebsite/Laravel-Excel) is a great alternative [to our simple-excel package](https://github.com/spatie/simple-excel).

## Closing thoughts #

The [simple-excel](https://github.com/spatie/simple-excel) package has some more interesting options that were not mentioned in this blogpost such as disabling automatic header rows, using styles, manually using a reader/writer object, using alternative delimiters, ... To learn more about these options, head over to [the documentation on GitHub](https://github.com/spatie/simple-excel).

Be sure to also check out [this list of open source packages](https://spatie.be/open-source/packages) my team and I have created previously.