Today I’ll show How to import Excel file in Laravel 5 and insert the data in the database | Laravel Tutorials. Stay with me and learn easily.
To communicate an excel file in laravel there is Laravel-Excel. The Laravel Excel is in the Github – https://github.com/Maatwebsite/Laravel-Excel
From them,
Laravel Excel is an eloquent way of importing and exporting Excel and CSV files for Laravel with the power of PHPExcel
Steps to use Laravel Excel:
Step 1:
Run the composer command to install Laravel-Excel
composer require "maatwebsite/excel:~2.1.0"
It will take some time depending on your internet connection. The cmd terminal will look like-
Now add the ServiceProvider to the providers array in config/app.php
'providers' => [ .... Maatwebsite\Excel\ExcelServiceProvider::class, ],
Now add the aliases config/app.php
'aliases' => [ .... 'Excel' => Maatwebsite\Excel\Facades\Excel::class, ],
Step 2:
Create a database in Xampp/Mamp/Wamp called – LaravelExcelDemo
Then in .env file configure this:
DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=LaravelExcelDemo DB_USERNAME=root DB_PASSWORD=
Step 3:
We’ll test this by making a student table.
Create a model Student with migration. Run the command-
php artisan make:model Student --migration
In students migration just add some fields for our testing.
public function up() { Schema::create('students', function (Blueprint $table) { $table->increments('id'); $table->string('name', 50); $table->string('email', 50); $table->string('phone', 20); }); }
In App/Providers>AppServiceProvider
use Illuminate\Support\Facades\Schema;
and in boot function
public function boot() { Schema::defaultStringLength(191); }
Run the migration command-
php artisan migrate
Step 4:
Create Route and controller setup:
php artisan make:controller StudentController
In route,
Route::get('/', 'StudentController@index')->name('index'); Route::post('import', 'StudentController@import')->name('import');
Make add-student.blade.php in view folder and there the full form of a file uploading
<!doctype html> <html lang="{{ app()->getLocale() }}"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="stylesheet" href="{{ URL::to('css/app.css') }}"> <title>Laravel Excel Import csv and XLS file in Database</title> <!-- Fonts --> <link href="https://fonts.googleapis.com/css?family=Raleway:100,600" rel="stylesheet" type="text/css"> <!-- Styles --> <style> html, body { background-color: #fff; color: #636b6f; font-family: 'Raleway', sans-serif; font-weight: 100; height: 100vh; margin: 0; padding: 5% } </style> </head> <body> <div class="container"> <h2 class="text-center"> Laravel Excel/CSV Import </h2> @if ( Session::has('success') ) <div class="alert alert-success alert-dismissible" role="alert"> <button type="button" class="close" data-dismiss="alert" aria-label="Close"> <span aria-hidden="true">×</span> <span class="sr-only">Close</span> </button> <strong>{{ Session::get('success') }}</strong> </div> @endif @if ( Session::has('error') ) <div class="alert alert-danger alert-dismissible" role="alert"> <button type="button" class="close" data-dismiss="alert" aria-label="Close"> <span aria-hidden="true">×</span> <span class="sr-only">Close</span> </button> <strong>{{ Session::get('error') }}</strong> </div> @endif @if (count($errors) > 0) <div class="alert alert-danger"> <a href="#" class="close" data-dismiss="alert" aria-label="close">×</a> <div> @foreach ($errors->all() as $error) <p>{{ $error }}</p> @endforeach </div> </div> @endif <form action="{{ route('import') }}" method="POST" enctype="multipart/form-data"> {{ csrf_field() }} Choose your xls/csv File : <input type="file" name="file" class="form-control"> <input type="submit" class="btn btn-primary btn-lg" style="margin-top: 3%"> </form> </div> </body> </html>
In this view I’ve made the validations and the success messages that we can get the exact user friendly output from this. Then add the form which route is in route(‘import’). Now, the StudentController is this->
<?php namespace App\Http\Controllers; use Illuminate\Support\Facades\DB; use Illuminate\Http\Request; use Session; use Excel; use File; class StudentController extends Controller { public function index() { return view('add-student'); } public function import(Request $request){ //validate the xls file $this->validate($request, array( 'file' => 'required' )); if($request->hasFile('file')){ $extension = File::extension($request->file->getClientOriginalName()); if ($extension == "xlsx" || $extension == "xls" || $extension == "csv") { $path = $request->file->getRealPath(); $data = Excel::load($path, function($reader) { })->get(); if(!empty($data) && $data->count()){ foreach ($data as $key => $value) { $insert[] = [ 'name' => $value->name, 'email' => $value->email, 'phone' => $value->phone, ]; } if(!empty($insert)){ $insertData = DB::table('students')->insert($insert); if ($insertData) { Session::flash('success', 'Your Data has successfully imported'); }else { Session::flash('error', 'Error inserting the data..'); return back(); } } } return back(); }else { Session::flash('error', 'File is a '.$extension.' file.!! Please upload a valid xls/csv file..!!'); return back(); } } } }
For importing look at the main import function
public function import(Request $request){ //validate the xls file $this->validate($request, array( 'file' => 'required' )); if($request->hasFile('file')){ $extension = File::extension($request->file->getClientOriginalName()); if ($extension == "xlsx" || $extension == "xls" || $extension == "csv") { $path = $request->file->getRealPath(); $data = Excel::load($path, function($reader) { })->get(); if(!empty($data) && $data->count()){ foreach ($data as $key => $value) { $insert[] = [ 'name' => $value->name, 'email' => $value->email, 'phone' => $value->phone, ]; } if(!empty($insert)){ $insertData = DB::table('students')->insert($insert); if ($insertData) { Session::flash('success', 'Your Data has successfully imported'); }else { Session::flash('error', 'Error inserting the data..'); return back(); } } } return back(); }else { Session::flash('error', 'File is a '.$extension.' file.!! Please upload a valid xls/csv file..!!'); return back(); } } }
Now take a valid xls file and upload it
The xls file is look like this-
After uploading it the screen will like-
Now go to our main database and see the data is really successfully inserted.
Thanks for Pingback.. Stay with me and learn advanced things of Laravel..
LikeLike
.
LikeLike
Great tutorial. Easy to follow and easy to modify to fit my specific solution.
LikeLike
i get error like this
Property [admin_id] does not exist on this collection instance.
could u explaine to me where i am doing wrong ? 😦
LikeLike
Show me your import function code and can say something then..
LikeLike
it worked easy and straight forward! thanks a bunch
please show how to display the db info to the views (front end ) 🙂
LikeLike
Hi tnx so much your tutorial is really simply but i have a small issue this load function cant be found ….. this is the error “Call to undefined method Maatwebsite\Excel\Excel::load()”
LikeLike
You may have installed the latest version of Maatwebsite\Excel. Please install the version-2 of Excel. There’s the load() function in version 2. Install Version 2 Link: https://laravel-excel.maatwebsite.nl/docs/2.1/getting-started/installation
LikeLike
saya memakai Database SQL Server 2016, Untuk Import Data dari File Excel ke Database SQL Server tidak bisa lebih dari 2100 Parameter.
bagaimana cara mengatasinya?
Terimaksih
Link: https://ibb.co/WpDdFMF
LikeLike
I use SQL Server 2016 Database, To Import Data from Excel File into SQL Server Database, it cannot exceed 2100 Parameters.
how to handle it?
Thank you
Link : https://ibb.co/WpDdFMF
LikeLike
I got error like this
Property [form_id] does not exist on this collection instance.
Please Help me
LikeLike