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 also foreach the 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 sheet
 Excel::selectSheetsByIndex(0)->load();

// First and second sheet
 Excel::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 a Carbon object. You can disable date formatting completly inside import.php by setting dates.enabled to false. 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 are not 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. Inside import.php config you can change the default behaviour by setting calculate to 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;

Excel::setValueBinder($myValueBinder)->load('file.xls', function($reader) {

// 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 config cache.php. You can choose between a couple of drivers and change a couple of settings. By default the caching is enabled and will use in memory caching.
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 with filter(‘chunk’); To import it into chunks you can use chunk($size, $callback) instead of the normal get(). 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
 })
 }

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);

});

});