Laravel 10/9 import export excel and csv to database; Through this tutorial, i am going to show you how to import and export excel and file from database in Laravel 10/9 apps.
In this example tutorial, i will use the Maatwebsite Package to import export large excel and csv file into mysql database in PHP Laravel 10/9. Before While importing file to database, i have validated files on laravel controller import method.
Laravel 10/9 Import and Export Excel and CSV File to Database Example
Follow the below given simple steps to import and export excel and csv file into database in Laravel 10/9 apps:
- Step 1 – Installing Laravel 10/9 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 10/9 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 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 10/9 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 10/9. 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');
}
/**
* @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 10/9 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 10/9</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
Be First to Comment