patosmack/laravel-excel

This package provides a way to export an Eloquent collection as an excel file and to import a Excel file as an Eloquent collection.

Laravel Excel ( THIS PROJECT HAS BEEN DISCONTINUED )

, _(*1)

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., _(*2)

Author: Simone Todaro
Contributors: Clément Blanco
Made with :heart: by Cyber-Duck Ltd, _(*3)

Installation
Export Excel
Import Excel
Different formats, _(*4)

Installation

Use composer to download the package:, _(*5)

composer require patosmack/laravel-excel

Register the service provider in config/app.php adding Patosmack\LaravelExcel\ExcelServiceProvider to the provider array., _(*6)

Note. If you are on Laravel 4, use Patosmack\LaravelExcel\ExcelLegacyServiceProvider, _(*7)

Export Excel

Generate and download an excel file

Add, _(*8)

use Exporter;

to your controller., _(*9)

In your controler function, create a new excel file from an Eloquent collection., _(*10)

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

The exporter class is fluent, so you can also write, _(*11)

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

The exporter class supports Query builder objects as well, _(*12)

$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, _(*13)

$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., _(*14)

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., _(*15)

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()., _(*16)

$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., _(*17)

Example, _(*18)

namespace App\Serialisers;

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

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

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

        return $row;
    }

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

Import Excel

Add, _(*19)

use Importer;

to your controller., _(*20)

In your controler function, import an excel file., _(*21)

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

The importer class is fluent, then you can also write, _(*22)

return Importer::make('Excel')->getCollection($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)., _(*23)

To import a different sheet, use setSheet($sheet), _(*24)

$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)., _(*25)

Example, _(*26)

namespace App\Parsers;

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

class ExampleSerialiser implements ParserInterface
{
    public function transform($row)
    {
        $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;
    }
}

Different formats

The package supports ODS and CSV files., _(*27)

ODS

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

CSV

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