★ Streaming a large export as a CSV file to the browser

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.

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.