Laravel 6.16 Import Export Excel to database Example

Laravel 6.16 Import Export Excel to database Example



In this tutorial, I would like to share with you how to export and import Excel spreadsheet or CSV file to the database in PHP laravel 6.16 framework. I will show you step by step examples of import CSV or excel file and export CSV or excel file using maatwebsite/excel version 3 composer package.
We almost require to implement excel or CSV file upload for data into users' tables, products table etc. because it helps to add multiple records at the same time. So if you are working on a big project then you almost require to upload data using CSV file. You also need to give features to export download CSV files. If you have the same requirement then follow this article and make it for you.


In this example, we will use maatwebsite/excel composer package for import and export tasks. maatwebsite/excel provides an easy way to import and export using database model. maatwebsite/excel updated to version 3 and they provide a great way to import-export data from the database, so first follow a few steps to get an example.
Step 1 : Install Laravel 6.7 Project
In first step, we will install Laravel 5.7 application using bellow command, So open your terminal OR command prompt and run bellow command:
composer create-project --prefer-dist laravel/laravel blog
Step 2: Install Maatwebsite Package
In this step we need to install Maatwebsite package via the Composer package manager, so one your terminal and fire bellow command:
composer require maatwebsite/excel
Now open config/app.php file and add service provider and aliase.
config/app.php
'providers' => [
....
Maatwebsite\Excel\ExcelServiceProvider::class,
],
'aliases' => [
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
],
Then you have to also make publish configuration file by using the following command:
php artisan vendor:publish
It will create a new config file named "config/excel.php".
Step 3: Create Dummy Records
In this step, we have to require the "users" table with some dummy records, so we can simply import and export. So first you have to run default migration that provided by laravel using the following command:
php artisan migrate
After that we need to run the following command to generate dummy users:
php artisan tinker
factory(App\User::class, 20)->create();
Step 4: Add Routes
In this step, we need to create a route of import-export file. so open your "routes/web.php" file and add the following route.
routes/web.php
Route::get('export', 'MyController@export')->name('export');
Route::get('importExportView', 'MyController@importExportView');
Route::post('import', 'MyController@import')->name('import');
Step 5: Create Import Class
maatwebsite 3 versions provide a way to built import class and we have to use it in the controller. So it would be a great way to create a new Import class. So you have to run following command and change following code on that file:
php artisan make:import UsersImport --model=User
app/Imports/UsersImport.php
<?php
namespace App\Imports;
use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
class UsersImport implements ToModel
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new User([
'name' => $row[0],
'email' => $row[1],
'password' => \Hash::make('123456'),
]);
}
}
Step 6: Create Export Class
maatwebsite 3 versions provide a way to built export class and we have to use it in the controller. So it would be a great way to create a new Export class. So you have to run following command and change following code on that file:
php artisan make:export UsersExport --model=User
app/Exports/UsersExport.php
<?php
namespace App\Exports;
use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;
class UsersExport implements FromCollection
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return User::all();
}
}
Step 7: Create Controller
In this step, now we should create new controller as MyController in this path "app/Http/Controllers/MyController.php". this controller will manage all importExportView, export and import request and return response, so put bellow content in controller file:
app/Http/Controllers/MyController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Exports\UsersExport;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
class MyController extends Controller
{
/**
* @return \Illuminate\Support\Collection
*/
public function importExportView()
{
return view('import');
}
/**
* @return \Illuminate\Support\Collection
*/
public function export()
{
return Excel::download(new UsersExport, 'users.xlsx');
}
/**
* @return \Illuminate\Support\Collection
*/
public function import()
{
Excel::import(new UsersImport,request()->file('file'));
return back();
}
}
Step 8: Create Blade File
In Last step, let's create import.blade.php(resources/views/import.blade.php) for layout and we will write design code here and put following code:
resources/views/import.blade.php
<!DOCTYPE html>
<html>
<head>
<title>Laravel 5.7 Import Export Excel to database Example - ItSolutionStuff.com</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
</head>
<body>
<div class="container">
<div class="card bg-light mt-3">
<div class="card-header">
Laravel 6 Import Export Excel to database Example - ItSolutionStuff.com
</div>
<div class="card-body">
<form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">
@csrf
<input type="file" name="file" class="form-control">
<br>
<button class="btn btn-success">Import User Data</button>
<a class="btn btn-warning" href="{{ route('export') }}">Export User Data</a>
</form>
</div>
</div>
</div>
</body>
</html>
Now you can check on your laravel 6.16 application.
I hope it can help you...
php artisan serve
http://127.0.0.1:8000/importExportView
Reactions

Post a Comment

1 Comments

  1. can you make crud laravel datatables with excell and pdf export?

    ReplyDelete

CAN FEEDBACK

Emoji
(y)
:)
:(
hihi
:-)
:D
=D
:-d
;(
;-(
@-)
:P
:o
:>)
(o)
:p
(p)
:-s
(m)
8-)
:-t
:-b
b-(
:-#
=p~
x-)
(k)

close