PHP Laravel 5.5 - import export data into excel and csv using maatwebsite

December 3, 2017 | Category : Laravel 5.5 Laravel 5 Laravel PHP

Hi Guys,

In this article, i will let you know how to import and export excel or csv file in your laravel 5.5 application. Here i will show you full example step by step to import excel file to database and export from database and give it to download.

We will use maatwebsite composer package for import export data into excel and csv file. maatwebsite package is popular for generate csv file and excel file. They also provide to import csv file to database.

Here, in this article i will give you small and simple example of import export data into excel and csv using maatwebsite composer package. So you have to just follow bellow few step to do this:

Step 1: Install maatwebsite Composer Package

First thing is we have to install Laravel maatwebsite package for use Excel facade. So simply run bellow composer command for install package.

composer require maatwebsite/excel

After that you have to add into providers array of configuration file. So let's add following way:

config/app.php

<?php

return [

....

'providers' => [

....

Maatwebsite\Excel\ExcelServiceProvider::class,

],

'aliases' => [

....

'Excel' => Maatwebsite\Excel\Facades\Excel::class,

],

...

Step 2: Create Products Table and Model

Here next, we need create migration for products table using Laravel 5.5 php artisan command, so first fire bellow command:

php artisan make:migration create_products_table

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

<?php


use Illuminate\Support\Facades\Schema;

use Illuminate\Database\Schema\Blueprint;

use Illuminate\Database\Migrations\Migration;


class CreateProductsTable extends Migration

{

/**

* Run the migrations.

*

* @return void

*/

public function up()

{

Schema::create('products', function (Blueprint $table) {

$table->increments('id');

$table->string('title');

$table->text('body');

$table->timestamps();

});

}


/**

* Reverse the migrations.

*

* @return void

*/

public function down()

{

Schema::dropIfExists('products');

}

}

Next, you need to create model for "products" table so just run bellow command and create new model:

php artisan make:model Product

So after run bellow command you will find app/Product.php and put bellow content in Product.php file:

app/Product.php

<?php


namespace App;


use Illuminate\Database\Eloquent\Model;


class Product extends Model

{

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

}

Step 3: Add Routes

In third step, we need to create new routes for export and import data. so open your routes/web.php file and add following route.

routes/web.php

Route::get('import-export-view', 'ExcelController@importExportView')->name('import.export.view');

Route::post('import-file', 'ExcelController@importFile')->name('import.file');

Route::get('export-file/{type}', 'ExcelController@exportFile')->name('export.file');

Step 4: Create ExcelController

In this step, we will create ExcelController with three method importExportView(), importFile() and exportFile(), So add like as bellow added.

app/Http/Controllers/ExcelController.php

<?php


namespace App\Http\Controllers;


use Illuminate\Http\Request;

use DB;

use App\Product;


class ExcelController extends Controller

{

/**

* Create a new controller instance.

*

* @return void

*/

public function importExportView(){

return view('import_export');

}


/**

* Create a new controller instance.

*

* @return void

*/

public function importFile(Request $request){

if($request->hasFile('sample_file')){

$path = $request->file('sample_file')->getRealPath();

$data = \Excel::load($path)->get();


if($data->count()){

foreach ($data as $key => $value) {

$arr[] = ['title' => $value->title, 'body' => $value->body];

}

if(!empty($arr)){

DB::table('products')->insert($arr);

dd('Insert Recorded successfully.');

}

}

}

dd('Request data does not have any files to import.');

}


/**

* Create a new controller instance.

*

* @return void

*/

public function exportFile($type){

$products = Product::get()->toArray();


return \Excel::create('hdtuto_demo', function($excel) use ($products) {

$excel->sheet('sheet name', function($sheet) use ($products)

{

$sheet->fromArray($products);

});

})->download($type);

}

}

Step 5: ADd View files

In last step, we will create one blade file, In this file we will write code for download csv file button and import file form. So let's create both file:

resources/views/import_export.blade.php

<html lang="en">

<head>

<title>Laravel 5.5 - import export data into excel and csv using maatwebsite </title>

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" >

</head>


<body>


<div class="container">

<div class="panel panel-primary">

<div class="panel-heading">Laravel 5.5 - import export data into excel and csv using maatwebsite </div>

<div class="panel-body">

<div class="row">

<div class="col-xs-12 col-sm-12 col-md-12">

<a href="{{ route('export.file',['type'=>'xls']) }}">Download Excel xls</a> |

<a href="{{ route('export.file',['type'=>'xlsx']) }}">Download Excel xlsx</a> |

<a href="{{ route('export.file',['type'=>'csv']) }}">Download CSV</a>

</div>

</div>

{!! Form::open(array('route' => 'import.file','method'=>'POST','files'=>'true')) !!}

<div class="row">

<div class="col-xs-12 col-sm-12 col-md-12">

<div class="form-group">

{!! Form::label('sample_file','Select File to Import:',['class'=>'col-md-3']) !!}

<div class="col-md-9">

{!! Form::file('sample_file', array('class' => 'form-control')) !!}

{!! $errors->first('sample_file', '<p class="alert alert-danger">:message</p>') !!}

</div>

</div>

</div>

<div class="col-xs-12 col-sm-12 col-md-12 text-center">

{!! Form::submit('Upload',['class'=>'btn btn-primary']) !!}

</div>

</div>

{!! Form::close() !!}

</div>

</div>

</div>


</body>

</html>

Now we are ready to run our example.

You can also get sample of csv file from here : Download Sample File.

I hope you found your best solution.