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.

Webmentions

Clemens John liked on 17th December 2020
Ashish Srivastava liked on 17th December 2020
Mudassir Ahmad Adili liked on 17th December 2020
Trường Phúc Võ liked on 17th December 2020
Brian Reeves liked on 17th December 2020
Kylian_LM 🚀 liked on 17th December 2020
Lee Cox liked on 17th December 2020
Ciro Lo Sapio liked on 17th December 2020
Gustavo Novaes liked on 17th December 2020
Charlie (Chazza) liked on 17th December 2020
Steven Roland liked on 17th December 2020
Shahram Mebashar liked on 17th December 2020
Sadam AMINOU liked on 17th December 2020
Evgeni Kovalski liked on 17th December 2020
Jose Cerrejon liked on 17th December 2020
Elliot Derhay liked on 17th December 2020
Phouvanh KCSV retweeted on 16th December 2020
Spatie retweeted on 16th December 2020
Shahram Mebashar retweeted on 16th December 2020
Nathan Rzepecki liked on 16th December 2020
Ligia Nopcea liked on 16th December 2020
Tauseef shah liked on 16th December 2020
Daniel liked on 16th December 2020
Saumya Sthapit liked on 16th December 2020
Cao Minh Đức liked on 16th December 2020
ali ali liked on 16th December 2020
Raul liked on 16th December 2020
Mark Armstrong liked on 16th December 2020
Hamza Makraz liked on 16th December 2020
Iago liked on 16th December 2020
DavideM liked on 16th December 2020
Jens liked on 16th December 2020
🇦🇷 Carolina 🇦🇷 liked on 16th December 2020
FoFa liked on 16th December 2020
Owen Melbourne replied on 15th December 2020
Make sure your PHP and NGINX/Apache timeouts are long enough 😂
Oliver Sarfas replied on 15th December 2020
Is there a similar for import streaming? The other packages ive seen always hit memory limits for csv!
Vitor liked on 15th December 2020
Vaibhav Pardeshi liked on 15th December 2020
iya liked on 15th December 2020
Rohan Jalil retweeted on 15th December 2020
Brenda 💕 liked on 15th December 2020
/dev/faisal liked on 15th December 2020
Shawn Hooper liked on 15th December 2020
Ant liked on 15th December 2020
Stephen Opoku Damoah🎼🎹 liked on 15th December 2020
Niko Halink liked on 15th December 2020
Niyati Rabadiya retweeted on 15th December 2020
Edwin I Arellano liked on 15th December 2020
🇳🇬 Oyin liked on 15th December 2020
Niyati Rabadiya liked on 15th December 2020
Kalle Palokankare liked on 15th December 2020
StoicDojo liked on 15th December 2020
Román Miranda liked on 15th December 2020
Khaled Al Wakeel liked on 15th December 2020
Ray Bonander liked on 15th December 2020
Vincenzo Raco #StayHome liked on 15th December 2020
Samuel Munoz liked on 15th December 2020
TheDeveloper liked on 15th December 2020
Songhua Hu liked on 15th December 2020
Vaggelis Yfantis liked on 15th December 2020
Jasper Helmich replied on 15th December 2020
In your blog post, you mention "This solution will only work for CSV files. Excel files are not streamable." - freek.dev/1515-streaming… But in the github readme file, you have an example stream downloading an excel file github.com/spatie/simple-… To stream or not to stream XLSX?
/e/halil 💻🎧📌 retweeted on 15th December 2020
Alan Wynn ツ retweeted on 15th December 2020
Alan Wynn ツ liked on 15th December 2020
devcasts liked on 15th December 2020
Hossein Rafiee liked on 15th December 2020
Avenger liked on 15th December 2020
सोझो केटो liked on 15th December 2020
Michael Aguiar liked on 15th December 2020
/e/halil 💻🎧📌 liked on 15th December 2020
Khin Me Me Latt liked on 15th December 2020
woody mendoza liked on 15th December 2020
Livewire wrecked me 🤯 retweeted on 15th December 2020
Mark Sitko liked on 15th December 2020
Willan Correia liked on 15th December 2020
Livewire wrecked me 🤯 liked on 15th December 2020
Aqil Hoshmand liked on 15th December 2020
Daniel liked on 15th December 2020
Parthasarathi G K liked on 15th December 2020
Ravi Teja liked on 15th December 2020
Thijs van den Anker liked on 15th December 2020
Great stuffs! Thanks for sharing 😊
Mithicher Baro retweeted on 15th December 2020
Stuart Harrison liked on 15th December 2020
Jk Que liked on 15th December 2020
Pradeep Rajvanshi liked on 15th December 2020
Julio Serpone liked on 15th December 2020
Mithicher Baro liked on 15th December 2020
Matt Lynch liked on 15th December 2020
/dev/gooners liked on 15th December 2020
/dev/gooners retweeted on 15th December 2020
Mohsin Patel retweeted on 15th December 2020
Tonistack liked on 15th December 2020
ArielMejiaDev retweeted on 15th December 2020
Akinyosoye Gabriel Oluwafemi retweeted on 15th December 2020
Mickaël Isaert liked on 15th December 2020
ArielMejiaDev liked on 15th December 2020
Akinyosoye Gabriel Oluwafemi liked on 15th December 2020
Mohsin Patel liked on 15th December 2020
Manuk Minasyan liked on 15th December 2020
eraldgreca liked on 15th December 2020
Rodrigue Jm Tusse retweeted on 15th December 2020
David Cottila retweeted on 15th December 2020
Meet Thosar liked on 15th December 2020
Rodrigue Jm Tusse liked on 15th December 2020
Ivan Bernat liked on 15th December 2020
Wyatt liked on 15th December 2020
Noe liked on 15th December 2020
Daniele Rosario liked on 15th December 2020
Simon Blonér liked on 15th December 2020
Francis Morissette liked on 15th December 2020
Simon Kollross liked on 15th December 2020
Cameron Scott liked on 15th December 2020
James Thatcher liked on 15th December 2020
Haneef Ansari 🍭 liked on 15th December 2020
Hanny Ramzy liked on 15th December 2020
patrick cool liked on 15th December 2020
ahgood liked on 15th December 2020
Philipp Bräutigam liked on 15th December 2020
Anthony liked on 15th December 2020
Richard Radermacher liked on 15th December 2020
Abdurrahman Kahraman liked on 15th December 2020
Omar Andrés Barbosa Ortiz liked on 15th December 2020
Jaume Capdevila liked on 15th December 2020
barthelemy.ehui retweeted on 15th December 2020
iBet7o retweeted on 15th December 2020
Taalay Eshen liked on 15th December 2020
iBet7o liked on 15th December 2020
Balero liked on 15th December 2020
Harry Gulliford liked on 15th December 2020
Specs liked on 15th December 2020
Gautam Kumar liked on 15th December 2020
Adam Pacz liked on 15th December 2020
Jose Alberto Lopez liked on 15th December 2020
Niff liked on 15th December 2020
Tom liked on 15th December 2020
Alexis Rojas liked on 15th December 2020
Elie Andraos liked on 15th December 2020
Sami Mazouz liked on 15th December 2020
Fong Hok Kin liked on 15th December 2020
barthelemy.ehui liked on 15th December 2020
Julien Bourdeau liked on 15th December 2020
Tobias Scharikow 🦊 liked on 15th December 2020
Danilo Polani liked on 15th December 2020
Emerson Broga liked on 15th December 2020
Maarten de Graaf liked on 15th December 2020
Olu Ude liked on 15th December 2020
Waqar Zafar Tarar liked on 15th December 2020
K. liked on 15th December 2020
Matthew Poulter liked on 15th December 2020
Jānis Lācis liked on 15th December 2020
Nii Adjetey Sowah liked on 15th December 2020
RR liked on 15th December 2020
Buggyman liked on 15th December 2020
Mark Topper liked on 15th December 2020
nick sorrell liked on 15th December 2020
Asif Mulla liked on 15th December 2020
Alexander Makhaev retweeted on 15th December 2020
Do Hoang Dinh Tien retweeted on 15th December 2020
kapil retweeted on 15th December 2020
PhanTOM retweeted on 15th December 2020
PHP Synopsis retweeted on 15th December 2020
M. Vug liked on 15th December 2020
Serkan Erip liked on 15th December 2020
محمد راشد liked on 15th December 2020
Alexander Makhaev liked on 15th December 2020
K PradeepKumar 🚀 liked on 15th December 2020
Owen Voke liked on 15th December 2020
Ken V. liked on 15th December 2020
Prince John Santillan liked on 15th December 2020
Samir Djelal ⚡️ liked on 15th December 2020
maesbn 🌲 liked on 15th December 2020
kevinhenney liked on 15th December 2020
Reza liked on 15th December 2020
Patrick Brouwers liked on 15th December 2020
Emre liked on 15th December 2020
Craig Lovelock liked on 15th December 2020
⚡️ Arash ⌘ آرش ⚡️ liked on 15th December 2020
Klemen Magajne liked on 15th December 2020
Ismail Xahy liked on 15th December 2020
Zach Leigh liked on 15th December 2020
Tahseen Alaa liked on 15th December 2020
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