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.

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.

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 "Streaming a large export as a CSV file to the browser"?

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