Categories
makitweb.com

Export Data in Excel and CSV format with Laravel Excel

Laravel Excel is a package which simplifies the import and export data in Laravel. It allows exporting data in various format – xlsx, csv, xml, html, pdf, etc. Require to create

Laravel Excel is a package which simplifies the import and export data in Laravel.

It allows exporting data in various format – xlsx, csv, xml, html, pdf, etc.

Require to create a separate class from where return data and set heading row.

In this tutorial, I am using it to export MySQL data in CSV and Excel format in Laravel project.

Export data in Excel and CSV format with Laravel Excel


Contents

  1. Table structure
  2. Database Configuration
  3. Install Laravel Excel Package
  4. Model
  5. Return Export Data
  6. Controller
  7. Route
  8. View
  9. Conclusion

 


1. Table structure

I am using users tables in the examples where stored some records –

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  `name` varchar(80) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `email` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Database Configuration

Open .env file.

Specify the host, database name, username, and password.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

3. Install Laravel Excel Package

Installing the package using composer.

  • Navigate to your project directory using the console and execute the following command.
composer require maatwebsite/excel
  • Create a UsersExport class.

Syntax – 

php artisan make:export [class-name] --model=App[model-name]

Execute command –

php artisan make:export UsersExport --model=AppPage

The Page Model create in next step.


4. Model

Create a Page Model.

php artisan make:model Page

To access the Database added use IlluminateSupportFacadesDB;.

Create a single method –

  • getUsers() – Fetch all records from users table and return an Array.

Completed Code

<?php

namespace App;

use IlluminateDatabaseEloquentModel;
use IlluminateSupportFacadesDB;

class Page extends Model {

   // Fetch all users
   public static function getUsers(){

     $records = DB::table('users')->select('username','name','gender','email')->orderBy('id', 'asc')->get()->toArray();

     return $records;
   }

}

5. Return Export Data

Open appExportsUserExport.php file.

Add use MaatwebsiteExcelConcernsWithHeadings; to set heading row.

Also, need to implement WithHeadings Interface.

  • headings() – This is an abstract method. From here, return Array of header column names.
  • collection() – Fetch Array of records by calling Page::getUsers() and convert it to collect and return it.

NOTE – In the collection() method I am converting array to collection type by passing Array value to collect() because from here need to return collection type data.

Completed Code

<?php

namespace AppExports;

use AppPage;
use MaatwebsiteExcelConcernsFromCollection;
use MaatwebsiteExcelConcernsWithHeadings;

class UsersExport implements FromCollection,WithHeadings {

  public function headings(): array {
    return [
       "username","name","gender","email"
    ];
  }

  /**
  * @return IlluminateSupportCollection
  */
  public function collection() {

     return collect(Page::getUsers());
     // return Page::getUsers(); // Use this if you return data from Model without using toArray().
  }
}

6. Controller

Create a PagesController controller.

php artisan make:controller PagesController

Add use AppExportsUsersExport; and use MaatwebsiteExcelFacadesExcel;.

Create two methods –

  • index – Load index view.
  • export – This method call on <form > submit. Check which button gets clicked.

If exportexcel button gets clicked then return –

return Excel::download(new UsersExport, 'users.xlsx');

If exportcsv button gets clicked then return –

return Excel::download(new UsersExport, 'users.xlsx');

Syntax – 

return Excel::download(new [Export-class-name], ['file-name']);

Completed Code

<?php

namespace AppHttpControllers;

use AppExportsUsersExport;
use MaatwebsiteExcelFacadesExcel;
use IlluminateHttpRequest;

class PagesController extends Controller {

   public function index(){
      return view('index');
   }

   // Export data
   public function export(Request $request){

     if ($request->input('exportexcel') != null ){
        return Excel::download(new UsersExport, 'users.xlsx');
     }

     if ($request->input('exportcsv') != null ){
        return Excel::download(new UsersExport, 'users.csv');
     }

     return redirect()->action('PagesController@index');
   }
}

7. Route

Open routes/web.php file.

Here, define two routes –

  • / 
  • /export – A post type route which uses in <form >.
<?php

Route::get('/', 'PagesController@index');
Route::post('/export', 'PagesController@export');

8. View

Create a new index.blade.php file in resources/views/ directory.

Create a <form > set method='post' and action='/export'.

Here, create two submit buttons –

  • The first button is used to export records in Excel format.
  • The second button is used to export records in CSV format.

Completed Code

<!DOCTYPE html>
<html>
   <head>
     <title>Export Data in Excel and CSV format with Laravel Excel</title>
   </head>
   <body>

     <form method='post' action='/export'>
       {{ csrf_field() }}
       <input type="submit" name="exportexcel" value='Excel Export'>
       <input type="submit" name="exportcsv" value='CSV Export'>
     </form>

   </body>
</html>

9. Conclusion

Customize your Array in the Model or in export class and update heading row in heading() method in the export class.

Make sure you need to return data in collection format from collection() method.

Learn about other formats from here.

If you found this tutorial helpful then don’t forget to share.

Leave a Reply

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