Export large data in excel or csv file [Laravel]

Export large data in excel or csv file [Laravel]

You might be using Maatwebsite\Excel\Facades\Excel with following line of code to export report. If so, for less data, this is not an issue. But if you want to download large data, this code is not recommended.

Excel::download(new SomeExport, $name . '.xlsx');

Well, let me introduce a library for Laravel to export large data easily. It is called FastExcel. And here are the steps you need to follow.

Install FastExcel

Install FastExcel by composer using following command in your terminal.

composer require rap2hpoutre/fast-excel

// If you get this error
// PHP Fatal error:  Allowed memory size of 1610612736 bytes exhausted
// Use this command instead
COMPOSER_MEMORY_LIMIT=-1 composer require rap2hpoutre/fast-excel

Open config/app.php file and add following line of code under aliases key.

'FastExcel' => Rap2hpoutre\FastExcel\Facades\FastExcel::class,

How to use it?

use Rap2hpoutre\FastExcel\FastExcel;
use App\User;

public function exportUser() {
    // Load users
    $users = User::all();
    
    // Export all users in excel
    FastExcel::data($users)->export('file_name.xlsx');
    
    // Or Export all users in csv
    FastExcel::data($users)->export('file_name.csv');
}

Above code will export your file within your server location. But what if you want to actually download it? Well, that is easy too and here is the sample code for it.

use Rap2hpoutre\FastExcel\FastExcel;
use App\User;

public function downloadUser() {
    // Load users
    $users = User::all();
    
    // Export all users in excel
    return FastExcel::data($users)->download('file_name.xlsx');
    
    // Or Export all users in csv
    return FastExcel::data($users)-> download('file_name.csv');
}

Please note, don’t forget to add return before FastExcel class name. If you forget to return, you wont be able to download the file. It will just redirect you to blank screen.

Load large datas?

What if you want to load 10+ million datas? Well, that is possible too. Go through the following to load large datas.

public function downloadUser()
{
        function usersGenerator()
        {
              foreach (User::cursor() as $user) {
                  yield $user;
              }
        }

        return FastExcel::data(usersGenerator())->download('file.csv', function ($user) {
            return [
                'First name' => $user->first_name,
                'Last name' => $user->last_name,
            ];
        });
}

Using cursor, now the app is ready to manage large set of datas easily. Also I have added option to customize excel column as needed. In above example, I have added first and last name only. You can add as you want.

And that’s all you need to know to export or download large set of datas easily. If you have any issue or not able to run the project, you can ask me in comment section.

Hope this blog was helpful.

Thank you.

Leave a Reply

santoshm