Laravel with Packages | Laravel Import Export Excel & CSV File Tutorial
Hi Artisan,
In this tutorial, we will learn how to import export Excel & CSV files in laravel 7/6. I wrote a simple tutorial for laravel 7/6 maatwebsite/excel. using maatwebsite/excel we can import export excel or CSV from the database in the laravel 7/6 application.
In this example I wrote the full script of how to import CSV files from databases in laravel 7/6 and how to export CSV files from databases in laravel 6. you can easily download excel & CSV files from databases in laravel 7/6.
We will simply create import data to CSV, Xls file, and also we can import data to the database using CSV file in laravel 6 application.
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 a 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 7/6
Here, we need to install the Laravel 6 application using below command, So open your terminal OR command prompt and run the below command:
composer create-project --prefer-dist laravel/laravel blog
Step 2: Install maatwebsite/excel Package
In this step we need to install maatwebsite/excel package via the Composer package manager, so one your terminal and fire bellow command:
composer require maatwebsite/excel
Now open the config/app.php file and add the service provider and alias.
config/app.php
'providers' => [....Maatwebsite\Excel\ExcelServiceProvider::class,],'aliases' => [....'Excel' => Maatwebsite\Excel\Facades\Excel::class,],
Step 3: Create Dummy Records
In this step, we have to require a “users” table with some dummy records, so we can simply import and export. So first you have to run the default migration that was 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 tinkerfactory(App\User::class, 20)->create();
Step 4: Add Routes
In this step, we need to create a route for the import-export file. so open your “routes/web.php” file and add the following route.
routes/web.php
Route::get('importExportView', 'MyController@importExportView');Route::get('export', 'MyController@export')->name('export');Route::post('import', 'MyController@import')->name('import');
Step 5: Create Import Class
In maatwebsite 3 versions provide a way to build 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 the following command and change the 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;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class UsersImport implements ToModel, WithHeadingRow
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new User([
'name' => $row['name'],
'email' => $row['email'],
'password' => \Hash::make($row['password']),
]);
}
}
Step 6: Create Export Class
maatwebsite 3 versions provide a way to build 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 the following command and change the 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 a new controller as MyController in this path “app/Http/Controllers/MyController.php”. this controller will manage all importExportView, export and import request,s and return responses, so put the below content in the 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 the last step, let’s create import.blade.php(resources/views/import.blade.php) for the layout and we will write the design code here and put the following code:
resources/views/import.blade.php
<!DOCTYPE html>
<html>
<head>
<title>Laravel 6 Import Export Excel to database Example - raviyatechnical</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 - raviyatechnical</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 application.
Now we are ready to run our example so run the below command so quick run:
php artisan serve
Now you can open the below URL on your browser:
http://localhost:8000/importExportView
I hope it can help you...