Hello anh em!
Nay mình sẽ giới thiều với mọi người thư viện maatwebsite để sử lý việc export file excel trong project laravel nào cũng bắt tay vào tạo project test thử nhé.
Cài đặt project laravel
Vào forder muốn đặt dự án và chạy lệnh dưới đây để tạo 1 prj laravel tên project là excel nhé. Nếu lỗi ‘Permission’ thì chạy bằng quyền sudo là được.$ composer create-project --prefer-dist laravel/laravel excel
Sau đó tạo DB và chỉnh sửa file .env để kết nối DB nhé
1 2 3 4 5 6 7 | DB_CONNECTION=mysql DB_HOST=localhost DB_PORT=3306 DB_DATABASE=excel DB_USERNAME=root DB_PASSWORD= |
Chạy lệnh để generate key $ php artisan key:generate
Chạy migrate để tạo bảng user$ php artisan migrate
Vậy là đã tạo xong project laravel, tiếp theo chúng ta tạo dữ liệu mẫu cho bảng user để tý nữa mình test export nhé.
Bước 1: Tạo 1 seeder mới cho bảng users có tên là UsersTableSeeder.php$ php artisan make:seeder UsersTableSeeder
Bước 2: Giờ đây chúng ta đã có 1 file UsersTableSeeder.php trong thư mục database/seeds/ . trong function chúng ta sẽ thêm câu lệnh để chạy factoryfactory(User::class, 100)->create();
và nhớ use model User vào nhé.
UsersTableSeeder.php hoàn chỉnh sẽ như sau:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <span class="token php language-php"><span class="token delimiter important"><?php</span> <span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation"></span>Database<span class="token punctuation"></span>Seeder</span><span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">App<span class="token punctuation"></span>User</span><span class="token punctuation">;</span> <span class="token keyword">class</span> <span class="token class-name">UsersTableSeeder</span> <span class="token keyword">extends</span> <span class="token class-name">Seeder</span> <span class="token punctuation">{</span> <span class="token comment">/** * Run the database seeds. * * @return void */</span> <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">run</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token function">factory</span><span class="token punctuation">(</span>User<span class="token punctuation">:</span><span class="token punctuation">:</span><span class="token keyword">class</span><span class="token punctuation">,</span> <span class="token number">100</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">create</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> |
Tiếp theo đăng ký seed UsersTableSeeder ở file DatabaseSeeder.php, file DatabaseSeeder.php, hoàn chỉnh sẽ như sau:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <span class="token php language-php"><span class="token delimiter important"><?php</span> <span class="token keyword">use</span> <span class="token package">Illuminate<span class="token punctuation"></span>Database<span class="token punctuation"></span>Seeder</span><span class="token punctuation">;</span> <span class="token keyword">class</span> <span class="token class-name">DatabaseSeeder</span> <span class="token keyword">extends</span> <span class="token class-name">Seeder</span> <span class="token punctuation">{</span> <span class="token comment">/** * Seed the application's database. * * @return void */</span> <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">run</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token variable">$this</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">call</span><span class="token punctuation">(</span>Users<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 punctuation">}</span> <span class="token punctuation">}</span> </span> |
Xong rồi, giờ chúng ta chạy lệnh:
php artisan db:seed
vào db kiểm tra sẽ có 100 bản ghi mẫu trong bảng user rồi, giờ chúng ta bắt đầu test export nhé.
Cài đặt thư viên maatwebsite
chạy lệnh sau để cài đặt thư viện
composer require maatwebsite/excel
Chạy lệnh sau để pulish config
php artisan vendor:publish --provider="MaatwebsiteExcelExcelServiceProvider"
Export
- Tạo route để khi gọi đến route này sẽ export ra file
Route::get('export', '[email protected]')->name('export');
- Tạo Controller
php artisan make:controller ExportController
- chạy câu lệnh bên dưới, mình tạo một file tên là UsersExport để sử lý việc export
php artisan make:export UsersExport --model=User
- Sau khi chạy câu lệnh bên trên, sẽ tạo cho ta file UsersExport.php trong AppExportsUserExport.php , Sửa file thành như sau
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 | <span class="token keyword">namespace</span> <span class="token package">App<span class="token punctuation"></span>Exports</span><span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">App<span class="token punctuation"></span>User</span><span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">Maatwebsite<span class="token punctuation"></span>Excel<span class="token punctuation"></span>Concerns<span class="token punctuation"></span>FromCollection</span><span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">Maatwebsite<span class="token punctuation"></span>Excel<span class="token punctuation"></span>Concerns<span class="token punctuation"></span>WithHeadings</span><span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">Maatwebsite<span class="token punctuation"></span>Excel<span class="token punctuation"></span>Concerns<span class="token punctuation"></span>WithMapping</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>WithHeadings<span class="token punctuation">,</span>WithMapping <span class="token punctuation">{</span> <span class="token comment">/** * @return IlluminateSupportCollection */</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 comment">/** * Returns headers for report * @return array */</span> <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">headings</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">:</span> <span class="token keyword">array</span> <span class="token punctuation">{</span> <span class="token keyword">return</span> <span class="token punctuation">[</span> <span class="token single-quoted-string string">'ID'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Name'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Email'</span><span class="token punctuation">,</span> <span class="token double-quoted-string string">"Created"</span><span class="token punctuation">,</span> <span class="token double-quoted-string string">"Updated"</span> <span class="token punctuation">]</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">map</span><span class="token punctuation">(</span><span class="token variable">$user</span><span class="token punctuation">)</span><span class="token punctuation">:</span> <span class="token keyword">array</span> <span class="token punctuation">{</span> <span class="token keyword">return</span> <span class="token punctuation">[</span> <span class="token variable">$user</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">id</span><span class="token punctuation">,</span> <span class="token variable">$user</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">name</span><span class="token punctuation">,</span> <span class="token variable">$user</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">email</span><span class="token punctuation">,</span> <span class="token variable">$user</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">created_at</span><span class="token punctuation">,</span> <span class="token variable">$user</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">updated_at</span> <span class="token punctuation">]</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> |
Giải thich file trên nhé
- Mình use AppUser để sử dụng Eloquent trong Laravel,
- Dùng class của MaatwebsiteExcel để sử dụng chức năng export
- Hàm headings(): tạo các cột tiêu đề
- Hàm Map($user): map những giá trị cần xuất ra với heading
- Tiếp theo ta sẽ cài đặt ở file Controller để có thể sử dụng file UsersExport.php, use AppUsersExport đển UserController.php
- Tiếp theo sửa controller
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <span class="token php language-php"><span class="token delimiter important"><?php</span> <span class="token keyword">namespace</span> <span class="token package">App<span class="token punctuation"></span>Http<span class="token punctuation"></span>Controllers</span><span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">App<span class="token punctuation"></span>Exports<span class="token punctuation"></span>UsersExport</span><span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">Maatwebsite<span class="token punctuation"></span>Excel<span class="token punctuation"></span>Facades<span class="token punctuation"></span>Excel</span><span class="token punctuation">;</span> <span class="token keyword">class</span> <span class="token class-name">ExportController</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> |
Vậy là xong rồi, truy câp vào đường dẫn đã tạo ở trên chúng ta sẽ xuất được ra 1 file users.xlsx rồi nhé, bạn có thể thay đổi định dạng file xuất thành users.csv hay tsv.
Cảm ơn mn đã quan tâm )!