import and export Excel and CSV files in laravel 5.1

An eloquent way of importing and exporting Excel and CSV files inLaravel 5.* with the power of PHPOffice’s PHPExcel
Installation

Require this package in your composer.json and update composer. This will download the package and PHPExcel of PHPOffice.

"maatwebsite/excel": "~2.0"

After updating composer, add the ServiceProvider to the providers array in app/config/app.php

'Maatwebsite\Excel\ExcelServiceProvider',

You can use the facade for shorter code. Add this to your aliasses:

'Excel' => 'Maatwebsite\Excel\Facades\Excel',

The class is binded to the ioC as excel

$excel = App::make('excel');

To publish the config settings in Laravel 5 use:

php artisan vendor:publish

This will add an excel.php config file to your config folder.
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 the getFile() method you return the filename and it’s location. Inside the getFilters() 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 the getFile() 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, $enclosure and $lineEnding as 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();
 }

}

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

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 the import.php config by setting ‘force_sheets_collection’ to true. 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 config excel::import.heading. Available options are: true|false|slugged|ascii|numeric|hashed|trans|original

True and slugged will be converted to ASCII as well when excel::import.to_ascii is 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 results
 Taking rows

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

// You can either use ->take()
 $reader->take(10);

// Or ->limit()
 $reader->limit(10);

Skipping rows

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

// Skip 10 results
 $reader->skip(10);

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

// Skip and take
 $reader->skip(10)->take(10);

// Limit with skip and take
 $reader->($skip, $take);

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

});

});

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);
Key Explanation
full Full path with filename
path Path without filename
file Filename
title File title
ext File 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')
));

Cell manipulation

$sheet->cell('A1', function($cell) {

    // manipulate the cell

});

$sheet->cells('A1:A5', function($cells) {

    // manipulate the range of cells

});

Set background

To change the background of a range of cells we can use ->setBackground($color, $type, $colorType)

// Set black background
$cells->setBackground('#000000');

Change fonts

// Set with font color
$cells->setFontColor('#ffffff');

// Set font family
$cells->setFontFamily('Calibri');

// Set font size
$cells->setFontSize(16);

// Set font weight to bold
$cells->setFontWeight('bold');

// Set font
$cells->setFont(array(
    'family'     => 'Calibri',
    'size'       => '16',
    'bold'       =>  true
));

Set borders

// Set all borders (top, right, bottom, left)
$cells->setBorder('solid', 'none', 'none', 'solid');

// Set borders with array
$cells->setBorder(array(
    'borders' => array(
        'top'   => array(
            'style' => 'solid'
        ),
    )
));

Set horizontal alignment

// Set alignment to center
$cells->setAlignment('center');

Set vertical alignment

// Set vertical alignment to middle
 $cells->setValignment('middle');

Sheet styling

General styling

If you want to change the general styling of your sheet (not cell or range specific), you can use the ->setStyle() method.

// Set font with ->setStyle()`
$sheet->setStyle(array(
    'font' => array(
        'name'      =>  'Calibri',
        'size'      =>  15,
        'bold'      =>  true
    )
));

Fonts

To change the font for the current sheet use ->setFont($array):

$sheet->setFont(array(
    'family'     => 'Calibri',
    'size'       => '15',
    'bold'       => true
));

Separate setters

// Font family
$sheet->setFontFamily('Comic Sans MS');

// Font size
$sheet->setFontSize(15);

// Font bold
$sheet->setFontBold(true);

Borders

You can set borders for the sheet, by using:

// Sets all borders
$sheet->setAllBorders('thin');

// Set border for cells
$sheet->setBorder('A1', 'thin');

// Set border for range
$sheet->setBorder('A1:F10', 'thin');

Go to the reference guide to see a list of available border styles

Freeze rows

If you want to freeze a cell, row or column, use:

// Freeze first row
$sheet->freezeFirstRow();

// Freeze the first column
$sheet->freezeFirstColumn();

// Freeze the first row and column
$sheet->freezeFirstRowAndColumn();

// Set freeze
$sheet->setFreeze('A2');

Auto filter

To enable the auto filter use ->setAutoFilter($range = false).

// Auto filter for entire sheet
$sheet->setAutoFilter();

// Set auto filter for a range
$sheet->setAutoFilter('A1:E10');

Cell size

Set column width

To set the column width use ->setWidth($cell, $width).

// Set width for a single column
$sheet->setWidth('A', 5);

// Set width for multiple cells
$sheet->setWidth(array(
    'A'     =>  5,
    'B'     =>  10
));

Set row height

To set the row height use ->setHeight($row, $height).

// Set height for a single row
$sheet->setHeight(1, 50);

// Set height for multiple rows
$sheet->setHeight(array(
    1     =>  50,
    2     =>  25
));

Set cell size

To set the cell size use ->setSize($cell, $width, $height).

// Set size for a single cell
$sheet->setSize('A1', 500, 50);

$sheet->setSize(array(
    'A1' => array(
        'width'     => 50
        'height'    => 500,
    )
));

Auto size

By default the exported file be automatically auto sized. To change this behaviour you can either change the config or use the setters:

// Set auto size for sheet
$sheet->setAutoSize(true);

// Disable auto size for sheet
$sheet->setAutoSize(false);

// Disable auto size for columns
$sheet->setAutoSize(array(
    'A', 'C'
));

The default config setting can be found in: export.php.

Column merging

Merging cells

To merge a range of cells, use ->mergeCells($range).

$sheet->mergeCells('A1:E1');

Merging columns and rows

To merge columns and rows, use ->setMergeColumn($array).

$sheet->setMergeColumn(array(
    'columns' => array('A','B','C','D'),
    'rows' => array(
        array(2,3),
        array(5,11),
    )
));

Column formatting

To tell Excel how it should interpret certain columns, you can use ->setColumnFormat($array).

// Format column as percentage
$sheet->setColumnFormat(array(
    'C' => '0%'
));

// Format a range with e.g. leading zeros
$sheet->setColumnFormat(array(
    'A2:K2' => '0000'
));

// Set multiple column formats
$sheet->setColumnFormat(array(
    'B' => '0',
    'D' => '0.00',
    'F' => '@',
    'F' => 'yyyy-mm-dd',
));

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

Calling PHPExcel’s native methods

It’s possible to call all native PHPExcel methods on the $excel and $sheet objects.

Calling Workbook methods

Example:

// Get default style for this workbook
$excel->getDefaultStyle();

Calling worksheet methods

Example:

// Protect cells
$sheet->protectCells('A1', $password);

 

 

@Blade to Excel

We can utilise the magic of Laravel’s Blade engine to power our Excel export. Sharing a view, loading a view per sheet, creating a html table inside a view, basic CSS styling, …

Loading a view for a single sheet

We can load a view for every sheet we create with ->loadView().

Excel::create('New file', function($excel) {

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

        $sheet->loadView('folder.view');

    });

});

Using different views for different sheets

Excel::create('New file', function($excel) {

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

        $sheet->loadView('view_first');
    });

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

        $sheet->loadView('view_second');
    });

});

Sharing a view for all sheets

We can share a view for all sheets with shareView().

Excel::shareView('folder.view')->create();

Unsetting a view for a sheet

When we are using a shared view, but we don’t want to use a view for the current sheet, we can use ->unsetView().

$sheet->unsetView();

Passing variables to the view

As parameter

We can pass variables to the view by using the second parameter inside the loadView() method.

$sheet->loadView('view', array('key' => 'value'));

With with()

Alternatively you can use the with() method which works the same as with Laravel views.

// Using normal with()
$sheet->loadView('view')
      ->with('key', 'value');

// using dynamic with()
$sheet->loadView('view')
      ->withKey('value');

Styling sheets

General styling

If you want to change the general styling of your sheet (not cell or range specific), you can use the ->setStyle() method or any of the other setters which can be found inside the export documentation.

// Font family
$sheet->setFontFamily('Comic Sans MS');

// Set font with ->setStyle()`
$sheet->setStyle(array(
    'font' => array(
        'name'      =>  'Calibri',
        'size'      =>  12,
        'bold'      =>  true
    )
));

Styling with PHPExcel methods

It’s possible to style the sheets and specific cells with help of PHPExcel methods. This package includes a lot of shortcuts (see export documentation), but also always the use of the native methods.

// Set background color for a specific cell
$sheet->getStyle('A1')->applyFromArray(array(
    'fill' => array(
        'type'  => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => array('rgb' => 'FF0000')
    )
));

Using HTML tags

Most of the HTML tags are supported.

<html>

    <!-- Headings -->
    <td><h1>Big title</h1></td>

    <!--  Bold -->
    <td><b>Bold cell</b></td>
    <td><strong>Bold cell</strong></td>

    <!-- Italic -->
    <td><i>Italic cell</i></td>

    <!-- Images -->
    <td><img src="img.jpg" /></td>

</html>

Inside the view.php config you can change how these tags will be interpreted by Excel by default.

Using HTML attributes

Some of the basic styling can be done with HTML attributes.

<html>

    <!-- Horizontal alignment -->
    <td align="right">Big title</td>

    <!--  Vertical alignment -->
    <td valign="middle">Bold cell</td>

    <!-- Rowspan -->
    <td rowspan="3">Bold cell</td>

    <!-- Colspan -->
    <td colspan="6">Italic cell</td>

    <!-- Width -->
    <td width="100">Cell with width of 100</td>

    <!-- Height -->
    <td height="100">Cell with height of 100</td>

</html>

Styling through inline-styles

It’s possible to use inline styles inside your view files. Most of the general styles are supported.

<html>

    <!-- Cell with black background -->
    <td style="background-color: #000000;">Cell</td>

</html>

Inside the reference guide you can find a list of supported styles.

Styling through external CSS file

Styling can be done through an external CSS file.

External css file:

#cell {
    background-color: #000000;
    color: #ffffff;
}

.cell {
    background-color: #000000;
    color: #ffffff;
}

tr td {
    background-color: #ffffff;
}

tr > td {
    border-bottom: 1px solid #000000;
}

Table:

<html>

    {{ HTML::style('css/table.css') }}

    <!-- Cell styled with class -->
    <td class="cell">Cell</td>

    <!-- Cell styled with ID -->
    <td id="cell">Cell</td>

</html>





Available file properties

Properties that can be set with $excel->set{$property}()

Property name
creator
lastModifiedBy
title
description
subject
keywords
category
manager
company

Available sheet properties

Properties that can be set with $sheet->set{$property}()

Property name Possible value
orientation string
paperSize integer
scale integer
fitToPage boolean
fitToHeight boolean
fitToWidth boolean
columnsToRepeatAtLeft array
rowsToRepeatAtTop array
horizontalCentered boolean
verticalCentered boolean
printArea range
firstPageNumber integer

Available CSS styles

Styles that can be used inside an external CSS file or as inline CSS.

Style name Example Value
background(-color) #000000
color #FFFFFF
font-weight bold
font-style italic
font-weight bold
font-size 20px
font-family Open Sans
text-decoration underline
text-align center
vertical-align middle
border(-*) 1px dashed #CCC
width 100(px)
height 1100(px)

Available border styles

Style name PHPExcel class reference
none PHPExcel_Style_Border::BORDER_NONE
dashDot PHPExcel_Style_Border::BORDER_DASHDOT
dashDotDot PHPExcel_Style_Border::BORDER_DASHDOTDOT
dashed PHPExcel_Style_Border::BORDER_DASHED
dotted PHPExcel_Style_Border::BORDER_DOTTED
double PHPExcel_Style_Border::BORDER_DOUBLE
hair PHPExcel_Style_Border::BORDER_HAIR
medium PHPExcel_Style_Border::BORDER_MEDIUM
mediumDashDot PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT
mediumDashDotDot PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT
mediumDashed PHPExcel_Style_Border::BORDER_MEDIUMDASHED
slantDashDot PHPExcel_Style_Border::BORDER_SLANTDASHDOT
thick PHPExcel_Style_Border::BORDER_THICK
thin PHPExcel_Style_Border::BORDER_THIN

Available column formatting

Format name PHPExcel class reference
General PHPExcel_Style_NumberFormat::FORMAT_GENERAL
@ PHPExcel_Style_NumberFormat::FORMAT_TEXT
0 PHPExcel_Style_NumberFormat::FORMAT_NUMBER
0.00 PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00
#,##0.00 PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1
#,##0.00_- PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2
0% PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE
0.00% PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00
yyyy-mm-dd PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2
yy-mm-dd PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD
dd/mm/yy PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY
d/m/y PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH
d-m-y PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYMINUS
d-m PHPExcel_Style_NumberFormat::FORMAT_DATE_DMMINUS
m-y PHPExcel_Style_NumberFormat::FORMAT_DATE_MYMINUS
mm-dd-yy PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14
d-mmm-yy PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15
d-mmm PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX16
mmm-yy PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX17
m/d/yy h:mm PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX22
d/m/y h:mm PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME
h:mm AM/PM PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME1
h:mm:ss AM/PM PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME2
h:mm PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3
h:mm:ss PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4
mm:ss PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME5
h:mm:ss PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME6
i:s.S PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME7
h:mm:ss;@ PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8
yy/mm/dd;@ PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH
“$”#,##0.00_- PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE
$#,##0_- PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD
[$EUR ]#,##0.00_- PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE

Closures

Method Closure class
create() Maatwebsite\Excel\Writers\LaravelExcelWriter
load() Maatwebsite\Excel\Readers\LaravelExcelReader
batch Maatwebsite\Excel\Readers\Batch
sheet() Maatwebsite\Excel\Classes\LaravelExcelWorksheet
cells() Maatwebsite\Excel\Writers\CellWriter
row() Maatwebsite\Excel\Writers\CellWriter
Deven Rathore

Deven is an Entrepreneur, and Full-stack developer, Constantly learning and experiencing new things. He currently runs CodeSource.io and Dunebook.com.

Published by
Deven Rathore

Recent Posts

Flutter App Development: Essential Things You Should Know

Divided by mobile operating systems, companies have to develop their apps twice and roll them…

7 hours ago

7 Tips To Build Responsive & Dynamic Drupal Website For Your Business

For optimal user experience, consumers want responsive websites that are easy to use, multi-device friendly,…

2 days ago

OpenCart vs Magento: What You Should Choose in 2021

Users all over the world are faced with the problem of choosing a platform for…

1 week ago

Top 20 Android Open Source Projects

Reading codes and contributing to open source has been proven to be one of the…

3 weeks ago

Top 5 tools to proofread and edit essays with the help of software

Poor grammar and incorrect spelling can significantly lower the value of any literary work. Going…

6 hours ago

The Best 5 Career Advice for Web Design Students

Are you thinking of a career in web design but not sure where to start? The…

4 weeks ago