1. Introduction
In any management project, it is indispensable to data statistics by exporting / importing data. Today I would like to introduce to you the Maatwebsite / Laravel-Excel package – a package that allows us to easily export / import data, with many more utilities.
First, let’s install the package Let’s GO ^^ In this article, I use the latest version 3.1.
2. Installation
2.1 Supported Versions
| Version | Laravel Version | Php Version 3 | support |
|---|---|---|---|
| 2.1 | <= 5.6 | <= 7.0 | Unsupported since 15-5-2018 |
| 3.0 | ^ 5.5 | ^ 7.0 | Unsupported since 31-12-2018 |
| 3.1 | ^ 5.5, ^ 6.0, ^ 7.0, ^ 8.0 | ^ 7.2 | New features |
2.2 Package installation
Very simply, we will install the package via composer with just one command:
1 2 | composer require maatwebsite/excel |
Wait a minute, after the package is installed, I can now use it.
3. Usage
Ummmmm … I will skip the steps of creating project laravel, migrate, seed data, … okay! Here I have created an export-project project and used the available table users. Maatwebsite / Laravel-Exce package supports many types of file extension such as: * .csv, * .xls, * .html, … it’s really convenient, right.
First I will create a file export app / Exports / UsersExport.php
1 2 | php artisan make:export UsersExport --model=ModelsUser |
Then in web.php I have defined a route:
1 2 | Route::get('export', 'AppHttpControllers <a class="__cf_email__" href="/cdn-cgi/l/email-protection">[email protected]</a> ')->name('export'); |
And the last step 1 creates an ExportController:
1 2 | php artisan make:controller ExportController |
In this Controller file, I will write an export function () with the following content:
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 | <?php namespace AppHttpControllers; use CarbonCarbon; use AppModelsUser; use AppExportsUsersExport; use MaatwebsiteExcelFacadesExcel; /** * Class ExportController * @package AppHttpControllers */ class ExportController extends Controller { /** * Export users * * @return void */ public function export() { $now = Carbon::now()->format('Y/m/d'); $data = User::select( 'id', 'name', 'email', 'created_at' )->get(); return Excel::download(new UsersExport($data, $now), 'users.xlsx'); } } |
Here you can freely get data according to the conditions you want. In addition to this way, you also may not need to process data on the Controller side but can handle the date on the UsersExport file in the collection () function. Now I go to UsersExport file to edit a bit.
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 | <?php namespace AppExports; use MaatwebsiteExcelConcernsFromCollection; class UsersExport implements FromCollection { /** * @param AppModelsUser $data */ public function __construct($data, $now) { $this->data = $data; $this->date = $now; } /** * @return IlluminateSupportCollection */ public function collection() { return $this->data; } } |
Then, access http: // localhost: 8000 / export and you can download a users.xlsx file to your computer. However, this file I have not had any format, so it is quite ugly
Part 4 of this I will guide you to some formats to make your files look more sparkling.
4. Some file formats you may not know
4.1 Format data
In addition to how in the UsersExport file implements FromCollection we can implements from FromArray . For example, here I do not want to get the timestamp column users.created_at, but I want to format it in a different format that looks good, I can use the function array () to reformat them mask:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | /** * @return array */ public function array(): array { foreach ($this->data as $item) { $data[] = [ $item->id, $item->name, $item->email, Carbon::parse($item->created_at)->format('Y/m/d'), ]; } return $this->data = $data; } |
You can format to add / remove any here. Each $ item corresponds to the values of 1 row.
4.2 Format heading
A file cannot lack the heading, right. Similar to the above here, UsersExport, I have additional implements WithHeadings . Ting Ting, I will add headings for users.xlsx file in the order of each column I just formatted above.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | /** * @return array */ public function headings(): array { // Moi mot [] đại diện cho một row return [ [ 'Ngay xuat file' . $this->date, ], [ null, ], [ // Thu tu column 'ID', 'User Name', 'Email', 'Created_At', ], ]; } |
4.3 Set StartCell
Implements WithCustomStartCell :
1 2 3 4 5 6 7 8 | /** * @return string */ public function startCell(): string { return 'B2'; } |
4.4 Set Title
Implements WithTitle :
1 2 3 4 5 6 7 8 | /** * @return string */ public function title(): string { return 'Users_' . $this->date; } |
4.5 Format Events
Implements WithEvents Now we will go to the font format, size, with, color, …
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 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | /** * @return array */ public function registerEvents(): array { $styleFontTextAll = [ 'font' => [ 'name' => 'ＭＳ Ｐゴシック', 'size' => 8, ], ]; $styleHeader = [ 'borders' => [ 'allBorders' => [ 'borderStyle' => Border::BORDER_THIN, ], ], 'alignment' => [ 'horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER, 'wrapText' => true, ], 'fill' => [ 'fillType' => Fill::FILL_SOLID, 'color' => ['argb' => 'ffcc99'], ], ]; $styleArray = [ 'borders' => [ 'allBorders' => [ 'borderStyle' => Border::BORDER_THIN, ], ], 'alignment' => [ 'vertical' => Alignment::VERTICAL_CENTER, 'wrapText' => true, ], ]; return [ AfterSheet::class => function (AfterSheet $event) use ( $styleFontTextAll, $styleHeader, $styleArray ) { $endRow = sizeof($this->data) + 4; $event->sheet->styleCells('B4:E4', $styleHeader); $event->sheet->styleCells('B2:E' . $endRow, $styleFontTextAll); $event->sheet->getDelegate()->getStyle('B5:E' . $endRow)->applyFromArray($styleArray); // set width $event->sheet->getDelegate()->getColumnDimension('A')->setWidth(2); $event->sheet->getDelegate()->getColumnDimension('B')->setWidth(5); $event->sheet->getDelegate()->getColumnDimension('C')->setWidth(25); $event->sheet->getDelegate()->getColumnDimension('D')->setWidth(30); $event->sheet->getDelegate()->getColumnDimension('E')->setWidth(25); // set height $event->sheet->getDelegate()->getRowDimension('1')->setRowHeight(10); $event->sheet->getDelegate()->getRowDimension('3')->setRowHeight(10); $event->sheet->getDelegate()->getRowDimension('4')->setRowHeight(25); }, ]; } |
And the result now I have a beautiful page like this nè!
Conclude
The above article I have shared with you how to use the Maatwebsite / Laravel-Excel package to serve the export of files in a laravel project.
To learn more about the package, you can refer to the package homepage! Thanks mn for reading my article
https://docs.laravel-excel.com/3.1/getting-started/ https://github.com/Maatwebsite/Laravel-Excel