Importing a file

To start importing a file, you can use->load($filename). The callback is optional.

Excel::load('file.xls', function($reader) {    // reader methods});

ExcelFile injections

Following the Laravel 5.0 philosophy with its new awesome FormRequest injections, we introduce you ExcelFile injections.

ExcelFile class

This class is a wrapper for a file on your server. Inside thegetFile()method you return the filename and it's location. Inside thegetFilters()method you can enable filters, like the chunk filter.

class UserListImport extends \Maatwebsite\Excel\Files\ExcelFile {    public function getFile()    {        return storage_path('exports') . '/file.csv';    }    public function getFilters()    {        return [            'chunk'        ];    }}

If you want to have thegetFile()dynamic based on user's input, you can easily do:

public function getFile(){    // Import a user provided file    $file = Input::file('report');    $filename = $this->doSomethingLikeUpload($file);    // Return it's location    return $filename;}

Usage

You can inject these ExcelFiles inside the __constructor or inside the method (when using Laravel 5.0), in e.g. the controller.

class ExampleController extends Controller {    public function importUserList(UserListImport $import)    {        // get the results        $results = $import->get();    }}

CSV Settings

You can pass through optional CSV settings, like$delimiter,$enclosureand$lineEndingas protected properties of the class.

class UserListImport extends \Maatwebsite\Excel\Files\ExcelFile {    protected $delimiter  = ',';    protected $enclosure  = '"';    protected $lineEnding = '\r\n';}

Import Handlers

To decouple your Excel-import code completely from the controller, you can use the import handlers.

class ExampleController extends Controller {    public function importUserList(UserListImport $import)    {        // Handle the import        $import->handleImport();    }}

ThehandleImport()method will dynamically call a handler class which is your class name appended withHandler

class UserListImportHandler implements \Maatwebsite\Excel\Files\ImportHandler {    public function handle(UserListImport $import)    {        // get the results        $results = $import->get();    }}

Handling imported results

Getting all sheets and rows

After you have loaded a file, you can->get()the results like so:

Excel::load('file.xls', function($reader) {})->get();

or

Excel::load('file.xls', function($reader) {    // Getting all results    $results = $reader->get();    // ->all() is a wrapper for ->get() and will work the same    $results = $reader->all();});

The->get()and->all()methods will return a sheet or row collection, depending on the amount of sheets the file has. You can disable this feature inside theimport.phpconfig by setting'force_sheets_collection'totrue. When set to true it will always return a sheet collection.

Table heading as attributes

By default the first row of the excel file will be used as attributes.

// Get the firstname$row->firstname;

Note: by default these attributes will be converted to a slug. You can change the default inside the configexcel::import.heading. Available options are:true|false|slugged|ascii|numeric|hashed|trans|original

True and slugged will be converted to ASCII as well whenexcel::import.to_asciiis set to true. You can change the default separator as well inside the config.

Collections

Sheets, rows and cells are collections, this means after doing a->get()you can use all default collection methods.

// E.g. group the results$reader->get()->groupBy('firstname');

Getting the first sheet or row

To get the first sheet or row, you can utilise->first().

$reader->first();

Note:depending on the config'force_sheets_collection'it will return the first row or sheet.

Workbook and sheet title

It's possible to retrieve the workbook and sheet title with->getTitle().

// Get workbook title$workbookTitle = $reader->getTitle();foreach($reader as $sheet){    // get sheet title    $sheetTitle = $sheet->getTitle();}

Limiting the file reading

Taking rows

When you only want to return the first x rows of a sheet, you can use->takeRows()or->limitRows().

// You can either use ->takeRows()$reader->takeRows(10);// Or ->limitRows()$reader->limitRows(10);

Skipping rows

When you want to skip a certain amount of rows you can use->skipRows()or->limitRows(false, 10)

// Skip 10 results$reader->skipRows(10);// Skip 10 results with limit, but return all other rows$reader->limitRows(false, 10);// Skip and take$reader->skipRows(10)->takeRows(10);

Taking columns

When you only want to return the first x columns of a sheet, you can use->takeColumns()or->limitColumns().

// You can either use ->takeColumns()$reader->takeColumns(10);// Or ->limitColumns()$reader->limitColumns(10);

Skipping columns

When you want to skip a certain amount of columns you can use->skipColumns()or->limitColumns(false, 10)

// Skip 10 results$reader->skipColumns(10);// Skip 10 results with limit, but return all other columns$reader->limitColumns(false, 10);// Skip and take$reader->skipColumns(10)->takeColumns(10);

Result mutators

When you want to get an array instead of an object, you can use->toArray().

$reader->toArray();

When you want an object, you can alternativly (instead of get() or all()) use->toObject().

$reader->toObject();

Displaying results

You can dump the results to a readable output by using->dump()or->dd().

// Dump the results$reader->dump();// Dump results and die$reader->dd();

Iterating the results

You can iterate the results by using->each().

// Loop through all sheets$reader->each(function($sheet) {    // Loop through all rows    $sheet->each(function($row) {    });});

Alternatively you can alsoforeachthe results.

Selecting sheets and columns

Selecting one specific sheet

If you want to select a single sheet, you can use->selectSheets($name). Only that sheet will be loaded.

Excel::selectSheets('sheet1')->load();

Selecting multiple sheets

If you want to select multiple sheets inside your file, you can pass an array as the parameter;

Excel::selectSheets('sheet1', 'sheet2')->load();

Selecting sheets by index

// First sheetExcel::selectSheetsByIndex(0)->load();// First and second sheetExcel::selectSheetsByIndex(0, 1)->load();

Selecting columns

If you want to select only a couple of columns, you can use->select($columns)or pass an array as the first parameter of->get($columns).

// Select$reader->select(array('firstname', 'lastname'))->get();// Or$reader->get(array('firstname', 'lastname'));

All get methods (like all(), first(), dump(), toArray(), ...) accept an array of columns.

Dates

By default the dates will be parsed as aCarbon object. You can disable date formatting completly insideimport.phpby settingdates.enabledtofalse.

To enable/disable date formatting for a single import, use->formatDates($boolean, $format)

// Format the dates$reader->formatDates(true);// Disable date formatting$reader->formatDates(false);// Format dates + set date format$reader->formatDates(true, 'Y-m-d');

Format dates

By default the dates arenot formatted, but returned as a Carbon object. There are a couple of options to format them.

Formatting results after ->get()

Inside your loop you can utilise the Carbon method->format($dateFormat)

$rows->each(function($row) {    $created_at = $row->created_at->format('Y-m-d');});

Setting a default date format

Inside the config you can set a default date format. A Carbon object will no longer be returned.

Or you can use->setDateFormat()

$reader->setDateFormat('Y-m-d');

Setting custom date columns

Cells which are not Excel formatted dates will not be parsed as a date. To force this behaviour (or to use this with CSV imports), you can set these date columns manually:->setDateColumns()

$reader->setDateColumns(array(    'created_at',    'deleted_at'))->get();

Calculate formulas

By default formulas inside the file are being calculated and it's result will be returned. Insideimport.phpconfig you can change the default behaviour by settingcalculateto the desired preference.

If you want to enable/disable it for a single import, you can use->calculate($boolean)

// Enable calculation$reader->calculate();// Disable calculation$reader->calculate(false);

Custom formatting values

By default Laravel Excel uses PHPExcel's default value binder to intelligently format a cells value when reading it. You may override this behavior by passing in your own value binder to suit your specific needs. Value binders must implement PHPExcel_Cell_IValueBinder and have a bindValue method. They may also extend PHPExcel_Cell_DefaultValueBinder to return the default behavior.

use PHPExcel_Cell;use PHPExcel_Cell_DataType;use PHPExcel_Cell_IValueBinder;use PHPExcel_Cell_DefaultValueBinder;class MyValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder{    public function bindValue(PHPExcel_Cell $cell, $value = null)    {        if (is_numeric($value))        {            $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC);            return true;        }        // else return default behavior        return parent::bindValue($cell, $value);    }}$myValueBinder = new MyValueBinder;$reader = Excel::setValueBinder($myValueBinder)->load('file.xls');// Reader methods

Available PHPExcel_Cell_DataType's are TYPE_STRING, TYPE_FORMULA, TYPE_NUMERIC, TYPE_BOOL, TYPE_NULL, TYPE_INLINE and TYPE_ERROR

To reset the value binder back to default and/or before calling Laravel Excel after setting a custom value binder you need to call the resetValueBinder method.

Excel::resetValueBinder();

Caching and Cell caching

Cell caching

You can enable cell caching inside the configexcel.php. You can choose between a couple of drivers and change a couple of settings. By default the caching isenabledand will usein memorycaching.

Remembering results

If you want to remember the results you can use->remember($minutes). Next time you will load the same file (if it's still inside the cache), it will return the cached results.

// Remember for 10 minutes$results = $reader->remember(10)->get();

Chunk importer

When dealing with big files, it's better to import the data in big chunks. You can enable this withfilter('chunk');To import it into chunks you can usechunk($size, $callback)instead of the normalget(). The first parameter is the size of the chunk. The second parameter is a closure which will return the results.

Excel::filter('chunk')->load('file.csv')->chunk(250, function($results){        foreach($results as $row)        {            // do stuff        }});

ExcelFile class example:

When working with ExcelFile injections (in the constructor or as method injection), you can enable the chunk filter inside the ExcelFile class

class UserListImport extends \Maatwebsite\Excel\Files\ExcelFile {    public function getFile()    {        return 'file.csv';    }    public function getFilters()    {        return [            'chunk'        ];    }}

Injected ExcelFile example:

public function importUserList(UserListImport $import){    $import->chunk(250, function($results)    {        // do stuff        // or return true if you want to stop importing.    });}

Queued chunks

We automatically queue every chunk for you, if you have enabled the queue driver in your config.

If you want to by-pass the behaviour, you can passfalseas the third parameter ofchunk($size, $callback, $shouldQueue).

A non-default job queue may be specified by passing its name as the third parameter ofchunk($size, $callback, $shouldQueue).

Batch import

Import a folder

To import an entire folder (only xls, xlsx and csv files will be imported), set the folder as the first parameter.

Excel::batch('app/storage/uploads', function($rows, $file) {    // Explain the reader how it should interpret each row,    // for every file inside the batch    $rows->each(function($row) {        // Example: dump the firstname        dd($row->firstname);    });});

Import multiple files

It's also possible to provide an array of files to import.

$files = array(    'file1.xls',    'file2.xls');Excel::batch($files, function($rows, $file) {});

Import a folder and multiple sheets

When your files contain multiple sheets, you should also loop the sheets

Excel::batch('app/storage/uploads', function($sheets, $file) {    $sheets->each(function($sheet) {    });});

Import by Config

When using advanced Excel files (e.g. without any heading columns), it can be complicated to import these.->byConfig()will help you handle this problem.

Insideexcel::import.sheetsconfig you can find an example.

Excel::load('file.xls')->byConfig('excel::import.sheets', function($sheet) {    // The firstname getter will correspond with a cell coordinate set inside the config    $firstname = $sheet->firstname;});

Note:if you are using multiple sheets.->byConfigwill loop through all sheets. If these getters are only exist on one sheet, you can always use->selectSheets().

Editing existing files

You can edit existing Excel files, by loading them and after modification exporting them.

Excel::load('file.csv', function($file) {    // modify stuff})->export('csv');

Converting

You can convert from one filetype to another by using->convert()

Excel::load('file.csv', function($file) {    // modify stuff})->convert('xls');

Extra

Disable using first row as collection attributes

By default we will use the first row of a file as table heading (so as attribute names for the collection).You can change the default behaviour insideimport.phpwithimport.heading.

To disable this for a single import, use->noHeading().

$reader->noHeading();

Setting the cell name separator

By default collection attribute names will be set by looking at the first row columns. Spaces will be translated to_.

E.g. Created at -> created_at

The default behaviour can be changed inside theimport.phpconfig by changing'separator'. Or you can use->setSeparator($separator).

$reader->setSeparator('-');

Ignoring empty cells

By default empty cells will not be ignored and presented as null inside the cell collection.

To change the default behaviour, you can change'ignoreEmpty' insideimport.phpor use->ignoreEmpty().

$reader->ignoreEmpty();

Input encoding

Inside theimport.phpconfig you can change the input encoding. In most casesUTF-8will be the best solution. Hower if you dump your results make sure your HTML page has this exact same meta charset!

Optionally you can pass the input encoding inside the->load()method.

// When utilising a closure, you can pass the input encoding as third parameter.Excel::load('filename.csv', function($reader) {}, 'UTF-8');// or without a closure, you can use it as second parameter.Excel::load('filename.csv', 'UTF-8');

CSV Settings

Inside thecsv.phpconfig you can change the default settings, like thedelimiter, theenclosureand theline_ending.