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.

0 comments:

Post a Comment