Exporting Laravel Eloquent to CSV/XLS

Exporting Laravel Models to CSV or XLS

As I was browsing Twitter, the question came up how you could easily export your Eloquent models to CSV or XLS.
There are many possible ways, so let's inspect a couple of those.

Using pure PHP

Yeah, I know - this might definitely not be the simplest solution - but it is always a good idea to know your tools, so we should at least take a brief look at how you can export your models to CSV with no third-party dependency at all.

PHP has a method called fputcsv, which allows you to write an array of data into a file handle. Pretty easy:

// Open a file handle in 'write' mode for
// a file called export.csv
$data = [
    [1, 'Marcel', '[e-mail]'],
  [2, 'Sebastian', '[e-mail]'],
];

$handle = fopen('export.csv', 'w');

foreach ($data as $row) {
    fputcsv($handle, $row);
}

fclose($handle);

This is pretty straight forward and simple to use. The resulting CSV file looks like this:

1,Marcel,[e-mail]
2,Sebastian,[e-mail]

If you want to use a ; instead of a comma for the separator, you can pass it as a third argument to the fputcsv method.

Now in the example above, we have only used a static data array for our export - not a collection of models. Making this change is pretty easy too:

$data = User::all();

$handle = fopen('export.csv', 'w');

foreach ($data as $row) {
    fputcsv($handle, $row->toArray(), ';');
}

fclose($handle);

In this example, we export all of our users, load them into a $data variable and then loop over these models to write them to the CSV file. Notice that we are using the $row->toArray() method, because fputcsv expects us to pass an array - not an object.

And this works just fine, here's the resulting CSV for this:

1;Marcel;[e-mail];;2021-04-01T06:37:47.000000Z;2021-04-01T06:37:47.000000Z
2;Sebastian;[e-mail];;2021-04-01T06:37:54.000000Z;2021-04-01T06:37:54.000000Z

As you can see, we get more data than in the first example - that's because our models contain more information, such as the created_at and updated_at timestamps. You can also notice an empty value in the CSV export above - that's from the email_verified_at column that ships with Laravel by default, which is NULL in this case.

Alright - so doing this with pure, raw PHP is not as fancy as the usage of a third-party package might be, but its certainly doable. This comes with some flaws though.

Because we have to load all of our models into memory (by stuffing them in our $data variable), this export will use a lot of memory, as your users table grows. An easy fix is the usage of the chunk method. Instead of loading thousands of rows into memory, this method will only receive a subset of Eloquent models and execute a closure with those models, saving a lot of memory.

The fputcsv method rewritten with chunking looks like this:

$handle = fopen('export.csv', 'w');

User::chunk(100, function ($users) use ($handle) {
    foreach ($users as $row) {
        fputcsv($handle, $row->toArray(), ';');
    }
});

fclose($handle);

We create the file handle, and then for a chunk of every 100 users, we put those users into the handle. This way we won't load thousands of users into memory - great!

Third Party Packages

There are a lot of third-party packages out there, that can help you with exporting data to either CSV or Excel. Laravel Excel is the most popular solution for this - by far.
Let's see how we can create our User export with Laravel Excel.

First of all, you need to install the package using composer:

composer require maatwebsite/excel

Next, we can create our first export. Exports are classes that live in your app/Exports folder and they contain all the logic for a given export.

The package comes with an artisan command to create such an Export class for us:

php artisan make:export UsersExport --model=User

Lets take a look at the generated UsersExport class:

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    public function collection()
    {
        return User::all();
    }
}

So all that we do in this class, is we provide a collection method that returns the data that should be exported.

To actually call our export from a controller, you can do this:

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

We can use the Excel facade provided by the package, call the download method to instruct the package that we want to invoke a file download, and last but not least give it an instance of our export, as well as the desired file name for the export.

But what about memory usage...

...you might wonder. If our User model would consist of thousands of rows, because we return User::all(), wouldn't we run into memory issues all over again?
Yes - we would. So let's fix this.

Laravel Excel allows us to also export our models from a query, which is then going to handle the chunking for us. We can modify our UsersExport class like this:

namespace App\Exports;

use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    public function query()
    {
        return User::query();
    }
}

This is now simply returning a base query from our User model, which will then be exported.

GUI solutions

While all the above solutions are great if you want to provide a CSV/XLS export from within your application, you sometimes simply need to export a given set of data to CSV or XLS, which you don't actually need inside of your application.

For example:

• You're running a SaaS and you want to have an export of the latest invoices that were created - you don't need to do this multiple times, so there's no need to add this to your application
• Your boss tells you: "Hey, give me a CSV file with all users that have relation X real quick!"
• Any other "one-off" exports that you want to make for yourself/others

For this, the usage of a GUI based tool can be great. Let me show you how you can create a CSV export using Tinkerwell.

Tinkerwell allows you to connect to your local or remote Laravel applications where you can then evaluate any PHP code within the context of your opened application - and there's no need to install a third-party package for this to work.

This means that we can simply evaluate our fputcsv code snippet on any local or remote Laravel application, like this:

In addition to writing this code yourself, you can also make use of Tinkerwell's table mode. This mode gives you a table view of any collection or array that your PHP code evaluates and it then allows you to browse the data, as well as export it to CSV.

Simply press the "Save CSV" button and you're good to go!

Getting fancier

If you prefer an even simpler solution, you can make use of Invoker. Invoker is a desktop application that, just like Tinkerwell, can connect to local or remote Laravel applications and provides you with an instant admin-panel of your Eloquent models, mailables, notifications, and much more.

Inside of Invoker, you can then browse all of your models, filter them by adding additional Eloquent queries, as well as export the results to XLS/CSV with a click of a button.

It doesn't get easier than that.

So we looked at a couple of different ways how we can export our Laravel models to CSV, either by using pure PHP methods, third-party libraries, or GUI tools for one-off exports and easier data-filtering.