In this article we will discuss about exporting database data to excel file and importing database data from excel file into database using laravel excel by maatwebsite https://github.com/maatwebsite/Laravel-Excel. Some basic requirements for installation includes PHP > 7.0
and Laravel 5.5
.
We will first implement import function and later export function. Export function was introduced in laravel excel 3.0 and import function was added in laravel 3.1.
Features of Laravel Excel includes:
- Easily export collections to Excel.
- Export queries with automatic chunking for better performance.
- Queue exports for better performance.
- Easily export Blade views to Excel.
- Easily import to collections.
- Read the Excel file in chunks.
- Handle the import inserts in batches.
Let’s get started by installing package first and implementing import and export.
Installation
We will use composer
to install this package which will download this package and PhpSpreadsheet.
composer require maatwebsite/excel
To publish the config, run the vendor publish command:
php artisan vendor:publish
Database Seeding
We will use users table to export to excel. First of all, we need to seed users table. For this, we will use tinker. Laravel comes with pre-built Factory class for User model, so we will run the following command in tinker. More information can be found about tinker at this article.
factory(App\User::class, 10)->create();
Export
We will implement export function by creating UsersExport
class. We will create the export class inside App/Exports
by running artisan command:
php artisan make:export UsersExport --model=User
The UsersExport looks like below:
Please note that we will be exporting into excel file from
collection
. You can implement the same usingEloquent Query
andView Html table
.Now, we can call the class from controller as like below:
use App\Exports\UsersExport; use Maatwebsite\Excel\Facades\Excel; use App\Http\Controllers\Controller; class UsersController extends Controller { public function export() { return Excel::download(new UsersExport, 'users.xlsx'); } }This will download the
users.xlsx
file from the browser.Import
Similar to export class, we will create export class inside
App/Imports
using artisan command as below:php artisan make:import UsersImport --model=UserWe will edit
model
function of the recently createdUsersImport
class. Our final class looks like below:$row[0], 'email' => $row[1], 'password' => Hash::make($row[2]), ]); } }On each row that gets imported, we create a new user. We simply return a new User instance, the saving is handled by the package.
In your UsersController you can now add an import method that will call our UsersImport class.
with('success', 'All good!'); } }This will import the file from default disk. But we can also import from external location. Third parameter is used to give the location of file.
Excel::import(new UsersImport, 'users.xlsx', 's3');We can also import from file from uploaded files using the following syntax.
Excel::import(new UsersImport, request()->file('your_file'));Please don't forget to add routes which point to controller method for import and export functions.
This is all for exporting from database and importing to database using maatwebsite laravel-excel package. If you face any issues or have comments regarding this article, please don't forget to leave a comment.
Read More Articles