Laravel import export to excel and csv using maatwebsite

Share Me
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Today now in this blog i will show you how to import and export to excel or csv file by using maatwebsite in laravel application. So now here in this post i will show you how to import excel or csv to store in database and also show you how to export or download excel or csv file from the database table by using maatwebsite. We know maatwebsite packages through we can easily get data, and also we can group by data, also can create more then one sheet etc. So here now i will show you a simple example of items table data, we can donwload in xls, xlsx and csv format and also we will import data in xls, xlsx and csv format file.Now In following few step we will implement the import and export both function in our project.

Step 1: Installation

Here need to open our composer.json file and need to add bellow line in required package.

"maatwebsite/excel": "~2.1.0"

Then,need to run the command composer update

So now open config/app.php file and then add service provider and aliase.

'providers' => [
	....

	'Maatwebsite\Excel\ExcelServiceProvider',

],

'aliases' => [

	....

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

],
Config

So now need to fire following command:

php artisan vendor:publish

So in this command will create a config file for excel package.

Step 3: Create Table and Model

Now in this step we need to create a migration for items table by using Laravel php artisan command, so first fire bellow command:

php artisan make:migration create_items_table

So after this command we will find one file in following path database/migrations and then we have to put bellow code in our migration file for create items table on our DB.

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateItemsTable extends Migration
{
   public function up()
   {
       Schema::create('items', function (Blueprint $table) {
           $table->increments('id');
           $table->string('title');
           $table->text('description');
           $table->timestamps();
       });
   }
   public function down()
   {
       Schema::drop("items");
   }
}

After the create “items” table we should create Item model for items, so at first need to create file in this path app/Models/Item.php and then need to put bellow content in item.php file:

app/Models/Item.php

namespace App;
use Illuminate\Database\Eloquent\Model;
class Item extends Model
{
   public $fillable = ['title','description'];
}
Step 3: Create Route

So in this is step we need to create route of import export file. so open your app/Http/routes.php file and add following route.

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

Ok good, now we should create a new controller as MaatwebsiteDemoController in this path app/Http/Controllers/MaatwebsiteDemoController.php. So on this controller will manage all impostExport, downloadExcel and importExcel request and also return response, so need to put bellow content in controller file:

app/Http/Controllers/MaatwebsiteDemoController.php

use Input;
use App\Models\Item;
use DB;
use Excel;
class MaatwebsiteDemoController extends Controller
{
	public function importExport()
	{
		return view('importExport');
	}
	public function downloadExcel($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);
	}
	public function importExcel()
	{
		if(Input::hasFile('import_file')){
			$path = Input::file('import_file')->getRealPath();
			$data = Excel::load($path, function($reader) {
			})->get();
			if(!empty($data) && $data->count()){
				foreach ($data as $key => $value) {
					$insert[] = ['title' => $value->title, 'description' => $value->description];
				}
				if(!empty($insert)){
					DB::table('items')->insert($insert);
					dd('Insert Record successfully.');
				}
			}
		}
		return back();
	}
}
Step 5: Create View

So let’s create a importExport.blade.php(resources/views/importExport.blade.php) for layout and then we will write design code here and put down following code :

importExport.blade.php

<html lang="en">
<head>
	<title>Import - Export Laravel</title>
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" >
</head>
<body>
	<nav class="navbar navbar-default">
		<div class="container-fluid">
			<div class="navbar-header">
				<a class="navbar-brand" href="#">Import - Export in Excel and CSV Laravel 5</a>
			</div>
		</div>
	</nav>
	<div class="container">
		<a href="{{ URL::to('downloadExcel/xls') }}"><button class="btn btn-success">Download Excel xls</button></a>
		<a href="{{ URL::to('downloadExcel/xlsx') }}"><button class="btn btn-success">Download Excel xlsx</button></a>
		<a href="{{ URL::to('downloadExcel/csv') }}"><button class="btn btn-success">Download CSV</button></a>
		<form style="border: 4px solid #a1a1a1;margin-top: 15px;padding: 10px;" action="{{ URL::to('importExcel') }}" class="form-horizontal" method="post" enctype="multipart/form-data">
			<input type="file" name="import_file" />
			<button class="btn btn-primary">Import File</button>
		</form>
	</div>
</body>
</html>

Read Also : Laravel and AngularJS CRUD with Search & Pagination Example

Thanks for read. I hope it help you. For more you can follow us on facebook.

About code chef

My name is Shahriar sagor. I'm a developer. I live in Bangladesh and I love to write tutorials and tips that will help to other Developer's. I am a big fan of PHP, Javascript, JQuery, Laravel, Codeigniter, VueJS, AngularJS and Bootstrap from the early stage.

View all posts by code chef →