Laravel-Excel 3.0: Export Custom Array into Excel
Recently popular package Laravel-Excel released a new version 3.0, which was a pretty radical change from previous versions. I needed to do a simple Excel export and noticed that their documentation covers only export from Laravel Collections but not from some custom structure. So I will show you a “workaround”.
First, this is how documentation describes the basic workflow:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
namespace App\Exports; use Maatwebsite\Excel\Concerns\FromCollection; class InvoicesExport implements FromCollection { public function collection() { return Invoice::all(); } } |
You need to create a separate class for export (similar how Laravel Notifications work) and you can define a collection to export.
There are also other ways to export the data:
From Eloquent query:
|
1 2 3 4 5 6 7 8 9 10 11 |
class InvoicesExport implements FromQuery { use Exportable; public function query() { return Invoice::query(); } } |
Or, from View HTML table:
|
1 2 3 4 5 6 7 8 9 10 11 |
class InvoicesExport implements FromView { public function view(): View { return view('exports.invoices', [ 'invoices' => Invoice::all() ]); } } |
But still, the problem is that all examples are based on Eloquent. What if I need Excel from some custom structure like array?
Imagine that I have this array:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[ [ 'name' => 'Povilas', 'surname' => 'Korop', 'email' => 'povilas@laraveldaily.com', 'twitter' => '@povilaskorop' ], [ 'name' => 'Taylor', 'surname' => 'Otwell', 'email' => 'taylor@laravel.com', 'twitter' => '@taylorotwell' ] ] |
How do I export that into Excel, so that keys would be columns, and values would be rows?
There are two ways.
Option 1. Turn your Array into a Collection and add Headings
This is how the Export class would look:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
namespace App\Exports; use Maatwebsite\Excel\Concerns\FromCollection; use Maatwebsite\Excel\Concerns\Exportable; use Maatwebsite\Excel\Concerns\WithHeadings; class CollectionExport implements FromCollection, WithHeadings { use Exportable; public function collection() { return collect([ [ 'name' => 'Povilas', 'surname' => 'Korop', 'email' => 'povilas@laraveldaily.com', 'twitter' => '@povilaskorop' ], [ 'name' => 'Taylor', 'surname' => 'Otwell', 'email' => 'taylor@laravel.com', 'twitter' => '@taylorotwell' ] ]); } public function headings(): array { return [ 'Name', 'Surname', 'Email', 'Twitter', ]; } } |
You need to implement collection() method by using Laravel’s collect() method and passing array to it. Also, you need to implement headings() to add a header row.
Finally, in Controller you will have this row at the end of the method:
|
1 2 3 |
return Excel::download(new CollectionExport(), 'export.xlsx'); |
Then you would have this as downloaded result:
Option 2. Pass Array into View
Alternatively, you can build this resources/views/exports/xml.blade.php:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<table> <thead> <tr> @foreach($data[0] as $key => $value) <th>{{ ucfirst($key) }}</th> @endforeach </tr> </thead> <tbody> @foreach($data as $row) <tr> @foreach ($row as $value) <td>{{ $value }}</td> @endforeach </tr> @endforeach </tbody> </table> |
And then have this in your Export class:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
namespace App\Exports; use Illuminate\Contracts\View\View; use Maatwebsite\Excel\Concerns\FromView; class BladeExport implements FromView { public function view(): View { return view('exports.xml', [ 'data' => [ [ 'name' => 'Povilas', 'surname' => 'Korop', 'email' => 'povilas@laraveldaily.com', 'twitter' => '@povilaskorop' ], [ 'name' => 'Taylor', 'surname' => 'Otwell', 'email' => 'taylor@laravel.com', 'twitter' => '@taylorotwell' ] ] ]); } } |
Controller stays the same, just different class name:
|
1 2 3 |
return Excel::download(new BladeExport(), 'export.xlsx'); |
Wait, but how to pass data into Export class?
Both of our examples have one flaw – the data is formed in the class itself. Actually, it shouldn’t even know about the data, it should accept it as a parameter. But how do we do that, if view() or collection() methods have no parameters?
We will pass it through __construct() method into a private variable. You could call it a dependency injection, although there are much better examples of dependency injection, look it up on Google.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
class BladeExport implements FromView { private $data; public function __construct($data) { $this->data = $data; } public function view(): View { return view('exports.xml', [ 'data' => $this->data ]); } } |
So we’re accepting $data as a parameter now. Then, in our Controller we can have this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
$data = [ [ 'name' => 'Povilas', 'surname' => 'Korop', 'email' => 'povilas@laraveldaily.com', 'twitter' => '@povilaskorop' ], [ 'name' => 'Taylor', 'surname' => 'Otwell', 'email' => 'taylor@laravel.com', 'twitter' => '@taylorotwell' ] ]; return Excel::download(new BladeExport($data), 'export.xlsx'); |
I hope that’s helpful. Check out other examples in official documentation of the package.
Want to generate Laravel adminpanel online?
You don't need any packages to do that!
You don't need any packages to do that!
Very smart notices and hints as we had expected with your Laravel’s articles. However, there is any mention for very large models. i.e suppose a model with more than 2 million records!
Hi, how are you? If you want to add color to the xls cells using xml.blade.php?
this does not work: NAME or NAME
any ideas?
Hello Juan,
This package has configurations for cell manipulation, please refer this link https://laravel-excel.maatwebsite.nl/2.1/export/cells.html