Hello Dev, Today we are going to learn Laravel 10 Import Export Excel & CSV File to Database Example. This tutorial will cover on how to import and export excel or csv file data to database with example.
This tutorial will give you a simple example of laravel 10 import export excel & csv file to database. In this tutorial, you will learn step by step how to import and export excel & csv file to database in laravel 10 using
maatwebsite/excel package.
Steps for Laravel 10 Import Export Excel & CSV File to Database Example:
Step 1: Installing fresh new Laravel 10 or 10+ Application
Step 2: Creating Database and .env Configure Database Connection
Step 3: Installing maatwebsite/excel Package
Step 4: Creating Dummy Records
Step 5: Creating Import Class
Step 6: Creating Controller
Step 7: Creating Routes
Step 8: Creating Blade File
Step 9: Testing Import Export
Step 1: Installing fresh new Laravel 10 or 10+ Application
We are going to install a fresh new Laravel Application. To install a laravel application run the following code in terminal.
composer create-project laravel/laravel {directory} 10.0 --prefer-distYou can ignore this step if you already have an application downloaded or else, run the following command to create a new laravel project.
Step 2: Creating Database and .env Configure Database Connection
In this type of project, we must give precedence to the database connection, generically it should be configured before getting started. Incorporate the following code in .env file with your database details.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=root
DB_PASSWORD=Step 3: Installing maatwebsite/excel Package
Commonly, to complete our foundational work, we require a third-party package. Ideally, we are talking about the Laravel-Excel plugin by Maatwebsite. It provides the robust mechanism to deal with Excel exports and imports in Laravel. In response, It has got the immense love of artisan’s on GitHub.
Run command to install the package.
composer require maatwebsite/excelRegister Plugin’s Service in Providers & Aliases
You can have the following code placed inside the config/app.php file.
'providers' => [
.......
.......
Maatwebsite\Excel\ExcelServiceProvider::class,
],
'aliases' => [
.......
.......
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
],Execute the vendor, publish command, and publish the config.
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"Step 4: Creating Dummy Records
In the First step, We migrate the user table. After migration run successfully We moved to the second step.
php artisan migrateIn the Second Step, We generate the fake record. Here We use tinker to generate the fake records. You can use a different method as of your requirement.
php artisan tinkerAfter Opening the tinker, you need to run this command to generate the fake records in our database.
User::factory()->count(100)->create();Step 5: Creating Import Class
The easiest way to create an import class is to use the make:import artisan command.
php artisan make:import UsersImport --model=UserIt should have created a UsersImport class in App/Imports. Because we used the --model option, the import already implements the ToModel concern. We can now implement the model method as follows:
<?php
namespace App\Imports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithValidation;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Illuminate\Http\Request;
class UsersImport implements ToCollection, WithHeadingRow, WithValidation, ToModel
{
private $rows = 0;
private $request = '';
public function __construct(Request $request){
$this->request = $request;
}
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function collection(Collection $collection){
//this loop use for group only
foreach($collection as $key => $value){
$user = new User();
$user->user_name = $value['user_name'];
$user->user_email = $value['user_email'];
$user->save();
}
}
public function model(array $row)
{
++$this->rows;
}
public function getRowCount(): int
{
return $this->rows;
}
public function rules(): array
{
return [
// Can also use callback validation rules
'user_name' => function ($attribute, $value, $onFailure) {
if (empty($value)) {
$onFailure('User name is empty or invalid please check and try again !');
}
}
];
}
}Step 6: Create a Controller
Now, you need to create a controller that will handle the import and export operations. You can create a controller using the following command:
php artisan make:controller UsersController
In this step, you’ll add the code that imports the data from the Excel or CSV file into the users table in your database. Open the UsersController file and add the following code:
<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use App\Models\User;
use Illuminate\Http\Request;
use App\Imports\UsersImport;
use Excel;
class UsersController extends Controller{
/**
* Create a new controller instance.
*
* @return void
*/
public function __construct(){
$this->middleware('auth');
}
public function index(Request $request){
return view('users.import');
}
public function bulkImport(Request $request){
$validated = $request->validate(['excel_file' => 'required|mimes:xlsx']);
try{
Excel::import(new UsersImport($request),$request->file('excel_file'));
} catch (\Maatwebsite\Excel\Validators\ValidationException $e) {
return back()->with('error', 'Check Your Excel File is incorrect data format !');
}
return back()->with('success', 'Data has been import successfully !');
}
}Step 7: Create the Route
Route::get('/users/bulk/import', [UsersController::class, 'index'])->name('import.users');
Route::post('/users/bulk/import/upload', [UsersController::class, 'bulkImport'])->name('import.users.upload');Step 8: Create the View
We have reached the last step. In general, here we need to formulate the view for handling importing and exporting through the frontend. Create a resources/views/users/import.blade.php file to set up the view. Place the following code inside the blade view file:
<form class="form" method="POST" enctype="multipart/form-data" action="{{route('import.users.upload')}}">
@csrf
<div class="card-body">
<div class="form-group row">
<div class="col-lg-6">
<label>Excel File:</label>
<div></div>
<div class="custom-file">
<input type="file" class="custom-file-input form-control-sm" id="customFile" name="excel_file"/>
<label class="custom-file-label" for="customFile">Choose file</label>
</div>
@error('excel_file')
<span class="text-danger">{{ $message }}</span>
@enderror
</div>
</div>
<div class="card-footer">
<button type="submit" class="btn btn-outline-success btn-sm mr-2"><i class="flaticon2-add-1"></i> Submit</button>
<a type="reset" class="btn btn-outline-danger btn-sm"><i class="flaticon2-refresh-button"></i> Cancel</a>
</div>
</form>Step 9: Testing import excel
Lastly, we have to run the Laravel application, for this, we have to go to the command prompt, and write the following command:
php artisan serveAfter executing this command, Open http://localhost:8000/users/bulk/import in your browser.