Blog Detail

16

Aug
Laravel Excel - Import Export Excel & CSV files in Laravel cover image

Laravel Excel - Import Export Excel & CSV files in Laravel

Laravel Excel is designed at being a Laravel-flavoured PhpSpreadsheet. It is a manageable and elegant wrapper around PhpSpreadsheet to simplify exports and imports. PhpSpreadsheet is a php based library that enables you to read and write different spreadsheet file formats, like Excel and LibreOffice Calc. Laravel Excel has the following features:

  • Easily export collections to Excel.
  • Export queries with automatic chunking for better performance.
  • Queue exports for better performance.
  • Easily export Blade views to Excel.
  • Easily import to collections.
  • Read the Excel file in chunks.
  • Handle the import inserts in batches.

Requirements

  • PHP: ^7.2 |^8.0
  • Laravel: ^5.8
  • PhpSpreadsheet: ^1.15
  • PHP extension php_zip enabled
  • PHP extension php_xml enabled
  • PHP extension php_gd2 enabled
  • PHP extension php_iconv enabled
  • PHP extension php_simplexml enabled
  • PHP extension php_xmlreader enabled
  • PHP extension php_zlib enabled

Installation

You can install Laravel Excel via composer. You've to run this command for the installation.

composer require maatwebsite/excel

After the installation, the Maatwebsite\Excel\ExcelServiceProvider is auto-discovered and registered by default. But if you want to register it yourself, add the ServiceProvider in config/app.php:

'providers' => [
    /*
     * Package Service Providers...
     */
    Maatwebsite\Excel\ExcelServiceProvider::class,
]

The Excel facade is also auto-discovered. If you want to add it manually, add the Facade in config/app.php:

'aliases' => [
    ...
    'Excel' => Maatwebsite\Excel\Facades\Excel::class,
]

Next, you've to publish the config, run the vendor publish command:

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

This will generate a new config file named config/excel.php.

A quick start!

Export

First, you've to create an export class in app/Exports. You can do this by utilizing the make:export command.

php artisan make:export UsersExport --model=User

The file can be found in app/Exports:

If you prefer to generate the export manually, you can generate the following in app/Exports:

<?php
namespace App\Exports;

use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromCollection
{
    public function collection()
    {
        return User::all();
    }
}

In your controller you can call this export now:

<?php
namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;

class UsersController extends Controller 
{
    public function export() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
}

And finally, add a route to be able to access the exports.

Route::get('users/export/', 'UsersController@export');

Find your users.xlsx in your downloads folder!

Import

Create an import class in app/Imports. You can do this by utilizing the make:import command.

php artisan make:import UsersImport --model=User

The file can be found in app/Imports. If you prefer to create the import manually, you can create the following in app/Imports:

<?php
namespace App\Imports;

use App\Models\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;

class UsersImport implements ToModel
{
    /**
     * @param array $row
     *
     * @return User|null
     */
    public function model(array $row)
    {
        return new User([
           'name'     => $row[0],
           'email'    => $row[1], 
           'password' => Hash::make($row[2]),
        ]);
    }
}

In your controller you can call this import now:

use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Controller;

class UsersController extends Controller 
{
    public function import() 
    {
        Excel::import(new UsersImport, 'users.xlsx');

        return redirect('/')->with('success', 'All good!');
    }
}

These are the basic things about this package, You can learn more about its advanced usage from its documentation on Github.

Published at : 16-08-2021

Author : Rizwan Aslam
AUTHOR
Rizwan Aslam

I am a highly results-driven professional with 12+ years of collective experience in the grounds of web application development especially in laravel, native android application development in java, and desktop application development in the dot net framework. Now managing a team of expert developers at Codebrisk.

web application development client
web application development client
web application development client
web application development client
web application development client

Launch project