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