Tuesday, 15 May 2018

How to Export Mysql Data to Excel File in Laravel



We have share new web tutorial on Laravel and in this post we have discuss how to export mysql data in laravel by using Laravel maatwebsite package for excel. By using this package we will seen how can we export data from mysql database to Excel spreadsheet in Laravel framework. Maatwebsite Laravel package give us very large functionality for export data into excel or csv file format in Laravel.

If we have developed very big ERP or e-commerce web application in Laravel framework then at that time we have required export of our mysql table data to excel format for reducing our database entry time in excel or csv file. So, by on single click we can transfer large amount of data in excel spreadsheet for our required purpose. We have already publish how to export data to excel sheet in PHP and codeigniter framework by using PHPExcel library. But in Laravel framework we will use maatwebsite package for excel file.

So, in this post we will seen how to make excel export functionality in our Laravel application by using maatwebsite package for excel. Here we have discuss step by step process for how to use maatwebsite package in Laravel application for export mysql table data to excel sheet.







Step 1 : Install maatwebsite/excel package


For install maatwebsite/excel package in our Laravel application we have to write following command in our command prompt.


composer require maatwebsite/excel


This command will download and install this package in our Laravel framework. After this we want to register this package in our application for we have to go to config/app.php file and add service provider and aliase details.


'providers' => [
 ....

 Maatwebsite\Excel\ExcelServiceProvider::class,

],

'aliases' => [

 ....

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

],


Step 2 : Create Controller


First we want to make one controller for handle http request in our laravel application. By using this controller we will display mysql data on web page and then after it will also handle request for export data to excel. First we want to write following command in command prompt for make controller in our laravel application.


php artisan make:controller ExportExcelController


This command will make ExportExcelController.php file under app/Http/Controllers folder. In this controller we have make two method. First index() method will display mysql table data on web page and second excel() method will export mysql table data to excel file.


<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;
use Excel;

class ExportExcelController extends Controller
{
    function index()
    {
     $customer_data = DB::table('tbl_customer')->get();
     return view('export_excel')->with('customer_data', $customer_data);
    }

    function excel()
    {
     $customer_data = DB::table('tbl_customer')->get()->toArray();
     $customer_array[] = array('Customer Name', 'Address', 'City', 'Postal Code', 'Country');
     foreach($customer_data as $customer)
     {
      $customer_array[] = array(
       'Customer Name'  => $customer->CustomerName,
       'Address'   => $customer->Address,
       'City'    => $customer->City,
       'Postal Code'  => $customer->PostalCode,
       'Country'   => $customer->Country
      );
     }
     Excel::create('Customer Data', function($excel) use ($customer_array){
      $excel->setTitle('Customer Data');
      $excel->sheet('Customer Data', function($sheet) use ($customer_array){
       $sheet->fromArray($customer_array, null, 'A1', false, false);
      });
     })->download('xlsx');
    }
}

?>


Step 3 : Create View


After this for display output in browser we have to create view files under resources/views folder. This file for display output of laravel application on web page.



<!--
 export_excel.blade.php
!-->

<!DOCTYPE html>
<html>
 <head>
  <title>Export Data to Excel in Laravel using Maatwebsite</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <style type="text/css">
   .box{
    width:600px;
    margin:0 auto;
    border:1px solid #ccc;
   }
  </style>
 </head>
 <body>
  <br />
  <div class="container">
   <h3 align="center">Export Data to Excel in Laravel using Maatwebsite</h3><br />
   <div align="center">
    <a href="{{ route('export_excel.excel') }}" class="btn btn-success">Export to Excel</a>
   </div>
   <br />
   <div class="table-responsive">
    <table class="table table-striped table-bordered">
     <tr>
      <td>Customer Name</td>
      <td>Address</td>
      <td>City</td>
      <td>Postal Code</td>
      <td>Country</td>
     </tr>
     @foreach($customer_data as $customer)
     <tr>
      <td>{{ $customer->CustomerName }}</td>
      <td>{{ $customer->Address }}</td>
      <td>{{ $customer->City }}</td>
      <td>{{ $customer->PostalCode }}</td>
      <td>{{ $customer->Country }}</td>
     </tr>
     @endforeach
    </table>
   </div>
   
  </div>
 </body>
</html>



Step 4 : Set Route


Lastly we want to set route for index() and excel() method in our application. For this we have to go to routes/web.php file and write following code for set route for above two method.


<?php
//routes/web.php

Route::get('/export_excel', 'ExportExcelController@index');

Route::get('/export_excel/excel', 'ExportExcelController@excel')->name('export_excel.excel');

?>


So here our code is ready for How to export mysql table data to excel file by using maatwebsite/excel package in Laravel.

29 comments:

  1. can you create video for import data from excel to database plesase

    ReplyDelete
  2. excellent contribution. thank you very much.

    ReplyDelete
  3. facing problem
    "Trying to get property 'name' of non-object"


    $st_array[]=array(
    'name'=>$st->name,
    'mobile'=>$st->mobile,
    'email'=>$st->email,
    'image'=>$st->image
    );

    thiss code

    ReplyDelete
  4. is this can be able to laravel 5.5

    ReplyDelete
    Replies
    1. You can download the 2.1 version , it worked with laravel 5.4

      Delete
  5. thanks,but maatwebsite/excel has not create function in 3.0 version !
    and
    Call to undefined method Maatwebsite\Excel\Excel::create()
    appears!!

    ReplyDelete
  6. Can anyone help me in downloading xlsx formate from command line in laravel,i could download csv formate,but xlsx file formate is showing error

    ReplyDelete
  7. help me please this error Call to undefined method Maatwebsite\Excel\Excel::create()

    ReplyDelete
  8. Nice tutorial thank you :)

    ReplyDelete
  9. It worked for me, download laravel updated version

    ReplyDelete
  10. Code should be upgraded as the version has been changed....this code has some faults

    ReplyDelete
  11. Just passed it. The solution should accept ::download or ::store methods. ::Create is not more available in Maatwebsite/Excel 3.0

    ReplyDelete
  12. hi, i also get same error
    my error: Call to undefined method Maatwebsite\Excel\Excel::create()
    laravel verson: 5.8
    php: 7.1

    ReplyDelete
  13. i have same error appears
    Call to undefined method Maatwebsite\Excel\Excel::create()

    ReplyDelete
  14. use the statement below as the return statement.
    return Excel::download($customer_array, 'users.xlsx');

    ReplyDelete
  15. Call to undefined method Maatwebsite\Excel\Excel::create()
    error

    ReplyDelete
  16. i have create function for upload data to my database and success, and a create function for export to excel form download cant show and not found show some error. please suggest me .thanks

    ReplyDelete
  17. Hello Sir!

    This is very helpful tutorial and working fine. Can you please add filter in this existing code like date range and user wise export features?

    Thank you.

    ReplyDelete
  18. Create function not found so not working this please give the best solution for this error

    ReplyDelete
  19. Call to undefined method Maatwebsite\Excel\Excel::create()
    my laravel version
    "maatwebsite/excel": "^3.1" and
    "laravel/framework": "5.8.*"
    https://wa.me/919925405228

    ReplyDelete
  20. Method App\Http\Controllers\downloadController::excel() does not exist
    Such error occurs..help me

    ReplyDelete
  21. Unable to resolve NULL driver for [Maatwebsite\Excel\Transactions\TransactionManager].

    ReplyDelete