I. Introduction
Nowadays, when making a web application, many of you have thought of importing and exporting data into a database through files such as excel, csv (importing product lists, articles, users …) because it helps us easily add and retrieve data from the site. In today’s article I will introduce to you the Maatwebsite/Laravel-Excel package, which can meet all the needs mentioned above with a lot of features included.
The version I use will be version 3.1 and below, I will guide you based on small examples, to help you easily imagine in learning about the package. Let’s get started 
II.Installation
1.Requirements
- PHP: ^ 7.0
- Laravel: ^ 5.5
- PhpSpreadsheet: ^ 1.6
- PHP extension: php_zip, php_xml, php_gd2
| Version | Laravel Version | Php Version | support |
|---|---|---|---|
| 2.1 | <= 5.6 | <= 7.0 | Unsupported since May 15, 2018 |
| 3.0 | ^ 5.5 | ^ 7.0 | Unsupported since 12/31-2018 |
| 3.1 | ^ 5.5 | ^ 6.0 | ^ 7.1 | New features |
2.Install the package
First, type this command into the terminal
1 2 | composer require maatwebsite/excel |
Add provider and alias to config/app.php file
1 2 3 4 5 6 7 | 'providers' => [ /* * Package Service Providers... */ MaatwebsiteExcelExcelServiceProvider::class, ] |
1 2 3 4 5 | 'aliases' => [ ... 'Excel' => MaatwebsiteExcelFacadesExcel::class, ] |
The next thing is to publish the configuration file of the package with the command
1 2 | php artisan vendor:publish --provider="MaatwebsiteExcelExcelServiceProvider" |
The above command will create the config file excel.php into the config directory and it is also the final step to install the Maatwebsite/Laravel-Excel package, now we move on to the next section to learn the cool features. This package is for us only
III.Learn about export data export
For import / export data into an Excel file, the Maatwebsite/Laravel-Excel package provides us with many features such as exporting files with multiple formats, exporting format, mapping data, exporting files via view blade, etc. But in this article I just listed some basic functions that I use and I think enough to meet your needs <33
1. Export a file
The first is we will create the appExports folder
Next, type the following command in terminal:
1 2 | php artisan make:export UsersExport --model=User |
After running the above command will create a file in the appExport folder named UserExport.php , you fix that file 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 | <?php namespace AppExports; use AppModelsUser; use MaatwebsiteExcelConcernsFromCollection; use MaatwebsiteExcelConcernsWithHeadings; class UserExport implements FromCollection, WithHeadings { public function collection() { return User::all(); } //Thêm hàng tiêu đề cho bảng public function headings() :array { return ["STT", "Tên tài khoản", "Email", "Loại"]; } } |
In the appHttpControllersUserController.php file appHttpControllersUserController.php we add the export() function
1 2 3 4 5 6 7 8 9 10 11 12 | ... use AppExportsUserExport; use MaatwebsiteExcelFacadesExcel; class UserController extends Controller { public function export() { return Excel::download(new UsersExport, 'users.xlsx'); } } |
You can change the file extension in users.xlsx to get the desired file format such as .xls , .csv , .ods , .html , …
In routesweb.php :
1 2 | Route::get('users/export/', ' <a class="__cf_email__" href="/cdn-cgi/l/email-protection" data-cfemail="a2f7d1c7d0d1e1cdccd6d0cdcecec7d0e2c7dad2cdd0d6">[email protected]</a> '); |
Thus, after entering the user/export , we will receive a file named users.xlsx created above. In the file received above, there is information about all fields of all user currently in the database.
| No. | account name | Species | |
|---|---|---|---|
| first | 17020907 | [email protected] | 0 |
| 2 | 17020908 | [email protected] | 0 |
| 3 | 17020909 | [email protected] | 0 |
| … | … | … | … |
2. Get the data according to conditions
In the above part, I have instructed to export to a file containing all users, now we will export a file containing only information of one user or several users.
In the UserExport.php file, we add the __construct() function:
1 2 3 4 5 6 7 8 9 10 11 12 | class ClassDetailExport implements FromCollection { public function __construct(int $id) { $this->id = $id; } public function collection() { return ClassSubject::findOrFail($this->id); } } |
In UserController.php we fix the following:
1 2 3 4 5 6 7 8 9 | class UserController extends Controller { public function exportDetailExcel($id) { $user = User::findOrFail($id); $file = Excel::download(new UserExport($id), $user->name.'.xlsx'); return $file; } } |
In routesweb.php :
1 2 | Route::get('export/user/{user}', ' <a class="__cf_email__" href="/cdn-cgi/l/email-protection" data-cfemail="2e7b5d4b5c6d41405a5c4142424b5c6e4b565e415c5a">[email protected]</a> '); |
So you can export the file containing the user according to the given id, with other conditions you do the same steps above.
3. Export file based on laravel view
As I said, this package provides us with a lot of useful features, including the export feature based on the .blade.php view. Personally, I think this feature makes it easier for us to create our own custom tables than above.
In the UsersExport.php file, you implements FromView :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | namespace AppExports; use AppUser; use IlluminateContractsViewView; use MaatwebsiteExcelConcernsFromView; class UsersExport implements FromView { public function view(): View { return view('exports.users', [ 'users' => User::all() ]); } } |
Create users.blade.php file containing the information of the user , the package will automatically convert the html table into excel table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | <table> <thead> <tr> <th>STT</th> <th>Tên tài khoản</th> <th>Email</th> <th>Loại</th> </tr> </thead> <tbody> @foreach($users as $user) <tr> <th>{{ $loop->index+1 }}</th> <td>{{ $user->username }}</td> <td>{{ $user->email }}</td> <td>{{ ($user->type == 0) ? 'Sinh viên' : 'Admin' }}</td> </tr> @endforeach </tbody> </table> |
In the file UserController.php you modify the export() function export()
1 2 3 4 5 | public function export() { return Excel::download(new UsersExport, 'users.xlsx'); } |
4. Create sheet
You probably all know Excel’s sheet , which are tabs that help us manage and manipulate data more easily. Below I will guide you to divide the sheet through the example of taking accounts for each month of participation and then dividing into sheet .
In the UsersExport.php file
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 | namespace AppExports; use MaatwebsiteExcelConcernsExportable; use MaatwebsiteExcelConcernsWithMultipleSheets; class UsersExport implements WithMultipleSheets { use Exportable; protected $year; public function __construct(int $year) { $this->year = $year; } public function sheets(): array { $sheets = []; for ($month = 1; $month <= 12; $month++) { $sheets[] = new UsersPerMonthSheet($this->year, $month); } return $sheets; } } |
Create UsersPerMonthSheet.php file, you can implements FromQuery , FromCollection , FromView …
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 | namespace AppExports; use AppModelsUser; use MaatwebsiteExcelConcernsFromQuery; use MaatwebsiteExcelConcernsWithTitle; class UsersPerMonthSheet implements FromQuery, WithTitle { private $month; private $year; public function __construct(int $year, int $month) { $this->month = $month; $this->year = $year; } public function query() { return User ::query() ->whereYear('created_at', $this->year) ->whereMonth('created_at', $this->month); } public function title(): string { return 'Month ' . $this->month; } } |
Add the export () function in the UserController.php controller:
1 2 3 4 5 | public function export() { return (new UsersExport(2019))->download('users.xlsx'); } |
IV. Learn about the data import feature import
1. Import a file
The first is we will create the appImports folder
Create the import file by typing the following command into the terminal:
1 2 | php artisan make:import UsersImport --model=User |
In the UsersImport.php file:
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 | namespace AppImports; use AppModelsUser; use IlluminateSupportFacadesHash; use MaatwebsiteExcelConcernsToModel; use MaatwebsiteExcelConcernsWithHeadingRow; class UserImport implements ToModel, WithHeadingRow { public function headingRow() : int { return 1; } public function model(array $row) { return new User([ 'username' => $row['username'] ?? $row['ten_tai_khoan'], 'email' => $row['email'], 'password' => Hash::make($row['password'] ?? $row['mat_khau'], 'type' => $row['type'] ?? $row['loai'] ]); } } |
Above, the headingRow() function aims to remove the table row headers, the function will return the number of rows you ignore, as in the above example, I will ignore the row containing the column’s information such as the name. account, email, password … Package allows us to use the keywords in the header to lock the array of each row. For example, I entered data for the username attribute based on the username column or account name.
Note:
- If in your Excel file the column name is “Account name” then the import file will be
$row['ten_tai_khoan'] - If you enter data in the format of dates from the Excel file and encounter errors, try to solve this way, I have been and fix errors quite effectively.
1 2 | 'birthday' => PhpOfficePhpSpreadsheetSharedDate::excelToDateTimeObject($row['birthday'] ?? $row['ngay_sinh'])->format('Y-m-d') |
In UserController.php :
1 2 3 4 5 6 7 8 9 10 11 12 13 | ... use AppImportsUserImport; class UserController extends Controller { ... public function import() { $import = Excel::import(new UserImport, request()->file('user_file')); return redirect()->back()->with('success', 'Success!!!'); } } |
In user.blade.php file:
1 2 3 4 5 6 7 8 9 10 11 | <form action="{{URL::to('import/user')}}" method="POST" enctype="multipart/form-data"> {{ csrf_field() }} <label for="user-file"> <div class="btn sbold green"> Add <i class="fa fa-plus"></i> </div> </label> <input id="user-file" type="file" name="user_file" class="hidden" accept=".xlsx, .xls, .csv, .ods"> <button type="submit">Import</button> </form> |
In routesweb.php :
1 2 | Route::post('import/user', ' <a class="__cf_email__" href="/cdn-cgi/l/email-protection" data-cfemail="96c3e5f3e4d5f9f8e2e4f9fafaf3e4d6fffbe6f9e4e2">[email protected]</a> '); |
2. Match inserts
When you import a large file, it means that a large number of rows will be imported from there, resulting in bottlenecks or bottlenecks, which will affect data entry. By implements WithBatchInserts we will solve that problem by deciding the number of lines that will be entered into the database each time.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | use MaatwebsiteExcelConcernsWithBatchInserts; class UsersImport implements ToModel, WithBatchInserts { public function model(array $row) { return new User([ 'name' => $row[0], ]); } public function batchSize(): int { return 1000; } } |
3.Chunk reading
Unlike the above, chunk reading will determine the number of lines that will be read each time instead of reading all and then temporarily stored in memory, thereby reducing memory usage, helping to better control memory.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | use MaatwebsiteExcelConcernsWithChunkReading; class UsersImport implements ToModel, WithChunkReading { public function model(array $row) { return new User([ 'name' => $row[0], ]); } public function chunkSize(): int { return 1000; } } |
Above are a few basic and useful features that the Maatwebsite/Laravel-Excel package has brought, helping a lot in importing and exporting data with Excel. If you want to learn more about other features, please read the docs of Maatwebsite link below.
Hopefully this article will be of some help to you in working with files such as excel, ods, csv … Thank you for patiently reading until the end of the article.