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

Leave a Comment / php / By

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

In this post we will give you information about Laravel 5 maatwebsite import excel into DB and export data into csv and excel. Hear we will give you detail about Laravel 5 maatwebsite import excel into DB and export data into csv and excelAnd how to use it also give you demo for it if it is necessary.

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' => [
	....
	'MaatwebsiteExcelExcelServiceProvider',
],
'aliases' => [
	....
	'Excel' => 'MaatwebsiteExcelFacadesExcel',
],

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

php artisan vendor:publish --provider="MaatwebsiteExcelExcelServiceProvider"


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 IlluminateDatabaseSchemaBlueprint;
  2. use IlluminateDatabaseMigrationsMigration;
  3. class CreateProductsTable extends Migration
  4. {
  5.    public functionup()
  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 functiondown()
  15. {
  16.        Schema::drop("products");
  17. }
  18. }
use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;
class CreateProductsTable extends Migration
{
   public function up()
   {
       Schema::create('products', function (Blueprint $table) {
           $table->increments('id');
           $table->string('name');
           $table->text('details');
           $table->timestamps();
       });
   }
   public function down()
   {
       Schema::drop("products");
   }
}

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 IlluminateDatabaseEloquentModel;
  3. class Product extends Model
  4. {
  5.    public $fillable=['name','details'];
  6. }
namespace App;
use IlluminateDatabaseEloquentModel;
class Product extends Model
{
   public $fillable = ['name','details'];
}


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
  3. namespace AppHttpControllers;
  5. use IlluminateHttpRequest;
  6. use AppHttpControllersController;
  7. use AppProduct;
  9. class FileController extends Controller {
  10.     public functionimportExportExcelORCSV(){
  11. returnview('file_import_export');
  12. }
  13.     public functionimportFileIntoDB(Request $request){
  14. if($request->hasFile('sample_file')){
  15. $path=$request->file('sample_file')->getRealPath();
  16. $data=Excel::load($path)->get();
  17. if($data->count()){
  18. foreach($dataas$key=>$value){
  19. $arr[]=['name'=>$value->name,'details'=>$value->details];
  20. }
  21. if(!empty($arr)){
  22. DB::table('products')->insert($arr);
  23. dd('Insert Record successfully.');
  24. }
  25. }
  26. }
  27. dd('Request data does not have any files to import.');
  28. }
  29.     public functiondownloadExcelFile($type){
  30. $products= Product::get()->toArray();
  31. returnExcel::create('onlinecode_demo',function($excel)use($products){
  32. $excel->sheet('sheet name',function($sheet)use($products)
  33. {
  34. $sheet->fromArray($products);
  35. });
  36. })->download($type);
  37. }
  38. }
  40. }
<?php

namespace AppHttpControllers;

use IlluminateHttpRequest;
use AppHttpControllersController;
use AppProduct;

class FileController extends Controller {
    public function importExportExcelORCSV(){
        return view('file_import_export');
    }
    public function importFileIntoDB(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[] = ['name' => $value->name, 'details' => $value->details];
                }
                if(!empty($arr)){
                    DB::table('products')->insert($arr);
                    dd('Insert Record successfully.');
                }
            }
        }
        dd('Request data does not have any files to import.');      
    } 
    public function downloadExcelFile($type){
        $products = Product::get()->toArray();
        return Excel::create('onlinecode_demo', function($excel) use ($products) {
            $excel->sheet('sheet name', function($sheet) use ($products)
            {
                $sheet->fromArray($products);
            });
        })->download($type);
    }      
}

}


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

       {!! Form::open(array('route' => 'import-csv-excel','method'=>'POST','files'=>'true')) !!}
        <div >
           <div >
                <div >
                    {!! Form::label('sample_file','Select File to Import:',['class'=>'col-md-3']) !!}
                    <div >
                    {!! Form::file('sample_file', array('class' => 'form-control')) !!}
                    {!! $errors->first('sample_file', '<p >:message</p>') !!}
                    </div>
                </div>
            </div>
            <div >
            {!! Form::submit('Upload',['class'=>'btn btn-primary']) !!}
            </div>
        </div>
       {!! Form::close() !!}
 </div>
</div>
</body>
</html>

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

Click here to know more about Laravel Excel : Laravel Excel

You can easily read excel file and upload excel file’s data into database. This is the best way to create and export a csv from a model.

Show Demo

Hope this code and post will helped you for implement Laravel 5 maatwebsite import excel into DB and export data into csv and excel. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve us. we will give you this type of more interesting post in featured also so, For more interesting post and code Keep reading our blogs

For More Info See :: laravel And github

Leave a Comment

Your email address will not be published. Required fields are marked *

1  +  2  =  

We're accepting well-written guest posts and this is a great opportunity to collaborate : Contact US