Laravel 7 | How to import data in excel to databases | Inert Edit Delete
Step 1: Install Laravel 7 Project
In the first step, we will install Laravel 7 application using bellow command, So open your terminal OR command prompt and run bellow command:
composer create-project --prefer-dist laravel/laravel blog
Step 2: Install Maatwebsite Package
In this step we need to install Maat website package via the Composer package manager, so one your terminal and fire bellow command:
composer require maatwebsite/excel
Now open config/app.php file and add service provider and alias.
config/app.php
'providers' => [
....
Maatwebsite\Excel\ExcelServiceProvider::class,
],
'aliases' => [
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
],
Then you have to also make publish configuration file by using the following command:
php artisan vendor:publish
It will create a new config file named "config/excel.php".
Step 1: Route
<?php use Illuminate\Support\Facades\Route; /* |-------------------------------------------------------------------------- | Web Routes |-------------------------------------------------------------------------- | | Here is where you can register web routes for your application. These | routes are loaded by the RouteServiceProvider within a group which | contains the "web" middleware group. Now create something great! | */ // ======================== import ======================== // Route::get('/import_excel', 'ImportExcelController@index')->name('import_excel'); Route::post('/import_excel/import', 'ImportExcelController@import'); // ------------------------ delete ------------------------- // Route::get('import_excel/{importID}','ImportExcelController@importDelete')->name('importDelete'); // ------------------------ insert ------------------------ // Route::post('importInsert','ImportExcelController@importInsert')->name('importInsert'); // ------------------------ update ------------------------ // Route::post('importUpdate','ImportExcelController@importUpdate')->name('importUpdate');
Step 2: migrations
In the first 2, we will install Laravel 7 run php artisan migrate
<?php use Illuminate\Database\Migrations\Migration; use Illuminate\Database\Schema\Blueprint; use Illuminate\Support\Facades\Schema; class CreateImportExcelModelsTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('import_excels', function (Blueprint $table) { $table->id(); $table->string('No')->nullable(); $table->string('Name')->nullable(); $table->string('Sex')->nullable(); $table->string('Age')->nullable(); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::dropIfExists('import_excels'); } }
Step 3: import_excel.blade.php
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script> <div class="container"> <div class="pcoded-content"> <div class="pcoded-inner-content"> <!-- Main-body start --> <div class="main-body"> <div class="page-wrapper"> <!-- Page-header start --> <div class="page-header"> <div class="row align-items-end"> <div class="col-lg-8"> <div class="page-header-title"> <div class="d-inline"> <h4>Excel File</h4> <span>Form for Import</span> </div> </div> </div> </div> </div> <!-- Page-header end --> <form action="" method="post" class="j-pro" id="j-pro"></form> <div class="j-wrapper"> <form method="post" class="j-pro" id="j-pro" enctype="multipart/form-data" action="{{ url('/import_excel/import') }}" enctype="multipart/form-data" novalidate=""> {{ csrf_field() }} <div class="j-content"> <!-- start file --> <div class="j-unit"> <div class="j-input j-append-big-btn"> <label class="j-icon-left" for="file_input"> <i class="icofont icofont-download"></i> </label> <div class="j-file-button"> Browse <input type="file" name="select_file" onchange="document.getElementById('file_input').value = this.value;"> </div> <input type="text" id="file_input" readonly="" placeholder="no file selected"> <span class="j-hint">.xls, .xslx</span> </div> </div> </div> <!-- end /.content --> <div class="j-footer"> <button type="submit" name="upload" class="btn btn-primary">Upload</button> </div> <!-- end /.footer --> </form> </div> <!-- Page-body end --> @if(count($errors) > 0) <div class="text-danger text-center"id="alert-error"> <ul> @foreach($errors->all() as $error) <li>{{ $error }}</li> @endforeach </ul> </div> @endif <!-- upload --> @if($message = Session::get('success')) <div class="text-success alert-block text-center"> <strong>{{ $message }}</strong> </div> @endif <!-- delete --> @if($message = Session::get('importDelete')) <div class="text-success alert-block text-center"> <strong>{{ $message }}</strong> </div> @endif <!-- insert --> @if($message = Session::get('importInsert')) <div class="text-success alert-block text-center"> <strong>{{ $message }}</strong> </div> @endif <!-- update --> @if($message = Session::get('importUpdate')) <div class="text-success alert-block text-center"> <strong>{{ $message }}</strong> </div> @endif <!-- code exit --> @if($message = Session::get('codesExists')) <div class="text-danger alert-block text-center"> <strong>{{ $message }}</strong> </div> @endif <div class="card" style="border-top: 3px solid #404E67;"> <div class="card-header"> <h5>Import Data</h5> <button type="button" class="btn btn-success float-right" data-toggle="modal" data-target="#ImportAdd"><i class="icofont icofont-check-circled"></i>Add</button> </div> <div class="card-block"> <div class="table-responsive dt-responsive"> <table id="dom-jqry" class="table table-sm table-striped table-bordered nowrap"> <thead> <tr> <th>ID</th> <th>Name</th> <th>Sex</th> <th>Age</th> <th>Action</th> </tr> </thead> <tbody> @foreach($data as $row) <tr role="row" class="odd"> <td class="idUpdate">{{ $row->id }}</td> <td class="No">{{ $row->No }}</td> <td class="Name">{{ $row->Name }}</td> <td class="Sex">{{ $row->Sex }}</td> <td class="Age">{{ $row->Age }}</td> <td class="text-center"> <a class="m-r-15 text-muted importEdit" data-toggle="modal" data-idUpdate="'.$row->id.'" data-target="#ImportUpdate">Edit</a> <a href="import_excel/{{ $row->id }}" onclick="return confirm('Are you sure to want to delete it?')" class="text-muted">Delect</a> </td> </tr> @endforeach </tbody> </table> </div> </div> </div> <!-- Modal Add New--> <div class="modal fade" id="ImportAdd" tabindex="-1" role="dialog" style="z-index: 1050; display: none;" aria-hidden="true"> <div class="modal-dialog modal-lg" role="document"> <div class="modal-content"> <div class="modal-header"> <h4 class="modal-title">Add New</h4> <button type="button" class="close" data-dismiss="modal" aria-label="Close"> <span aria-hidden="true"><i class="ti-close"></i></span> </button> </div> <form action="{{ route('importInsert') }}" method = "post"><!-- form add --> {{ csrf_field() }} <div class="modal-body"> <div class="form-group row"> <label class="col-sm-3 col-form-label">No</label> <div class="col-sm-9"> <input type="text" id="No"name="No" class="form-control" placeholder="Enter No"> </div> </div> <div class="form-group row"> <label class="col-sm-3 col-form-label">Name</label> <div class="col-sm-9"> <input type="text" id="Name"name="Name" class="form-control" placeholder="Enter Name"> </div> </div> <div class="form-group row"> <label class="col-sm-3 col-form-label">Sex</label> <div class="col-sm-9"> <input type="text" id="Sex"name="Sex" class="form-control" placeholder="Enter Sex"> </div> </div> <div class="form-group row"> <label class="col-sm-3 col-form-label">Age</label> <div class="col-sm-9"> <input type="text" id="Age"name="Age" class="form-control" placeholder="Enter Age"> </div> </div> </div> <div class="modal-footer"> <button type="button" class="btn btn-danger" data-dismiss="modal"><i class="icofont icofont-eye-alt"></i>Close</button> <button type="submit" id=""name="" class="btn btn-success waves-light"><i class="icofont icofont-check-circled"></i>Save</button> </div> </form><!-- form add end --> </div> </div> </div> <!-- End Modal Add New--> <!-- Modal Update--> <div class="modal fade" id="ImportUpdate" tabindex="-1" role="dialog" style="z-index: 1050; display: none;" aria-hidden="true"> <div class="modal-dialog modal-lg" role="document"> <div class="modal-content"> <div class="modal-header text-write"> <h4 class="modal-title">Update</h4> <button type="button" class="close" data-dismiss="modal" aria-label="Close"> <span aria-hidden="true"><i class="ti-close"></i></span> </button> </div> <form action="{{ route('importUpdate') }}" method = "post"><!-- form delete --> {{ csrf_field() }} <input type = "text"hidden class="col-sm-9 form-control"id ="idUpdate" name ="idUpdate" value="" /> <div class="modal-body"> <div class="form-group row"> <label class="col-sm-3 col-form-label">No</label> <div class="col-sm-9"> <input type="text" id="e_No"name="No" class="form-control" value="" /> </div> </div> <div class="form-group row"> <label class="col-sm-3 col-form-label">Name</label> <div class="col-sm-9"> <input type="text" id="e_Name"name="Name" class="form-control" value="" /> </div> </div> <div class="form-group row"> <label class="col-sm-3 col-form-label">Sex</label> <div class="col-sm-9"> <input type="text" id="e_Sex"name="Sex" class="form-control" value="" /> </div> </div> <div class="form-group row"> <label class="col-sm-3 col-form-label">Age</label> <div class="col-sm-9"> <input type="text" id="e_Age"name="Age" class="form-control" value="" /> </div> </div> </div> <div class="modal-footer"> <button type="button" class="btn btn-danger" data-dismiss="modal"><i class="icofont icofont-eye-alt"></i>Close</button> <button type="submit" id=""name="" class="btn btn-success waves-light"><i class="icofont icofont-check-circled"></i>Update</button> </div> </form><!-- form delete end --> </div> </div> </div> <!-- End Modal Delete--> </div><!-- Main-body end --> </div> </div> </div> </div> <script> // select import $(document).on('click', '.importEdit', function() { var _this = $(this).parents('tr'); $('#idUpdate').val(_this.find('.idUpdate').text()); $('#e_No').val(_this.find('.No').text()); $('#e_Name').val(_this.find('.Name').text()); $('#e_Sex').val(_this.find('.Sex').text()); $('#e_Age').val(_this.find('.Age').text()); }); </script>
Step 4: ExcelImport.php
php artisan make:export Imports--model=User
app/Exports/ExcelImport.php
<?php
namespace App\Imports;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use DB;
class ExcelImport implements ToCollection
{
/**
* @param Collection $collection
*/
public function collection(Collection $collection)
{
DB::table('import_excels')->truncate();
foreach($collection as $key => $value)
{
if($key > 0)
{
DB::table('import_excels')->insert([
'No' =>$value[0]
,'Name' =>$value[1]
,'Sex' =>$value[2]
,'Age' =>$value[3]
]);
}
}
}
}
Step 6: 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 and return a response, so put bellow content in the controller file:
app/Http/Controllers/ImportExcelController.php
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use DB; use App\Imports\ExcelImport; use Maatwebsite\Excel\Facades\Excel; use App\User; use Session; use Hash; use Auth; use Image; use Carbon\Carbon; class ImportExcelController extends Controller { function index() { $data = DB::table('import_excels')->orderBy('id', 'DESC')->get(); $user = Auth::user(); return view('import_excel', compact('data','user',$user)); } function import(Request $request) { $this->validate($request, [ 'select_file' => 'required|mimes:xls,xlsx' ], [ 'select_file.required' => __('.'), ]); $file = $request->select_file; Excel::import(new ExcelImport ,$file); return redirect()->back()->with('success', 'Import successful.!'); } // insert public function importInsert(Request $request) { $request->validate([ 'No' => 'required', 'Name' => 'required', 'Sex' => 'required', 'Age' => 'required' ]); if($request->get('No')) { $codesExists = $request->get('No'); $data = DB::table("import_excels")->where('id', $codesExists)->count(); if($data > 0) { return redirect()->back()->with('codesExists',"Exit already.!"); } else { $importInsert = [ 'No' => $request->No, 'Name' => $request->Name, 'Sex' => $request->Sex, 'Age' => $request->Age ]; DB::table('import_excels')->insert($importInsert); return redirect()->back()->with('importInsert','Insert Sucessful.!'); } } } // update public function importUpdate(Request $request) { $importUpdate = [ 'id' => $request->idUpdate, 'No' => $request->No, 'Name' => $request->Name, 'Sex' => $request->Sex, 'Age' => $request->Age ]; DB::table('import_excels')->where('id',$request->idUpdate)->update($importUpdate); return redirect()->back()->with('importUpdate' ,'Update Successfull.!'); } // delete public function importDelete($importID) { DB::table('import_excels')->where('id',$importID)->delete(); return redirect()->back()->with('importDelete','Delect Successfull.!'); } }
0 Comments
CAN FEEDBACK
Emoji