Oh Dear is the all-in-one monitoring tool for your entire website. We monitor uptime, SSL certificates, broken links, scheduled tasks and more. You'll get a notifications for us when something's wrong. All that paired with a developer friendly API and kick-ass documentation. O, and you'll also be able to create a public status page under a minute. Start monitoring using our free trial now.

A PHP package to read and write simple Excel or CSV files

Original – by Freek Van der Herten – 5 minute read

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.

Using simple-excel

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

Imagine you have a CSV with this content.

// $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. 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.

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

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:

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

The file at pathToCsv will contain:

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 is listed as a dependency.

Let's work with this CSV:

This is how you can use Spout directly.

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:

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.

Here's the code that wraps Spout's generator in a LazyCollection.

// 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.

If you need something framework agnostic, that can handle both CSVs and Excel files, you could also opt to use Spout directly.

In the Laravel ecosystem, Laravel Excel 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 and exportables to describe your files.

Patrick, 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). 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 is a great alternative to our simple-excel package.

Closing thoughts

The 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.

Be sure to also check out this list of open source packages my team and I have created previously.

Stay up to date with all things Laravel, PHP, and JavaScript.

You can follow me on these platforms:

On all these platforms, regularly share programming tips, and what I myself have learned in ongoing projects.

Every month I send out a newsletter containing lots of interesting stuff for the modern PHP developer.

Expect quick tips & tricks, interesting tutorials, opinions and packages. Because I work with Laravel every day there is an emphasis on that framework.

Rest assured that I will only use your email address to send you the newsletter and will not use it for any other purposes.

Comments

What are your thoughts on "A PHP package to read and write simple Excel or CSV files"?

Comments powered by Laravel Comments
Want to join the conversation? Log in or create an account to post a comment.

Webmentions

juan pineda liked on 16th July 2020
Jk Que liked on 16th July 2020
Coderatio retweeted on 16th July 2020
Coderatio liked on 16th July 2020
Abdulkadir Olatunji liked on 16th July 2020
Murali ⌘ Krishna liked on 16th July 2020
Julio Serpone liked on 16th July 2020
Stuart Wilsdon liked on 16th July 2020
NoMad42 liked on 16th July 2020
Сута Фунду retweeted on 15th July 2020
Сута Фунду liked on 15th July 2020
Ronan Whelan liked on 15th July 2020
Umar Jadoon liked on 15th July 2020
Francisco Campos 🇦🇴 liked on 15th July 2020
Jordan Hall retweeted on 15th July 2020
Jordan Hall liked on 15th July 2020
Franco Gilio liked on 15th July 2020
Boom Boom liked on 15th July 2020
Darko Stoilov retweeted on 15th July 2020
Darko Stoilov liked on 15th July 2020
Jose Alberto Lopez liked on 15th July 2020
Otar Chekurishvili🦔 liked on 15th July 2020
Cyril de Wit liked on 15th July 2020
1giba liked on 15th July 2020
Aniket Mahadik retweeted on 15th July 2020
Aniket Mahadik liked on 15th July 2020
Hardik Shah liked on 15th July 2020
Andre Sayej liked on 15th July 2020
Risal Hidayat liked on 15th July 2020
Aryeh Feld liked on 15th July 2020
Willan Correia liked on 15th July 2020
Isaac Adzah Sai liked on 15th July 2020
dSoto retweeted on 15th July 2020
Osmar Alves liked on 15th July 2020
Gareth Redfern liked on 15th July 2020
Sowren Sen liked on 15th July 2020
Nguyễn Đăng Dũng liked on 15th July 2020
Codefuel liked on 15th July 2020
Iruku Kagika liked on 15th July 2020
Albert Suntic liked on 15th July 2020
Yann Haefliger liked on 15th July 2020
Haneef Ansari 🍭 liked on 15th July 2020
ArielMejiaDev retweeted on 15th July 2020
ArielMejiaDev liked on 15th July 2020
Spatie retweeted on 15th July 2020
MoKaDev liked on 15th July 2020
Bruno Montibeller liked on 15th July 2020
Parthasarathi G K liked on 15th July 2020
{coding:x} retweeted on 15th July 2020
Iftakharul Alam retweeted on 15th July 2020
Manish Manghwani retweeted on 15th July 2020
Snr Apst. MAKANAKY liked on 15th July 2020
/dev/prabakaran retweeted on 15th July 2020
Tom Witkowski liked on 15th July 2020
Manish Manghwani liked on 15th July 2020
/dev/prabakaran liked on 15th July 2020
Ahmed Abd El Ftah liked on 15th July 2020
Manish Manghwani replied on 15th July 2020
Going to implement lazy collection for my package. It's a perfect match. github.com/manish-manghwa…
Iftakharul Alam liked on 15th July 2020
agus dwi darmawan retweeted on 31st October 2019
Greg Upton replied on 29th October 2019
Anyone else tried reading a CSV from an S3 bucket? I've not had any joy so far.
Craig Potter liked on 29th October 2019
George liked on 29th October 2019
Edwin Heij liked on 29th October 2019
Stuart Wilsdon liked on 29th October 2019
theonlyub liked on 29th October 2019
Sunit Panwar liked on 29th October 2019
Oilmone liked on 29th October 2019
Jordan Hall liked on 29th October 2019
John liked on 29th October 2019
John retweeted on 29th October 2019
Jordan Hall retweeted on 29th October 2019
Branden liked on 29th October 2019
GrandBlond ?? liked on 29th October 2019
Stefam liked on 29th October 2019
Growing muscles with ALC4.0 ? liked on 29th October 2019
Spatie retweeted on 29th October 2019
Johan Alvarez liked on 29th October 2019
Ⓜ️ fuz liked on 29th October 2019
/dev/null replied on 29th October 2019
Took me 5 minutes to try this today and it's perfect for a small feature I'm building. Thanks again for all the quality packages you keep putting out ?
David Mohamed liked on 29th October 2019
Al Mujahid liked on 29th October 2019
Muhammad Sumon Molla Selim liked on 29th October 2019
Fede liked on 29th October 2019
Enzo Innocenzi liked on 29th October 2019
jopereyral liked on 28th October 2019
Miguel Piedrafita ? liked on 28th October 2019
Wilman Barrios liked on 28th October 2019
Ndirangu Waweru liked on 28th October 2019
marco asperti retweeted on 28th October 2019
Mak Man liked on 28th October 2019
Mickaël Isaert liked on 28th October 2019
mkalina liked on 28th October 2019
Vikas Roy liked on 28th October 2019
Volkan Metin liked on 28th October 2019
Milan Chheda ?‍? liked on 28th October 2019
Osama Kamel liked on 28th October 2019
Mazedul Islam Khan liked on 28th October 2019
Ashish Srivastava liked on 28th October 2019
Peter Sowah liked on 28th October 2019
Patrick Brouwers liked on 28th October 2019
Mithicher Baro liked on 28th October 2019
oluwajubelo loves VueJS ? retweeted on 28th October 2019
ahgood liked on 28th October 2019
Mithicher Baro retweeted on 28th October 2019
Patrick Brouwers retweeted on 28th October 2019
PHP Synopsis retweeted on 28th October 2019
Adam Lukačovič liked on 28th October 2019
César Rodas liked on 28th October 2019
\Zloi\Freelancer liked on 28th October 2019
\Zloi\Freelancer retweeted on 28th October 2019
Joey Kudish liked on 28th October 2019
abhishek retweeted on 28th October 2019
Noe liked on 28th October 2019
Jordan Pittman replied on 28th October 2019
You can do this with SPLFileObject by writing to php://output so for CSVs it should be completely possible. I don’t know about Excel formats though.
Marcin liked on 28th October 2019
JOSIAH YAHAYA retweeted on 28th October 2019
pxgamer liked on 28th October 2019
Ndubuisi Onyemenam retweeted on 28th October 2019
Morteza liked on 28th October 2019
Joseph Silber replied on 28th October 2019
Yeah I know it's technically possible. I was asking if the package supports it.
Ndubuisi Onyemenam liked on 28th October 2019
JOSIAH YAHAYA liked on 28th October 2019
Christian Leo-Pernold retweeted on 28th October 2019
Clayton Stone ? liked on 28th October 2019
ali ali liked on 28th October 2019
Joseph Silber replied on 28th October 2019
One thing I haven't seen addressed in any of these solutions: writing while streaming to the client. Imagine you want to export a huge SQL table. I want to use a cursor, and stream a CSV file to the client directly from the DB, never increasing my memory usage. Possible?
totallynoob.com liked on 28th October 2019
Ashish K. Poudel liked on 28th October 2019
Christian Leo-Pernold liked on 28th October 2019
Edwin I Arellano liked on 28th October 2019
Matt Woods liked on 28th October 2019
Connor McManus retweeted on 28th October 2019