My team at Spatie is currently building Mailcoach, a solution to self host your e-mail newsletter. Mailcoach can be used as stand alone software or as a Laravel package. Subscribe now at Mailcoach to get a notification as soon as we release it.

Streaming a large export as a CSV file to the browser

Original – by Freek Van der Herten – 3 minute read

A while ago, I created an easy to use framework agnostic PHP package to read and write CSV and Excel files called spatie/simple-excel. Behind the scenes, generators are used to ensure low memory usage, even when working with large files. Today I added a method that allows you to stream CSV files to the browser. In this small blog post, I'd like to demonstrate how you can use it.

The problem

I'm currently building Mailcoach, an app to self-host your newsletters. In Mailcoach, people can subscribe to email lists. These lists can potentially contain a large number of subscribers. In the UI of Mailcoach, there is a button to download a CSV containing all subscribers.

Screenshot

Here's how you could implement the download of that export.

class SubscribersExportController
{
    public function __invoke()
    {
        $exportFile = storage_path('exports/subscribers.csv');

        $exportCsv = SimpleExcelWriter::create($exportFile);

        Subscriber::each(function (Subscriber $subscriber) use ($exportCsv) {
            $exportCsv->addRow($subscriber->toArray());
        });

        return response()->download($exportFile)->deleteFileAfterSend();
    }
}

That each static method on a model will, behind the scenes, retrieved all the models in a chunked way. This will keep memory usage low.

The code above does the job, but it has two significant drawbacks that become apparent when you want to export a great many rows.

  1. The code above writes a file on disk. For large lists, this export file could take could some disk space.
  2. The download will only start after the entire export file has been created. For large exports, the user must potentially wait a long time.

The solution

Both of these problems can be solved with the new capabilities of spatie/simple-excel.

Using streamDownload and toBrowser, you can stream the export to the browser while you're fetching results from the database.

class SubscribersExportController
{
    public function __invoke()
    {
        $subscriberCsv = SimpleExcelWriter::streamDownload('subscribers.csv');

        Subscriber::each(function (Subscriber $subscriber) use ($subscriberCsv) {
            $subscriberCsv->addRow($subscriber->toArray());
        });

        $subscriberCsv->toBrowser();
    }
}

We've solved the two problems with the previous code. No file will be created on disk. The download will immediately start, even if there is a large number of subscribers.

This solution will only work for CSV files. Excel files are not streamable.

In conclusion

Take a look at spatie/simple-excel on GitHub to know more about the package.

Together with Mailcoach, we'll release a video course on how Mailcoach is built. You'll learn a lot of useful things, similar to what you read in this post, to improve your code. To be notified when Mailcoach and the video course launch, subscribe to the mailing list at Mailcoach .app.

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

Follow me on Twitter. I regularly tweet out programming tips, and what I myself have learned in ongoing projects.

Every two weeks 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

You can comment on this post by replying to this tweet.
Martin Medina liked on 2nd December 2019
Claudson Martins liked on 1st December 2019
bernard kssy retweeted on 30th November 2019
imzeeshan liked on 30th November 2019
Albert Suntic liked on 30th November 2019
George Drakakis ? liked on 30th November 2019
Feras Shaer ?? liked on 30th November 2019
Romain Norberg liked on 30th November 2019
kapil retweeted on 30th November 2019
Danijel K. liked on 29th November 2019
Manojkiran liked on 29th November 2019
Ronan Whelan liked on 29th November 2019
Ila liked on 29th November 2019
Wyatt liked on 29th November 2019
Dmitry Bubyakin liked on 29th November 2019
Vladyslav liked on 29th November 2019
Simpledev retweeted on 29th November 2019
Rati Wannapanop liked on 29th November 2019
M. Vug liked on 29th November 2019
Nara liked on 29th November 2019
Simpledev liked on 29th November 2019
Mike liked on 29th November 2019
Swapnil Bhavsar liked on 29th November 2019
Joseph Silber replied on 29th November 2019
Two questions: 1. Can this be made to work as a streamed Laravel/Symfony response? Would fit better within Laravel. (Maybe just do the whole thing a stream callback) 2. How does this even work? You're only calling toBrowser() after having added all rows
Jordan Pittman replied on 29th November 2019
You could run the openToBrowser method inside the callback of response()->streamDownload(). The main problem I see though is it calls `header()` but it does it through a class that I think could be subclassed. I’m not sure the effort required here though.
Freek Van der Herten replied on 29th November 2019
Behind the scenes it just leverages what the box/spout package offers. I don’t see a good way for me to make it work work with a Symfony response The toBrowser method does nothing besides halting the request so the response doesn’t become invalid.
Christian Leo-Pernold retweeted on 29th November 2019
Christian Leo-Pernold liked on 29th November 2019
jiban liked on 29th November 2019
Jonas Pardon liked on 29th November 2019