Laravel-Excel package is great for exporting data. But not a lot of info there about formatting Excel cells – widths, word wraps, fonts etc. So when I encountered this in a client’s project, I decided to write this article with a few tips on this topic.
Default Laravel Export
Let’s create a dummy project with Users table and default Laravel Auth. Then we create 10 random users. Actually, Laravel comes with pre-built Factory class for User model, so all we need to do is to create a UsersTableSeeder.php
class UsersTableSeeder extends Seeder { public function run() { factory(App\User::class, 10)->create(); } }
Then, we require Laravel Excel package:
composer require maatwebsite/excel
Now, we export all users to Excel. To do that, we create a separate class app/Exports/UsersExport.php:
namespace App\Exports; use App\User; use Maatwebsite\Excel\Concerns\FromCollection; use Maatwebsite\Excel\Concerns\WithHeadings; class UsersExport implements FromCollection, WithHeadings { public function collection() { return User::all(); } public function headings(): array { return [ '#', 'Name', 'Email', 'Created at', 'Updated at' ]; } }
Finally, let’s use it from Controller:
namespace App\Http\Controllers; use App\Exports\UsersExport; use Maatwebsite\Excel\Facades\Excel; class ExportController extends Controller { function export() { return Excel::download(new UsersExport, 'users.xlsx'); } }
Here’s the visual result:
It does the job, exports the data. But does it look good and readable? Far from it. So let’s tweak it a little.
Step 1. Auto-formatting column widths
Somewhere deep in the official documentation I’ve found a really easy way to adjust the column sizes automatically. All you need is to use one Trait ShouldAutoSize:
// ... use Maatwebsite\Excel\Concerns\ShouldAutoSize; class UsersExport implements FromCollection, WithHeadings, ShouldAutoSize { // ...
And here’s the new Excel:
Better, huh? Let’s move on.
Step 2. Formatting the fonts and sizes
You can adjust any cell styles using method called registerEvents() and AfterSheet event:
// ... use Maatwebsite\Excel\Concerns\WithEvents; use Maatwebsite\Excel\Events\AfterSheet; class UsersExport implements FromCollection, WithHeadings, ShouldAutoSize, WithEvents { // ... /** * @return array */ public function registerEvents(): array { return [ AfterSheet::class => function(AfterSheet $event) { $cellRange = 'A1:W1'; // All headers $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14); }, ]; } }
Here’s how the header row looks now:
The main logic here is this line:
$event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);
It just takes whatever cell range we pass, and changes the styles.
Here’s where we need to dig deeper and look at the package which is the base of Laravel Excel. It’s actually based on PHPSpreadsheet package. So let’s take a look at its documentation, and specifically section Recipes.
Some examples from there:
Wrap text:
$spreadsheet->getActiveSheet()->getStyle('A1:D4') ->getAlignment()->setWrapText(true);
Default style of a worksheet:
$spreadsheet->getDefaultStyle()->getFont()->setName('Arial'); $spreadsheet->getDefaultStyle()->getFont()->setSize(8);
Styling borders and applying styles from array:
$styleArray = [ 'borders' => [ 'outline' => [ 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK, 'color' => ['argb' => 'FFFF0000'], ], ], ]; $worksheet->getStyle('B2:G8')->applyFromArray($styleArray);
You can find more examples in that recipes section, just apply it to your individual needs.
That’s it, that’s how you format exported excel with Laravel and PHP.
Related articles:
Hello.
I was wondering, what about the route? and if I want to click a button in a blade view to trigger the download, how should I do it? Thanks!
Hi, sorry, I didn’t mention this in the article, cause I thought it was pretty basic thing people would figure out.
In this article I’m using controller ExportController and method export(), so you need to add this line to your routes:
Route::get('export', 'ExportController@export')->name('export');
And then somewhere in your blade files:
<a href="{{ route('export') }}">Export</a>
Nice article!
I expected to see the final result at the end though, but alas..
Hi,
I was wondering in which file do I put this line :
$spreadsheet->getActiveSheet()->getStyle(‘A1:D4’)
->getAlignment()->setWrapText(true);
Thanks
Jerome
Beautiful article, but in the case of importing a spreadsheet file using this package how do we catch errors, let’s say an invalid file was uploaded, also in the case where we expect the uploaded file to have some named headings how do we check if the headings are there? Thanks
I think that validation has to be done manually
Nice article. Thankyou so much, its so helpful. Btw, I was wondering what if I want the header with two row? How to do that? Thankyou
try the following:
public function headings(): array
{
return [
[
‘#’,
‘Name’,
‘Email’,
‘Created at’,
‘Updated at’
], [
‘#2’,
‘Name2’,
‘Email2’,
‘Created at2’,
‘Updated at2’
]
];
}
Hello thank you for your contribution, I have a question if I would like to add a new sheet to your example with a glossary of the results of the first one can be done? is that I have been investigating for some time and I do not get anything thanks in advance greetings
This was really helpful article Thnkx mate.
A better and dynamic solution for the cellRange would be:
$cellRange = ‘A1:’.$event->sheet->getDelegate()->getHighestColumn().’1′; // All headers
Nice Article , I have a question how can I do this? does data are coming from my database. Thank you.
Branch: XXX111
PO Number: PO1111
PO Creater: Username
Date PO: 4/12/2019
Inclusion Date: 04/05/2019 TO 04/05/2019 Delivery Date: 04/15/2019
Supplier: SAmple Supplier
Description PO Qty RackNO
7777-Air Freshener 10 D
Hello…Thank you for your useful post…I would like to ask when you use $worksheet or $spreadsheet for example in $spreadsheet->getDefaultStyle()->getFont()->setName(‘Arial’) ….what is exactly $spreadsheet come from…help please
Hi, it’s the same as $event->sheet in examples above.
Hi Lukcy, I think this might help you.
Thanks for the post Povilas 🙂
AfterSheet::class => function(AfterSheet $event) {
$cellRange = ‘A1:W1’; // All headers
$sheet = $event->sheet->getDelegate();
$sheet->getStyle($cellRange)->getFont()->setSize(14);
$spreadsheet = $event->sheet->getParent()->getProperties()
->setCreator(“Maarten Balliauw”)
->setLastModifiedBy(“Maarten Balliauw”)
->setTitle(“Office 2007 XLSX Test Document”)
->setSubject(“Office 2007 XLSX Test Document”)
->setDescription(
“Test document for Office 2007 XLSX, generated using PHP classes.”
)
->setKeywords(“office 2007 openxml php”)
->setCategory(“Test result file”);;
},
Hi,if i to use styling according to query,
for example :if a row has status 1,then ,that row should be red ,other wise no change
How can I do this?
Hii! How to set backround color only for the first row?
i have question how passing parameter to beforeClass
example :: param costructor
———————————————–
protected $jobdescp;
public function __construct($jobdescp)
{
$this->jobdescp = $jobdescp;
}
————————————————————
public static function beforeSheet(BeforeSheet $event)
{
return [
$event->sheet->setCellValue(‘A1’,$this->jobdescp)->mergeCells(‘A1:E3’)
->getStyle(‘A1:E3’)
->getAlignment()
->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)
,
]}
if i using like that ” setCellValue(‘A1’,$this->jobdescp)” i get eror $this object non context
can u help me confused to find solution , thanks for artikel
Very very useful article . I have one question if we have a column I want to show total at the last . Then how can I do this . I had tried many things but I could not get the answer .
is there a possibility of limitation when exporting rows and columns? Example: Bring only A-H columns? Because my export only goes to the letter H and it brings me from I to Z without any registration.
Thanks.
how to add logo before heading can you help me please
Nice article! It really helps me a lot
I;m try to apply comma style to cell generate by laravel excel , but it keep saying “style comma not found” . I wonder why
Hi Povilas Korop,
I want export specific data value to a specific cell on excel. How can I do?
Hi how can i set aling-left to ALL the cells in a dynamic document
hello sir i am working on laravel excel but i am not able to format cells i am doing the exact as you have told in this article but there is no change on excel can you please help me with it ?? thanks in advance
how to do this dynamically i mean i dont know the cell or row i just want to change background color on row where i can match some value then append my attribute on that particular row.
Hello sir,
how to covert row to column?
nice, its very helpuly..
but I have question, for export data automatic read date of column and then can filter by date in excel file without modify data after export?