I. Vấn đề.
Làm việc với file không phải điều gì quá xa lạ với các dự án của laravel. Laravel-excel hay còn gọi package maatwebsite cũng quá gần gũi với những ai cần thao tác với file. Với các chức năng hữu ích như :
- 12Dễ dàng xuất dữ liệu ra file Excel.
- 12Sử dụng các truy vấn với tính năng phân khúc tự động để có hiệu suất tốt hơn.
- 12Sử dụng hàng đợi để có hiệu suất tốt hơn.
- 12Dễ dàng xuất dạng view Blade sang Excel.
- 12Dễ dàng nhập dữ liệu vào file.
- 12Đọc tệp Excel theo từng phần.
- 12Xử lý chèn, nhập.
Với bản 3.1 mới nhất đối với mình có thể xem là khó sử dụng do doc viết khá ngắn, các chức năng mình cần hầu như không được nhắc đến. Không biết vô tình hay cố ý nhưng những bản cũ hơn mình lại thấy viết khá chi tiết và có những gì mình cần như auto dãn cách các hàng các cột, chính vì thế vừa mới lần đầu làm export với laravel-excel mình khá là rối. Nhiều lúc định cài xuống bản 2.1 cho nó dễ dùng thì lại nhận được lời khuyên của mấy anh “Do mày chưa hiểu thôi ” thế là mình lại dành ra vài giờ để tiếp tục với những gì mình cần trong bản 3.1.
II. Cài đặt.
Chắc cũng không cần viết ra làm gì khi doc đã viết rất rõ cách để cài laravel rồi nhưng dành cho bạn nào lười không muốn chuyển link thì mình copy cách cài trên doc của laravel-excel qua đây cho các bạn pase cho nhanh khỏi click qua click lại mất công.
1 2 | composer <span class="token keyword">require</span> maatwebsite<span class="token operator">/</span>excel |
Lệnh sau dành cho trường hợp sau này có bản mới mà các bạn vẫn muốn dùng bản 3.1
1 2 | composer <span class="token keyword">require</span> maatwebsite<span class="token operator">/</span>excel<span class="token punctuation">:</span><span class="token number">3.1</span> |
Trong config/app.php thêm vào providers
1 2 3 4 5 6 7 | <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<span class="token punctuation"></span>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> |
Trong config/app.php thêm vào aliases
1 2 3 4 5 | <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<span class="token punctuation"></span>Facades<span class="token punctuation"></span>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> |
Để publish config, chạy vendor publish command. Trong config một file mới excel.php được tạo để bạn có thể cấu hình tùy ý.
1 2 | php artisan vendor<span class="token punctuation">:</span>publish |
Vậy là đã cài xong rùi đó khá đơn giản và nhẹ nhàng.
III. Tìm hiểu về tính năng xuất dữ liệu export.
package Maatwebsite/Laravel-Excel cung cấp cho chúng ta rất nhiều tính năng như xuất file với nhiều định dạng, export format, mapping data, xuất file qua view blade,… nhưng trong bài viết này mình chỉ sẽ tập trung vào phần export và những gì mình tìm hiểu được. Các phần khác các bạn có thể đọc thêm ở doc
1. Xuất một file.
Đầu tiên bạn cần tạo một file app/Exports
Tiếp theo bạn gõ câu lệnh sau vào terminal để tạo một file nhằm mục đích xử lý dữ liệu cần export ra file và cấu hình cho file excel khi được export ra.
1 2 | php artisan make<span class="token punctuation">:</span>export ExportFile <span class="token operator">--</span>model<span class="token operator">=</span>User |
User là tên model bạn cần truy vấn dữ liệu, có thể có, có thể không vì khi bạn thêm đoạn đấy file ExportFile.php được tạo ra nó sẽ use thêm cái model đấy vào thôi. Không có thì nó sẽ không use vào.
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<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">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> |
Bạn có thể thấy dòng user AppUser dòng này được tự động thêm vào vì khi tạo file mình có thêm phần –model=User.
Giờ bạn tạo nhanh controller:
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<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">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> |
Và router:
1 2 | 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 href="/cdn-cgi/l/email-protection" class="__cf_email__">[email protected]</a>'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> |
Điều còn thiếu là bảng user nữa là có thể export được file rồi.
1 2 | php artisan make<span class="token punctuation">:</span>migration create_table_users_table <span class="token operator">--</span>create<span class="token operator">=</span>users |
Mở file migrate mới được tạo và thêm vài trường dữ liệu nào, đừng quên tạo model User.
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 use IlluminateSupportFacadesSchema; use IlluminateDatabaseSchemaBlueprint; use IlluminateDatabaseMigrationsMigration; class CreateUsersTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('users', function (Blueprint $table) { $table->increments('id'); $table->string('name'); $table->string('airline'); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::dropIfExists('users'); } } |
Bạn nhớ thêm dữ liệu vào bảng users và truy cập đường dẫn router là sẽ thấy file excel đã được tải về rồi.
2. Tùy chỉnh.
Như vậy ở trên mình đã demo nhanh cách export file excel với laravel-excel rồi. Khá dễ phải không ạ, thật ra trên doc cũng có viết rồi không quá chi tiết nhưng bạn nào biết về laravel đều có thể làm theo được.
2.1 Truyền data từ controller qua file export.
Chắc các bạn hiểu ý mình. Khi làm file export mình gặp phải vấn đề khá chuối đó là bên phía controller cần xử lý dữ liệu trước sau đó mới export ra hay khi mình muốn export dữ liệu của user nhưng phải theo id chẳng hạn. Vấn đề là bên file export như trong ví dụ là ExportFile.php cần id trên router để lấy dữ liệu của user đó export ra file excel.
Mình đã truyền dữ liệu từ controller qua ExportFile.php như sau.
Bên controller trước khi tải file về mình sẽ truyền dữ liệu đã xử lý qua bên ExportFile.php sau đó mới tải dữ liệu xuống ví dụ:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <span class="token keyword">use</span> <span class="token package">App<span class="token punctuation"></span>Exports<span class="token punctuation"></span>ExportFile</span><span class="token punctuation">;</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">export</span><span class="token punctuation">(</span><span class="token variable">$id</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 variable">$point</span> <span class="token operator">=</span> <span class="token punctuation">[</span> <span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">3</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">,</span> <span class="token number">9</span><span class="token punctuation">]</span> <span class="token punctuation">]</span> <span class="token variable">$data</span> <span class="token operator">=</span> <span class="token punctuation">(</span>object<span class="token punctuation">)</span> <span class="token keyword">array</span><span class="token punctuation">(</span> <span class="token single-quoted-string string">'points'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token variable">$point</span><span class="token punctuation">,</span> <span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$export</span> <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">ExportFile</span><span class="token punctuation">(</span><span class="token punctuation">[</span><span class="token variable">$data</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 variable">$export</span><span class="token punctuation">,</span> abc<span class="token punctuation">.</span>xlsx<span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> |
Như bên trên bạn có thể tùy ý gửi dữ liệu qua bên file export excel để giải quyết vấn đề như mình đưa ra bên trên, ở đây mình có thể tùy ý xử lý dữ liệu từ trước rồi sau đó mới truyền đi để export dữ liệu ra file excel. Ngoài ra bạn có thể truyền thêm nhiều giá trị khác như headings chẳng hạn hay bất cứ đữ liệu gì khác bạn cần. Ok giờ bên file export cụ thể của mình là ExportFile.php (file này là tạo từ ví dụ bên trên nằm trong app/Exports nhé kẻo các bạn không nhớ).
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 | <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>Exports</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>FromArray</span><span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">Config</span><span class="token punctuation">;</span> <span class="token keyword">class</span> <span class="token class-name">ExportScorerFile</span> <span class="token keyword">implements</span> <span class="token class-name">FromArray</span> <span class="token punctuation">{</span> <span class="token keyword">use</span> <span class="token package">RegistersEventListeners</span><span class="token punctuation">;</span> <span class="token comment">/** * @return IlluminateSupportCollection */</span> <span class="token keyword">public</span> <span class="token variable">$points</span><span class="token punctuation">;</span> <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">__construct</span><span class="token punctuation">(</span><span class="token keyword">array</span> <span class="token variable">$points</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 property">points</span> <span class="token operator">=</span> <span class="token variable">$points</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 keyword">array</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 variable">$this</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">points</span><span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">points</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> </span> |
Ở đây bạn có thể thấy mình dùng FromArray và tạo construct để hứng dữ liệu bên controller đẩy qua. Giờ thì bạn có thể truyền data đã xử lý từ controller đi để export ra file excel rồi.
2.2 Tự động căn chỉnh chiều cao và chiều rộng.
Với dữ liệu dài loằng ngoằng thì hẳn bạn sẽ thấy có vấn đề khi bị khuất text hay không hiển thị hết đoạn text. Lúc này mình cần tự động căn chỉnh cho tất cả các hàng cần hiển thị hết dữ liệu mà không bị che khuất.
Ở đây mình dùng ShouldAutoSize đơn giản 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 | <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>Exports</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>FromArray</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>ShouldAutoSize</span><span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">Config</span><span class="token punctuation">;</span> <span class="token keyword">class</span> <span class="token class-name">ExportScorerFile</span> <span class="token keyword">implements</span> <span class="token class-name">FromArray</span><span class="token punctuation">,</span> ShouldAutoSize <span class="token punctuation">{</span> <span class="token keyword">use</span> <span class="token package">RegistersEventListeners</span><span class="token punctuation">;</span> <span class="token comment">/** * @return IlluminateSupportCollection */</span> <span class="token keyword">public</span> <span class="token variable">$points</span><span class="token punctuation">;</span> <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">__construct</span><span class="token punctuation">(</span><span class="token keyword">array</span> <span class="token variable">$points</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 property">points</span> <span class="token operator">=</span> <span class="token variable">$points</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 keyword">array</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 variable">$this</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">points</span><span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">points</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> </span> |
2.3 Tạo headings thành cột.
Cái này trên doc cũng có nói nhưng là tạo theo hàng ngang nếu các bạn để ý có thể dễ dàng tạo theo chiều dọc bằng cách return nhiều array như sau.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <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 punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">class</span> <span class="token class-name">ExportScorerFile</span> <span class="token keyword">implements</span> <span class="token class-name">FromArray</span><span class="token punctuation">,</span> WithHeadings<span class="token punctuation">,</span> ShouldAutoSize <span class="token punctuation">{</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 variable">$headings</span> <span class="token operator">=</span> <span class="token punctuation">[</span> <span class="token punctuation">[</span><span class="token single-quoted-string string">'a'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token single-quoted-string string">'b'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token single-quoted-string string">'c'</span><span class="token punctuation">]</span> <span class="token punctuation">]</span><span class="token punctuation">;</span> <span class="token keyword">return</span> <span class="token variable">$headings</span><span class="token punctuation">;</span> <span class="token punctuation">}</span> <span class="token punctuation">}</span> |
Nhớ user WithHeadings cái này trên doc cũng khá rõ nên mình ví dụ qua thôi.
2.4 Merge hàng và căn chỉnh số sang bên trái.
Dĩ nhiên việc gộp các hàng cũng thường hay xảy ra trong excel và việc số hay nằm về bên phải ô excel thì ở đây mình cũng có cách giải quyết cho hai vấn đề này.
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 69 70 71 72 73 74 75 | <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>Exports</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>FromArray</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>WithStyles</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>RegistersEventListeners</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>Events<span class="token punctuation"></span>AfterSheet</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>WithEvents</span><span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">PhpOffice<span class="token punctuation"></span>PhpSpreadsheet<span class="token punctuation"></span>Worksheet<span class="token punctuation"></span>Worksheet</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>ShouldAutoSize</span><span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">Config</span><span class="token punctuation">;</span> <span class="token keyword">class</span> <span class="token class-name">ExportScorerFile</span> <span class="token keyword">implements</span> <span class="token class-name">FromArray</span><span class="token punctuation">,</span> WithHeadings<span class="token punctuation">,</span> WithStyles<span class="token punctuation">,</span> WithEvents<span class="token punctuation">,</span> ShouldAutoSize <span class="token punctuation">{</span> <span class="token keyword">use</span> <span class="token package">RegistersEventListeners</span><span class="token punctuation">;</span> <span class="token comment">/** * @return IlluminateSupportCollection */</span> <span class="token keyword">public</span> <span class="token variable">$points</span><span class="token punctuation">;</span> <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">__construct</span><span class="token punctuation">(</span><span class="token keyword">array</span> <span class="token variable">$points</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 property">points</span> <span class="token operator">=</span> <span class="token variable">$points</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 keyword">array</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 variable">$this</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">points</span><span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">points</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">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 variable">$headings</span> <span class="token operator">=</span> <span class="token punctuation">[</span> <span class="token punctuation">[</span><span class="token single-quoted-string string">'a'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token single-quoted-string string">'b'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token single-quoted-string string">'c'</span><span class="token punctuation">]</span> <span class="token punctuation">]</span><span class="token punctuation">;</span> <span class="token keyword">return</span> <span class="token variable">$headings</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">styles</span><span class="token punctuation">(</span>Worksheet <span class="token variable">$sheet</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A1:B1'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A2:B2'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A3:B3'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A4:B4'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A5:B5'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A6:B6'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C1:D1'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C2:D2'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C3:D3'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C4:D4'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C5:D5'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C6:D6'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A7:D7'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">setCellValue</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C1'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Xin chao'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">setCellValue</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C2'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Xin chao'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">setCellValue</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C3'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Xin chao'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">setCellValue</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C4'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Xin chao'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">setCellValue</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C5'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Xin chao'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">setCellValue</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C6'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Xin chao'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token function">range</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">7</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token variable">$number</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">getStyle</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C'</span> <span class="token punctuation">.</span> <span class="token variable">$number</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">getAlignment</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">applyFromArray</span><span class="token punctuation">(</span> <span class="token keyword">array</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'horizontal'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token single-quoted-string string">'left'</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> |
Ở đây bạn có thể dễ dàng nhận thấy mình còn có thể set giá trị cho từng ô của excel.
2.5 Chỉnh size và color.
Ở phần 2.4 bạn có thể thấy mình có user thằng RegistersEventListeners mà không dùng, do mình xóa thiếu đó giờ mình mới dùng đến nó này.
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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | <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>Exports</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>FromArray</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>WithStyles</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>RegistersEventListeners</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>Events<span class="token punctuation"></span>AfterSheet</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>WithEvents</span><span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">PhpOffice<span class="token punctuation"></span>PhpSpreadsheet<span class="token punctuation"></span>Worksheet<span class="token punctuation"></span>Worksheet</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>ShouldAutoSize</span><span class="token punctuation">;</span> <span class="token keyword">use</span> <span class="token package">Config</span><span class="token punctuation">;</span> <span class="token keyword">class</span> <span class="token class-name">ExportScorerFile</span> <span class="token keyword">implements</span> <span class="token class-name">FromArray</span><span class="token punctuation">,</span> WithHeadings<span class="token punctuation">,</span> WithStyles<span class="token punctuation">,</span> WithEvents<span class="token punctuation">,</span> ShouldAutoSize <span class="token punctuation">{</span> <span class="token keyword">use</span> <span class="token package">RegistersEventListeners</span><span class="token punctuation">;</span> <span class="token comment">/** * @return IlluminateSupportCollection */</span> <span class="token keyword">public</span> <span class="token variable">$points</span><span class="token punctuation">;</span> <span class="token keyword">public</span> <span class="token keyword">function</span> <span class="token function">__construct</span><span class="token punctuation">(</span><span class="token keyword">array</span> <span class="token variable">$points</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 property">points</span> <span class="token operator">=</span> <span class="token variable">$points</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 keyword">array</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 variable">$this</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">points</span><span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">points</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">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 variable">$headings</span> <span class="token operator">=</span> <span class="token punctuation">[</span> <span class="token punctuation">[</span><span class="token single-quoted-string string">'a'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token single-quoted-string string">'b'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token single-quoted-string string">'c'</span><span class="token punctuation">]</span> <span class="token punctuation">]</span><span class="token punctuation">;</span> <span class="token keyword">return</span> <span class="token variable">$headings</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">styles</span><span class="token punctuation">(</span>Worksheet <span class="token variable">$sheet</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A1:B1'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A2:B2'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A3:B3'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A4:B4'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A5:B5'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A6:B6'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C1:D1'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C2:D2'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C3:D3'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C4:D4'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C5:D5'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C6:D6'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">mergeCells</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'A7:D7'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">setCellValue</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C1'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Xin chao'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">setCellValue</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C2'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Xin chao'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">setCellValue</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C3'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Xin chao'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">setCellValue</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C4'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Xin chao'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">setCellValue</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C5'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Xin chao'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">setCellValue</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C6'</span><span class="token punctuation">,</span> <span class="token single-quoted-string string">'Xin chao'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token keyword">foreach</span> <span class="token punctuation">(</span><span class="token function">range</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">7</span><span class="token punctuation">)</span> <span class="token keyword">as</span> <span class="token variable">$number</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token variable">$sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">getStyle</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'C'</span> <span class="token punctuation">.</span> <span class="token variable">$number</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">getAlignment</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">applyFromArray</span><span class="token punctuation">(</span> <span class="token keyword">array</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'horizontal'</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token single-quoted-string string">'left'</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 keyword">public</span> <span class="token keyword">function</span> <span class="token function">registerEvents</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> AfterSheet<span class="token punctuation">:</span><span class="token punctuation">:</span><span class="token keyword">class</span> <span class="token operator">=</span><span class="token operator">></span> <span class="token keyword">function</span> <span class="token punctuation">(</span>AfterSheet <span class="token variable">$event</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token variable">$cellRange</span> <span class="token operator">=</span> <span class="token single-quoted-string string">'A8:W8'</span><span class="token punctuation">;</span> <span class="token variable">$event</span><span class="token operator">-</span><span class="token operator">></span><span class="token property">sheet</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">getDelegate</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">-</span><span class="token operator">></span><span class="token function">getStyle</span><span class="token punctuation">(</span><span class="token variable">$cellRange</span><span class="token punctuation">)</span> <span class="token operator">-</span><span class="token operator">></span><span class="token function">getFont</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">-</span><span class="token operator">></span><span class="token function">setSize</span><span class="token punctuation">(</span><span class="token number">13</span><span class="token punctuation">)</span> <span class="token operator">-</span><span class="token operator">></span><span class="token function">getColor</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token operator">-</span><span class="token operator">></span><span class="token function">setRGB</span><span class="token punctuation">(</span><span class="token single-quoted-string string">'0000ff'</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> |
Với hàm registerEvents mình có thể tùy ý chỉnh sửa font, size, colo của file excel.
IV. Kết luận.
Như vậy mình vừa chia sẻ một chút những hiểu biết của mình sau khi tìm hiểu qua laravel-excel. Vì chưa tìm hiểu được lâu nên còn nhiều thiếu. Thank!
Tham khảo : https://docs.laravel-excel.com/3.1/exports/