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.