Import and Export data into csv and excel in Laravel 5

Step: 1 Download the maatwebsite/excel package through composer.

composer require maatwebsite/excel

maatwebsite/excel is a Laravel specific package, which provides us some methods to import and export our data from our database.

Step: 2 Add the ServiceProvider to the provider’s array in config/app.php file.

Maatwebsite\Excel\ExcelServiceProvider::class,

Step: 3 You can use the facade so, add this to your aliases in config/app.php file.

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

Step: 4 To publish the config settings in Laravel 5.4 use.

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

Step: 5 Create an items table via migration command.

php artisan make:migration create_items_table

Step: 6 Define columns in our items table.

Go to project folder >> database >> migrations >> items migration file and edit the file.

// 2017_06_13_000837_create_items_table.php
<?php
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('item_name');
$table->string('item_code');
$table->string('item_price');
$table->integer('item_qty');
$table->integer('item_tax');
$table->boolean('item_status');
$table->timestamp('created_at');
});
}
    /**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('items');
}
}

Step: 7 Run the migration.

php artisan migrate

Now the table is created in the database. Next step is to build a view for the table to import the file.

Step: 8 Create the items.blade.php file.

// items.blade.php
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Import-Export Data</title>
<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"
</head>
<body>
<div class="container">
<br />
<div class="row">
<div class="col-md-8"></div>
<div class="col-md-2">
<button class="btn btn-primary">Import</button>
</div>
<div class="col-md-2">
<button class="btn btn-success">Export</button>
</div>
</div>
</div>
</body>
</html>

Step: 9 Create an ItemController file.

php artisan make:controller ItemController --resource

Step: 10 Register the route for that item view.

// web.php
Route::get('items', 'ItemController@index');

Step: 11 Write index function in ItemController file.

// ItemController.php
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
return view('items');
}

Step: 12 Create import form in the view file.

<!--items.blade.php -->
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Import-Export Data</title>
<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"
</head>
<body>
<div class="container">
<br />
<div class="row">
<div class="col-md-4"></div>
<div class="col-md-6">
<div class="row">
<form action="" method="post" enctype="multipart/form-data">
<div class="col-md-6">
<input type="file" name="imported-file"/>
</div>
<div class="col-md-6">
<button class="btn btn-primary" type="submit">Import</button>
</div>
</form>
</div>
</div>
<div class="col-md-2">
<button class="btn btn-success">Export</button>
</div>
</div>
</div>
</body>
</html>

Step: 13 Create a model for items table

php artisan make:model Item

so your model will look like this.

// Item.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Item extends Model
{
protected $fillable = [
'item_name',
'item_code',
'item_price',
'item_qty',
'item_tax',
'item_status',
'created_at'
];
}

Here I have added protected $fillable field to prevent mass assignment exception.

Step: 14 Update the routes and also update actions according to routes.

// web.php
<?php
Route::get('/', function () {
return view('welcome');
});
Route::get('users', 'UserController@index');
Route::get('items', 'ItemController@index');
Route::post('items/import', 'ItemController@import');

Step: 15 Update items.blade.php according to action provided by routes file web.php

<!-- items.blade.php -->
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Import-Export Data</title>
<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"
</head>
<body>
<div class="container">
<br />
<div class="row">
<div class="col-md-4"></div>
<div class="col-md-6">
<div class="row">
<form action="{{url('items/import')}}" method="post" enctype="multipart/form-data">
<div class="col-md-6">
{{csrf_field()}}
<input type="file" name="imported-file"/>
</div>
<div class="col-md-6">
<button class="btn btn-primary" type="submit">Import</button>
</div>
</form>
</div>
</div>
<div class="col-md-2">
<button class="btn btn-success">Export</button>
</div>
</div>
</div>
</body>
</html>

I have also added {{csrf_field()}} to prevent token mismatch exception because this form uses POST request. For more details, please visit https://laravel.com/docs/5.4/csrf

Step: 16 Write import function in ItemController.

// ItemController.php
  /**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function import(Request $request)
{
if($request->file('imported-file'))
{
$path = $request->file('imported-file')->getRealPath();
$data = Excel::load($path, function($reader) {
})->get();
			if(!empty($data) && $data->count())
{
$data = $data->toArray();
for($i=0;$i<count($data);$i++)
{
$dataImported[] = $data[$i];
}
}
Item::insert($dataImported);
}
return back();
}

I am uploading that excel file in here. Items Excel File

Now you can upload any excel file and import it, and it can easily be imported into your database.

The new table with inserted values will look like this.

Now, if the database column names and excel sheet headers are different then we can use following code.

<!-- ItemController.php -->
/**
* import a file in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function import(Request $request)
{
if($request->file('imported-file'))
{
$path = $request->file('imported-file')->getRealPath();
$data = Excel::load($path, function($reader)
{
})->get();
          if(!empty($data) && $data->count())
{
foreach ($data->toArray() as $row)
{
if(!empty($row))
{
$dataArray[] =
[
'item_name' => $row['name'],
'item_code' => $row['code'],
'item_price' => $row['price'],
'item_qty' => $row['quantity'],
'item_tax' => $row['tax'],
'item_status' => $row['status'],
'created_at' => $row['created_at']
];
}
}
if(!empty($dataArray))
{
Item::insert($dataArray);
return back();
}
}
}
}

Here is the excel file which has header name and database column name different. Different Header file name

For CSV it works the same so, you can use this code for CSV implementation.

Next step is to Export the files in Excel or CSV format. So first step is to view that table into the items.blade.php

Step: 17 Create table in the items.blade.php file.

<!-- items.blade.php -->
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Import-Export Data</title>
<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"
</head>
<body>
<div class="container">
<br />
<div class="row">
<div class="col-md-4"></div>
<div class="col-md-6">
<div class="row">
<form action="{{url('items/import')}}" method="post" enctype="multipart/form-data">
<div class="col-md-6">
{{csrf_field()}}
<input type="file" name="imported-file"/>
</div>
<div class="col-md-6">
<button class="btn btn-primary" type="submit">Import</button>
</div>
</form>
</div>
</div>
<div class="col-md-2">
<form action="{{url('items/export')}}" enctype="multipart/form-data">
<button class="btn btn-success" type="submit">Export</button>
</form>
</div>
</div>
<div class="row">
@if(count($items))
<table class="table table-striped">
<thead>
<tr>
<td>item_name</td>
<td>item_code</td>
<td>item_price</td>
<td>item_qty</td>
<td>item_tax</td>
<td>item_status</td>
</tr>
</thead>
@foreach($items as $item)
<tr>
<td>{{$item->item_name}}</td>
<td>{{$item->item_code}}</td>
<td>{{$item->item_price}}</td>
<td>{{$item->item_qty}}</td>
<td>{{$item->item_tax}}</td>
<td>{{$item->item_status}}</td>
</tr>
@endforeach
</table>
@endif
</div>
</div>
</body>
</html>

Also, I have defined the action in the export button.

Now create an action in the web.php file.

// web.php
<?php
Route::get('/', function () {
return view('welcome');
});
Route::get('users', 'UserController@index');
Route::get('items', 'ItemController@index');
Route::post('items/import', 'ItemController@import');
Route::get('items/export', 'ItemController@export');

Step: 18 Create an export function in ItemControlller.php file.

// ItemController.php
/**
* export a file in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function export(){
$items = Item::all();
Excel::create('items', function($excel) use($items) {
$excel->sheet('ExportFile', function($sheet) use($items) {
$sheet->fromArray($items);
});
})->export('xls');
    }

Now when you press the export button, one .xls file will be downloaded in your browser.

So finally our tutorial, How to import and export data into CSV and excel in Laravel 5.4 is over. This example demonstrates Laravel import CSV or excel to the database.