Laravel 8 import export excel and csv to database example tutorial. In this post, i will share with you how to import and export excel and file from database in laravel 8.
In this example post, i will use the Maatwebsite Package to import export large excel and csv file into mysql database in PHP Laravel 8. Before While importing file to database, i have validated files on laravel controller import method.
And also you will learn how to import export excel file, csv file data to database in laravel 8 using the Maatwebsite Package.
Laravel 8 import export excel file, and csv file to database app will look like in the following image:
- Laravel import excel and csv file validation:
- Succussfully import excel and csv file data into database:
Laravel 8 Import and Export Excel and CSV File to Database Example
- Step 1 – Installing Laravel 8 App
- Step 2 – Configuring Database in .env File
- Step 3 – Installing Maatwebsite For Import Export Excel, CSV
- Step 4 – Run Migration
- Step 5 – Create Routes
- Step 6 – Make Import And Export Class
- Step 7 – Creating Controller
- Step 8 – Create Blade View
- Create Import Form
- Create Export Excel Button on Import Form
- Create Export CSV Button on Import Form
- Step 9 – Start Development Server
Step 1 – Installing Laravel 8 App
In step 1, open your terminal and navigate to your local web server directory using the following command:
//for windows user cd xampp/htdocs //for ubuntu user cd var/www/html
Then install laravel 8 latest application using the following command:
composer create-project --prefer-dist laravel/laravel LaravelImportExport
Step 2 – Configuring Database in .env File
In step 2, open your downloaded laravel 8 app into any text editor. Then find .env file and configure database detail like following:
DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=db name DB_USERNAME=db user name DB_PASSWORD=db password
Step 3 – Installing Maatwebsite For Import Export Excel, CSV
In step 3, Navigate to your downloaded LaravelImportExport directory. And then install Maatwebsite Packages in your laravel 8. Open terminal and run the following command:
cd / LaravelImportExport composer require maatwebsite/excel
Then configure this package in app.php file, which is located inside config directory.
Add the ServiceProvider in config/app.php:
'providers' => [
/*
* Package Service Providers...
*/
Maatwebsite\Excel\ExcelServiceProvider::class,
]
Then add the Facade in config/app.php:
'aliases' => [
...
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
]
To publish the config, run the vendor publish command:
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
Step 4 – Run Migration
Now, open again your terminal and type the following command on cmd to create tables into your selected database:
php artisan migrate
Step 5 – Create Routes
In step 5, open your web.php file, which is located inside routes directory. Then add the following routes into web.php file:
use App\Http\Controllers\ImportExportController;
Route::get('import-excel-csv', [ImportExportController::class, 'index']);
Route::post('import', [ImportExportController::class, 'import']);
Route::get('export', [ImportExportController::class, 'export']);
Step 6 – Make Import And Export Class
In step 6, create import and export excel and csv file class by using the following command, so open command prompt and type the following command:
php artisan make:import UsersImport --model=User php artisan make:export UsersExport --model=User
Then open UsersImport.php, which is located inside app/Imports/ directory. And then add the following code into it:
<?php
namespace App\Imports;
use App\Models\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']),
]);
}
}
Next, open UsersExport.php, which is located inside app/Imports/ directory. And then add the following code into it:
<?php
namespace App\Exports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;
class UsersExport implements FromCollection
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return User::all();
}
}
Step 7 – Creating Controller
In step 7, create import export excel and csv file controller by using the following command:
php artisan make:controller ImportExportController
The above command will create ImportExportController.php file, which is located inside LaravelImportExportCSV/app/Http/Controllers/ directory. So add the following code into it:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Exports\UsersExport;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
use App\Models\User;
class ImportExportController extends Controller
{
/**
* @return \Illuminate\Support\Collection
*/
public function index()
{
return view('import-form');
}
/**
* @return \Illuminate\Support\Collection
*/
public function import(Request $request)
{
$validatedData = $request->validate([
'file' => 'required',
]);
Excel::import(new UsersImport,$request->file('file'));
return redirect('import-excel-csv')->with('status', 'The file has been imported in laravel 8');
}
/**
* @return \Illuminate\Support\Collection
*/
public function export($slug)
{
return Excel::download(new UsersExport, 'users.'.$slug);
}
}
Step 8 – Create Blade View
In step 8, create new blade view file that named import.blade.php inside resources/views directory for importing csv file data into database and export data in csv format in laravel.
- Create Import CSV Form
<form id="import-csv-form" method="POST" action="{{ url('import') }}" accept-charset="utf-8" enctype="multipart/form-data">
@csrf
<div class="row">
<div class="col-md-12">
<div class="form-group">
<input type="file" name="file" placeholder="Choose file">
</div>
@error('file')
<div class="alert alert-danger mt-1 mb-1">{{ $message }}</div>
@enderror
</div>
<div class="col-md-12">
<button type="submit" class="btn btn-primary" id="submit">Submit</button>
</div>
</div>
</form>
- Create Export Excel Button on Import Form
<a href="{{url('export/xlsx')}}" class="btn btn-success mr-1">Export Excel</a>
- Create Export CSV Button on Import Form
<a href="{{url('export/csv')}}" class="btn btn-success">Export CSV</a>
The full source code of import.blade.php file is following:
<!DOCTYPE html>
<html>
<head>
<title>Laravel 8 Import Export Excel and CSV File To Database Example Tutorial</title>
<meta name="csrf-token" content="{{ csrf_token() }}">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
<div class="container mt-5">
@if(session('status'))
<div class="alert alert-success">
{{ session('status') }}
</div>
@endif
<div class="card">
<div class="card-header font-weight-bold">
<h2 class="float-left">Import Export Excel, CSV File In Laravel 8</h2>
<h2 class="float-right"><a href="{{url('export/xlsx')}}" class="btn btn-success mr-1">Export Excel</a><a href="{{url('export/csv')}}" class="btn btn-success">Export CSV</a></h2>
</div>
<div class="card-body">
<form id="import-csv-form" method="POST" action="{{ url('import') }}" accept-charset="utf-8" enctype="multipart/form-data">
@csrf
<div class="row">
<div class="col-md-12">
<div class="form-group">
<input type="file" name="file" placeholder="Choose file">
</div>
@error('file')
<div class="alert alert-danger mt-1 mb-1">{{ $message }}</div>
@enderror
</div>
<div class="col-md-12">
<button type="submit" class="btn btn-primary" id="submit">Submit</button>
</div>
</div>
</form>
</div>
</div>
</div>
</body>
</html>
Step 9 – Start Development Server
In step 9, open your browser and fire the following url into your browser:
http://127.0.0.1:8000/import-excel-csv