How to Handle Large Data Exports in Laravel Using Laravel Excel
In modern web applications, exporting data to Excel is a frequent and essential feature—whether it’s for generating reports, analyzing metrics, or backing up important records. Fortunately, if you're using Laravel, there's a powerful tool that makes this process simple and efficient: Laravel Excel.
Laravel Excel is a feature-rich package that allows you to export (and even import) Excel and CSV files with minimal setup. Whether you’re dealing with user data, sales reports, or inventory lists, this package helps you create professional, downloadable Excel files with just a few lines of code.
In this blog post, we’ll walk you through how to set up Laravel Excel and demonstrate how to export data effortlessly. Let’s get started!
Why Use Laravel Excel?
Laravel Excel is a powerful and developer-friendly package that streamlines the process of working with Excel and CSV files in Laravel applications. Built on top of PhpSpreadsheet, it brings a clean, expressive syntax to what would otherwise be a complex and tedious task.
Here’s why it stands out:
- Simple API – Laravel Excel offers an intuitive and Laravel-style syntax, making it easy to learn and integrate.
- Fast and Efficient – It’s optimized for performance, even when dealing with large datasets.
- Versatile – You can export collections, arrays, Eloquent models, and even database queries directly to Excel or CSV.
- Supports Imports – In addition to exporting, you can also import data from Excel files into your application.
- Customizable – Format cells, apply styles, headings, and more—giving your Excel files a polished, professional look.
Whether you're building admin dashboards, generating reports, or creating data backups, Laravel Excel makes working with spreadsheets a breeze.
⚙️ 1. Install Laravel Excel
To get started, install the Laravel Excel package using Composer:
composer require maatwebsite/excel
This will pull in the latest version of the package developed by Maatwebsite, built on top of PhpSpreadsheet.
Next, ensure your database tables are ready. If you haven't already set up the default Laravel tables (including the users table), run the migration command:
php artisan migrate
This will create the necessary tables based on Laravel’s default migration files.
2. Create an Export Class
Run the following command to generate an export class:
php artisan make:export UsersExport --model=User
Since your database contains a large number of users (500,000+ records), using get() to fetch all records at once may cause memory exhaustion. Instead, you should use chunking or query-based exports to handle large data efficiently in Laravel Excel.
Solution: Use FromQuery Instead of FromCollection
Instead of FromCollection, use FromQuery, which loads data in chunks.
Now, modify the generated UsersExport.php file located in app/Exports/:
<?php
namespace App\Exports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
class UsersExport implements FromQuery, WithHeadings
{
/**
* @return \Illuminate\Database\Eloquent\Builder
*/
public function query()
{
return User::select('id', 'name', 'email');
}
public function headings(): array
{
return ["ID", "Name", "Email"];
}
}
3. Create a Controller
If you don’t have a UserController, create one using:
php artisan make:controller UserController
Now, modify app/Http/Controllers/UserController.php:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Exports\UsersExport;
use App\Models\User;
use Maatwebsite\Excel\Facades\Excel;
class UserController extends Controller
{
/**
* Display a list of users.
*/
public function index()
{
$users = User::all();
return view('users.index', compact('users'));
}
/**
* Export users to an Excel file.
*/
public function export()
{
return Excel::download(new UsersExport, 'users.xlsx');
}
}
4. Define Routes
In routes/web.php, add the following routes:
use App\Http\Controllers\UserController;
use Illuminate\Support\Facades\Route;
Route::get('/users', [UserController::class, 'index'])->name('users.index');
Route::get('/export-users', [UserController::class, 'export'])->name('users.export');
5. Create a Blade View
Create a Blade file at resources/views/users/index.blade.php:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>User List</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container mt-5">
<h2 class="mb-4">User List</h2>
<a href="{{ route('users.export') }}" class="btn btn-success mb-3">Export to Excel</a>
<table class="table table-bordered">
<thead class="table-dark">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
</tr>
</thead>
<tbody>
@foreach($users as $user)
<tr>
<td>{{ $user->id }}</td>
<td>{{ $user->name }}</td>
<td>{{ $user->email }}</td>
</tr>
@endforeach
</tbody>
</table>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
6. Seed the Users Table
If you need dummy users, create a seeder using:
php artisan make:seeder UsersTableSeeder
Modify database/seeders/UsersTableSeeder.php:
<?php
namespace Database\Seeders;
use Illuminate\Database\Seeder;
use App\Models\User;
use Illuminate\Support\Facades\Hash;
use Faker\Factory as Faker;
class UsersTableSeeder extends Seeder
{
public function run()
{
$faker = Faker::create();
// Create 50 fake users
for ($i = 0; $i < 50; $i++) {
User::create([
'name' => $faker->name,
'email' => $faker->unique()->safeEmail,
'password' => Hash::make('password'), // Default password
]);
}
}
}
Run the seeder:
php artisan db:seed --class=UsersTableSeeder
7. Run the Laravel Project
Start the Laravel development server:
php artisan serve
Now, visit:
http://127.0.0.1:8000/users
Click the Export to Excel button to download the user list.
0 Comments