Laravel 5 maatwebsite import excel into DB and export data into csv and excel

Laravel 5 maatwebsite import excel into DB and export data into csv and excel

In this tutorial, I will let you know how to import excel or csv file into database table and export or download in different format using maatwebsite package with the power of PHPOffice's PHPExcel.

For e.g. you can download the file in xls format, xlsx format and csv format.

There are lots of functionality available with Laravel Excel, such as you can also export blade view for a single sheet, load different view for different sheets.

Step 1: Installation

In this step you need to require "maatwebsite/excel" package into your application. So update your composer.json file by adding following line in require package :

"maatwebsite/excel": "~2.1.0"

After adding above line into composer.json file run following command to update composer :

 composer update 

Once composer file get updated successfully then add service provide and aliases to the providers and aliases array respectively in config/app.php.

'providers' => [
	....
	'Maatwebsite\Excel\ExcelServiceProvider',
],
'aliases' => [
	....
	'Excel' => 'Maatwebsite\Excel\Facades\Excel',
],

To publish this config setting in Laravel 5, you need to run following command :

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
Step 2: Create Table and Model

In this step, we need to create table where you will import data from excel or csv file and export that data in required format.

If you wish, you can run migration command to create table using php artisan command in Laravel 5 or directly you can create table into your database from phpMyAdmin, GUI for your database.

OK, we will here create table by using migration command, so first create migration file by running following command :

php artisan make:migration create_products_table

Once you run above command then you will see migration file in following path database/migrations where you define table schema.

Open the migration file and put following line of code for demo :

  1. use Illuminate\Database\Schema\Blueprint;
  2. use Illuminate\Database\Migrations\Migration;
  3. class CreateProductsTable extends Migration
  4. {
  5. public function up()
  6. {
  7. Schema::create('products', function (Blueprint $table) {
  8. $table->increments('id');
  9. $table->string('name');
  10. $table->text('details');
  11. $table->timestamps();
  12. });
  13. }
  14. public function down()
  15. {
  16. Schema::drop("products");
  17. }
  18. }

Once products table has been created successfully into your database then create Product model for the table products.

Create a file Product.php in following path app/Product.php and add following line of code into that model file.

app/Product.php

  1. namespace App;
  2. use Illuminate\Database\Eloquent\Model;
  3. class Product extends Model
  4. {
  5. public $fillable = ['name','details'];
  6. }

Step 3: Add Route

In this step, we will add route to handle request for import and export file.

You will see your route file in following path app/Http/routes.php, so open your route file and add following routes.

Route::get('import-export-csv-excel',array('as'=>'excel.import','uses'=>'FileController@importExportExcelORCSV'));
Route::post('import-csv-excel',array('as'=>'import-csv-excel','uses'=>'FileController@importFileIntoDB'));
Route::get('download-excel-file/{type}', array('as'=>'excel-file','uses'=>'FileController@downloadExcelFile'));
Step 4: Create Controller

Now we will create a controller FileController.php in following path app/Http/Controllers/.

In FileController, we will write functionality to read file data to import into table and download the table data into excel sheets.

app/Http/Controllers/FileController.php
  1. <?php
  2. namespace App\Http\Controllers;
  3. use Illuminate\Http\Request;
  4. use App\Http\Controllers\Controller;
  5. use App\Product;
  6. class FileController extends Controller {
  7. public function importExportExcelORCSV(){
  8. return view('file_import_export');
  9. }
  10. public function importFileIntoDB(Request $request){
  11. if($request->hasFile('sample_file')){
  12. $path = $request->file('sample_file')->getRealPath();
  13. $data = \Excel::load($path)->get();
  14. if($data->count()){
  15. foreach ($data as $key => $value) {
  16. $arr[] = ['name' => $value->name, 'details' => $value->details];
  17. }
  18. if(!empty($arr)){
  19. \DB::table('products')->insert($arr);
  20. dd('Insert Record successfully.');
  21. }
  22. }
  23. }
  24. dd('Request data does not have any files to import.');
  25. }
  26. public function downloadExcelFile($type){
  27. $products = Product::get()->toArray();
  28. return \Excel::create('expertphp_demo', function($excel) use ($products) {
  29. $excel->sheet('sheet name', function($sheet) use ($products)
  30. {
  31. $sheet->fromArray($products);
  32. });
  33. })->download($type);
  34. }
  35. }
  36. }
Step 5: Create Blade File

In this last step, we will create a view blade file file_import_export.blade.php in following path resources/views/ where you can select file to upload/import and download file into excel sheet.

file_import_export.blade.php
  1. <html lang="en">
  2. <head>
  3. <title>Laravel 5 maatwebsite export into csv and excel and import into DB</title>
  4. <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" >
  5. </head>
  6. <body>
  7. <div class="panel panel-primary">
  8. <div class="panel-heading">Laravel 5 maatwebsite export into csv and excel and import into DB</div>
  9. <div class="panel-body">
  10. <div class="row">
  11. <div class="col-xs-12 col-sm-12 col-md-12">
  12. <a href="{{ route('excel-file',['type'=>'xls']) }}">Download Excel xls</a> |
  13. <a href="{{ route('excel-file',['type'=>'xlsx']) }}">Download Excel xlsx</a> |
  14. <a href="{{ route('excel-file',['type'=>'csv']) }}">Download CSV</a>
  15. </div>
  16. </div>
  17. {!! Form::open(array('route' => 'import-csv-excel','method'=>'POST','files'=>'true')) !!}
  18. <div class="row">
  19. <div class="col-xs-12 col-sm-12 col-md-12">
  20. <div class="form-group">
  21. {!! Form::label('sample_file','Select File to Import:',['class'=>'col-md-3']) !!}
  22. <div class="col-md-9">
  23. {!! Form::file('sample_file', array('class' => 'form-control')) !!}
  24. {!! $errors->first('sample_file', '<p class="alert alert-danger">:message</p>') !!}
  25. </div>
  26. </div>
  27. </div>
  28. <div class="col-xs-12 col-sm-12 col-md-12 text-center">
  29. {!! Form::submit('Upload',['class'=>'btn btn-primary']) !!}
  30. </div>
  31. </div>
  32. {!! Form::close() !!}
  33. </div>
  34. </div>
  35. </body>
  36. </html>

Click here to download the sample sheet to import data into database :Sample File

Phone: (+91) 8800417876
Noida, 201301
okey metin2 pvp serverler metin2 pvp
ataşehir escort maltepe escort bostancı escort kadıköy escort maltepe escort ataşehir escort tuzla escort pendik escort ümraniye escort kartal escort
pendik escort
izmir escort izmir escort izmir escort izmir escort denizli escort antalya escort antalya escort antalya escort izmir escort izmir escort izmir escort izmir escort izmir escort
hatay escort nevsehir escort nigde escort kirikkale escort batman escort osmaniye escort kirklareli escort tokat escort rize escort adiyaman escort gumushane escort batman escort
cialis satış cialis fiyat cialis 20 mg cialis 100 mg cialis 5 mg fiyat

viagra fiyatı

viagra 100 mg fiyat

viagra sipariş

orjinal viagra

viagra satın al

izmir escort
kocaeli escort izmit escort bayan gebze escort izmit sınırsız escort escort bursa tuzla escort aydınlı escort orhanlı escort bursa escort escort bayan istanbul escort istanbul sınırsız escort istanbul bayan escort izmit escort izmit escort kocaeli escort sisli escort mecidiyekoy escort bursa escort bayan bursa görükle escort maltepe escort goztepe escort yakacik-yeni-escort bursa sınırsız escort sisli escort bayan taksim escort bursa escort porno izle sikiş izle atasehir escort antalya escort
ankara escort marmaris escort bursa escort mersin escort antalya escort izmir escort samsun escort bodrum escort çeşme escort kuşadası escort adana escort eskişehir escort gaziantep escort esenyurt escort pendik escort tuzla escort fatih escort ümraniye escort mecidiyeköy escort kartal escort bahçelievler escort bahçeşehir escort ataşehir escort bakırköy escort nişantaşı escort avcılar escort büyükçekmece escort kurtkoy escort konya escort beşiktaş escort taksim escort beylikdüzü escort malatya escort sakarya escort şişli escort maltepe escort kadıköy escort ataköy escort bostancı escort şirinevler escort bağcılar escort üsküdar escort etiler escort başaksehir escort ortaköy escort kağıthane escort sultangazi escort halkalı escort çekmeköy escort şaşkınbakkal escort mamak escort sincan escort keçiören escort çankaya escort beypazarı escort akyurt escort pursaklar escort kahramankazan escort göztepe escort alsancak escort bornova escort buca escort karabaglar escort karşıyaka escort konak escort narlıdere escort izmit escort başiskele escort kandira escort körfez escort karamürsel escort dilovası escort gölcük escort gebze escort kocaeli escort kayseri escort elazığ escort denizli escort diyarbakir escort adana escort ankara escort izmir escort antalya escort gaziantep escort kayseri escort konya escort mersin escort denizli escort kocaeli escort diyarbakır escort