How to Import and Export SQL data to an Excel file
Step 1 : Install Laravel Project
composer create-project --prefer-dist laravel/laravel blogStep 2 : Install Maatwebsite Package
If you want to import excel file data in Laravel, you have to first download Maatwebsite package, this package will communicate with Excel spreadsheet data.
composer require maatwebsite/excelNow 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,
],Make publish configuration file by using following command :
php artisan vendor:publishIt will create new config file named config/excel.php.
Step 3 : Create Dummy Records
php artisan migrateGenerate dummy users :
php artisan tinkerfactory(App\User::class, 20)->create();Step 4 : Add Routes
Open your routes/web.php file and add following route.
#routes/web.php
Route::get('export', 'MyUserController@export')->name('export');
Route::get('importExportView', 'MyUserController@importExportView');
Route::post('import', 'MyUserController@import')->name('import');Step 5 : Create Import Class
php artisan make:import UsersImport --model=User#app/Imports/UsersImport.php
namespace App\Imports;
use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
class UsersImport implements ToModel
{
public function model(array $row)
{
return new User([
'name' => $row[0],
'email' => $row[1],
'password' => \Hash::make('devnotes@016'),
]);
}
}Step 6 : Create Export Class
php artisan make:export UsersExport --model=User#app/Exports/UsersExport.php
namespace App\Exports;
use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;
class UsersExport implements FromCollection
{
public function collection()
{
return User::all();
}
}Step 7 : Create Controller
#app/Http/Controllers/MyUserController.php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Exports\UsersExport;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
class MyUserController extends Controller
{
public function importExportView()
{
return view('import');
}
public function export()
{
return Excel::download(new UsersExport, 'user.xlsx');
}
public function import()
{
Excel::import(new UsersImport,request()->file('file'));
return back();
}
}Step 8 : Create Blade File
#resources/views/index.blade.php
<!DOCTYPE html>
<html>
<head>
<title>How to Import and Export SQL data to an Excel file</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">
How to Import and Export SQL data to an Excel file
</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 Info</button>
<a class="btn btn-warning" href="{{ route('export') }}">Export User Info</a>
</form>
</div>
</div>
</div>
</body>
</html>
