Laravel 5.3 - import export csv and excel file into database

Laravel 5.3 release few days ago and they upgrade several feature on Laravel 5.3. They also some changes on directory structure and etc.

So, Today I am going to give you one example how to import and export xls, xlsx and csv files. I also added post for Laravel 5, but this example will help you to implement on your Laravel 5.3 application.

In this example through you can import excel or csv to store in database and export or download excel or csv file from database table. For this example i use maatwebsite package from scratch.

Maatwebsite package though we can easily working with csv and excel file. maatwebsite packages also provide to easily get data, also you can group by data, also create more then one sheet etc.

So, For this example you have to just follow bellow step and you will get output as bellow:

Preview:

Preview of Import File:

Step 1: Install Laravel 5.3 Application

In this step, if you haven't laravel 5.3 application setup then we have to get fresh laravel 5.3 application. So run bellow command and get clean fresh laravel 5.3 application.

composer create-project --prefer-dist laravel/laravel blog

Step 2: Install Package

In this step we have to add maatwebsite package so one your cmd or terminal and fire bellow command:

composer require maatwebsite/excel

After successfully install package, 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,
],

Step 3: Create Item Table and Model

In this step we have to create migration for items table using Laravel 5.3 php artisan command, so first fire bellow command:

php artisan make:migration create_items_table

After this command you will find one file in following path database/migrations and you have to put bellow code in your migration file for create items table.

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateItemsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('items', function (Blueprint $table) {
           $table->increments('id');
           $table->string('title');
           $table->text('description');
           $table->timestamps();
       });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop("items");
    }
}

After create "items" table you should create Item model for items, so first create file in this path app/Item.php and put bellow content in item.php file:

app/Item.php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Item extends Model
{

   public $fillable = ['title','description'];

}

Step 4: Create Route

In this is step we need to create route of import export file. so open your routes/web.php file and add following route.

routes/web.php

Route::get('importExport', 'MaatwebsiteDemoController@importExport');
Route::get('downloadExcel/{type}', 'MaatwebsiteDemoController@downloadExcel');
Route::post('importExcel', 'MaatwebsiteDemoController@importExcel');

Step 5: Create Controller

In this point, now we should create new controller as MaatwebsiteDemoController in this path app/Http/Controllers/MaatwebsiteDemoController.php. this controller will manage all impostExport, downloadExcel and importExcel request and return response, so put bellow content in controller file:

app/Http/Controllers/MaatwebsiteDemoController.php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Item;
use Excel;

class MaatwebsiteDemoController extends Controller
{

	/**
     * Return View file
     *
     * @var array
     */
	public function importExport()
	{
		return view('importExport');
	}

	/**
     * File Export Code
     *
     * @var array
     */
	public function downloadExcel(Request $request, $type)
	{
		$data = Item::get()->toArray();
		return Excel::create('itsolutionstuff_example', function($excel) use ($data) {
			$excel->sheet('mySheet', function($sheet) use ($data)
	        {
				$sheet->fromArray($data);
	        });
		})->download($type);
	}

	/**
     * Import file into database Code
     *
     * @var array
     */
	public function importExcel(Request $request)
	{

		if($request->hasFile('import_file')){
			$path = $request->file('import_file')->getRealPath();

			$data = Excel::load($path, function($reader) {})->get();

			if(!empty($data) && $data->count()){

				foreach ($data->toArray() as $key => $value) {
					if(!empty($value)){
						foreach ($value as $v) {		
							$insert[] = ['title' => $v['title'], 'description' => $v['description']];
						}
					}
				}

				
				if(!empty($insert)){
					Item::insert($insert);
					return back()->with('success','Insert Record successfully.');
				}

			}

		}

		return back()->with('error','Please Check your file, Something is wrong there.');
	}

}

Step 6: Create View

In Last step, let's create importExport.blade.php(resources/views/importExport.blade.php) for layout and we will write design code here and put following code:

resources/views/importExport.blade.php

<html lang="en">
<head>
	<title>Import - Export Laravel 5</title>
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" >
</head>

<body>
<br/>
<br/>
	<div class="container">		
		<div class="panel panel-primary">
		  <div class="panel-heading">
		    <h3 class="panel-title" style="padding:12px 0px;font-size:25px;"><strong>Laravel 5.3 - import export csv or excel file into database example</strong></h3>
		  </div>
		  <div class="panel-body">

		  		@if ($message = Session::get('success'))
					<div class="alert alert-success" role="alert">
						{{ Session::get('success') }}
					</div>
				@endif

				@if ($message = Session::get('error'))
					<div class="alert alert-danger" role="alert">
						{{ Session::get('error') }}
					</div>
				@endif

				<h3>Import File Form:</h3>
				<form style="border: 4px solid #a1a1a1;margin-top: 15px;padding: 20px;" action="{{ URL::to('importExcel') }}" class="form-horizontal" method="post" enctype="multipart/form-data">

					<input type="file" name="import_file" />
					{{ csrf_field() }}
					<br/>

					<button class="btn btn-primary">Import CSV or Excel File</button>

				</form>
				<br/>

		    	
		    	<h3>Import File From Database:</h3>
		    	<div style="border: 4px solid #a1a1a1;margin-top: 15px;padding: 20px;"> 		
			    	<a href="{{ url('downloadExcel/xls') }}"><button class="btn btn-success btn-lg">Download Excel xls</button></a>
					<a href="{{ url('downloadExcel/xlsx') }}"><button class="btn btn-success btn-lg">Download Excel xlsx</button></a>
					<a href="{{ url('downloadExcel/csv') }}"><button class="btn btn-success btn-lg">Download CSV</button></a>
		    	</div>

		  </div>
		</div>
	</div>

</body>

</html>

Now you can check on your laravel application with demo file for testing.

Demo Excel File for Testing :Click Here