How to Export Data to CSV or Excel Format in Laravel 9
Updated on
...
11th May 2023
Data import and export is a vital feature for web applications, as it enables easy batch processing of data. With Laravel, there is no need to write extensive code for exporting data, as the Laravel Excel package simplifies the process.
Laravel Excel provides the ability to export data in multiple formats, including XLSX, CSV, XLS, HTML, and more.
In this tutorial, we will see how we can export data in Excel step by step.
For exporting data in Excel format we will follow these simple steps:
- Step 1: Install Laravel Application
- Step 2: Configure Database in the .env file
- Step 3: Create Model, controller, and seeder file using a single command
- Step 4: Migrate the database and seed dummy data
- Step 5: Install the Excel package maatwebsite/excel
- Step 6: Create Routes
- Step 7: Create an export class
- Step 8: Create a Controller
- Step 9: Create a View
- Step 10: Run Laravel Application
Note: If you have already set up your laravel projects you can skip Step 1, Step 2, Step 3, and Step 4. else you can start from Step 1.
Step 1: Install Laravel Application
If you're not familiar with the process, you can refer to the "Install Laravel 9 from Scratch" tutorial. Alternatively, you can run the following command to proceed with this step:
composer create-project laravel/laravel laravel_export_app
Step 2: Configure Database in the .env file
After installing your Laravel application, it is necessary to create a database and configure the .env file appropriately. Below is an example that demonstrates how to configure your .env file:
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel_export_app
DB_USERNAME=root
DB_PASSWORD=
Step 8: Create a Controller
Now open the App/Http/Controllers/PostController.php file and paste the below code.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use App\Models\Post;
use App\Exports\PostExport;
class PostController extends Controller
{
public function index()
{
$post = Post::get();
return view('posts.index', compact('post'));
}
public function post_export(){
return Excel::download(new PostExport, 'posts.xlsx');
}
}
Step 9: Create a View
Now create an index.blade.php file inside the resources/views/posts folder.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="csrf-token" content="{{ csrf_token() }}" />
<title>Try it your self</title>
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet"
integrity="sha384-rbsA2VBKQhggwzxH7pPCaAqO46MgnOM80zW1RWuH61DGLwZJEdK2Kadq2F9CUG65" crossorigin="anonymous">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css"
integrity="sha512-iecdLmaskl7CVkqkXNQ/ZH/XLlvWZOJyj7Yy7tcenmpD1ypASozpmT/E0iPtmFIB46ZmdtAc9eNBvH0H/ZpiBw=="
crossorigin="anonymous" referrerpolicy="no-referrer" />
<script src="https://code.jquery.com/jquery-3.6.4.min.js"
integrity="sha256-oP6HI9z1XaZNBrJURtCoUT5SUnxFr8s3BzRl+cbzUq8=" crossorigin="anonymous"></script>
<style>
body {
background: #ededed;
}
.table_outer {
padding: 20px 0;
}
table td,
table th {
text-overflow: ellipsis;
white-space: nowrap;
overflow: hidden;
}
.card {
border-radius: .5rem;
}
.custom_table tbody .persons {
padding: 0;
margin: 0;
}
.custom_table tbody .persons li {
padding: 0;
margin: 0 0 0 -15px;
list-style: none;
display: inline-block;
}
.custom_table tbody .persons li a {
display: inline-block;
width: 36px;
}
.custom_table tbody .persons li a img {
border-radius: 50%;
max-width: 100%;
}
.custom_table tbody .persons.single li a {
margin-left: 7px;
}
table button.btn {
border-radius: 50%;
width: 30px;
height: 30px;
text-align: center;
line-height: 30px;
padding: 0px !important;
}
table .remove_tr {
box-shadow: 0 0 20px 0 rgba(255, 0, 0, .5);
border: 2px solid rgba(255, 0, 0, 1);
}
</style>
</head>
<body>
<section class="table_outer">
<div class="container">
<div class="row justify-content-center">
<div class="col-12">
<div class="card border-0 shadow">
<div
class="card-header with-border d-flex justify-content-start align-items-center media_card_header">
<div class="container-fluid">
<div class="row">
<div class="col-md-4">
<div>
<a href="{{ url('post_export') }}" class="btn btn-primary">Export Data</a>
</div>
</div>
</div>
</div>
</div>
<div class="card-body">
<div class="table-responsive">
<table class="table table-hover table-striped table-borderless custom_table">
<thead class="table-success">
<tr>
<th scope="col">
<div class="form-check">
<input class="form-check-input" type="checkbox" value=""
id="select_all" />
</div>
</th>
<th scope="col">Title
</th>
<th scope="col">Description</th>
<th scope="col">Status</th>
<th scope="col">feature Image</th>
<th scope="col">ACTION</th>
</tr>
</thead>
<tbody>
@foreach ($post as $item)
<tr class="">
<th scope="row">
<div class="form-check">
<input class="form-check-input checkbox" type="checkbox"
value="" id="flexCheckDefault1"
data-id="{{ $item->id }}" value="{{ $item->id }}" />
</div>
</th>
<td>{{ $item->title }}</td>
<td>
{{ $item->description }}
</td>
<td> {{ $item->status }}</td>
<td>
<ul class="persons single">
<li>
<a href="#">
<img src="https://picsum.photos/id/64/100/100"
alt="Person" class="img-fluid">
</a>
</li>
</ul>
</td>
<td>
<button type="button" class="btn btn-success btn-sm px-2">
<i class="fa-solid fa-pen-to-square"></i>
</button>
<button type="button" class="btn btn-primary btn-sm px-2">
<i class="fa-solid fa-eye"></i>
</button>
<button type="button" class="btn btn-danger btn-sm px-2">
<i class="fa-solid fa-trash"></i>
</button>
</td>
</tr>
@endforeach
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
</section>
<script>
$(document).ready(function() {
// Select/deselect all checkboxes
$('#select_all').click(function() {
if ($(this).is(':checked')) {
$('.checkbox').prop('checked', true);
} else {
$('.checkbox').prop('checked', false);
}
});
// If all checkboxes are selected, select the top checkbox
$('.checkbox').click(function() {
if ($('.checkbox:checked').length === $('.checkbox').length) {
$('#select_all').prop('checked', true);
} else {
$('#select_all').prop('checked', false);
}
});
});
</script>
</body>
</html>
Step 10: Run Laravel Application
Now run the command below to run the app:
php artisan serve
That's it, now you have all done open Chrome or any browser and hit URL Now open http://127.0.0.1:8000/posts or http://localhost:8000/posts.
If you are using this code after downloading from here make sure to run the bellow command then start your application.
composer install
Step 3: Create Model, controller, and seeder file using a single command
php artisan make:model Post -msc
Step 4: Migrate the database and seed dummy data
<?php
namespace Database\Seeders;
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
use Illuminate\Database\Seeder;
use App\Models\Post;
class PostSeeder extends Seeder
{
public function run()
{
for($i =1; $i<=20; $i++){
Post::create([
'title' => 'Test post title '.$i,
'status' => rand(0, 1),
'description' => 'Lorem ipsum dolor sit amet, consectetur adipisicing elit. Odio ratione eius qui',
]);
}
}
}
Now Call it inside DatabaseSeeder.php
public function run()
{
$this->call([
PostSeeder::class,
]);
}
Then execute the following command to create all the tables and seed the dummy data defined in the PostSeeder class:
php artisan migrate --seed
Step 5: Install the Excel package maatwebsite/excel
composer require maatwebsite/excel
If you are getting any issues with the above command you can run the below command to instal laravel excel package.
composer require maatwebsite/excel --ignore-platform-reqs
//or
composer require maatwebsite/excel:3.1 --ignore-platform-reqs
Step 6: Create Routes
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\PostController;
Route::get('posts', [PostController::class, 'index'])->name('posts');
Route::get('/post_export', [PostController::class, 'post_export'])->name('post_export');
Step 7: Create an export class
Now we have to create an export class using the below command
php artisan make:export PostExport --model=PostThen define the functions inside it open App/Postexport.php and paste the below code
<?php
namespace App\Exports;
use App\Models\Post;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class PostExport implements WithHeadings, FromCollection
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
$query_data = Post::select(
'id',
'title',
'description',
'status',
'created_at')->get();
return $query_data;
}
public function headings(): array{
return[
'id',
'Title',
'Descriptions',
'Status',
'Published Date'
];
}
}
If you don't need a heading in your excel sheets then alternatively you can modify the above code like below.
<?php
namespace App\Exports;
use App\Models\Post;
use Maatwebsite\Excel\Concerns\FromCollection;
class PostExport implements FromCollection
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return Post::all();
}
}
