Laravel excel export from query builder

Laravel Excel

Exporting and importing Excel, CSV and OpenOffice stylesheets using Eloquent Collections and Query Builders in Laravel (5.* and 4.*).
It's based on box/spout.

Author: Simone Todaro
Contributors: Clément Blanco
Made with ❤️ by Cyber-Duck Ltd

Installation
Export Excel
Import Excel
Different formats

Installation

Use composer to download the package:

composer require cyber-duck/laravel-excel

Laravel 4.x

Register the service provider in config/app.php by adding this line to providers array.

'providers' => [
	Cyberduck\LaravelExcel\ExcelLegacyServiceProvider::class,
],

Laravel < 5.5

Register the service provider in config/app.php by adding this line to providers array.

'providers' => [
	Cyberduck\LaravelExcel\ExcelServiceProvider::class,
],

Laravel > 5.5

No need to register anything, since it used package auto discovery feature in Laravel 5.5.

Export Excel

Generate and download an excel file

Add

to your controller.

In your controler function, create a new excel file from an Eloquent collection.

$excel = Exporter::make('Excel');
$excel->load($yourCollection);
return $excel->stream($yourFileName);

The exporter class is fluent, so you can also write

return Exporter::make('Excel')->load($yourCollection)->stream($yourFileName);

The exporter class supports Query builder objects as well

$query = DB:table('table')->select('col1','col2');
$excel = Exporter::make('Excel');
$excel->loadQuery($query);
return $excel->stream($yourFileName);

If you deal with big tables, you can set the chunk size to minimise the memory usage

$query = DB:table('table')->select('col1','col2');
$excel = Exporter::make('Excel');
$excel->loadQuery($query);
$excel->setChunk(1000);
return $excel->stream($yourFileName);

Generate and save an excel file

To save the excel file on the server, use the save method.

return $excel->save($yourFileNameWithPath);

Advanced usage

By default, every element of the Collection becomes a row and every unprotected field of the Model becomes a cell.
No headers row is printed.

To change this behaviour, create a class extending Cyberduck\LaravelExcel\Contract\SerialiserInterface, implement the methods getHeaderRow() and getData(Model $data) and set this class on the excel object usint setSerialiser().

$serialiser = new CustomSerialiser();
$excel = Exporter::make('Excel');
$excel->load($collection);
$excel->setSerialiser($serialiser);
return $excel->stream($yourFileName);

getHeaderRow() must return an array of string where every element is a cell of the first row. To not print the header row, simply return a void array [].
getData(Model $data) must return an array of string, and every elements is a cell.

Example

namespace App\Serialisers;

use Illuminate\Database\Eloquent\Model;
use Cyberduck\LaravelExcel\Contract\SerialiserInterface;

class ExampleSerialiser implements SerialiserInterface
{
    public function getData($data)
    {
        $row = [];

        $row[] = $data->field1;
        $row[] = $data->relationship->field2;

        return $row;
    }

    public function getHeaderRow()
    {
        return [
            'Field 1',
            'Field 2 (from a relationship)'
        ];
    }
}

then set the serialiser before saving the file the collection.

$collection = Exporter::make('Excel')->load($yourCollection)->setSerialiser(new ExampleSerialiser)->stream($yourFileName);

Import Excel

Add

to your controller.

In your controler function, import an excel file.

$excel = Importer::make('Excel');
$excel->load($filepath);
$collection = $excel->getCollection();
//dd($collection)

The importer class is fluent, then you can also write

return Importer::make('Excel')->load($filepath)->getCollection();

Advanced usage

By default, every row of the first sheet of the excel file becomes an array and the final result is wraped in a Collection (Illuminate\Support\Collection).

To import a different sheet, use setSheet($sheet)

$excel = Importer::make('Excel');
$excel->load($filepath);
$excel->setSheet($sheetNumber);
$collection = $excel->getCollection();
//dd($collection)

To import each row in an Eloquent model, create a class extending Cyberduck\LaravelExcel\Contract\ParserInterface and implement the methods transform($row).

Example

namespace App\Parsers;

use App\Models\YourModel;
use Cyberduck\LaravelExcel\Contract\ParserInterface;

class ExampleParser implements ParserInterface
{
    public function transform($row, $header)
    {
        $model = new YourModel();
        $model->field1 = $row[0];
        $model->field2 = $row[1];
        // We can manunipulate the data before returning the object
        $model->field3 = new \Carbon($row[2]);
        return $model;
    }
}

then set the parser before creating the collection.

$collection = Importer::make('Excel')->load($filepath)->setParser(new ExampleParser)->getCollection();

Different formats

The package supports ODS and CSV files.

ODS

$exporter = Exporter::make('OpenOffice');
$importer = Importer::make('OpenOffice');

CSV

$exporter = Exporter::make('Csv');
$importer = Importer::make('Csv');