Importing Data from CSV or Excel and Exporting Table Schema in Laravel 9
Importing and exporting data is a crucial aspect of web applications, as it facilitates the smooth processing of data in batches. With Laravel, the process of exporting data is simplified through the Laravel Excel package, which eliminates the need for extensive code.
The Laravel Excel package also supports data import from multiple formats such as XLSX, CSV, XLS, HTML, and more. In this tutorial, we will explore how to import data from Excel to a MySQL database and also demonstrate how to export a sample sheet that guides users on the correct Excel format to import 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 Import class
Step 8: Create an ExportSchema class
Step 9: Create a Controller
Step 10: Create a View
Step 11: 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
The given code is an implementation of the ToModel, WithHeadingRow, and withStartRow interfaces from the Maatwebsite\Excel\Concerns namespace. The ToModel interface is used for mapping data to Eloquent models, the WithHeadingRow interface is used to indicate that the imported file has a header row, and the withStartRow interface is used to specify the starting row for data mapping.
The PostImport class contains two methods: startRow() and model(). The startRow() method returns the starting row for the data mapping process, which in this case is the second row. The model() method maps the data from the imported file to an instance of the Post model.
Step 8: Create an ExportSchema class
Run the below command to create an import class:
php artisan make:export PostExportSchema --model=Post
Then open it from App\Export\PostExport.php and paste the below code
<?php
namespace App\Exports;
use App\Models\Post;
use Maatwebsite\Excel\Concerns\FromCollection;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
class PostExportSchema implements FromQuery, WithHeadings
{
protected $id;
public function __construct($id)
{
$this->id = $id;
}
public function headings(): array
{
$columns = DB::select(DB::raw('SHOW columns FROM posts'));
return array_map(function ($column) {
return $column->Field;
}, $columns);
}
public function query()
{
return DB::table('posts')->where('id', $this->id)->orderBy('id');
}
}
Step 9: 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\Imports\PostImport;
use App\Exports\PostExportSchema;
class PostController extends Controller
{
public function index()
{
$post = Post::get();
return view('posts.index', compact('post'));
}
public function import(Request $request){
Excel::import(new PostImport, $request->file('file')->store('files'));
return redirect()->back();
}
public function post_export_schema(){
// Excel::store(new PostExportSchema(1), 'post.csv');
return Excel::download(new PostExportSchema(1), 'posts.csv');
}
}
Step 11: 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-2">
<a href="{{ url('post_export_schema') }}" class="btn btn-primary">Export
Demo
file</a>
</div>
<div class="col-md-3">
<form action="{{ route('import') }}" method="POST"
enctype="multipart/form-data">
@csrf
<div class="input-group">
<input type="file" name="file" class="form-control" required
aria-describedby="basic-addon2">
<button type="submit" class="input-group-text btn btn-primary"
id="basic-addon2">Import</button>
</div>
</form>
</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 2: Configure Database in the .env file
Once you have installed your Laravel application, it is crucial to create a database and configure the .env file accordingly. Here is an example that illustrates how to configure your .env file:
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel_export_import_app
DB_USERNAME=root
DB_PASSWORD=
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 install the 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('/', function () {
return view('welcome');
});
Route::get('posts', [PostController::class, 'index'])->name('items.filter');
Route::post('/import',[PostController::class, 'import'])->name('import');
Route::get('/post_export_schema', [PostController::class, 'post_export_schema'])->name('post_export_schema');
Step 7: Create an Import class
Run the below command to create an import class:
php artisan make:import PostImport --model=Post
Then open it from App\Import\PostImport.php and paste the below code
<?php
namespace App\Imports;
use App\Models\Post;
use Illuminate\Support\Carbon;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\withStartRow;
class PostImport implements ToModel , WithHeadingRow, withStartRow
{
public function startRow(): int
{
return 2;
}
public function model(array $row)
{
//dd($row);
// trim the spaces from each value in the row
$row = array_map('trim', $row);
return new Post([
'title' => $row['title'],
'description' => $row['description'],
'status' => $row['status'],
'created_at' => Carbon::createFromFormat('Y-m-d H:i:s', $row['created_at']),
'updated_at' => Carbon::createFromFormat('Y-m-d H:i:s', $row['updated_at'])
]);
}
}
