Introduce
Laravel Excel is a simple but elegant wrapper around PhpSpreadsheet with the goal of simplifying the export and import of files.
And here is the PhpSpreadsheet for those of you who are wondering and wondering about it
PhpSpreadsheet is a library written in pure PHP and provides a set of classes that allow you to read and write to various spreadsheet file formats, such as Excel and LibreOffice Calc.
The functions of Laravel Excel
- Easily export collections to Excel.
- Export queries with automatic chunking for better performance.
- Export queues for better performance.
- Easily export Blade views to Excel.
- Easy to enter collection.
- Read Excel file in block.
- Handling import inserts in batches.
Setting
To install Laravel Excel, you need to have the following:
- PHP:
7.0
- Laravel:
5.5
- PhpSpreadsheet:
1.6
- The PHP extensions are
php_zip, php_xml, php_gd2
that are enabled
After you have met all the above requirements, you need to install this package into the composer.json file of the laravel project by opening the terminal and typing this command.
1 2 | composer require maatwebsite/excel |
Now MaatwebsiteExcelExcelServiceProvider
will automatically be registered for you. However, if you want to register yourself, you can add this to your config/app.php
file
1 2 3 4 5 6 7 8 9 10 11 12 13 | <span class="token comment">// Ở mục providers thêm dòng này.</span> <span class="token single-quoted-string string">'providers'</span> <span class="token operator">=</span> <span class="token operator">></span> <span class="token punctuation">[</span> <span class="token comment">/* * Package Service Providers... */</span> Maatwebsite <span class="token package">Excel ExcelServiceProvider</span> <span class="token punctuation">:</span> <span class="token punctuation">:</span> <span class="token keyword">class</span> <span class="token punctuation">,</span> <span class="token punctuation">]</span> <span class="token comment">// Ở mục aliases thêm dòng này.</span> <span class="token single-quoted-string string">'aliases'</span> <span class="token operator">=</span> <span class="token operator">></span> <span class="token punctuation">[</span> <span class="token punctuation">.</span> <span class="token punctuation">.</span> <span class="token punctuation">.</span> <span class="token single-quoted-string string">'Excel'</span> <span class="token operator">=</span> <span class="token operator">></span> Maatwebsite <span class="token package">Excel Facades Excel</span> <span class="token punctuation">:</span> <span class="token punctuation">:</span> <span class="token keyword">class</span> <span class="token punctuation">,</span> <span class="token punctuation">]</span> |
Use
Export
First you need to create an export class in the App/Export
folder. Or you can use the following command to create automatically:
1 2 | php artisan make:export ExportName --model=ModelExportName |
Now in the App/Exports
folder, your file will be created. Specifically here I run the command php artisan make:export UsersExport --model=User
so there will be a directory structure like this.
1 2 3 4 5 6 | ├── app │ ├── Exports │ │ ├── UsersExport.php │ └── composer.json |
Now your UsersExport.php
file will look like this.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <span class="token php language-php"><span class="token delimiter important"><?php</span> <span class="token keyword">namespace</span> <span class="token package">App Exports</span> <span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">App User</span> <span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">Maatwebsite Excel Concerns FromCollection</span> <span class="token punctuation">;</span> <span class="token keyword">class</span> <span class="token class-name">UsersExport</span> <span class="token keyword">implements</span> <span class="token class-name">FromCollection</span> <span class="token punctuation">{</span> <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">collection</span> <span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">return</span> User <span class="token punctuation">:</span> <span class="token punctuation">:</span> <span class="token function">all</span> <span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> </span> |
Now, just call the export class created above return Excel::download(new UsersExport, 'users.xlsx');
to export your User model to users.xlsx file.
Finally, adding a path to access when you need to export is great. You can refer to the example below offline.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <span class="token comment">// UserController</span> <span class="token keyword">use</span> <span class="token package">App Exports UsersExport</span> <span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">Maatwebsite Excel Facades Excel</span> <span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">App Http Controllers Controller</span> <span class="token punctuation">;</span> <span class="token keyword">class</span> <span class="token class-name">UsersController</span> <span class="token keyword">extends</span> <span class="token class-name">Controller</span> <span class="token punctuation">{</span> <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">export</span> <span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">return</span> Excel <span class="token punctuation">:</span> <span class="token punctuation">:</span> <span class="token function">download</span> <span class="token punctuation">(</span> <span class="token keyword">new</span> <span class="token class-name">UsersExport</span> <span class="token punctuation">,</span> <span class="token single-quoted-string string">'users.xlsx'</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> <span class="token comment">// Route</span> Route <span class="token punctuation">:</span> <span class="token punctuation">:</span> <span class="token function">get</span> <span class="token punctuation">(</span> <span class="token single-quoted-string string">'users/export/'</span> <span class="token punctuation">,</span> <span class="token single-quoted-string string">' <a class="__cf_email__" href="/cdn-cgi/l/email-protection">[email protected]</a> '</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> |
After doing the above, you just need to run the php artisan serve
command and then go to your-local-host:8000/users/export
to see and feel
Import
Similar to the time of export, in order to be able to import, you also need to create an import class in the App/Imports
directory, and the creation by rice power or via the command php artisan make:import ImportName --model=ModelImportName
, Everyone decide for themselves.
Once created, the directory structure will be like that.
1 2 3 4 5 6 | ├── app │ ├── Imports │ │ ├── UsersImport.php │ └── composer.json |
Now open the file UsersImport.php
just created above and add the code below to perform the creation of a new User with each row of data that we have imported.
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 | <span class="token php language-php"><span class="token delimiter important"><?php</span> <span class="token keyword">namespace</span> <span class="token package">App Imports</span> <span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">App User</span> <span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">Illuminate Support Facades Hash</span> <span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">Maatwebsite Excel Concerns ToModel</span> <span class="token punctuation">;</span> <span class="token keyword">class</span> <span class="token class-name">UsersImport</span> <span class="token keyword">implements</span> <span class="token class-name">ToModel</span> <span class="token punctuation">{</span> <span class="token comment">/** * @param array $row * * @return User|null */</span> <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">model</span> <span class="token punctuation">(</span> <span class="token keyword">array</span> <span class="token variable">$row</span> <span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token keyword">return</span> <span class="token keyword">new</span> <span class="token class-name">User</span> <span class="token punctuation">(</span> <span class="token punctuation">[</span> <span class="token single-quoted-string string">'name'</span> <span class="token operator">=</span> <span class="token operator">></span> <span class="token variable">$row</span> <span class="token punctuation">[</span> <span class="token number">0</span> <span class="token punctuation">]</span> <span class="token punctuation">,</span> <span class="token single-quoted-string string">'email'</span> <span class="token operator">=</span> <span class="token operator">></span> <span class="token variable">$row</span> <span class="token punctuation">[</span> <span class="token number">1</span> <span class="token punctuation">]</span> <span class="token punctuation">,</span> <span class="token single-quoted-string string">'password'</span> <span class="token operator">=</span> <span class="token operator">></span> Hash <span class="token punctuation">:</span> <span class="token punctuation">:</span> <span class="token function">make</span> <span class="token punctuation">(</span> <span class="token variable">$row</span> <span class="token punctuation">[</span> <span class="token number">2</span> <span class="token punctuation">]</span> <span class="token punctuation">)</span> <span class="token punctuation">,</span> <span class="token punctuation">]</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> </span> |
Finally put in the controller to perform the import
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <span class="token keyword">use</span> <span class="token package">App Imports UsersImport</span> <span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">Maatwebsite Excel Facades Excel</span> <span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">App Http Controllers Controller</span> <span class="token punctuation">;</span> <span class="token keyword">class</span> <span class="token class-name">UsersController</span> <span class="token keyword">extends</span> <span class="token class-name">Controller</span> <span class="token punctuation">{</span> <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">import</span> <span class="token punctuation">(</span> <span class="token punctuation">)</span> <span class="token punctuation">{</span> Excel <span class="token punctuation">:</span> <span class="token punctuation">:</span> <span class="token function">import</span> <span class="token punctuation">(</span> <span class="token keyword">new</span> <span class="token class-name">UsersImport</span> <span class="token punctuation">,</span> <span class="token single-quoted-string string">'users.xlsx'</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token keyword">return</span> <span class="token function">redirect</span> <span class="token punctuation">(</span> <span class="token single-quoted-string string">'/'</span> <span class="token punctuation">)</span> <span class="token operator">-</span> <span class="token operator">></span> <span class="token function">with</span> <span class="token punctuation">(</span> <span class="token single-quoted-string string">'success'</span> <span class="token punctuation">,</span> <span class="token single-quoted-string string">'All good!'</span> <span class="token punctuation">)</span> <span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> |
summary
Above are the basics to know to be able to export and import files in laravel. There are also many useful functions like export / import multiple sheets, export / import using queues, … but time and english are limited so I still have not been able to dig deeper.
Everyone can visit the document page of the package here to learn more.
Thanks for reading the whole article without throwing bricks