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-

Laravel Excel Composer Install
Laravel Excel Composer Install

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-

valid-excel-file-laravel-excelr.jpg

After uploading it the screen will like- valid-excel-file-laravel-excel-accept-laravel.jpg

Now go to our main database and see the data is really successfully inserted. valid-excel-file-laravel-excel-laravel.jpg

Download Full Source Code From here

Advertisements