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