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.

Inside excel::import.sheets config 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. ->byConfig will 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 inside import.php with import.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 the import.php config 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‘ inside import.php or use ->ignoreEmpty().

$reader->ignoreEmpty();

Input encoding

Inside the import.php config you can change the input encoding. In most cases UTF-8 will 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 the csv.php config you can change the default settings, like the delimiter, the enclosure and the line_ending.

 

Simple Excel Export

 

Basics

A new file can be created using the create method with the filename as first parameter.

Excel::create('Filename');

To manipulate the creation of the file you can use the callback

Excel::create('Filename', function($excel) {

    // Call writer methods here

});

Changing properties

There are a couple of properties we can change inside the closure. Most of them are set to the config values by default. See app/config/packages/maatwebsite/excel/config.php.

Excel::create('Filename', function($excel) {

    // Set the title
    $excel->setTitle('Our new awesome title');

    // Chain the setters
    $excel->setCreator('Maatwebsite')
          ->setCompany('Maatwebsite');

    // Call them separately
    $excel->setDescription('A demonstration to change the file properties');

});

Go to the reference guide to see a list of available properties.

Exporting

To download the created file, use ->export($ext) or ->download($ext).

Export to Excel5 (xls)

Excel::create('Filename', function($excel) {

})->export('xls');

// or
->download('xls');

Export to Excel2007 (xlsx)

->export('xlsx');

// or
->download('xlsx');

Export to CSV

->export('csv');

// or
->download('csv');

You can set the default enclosure and delimiter inside the config

Export to PDF

To export files to pdf, you will have to include "dompdf/dompdf": "~0.6.1", "mpdf/mpdf": "~5.7.3" or "tecnick.com/tcpdf": "~6.0.0" in your composer.json and change the export.pdf.driver config setting accordingly.

->export('pdf');

NewExcelFile injections

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

NewExcelFile class

This NewExcelFile is a wrapper for a new Excel file. Inside the getFilename() you can declare the wanted filename.

class UserListExport extends \Maatwebsite\Excel\Files\NewExcelFile {

    public function getFilename()
    {
        return 'filename';
    }
}

Usage

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

class ExampleController extends Controller {

    public function exportUserList(UserListExport $export)
    {
        // work on the export
        return $export->sheet('sheetName', function($sheet)
        {

        })->export('xls');
    }

}

Export Handlers

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

class ExampleController extends Controller {

    public function exportUserList(UserListExport $export)
    {
        // Handle the export
        $export->handleExport();
    }

}

The handleExport() method will dynamically call a handler class which is your class name appended with Handler

class UserListExportHandler implements \Maatwebsite\Excel\Files\ExportHandler {

    public function handle(UserListExport $export)
    {
        // work on the export
        return $export->sheet('sheetName', function($sheet)
        {

        })->export('xls');
    }

}

Store on server

To store the created file on the server, use ->store($ext, $path = false, $returnInfo = false) or ->save().

Normal export to default storage path

By default the file will be stored inside the app/storage/exports folder, which has been defined in the export.php config file.

Excel::create('Filename', function($excel) {

    // Set sheets

})->store('xls');

Normal export to custom storage path

If you want to use a custom storage path (e.g. to separate the files per client), you can set the folder as the second parameter.

->store('xls', storage_path('excel/exports'));

Store and export

->store('xls')->export('xls');

Store and return storage info

If you want to return storage information, set the third paramter to true or change the config setting inside export.php.

->store('xls', false, true);
KeyExplanation
fullFull path with filename
pathPath without filename
fileFilename
titleFile title
extFile extension

Make sure your storage folder is writable!

 

Sheets

Creating a sheet

To create a new sheet inside our newly created file, use ->sheet('Sheetname').

Excel::create('Filename', function($excel) {

    $excel->sheet('Sheetname', function($sheet) {

        // Sheet manipulation

    });

})->export('xls');

Creating multiple sheets

You can set as many sheets as you like inside the file:

Excel::create('Filename', function($excel) {

    // Our first sheet
    $excel->sheet('First sheet', function($sheet) {

    });

    // Our second sheet
    $excel->sheet('Second sheet', function($sheet) {

    });

})->export('xls');

Changing properties

There are a couple of properties we can change inside the closure. Most of them are set to the config values by default. See app/config/packages/maatwebsite/excel/config.php.

Excel::create('Filename', function($excel) {

    $excel->sheet('Sheetname', function($sheet) {

        $sheet->setOrientation('landscape');

    });

})->export('xls');

Go to the reference guide to see a list of available properties.

Default page margin

It’s possible to set the default page margin inside the config file excel::export.sheets. It accepts boolean, single value or array.

To manually set the page margin you can use: ->setPageMargin()

// Set top, right, bottom, left
$sheet->setPageMargin(array(
    0.25, 0.30, 0.25, 0.30
));

// Set all margins
$sheet->setPageMargin(0.25);

Password protecting a sheet

A sheet can be password protected with $sheet->protect():

// Default protect
$sheet->protect('password');

// Advanced protect
$sheet->protect('password', function(\PHPExcel_Worksheet_Protection $protection) {
    $protection->setSort(true);
});

Creating a sheet from an array

Array

To create a new file from an array use ->fromArray($source, $nullValue, $startCell, $strictNullComparison, $headingGeneration) inside the sheet closure.

Excel::create('Filename', function($excel) {

    $excel->sheet('Sheetname', function($sheet) {

        $sheet->fromArray(array(
            array('data1', 'data2'),
            array('data3', 'data4')
        ));

    });

})->export('xls');

Alternatively you can use ->with().

$sheet->with(array(
    array('data1', 'data2'),
    array('data3', 'data4')
));

If you want to pass variables inside the closure, use use($data)

$data = array(
    array('data1', 'data2'),
    array('data3', 'data4')
);

Excel::create('Filename', function($excel) use($data) {

    $excel->sheet('Sheetname', function($sheet) use($data) {

        $sheet->fromArray($data);

    });

})->export('xls');

Null comparision

By default 0 is shown as an empty cell. If you want to change this behaviour, you can pass true as 4th parameter:

// Will show 0 as 0
$sheet->fromArray($data, null, 'A1', true);

To change the default behaviour, you can use excel::export.sheets.strictNullComparison config setting.

Eloquent model

It’s also possible to pass an Eloquent model and export it by using ->fromModel($model). The method accepts the same parameters as fromArray

Auto heading generation

By default the export will use the keys of your array (or model attribute names) as first row (header column). To change this behaviour you can edit the default config setting (excel::export.generate_heading_by_indices) or pass false as 5th parameter:

// Won't auto generate heading columns
$sheet->fromArray($data, null, 'A1', false, false);

Row manipulation

Manipulate certain row

Change cell values

// Manipulate first row
$sheet->row(1, array(
     'test1', 'test2'
));

// Manipulate 2nd row
$sheet->row(2, array(
    'test3', 'test4'
));

Manipulate row cells

// Set black background
$sheet->row(1, function($row) {

    // call cell manipulation methods
    $row->setBackground('#000000');

});

Append row

// Append row after row 2
$sheet->appendRow(2, array(
    'appended', 'appended'
));

// Append row as very last
$sheet->appendRow(array(
    'appended', 'appended'
));

Prepend row

// Add before first row
$sheet->prependRow(1, array(
    'prepended', 'prepended'
));

// Add as very first
$sheet->prependRow(array(
    'prepended', 'prepended'
));

Append multiple rows

// Append multiple rows
$sheet->rows(array(
    array('test1', 'test2'),
    array('test3', 'test4')
));

// Append multiple rows
$sheet->rows(array(
    array('test5', 'test6'),
    array('test7', 'test8')
));